SQL*PLUS Timing

The other week I had to check the time taken to execute an SQL statement for a test scenario and confirm whether the SQL plan was taking longer than is should. SQL* PLUS has a handy little inbuilt timing feature.

NOTE the feature doesn’t measure CPU time it will be measured as some have referenced before me as an off the wall-clock time (not very accurate) as factors such as load and affect the elapsed time or a loop in the script being executed for the plan.

To turn on the timing function.

SET TIMING ON

After the timer has been started you can enter a query and the executed time will be shown as below.

e.g.

SELECT VIEW_NAME

FROM DBA_VIEWS

WHERE VIEW_NAME = “TABLE_C “;

Output example below

VIEW_NAME

————————————————————–

TABLE_C

Elapsed: 00:00:01.06

To turn off the utility

SET TIMING OFF

© 2010 TestingFocus

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s