SQL Tuning Tips-Identify Bad SQL Statements
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 With Disk Reads > 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 “Response”
AND executions > 0
ORDER BY hash_value, child_number;
Once you’ve identified poorly performing SQL statements, you can work to tune them.