EXPERT RESPONSE
Since you are not specifying a Where clause, this query will perform a table scan of the entire table. Has the table grown a great deal lately? To find out what is happening you need to do a session trace of a session running this SQL.
- Open up SQL*Plus and connect to your database.
- Enable timed statistics if it not already enabled.
alter session set time_statistics=true;
- Turn on a level 8 SQL Trace of your session. A level 8 trace
will capture your wait events. The alter session set events
statement below is what enables the SQL Trace of your session.
alter session set tracefile_identifier='EMP_Select';
alter session set events '10046 trace name context forever, level 8';
- Run your Select and then exit SQL*Plus.
- On your database server, go to the $UDUMP directory and find your trace file. It will have 'EMP_Select' as part of the name.
- Using your trace file as input use the tkprof utility to format your trace file into a more readable format. If you invoke tkprof with no arguments, it will give you a help screen with the valid options to use.
Make sure you specify the EXPLAIN_PLAN and WAITS=YES options.
Using the output of the TKProf utility you should be able to identify the problem. You would need to especially review the Wait events and Explain plan output.
Hope this helps.
|
