O'Reilly logo

Oracle SQL Tuning Pocket Reference by Mark Gurry

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Handy SQL Tuning Tips

The following sections list some SQL tuning tips that you may find useful both when writing SQL statements and when troubleshooting performance problems.

Identify Bad SQL

The SQL statements in this section demonstrate how to identify SQL statements that have an expected response time of more than 10 seconds. The assumption has been made that 300 disk I/Os can be performed per second, and that 4,000 buffer gets can be performed per second. These times are typical of a medium- to high-end machine.

Use the following SQL*Plus commands to identify statements using, on average, more than 3,000 disk reads (10 seconds' worth) per execution:

column "Response" format 999,999,999.99;
column nl newline;

ttitle 'SQL With Disk Reads > 10 Seconds'

SELECT sql_text nl, 'Executions='|| 
            executions  nl,
  'Expected Response Time in Seconds= ', 
   disk_reads / decode(executions, 0, 1, 
                    executions) / 300   
              "Response"  
  FROM v$sql
WHERE  disk_reads / decode(executions,0,1, executions) 
                   / 300 > 10
  AND executions > 0
ORDER BY hash_value, child_number;

Similarly, the following SQL*Plus commands identify statements that result in more than 40,000 buffer gets:

column "Response" format 999,999,999.99 ttitle 'SQL Buffer Scan > 10 Seconds' SELECT sql_text nl, 'Executions='|| executions nl, 'Expected Response Time in Seconds= ', buffer_gets / decode(executions, 0, 1, executions) / 4000 "Response" FROM v$sql WHERE buffer_gets / decode(executions, 0,1, executions) / 4000 > 10 AND executions > 0 ORDER BY hash_value, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required