select * from emp;Thank you.
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.
Dig Deeper on Oracle and SQL
Related Q&A from Phillip Bracken
I have always used TKPROF to do SQL tuning in previous versions of Oracle (7,8 and 9). Can I still use TKPROF in Oracle 10g R2? Continue Reading
A user complains that every time he tries to access a table (select only), it takes more than two hours to get the results. There are no DML ... Continue Reading
I have a table with 1.5 million records that needs to have a column updated based on a correlated subselect. This update is currently sitting at four... Continue Reading