Simple SQL query takes longer than usual

A simple SQL query is taking a longer time than it usually takes. How do we need to check to find out why it is taking so long?

A simple SQL query is taking a longer time than it usually takes. Where do we need to check why it is taking so long and what needs to be done? Let the simple query be:
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