DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: statistics

  1. #1
    Join Date
    Jan 2001
    Hi all ,

    Any solution if I have to spool out information of what are the tables being accessed and in what way (either full or index scan etc..) whenever an .exe of an application is fired from a user. please help me on this

  2. #2
    Join Date
    Feb 2001

    I would suggest that on a day when the workload is normal, set sql_trace to true in init.ora file.This would generate lots of trace files in your user_dump_dest directory.This is one way.

    The other way is, invoke your .exe application,identify the sid of the session, and use dbms_system.set_sqltrace_in_session (I am not sure with the spelling,pls.refer documentation), procedure to invoke the trace for this particular sid.This will generate less no. of trace files than the first method.

    Once the trace files are obtained, you can use the tkprof utility with an option to generate the explain plans and store the output in a system defined/user defined table.From this table,you can select the appropriate columns,identify the tables and other info what you wanted.(You may refer tkprof help on this).


  3. #3
    Join Date
    Jan 2001
    Thanks ramki
    I will give it a try and get back to you. Thanks a lot for spending your time.


  4. #4
    Join Date
    Mar 2001

    Ratio is a good overall indicator

    The following query does not zero in on a particular session, but it will give you a good idea of the general ratio of small table scans to to full table scans. Generally, I look for a ratio less than 75 otherwise you may have untuned sql out there.

    SELECT sc.value "short tbl.", ls.value "LONG tab. scans",
    round(sc.value/(sc.value+ls.value)*100,0) "ratio"
    FROM v$sysstat ls, v$sysstat sc
    WHERE sc.name='table scans (short tables)'
    AND ls.name='table scans (long tables)';

  5. #5
    Join Date
    Nov 1999
    My ratio is coming 94

    What do I need to do , Create indexes on ones wit full scans .
    Please advise

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.