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 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
I have a critical performance issue due to the large volume of data for a specific customer. Whenever customer XYZ's data is being fetched the query ... Continue Reading
I increased the RAM from 2GB to 3GB, but when I try to increase the sga_max_size I get the following error on startup: "ORA-27102: out of memory." Continue Reading