How to determine poor performance SQL statements in Oracle

The former is easy to locate. All operating systems allow us to look at CPU-intensive tasks. These tasks can be traced back to a specific user, a specific application module. CPU-intensive modules are generally caused by poor code and/or structure, rather than poor performance SQL. Once you've identified the module, you have to try to make it more efficient. One possible solution is to take some processing removal programs and let the database process (smart point SQL, storage objects, inline functions, array processing, etc.).

The second is I/O-intensive SQL statements. These statements can result in a large number of database I/O (full table scans, sorting, updates, and so on) and run at a high cost for several hours. Starting with Oracle 7, the SQL identification problem was resolved. By querying the database shared pool area, we can easily identify most I/o-intensive SQL statements.

The following SQL statement demonstrates how to determine the SQL statement with an I/O hit rate less than 80%. The hit rate is that since the SQL statement was first parsed into a shared pool, the overall I/O was reflected through all executed statements. The following may be the result of the last few minutes or days:

The code is as follows
sql> SELECT Executions,

2 Disk_reads,

3 Buffer_gets,

4 ROUND ((buffer_gets-disk_reads)/buffer_gets, 2) Hit_ratio,

5 Sql_text

6 from V$sqlarea

7 WHERE Executions > 0

8 and buffer_gets > 0

9 and (buffer_gets-disk_reads)/Buffer_gets < 0.80

Desc by 4;

Executions Disk_reads buffer_gets Hit_ratio Sql_text

---------- ---------- ----------- ---------- ---------------------------------------------------------------------- -

180 369 SELECT Sku,prepack_ind,case_id,transfer_qty,unit_cost,unit_retail,rowid

From Tsf_detail WHERE transfer =: 1 ORDER BY SKU

SELECT transfer,to_store,to_wh from tsfhead WHERE TRANSFER =: B1 and

Transfer_status = ' A '

2 3 7 SELECT SKU from Upc_ean WHERE UPC =: B1

SELECT SUBSTR (desc_up,1,30), dept,system_ind from Desc_look WHERE

SKU =: B1

Unit_cost,unit_retail,subclass SELECT from Win_skus WHERE SKU =: B1

In fact, we find that the data on the specific SQL is misleading, but the statement is not a problem. Consider the following V$sqlarea output:

Executions Disk_reads buffer_gets Hit_ratio Sql_text

---------- ---------- ----------- --------- --------------------

2 6 0.68 SELECT a.emp_no, ...

This statement has a low hit rate, but in fact it works. Because SQL is a UNIQUE index operation, the number of physical disk reads is almost the same as logical reading. The UNIQUE index significantly reduces the overall physical and logical disk I/O number, resulting in a misleading low hit rate.

The following example, the hit rate is very good. But is it really good?

The code is as follows
Executions Disk_reads buffer_gets Hit_ratio Sql_text

---------- ---------- ----------- --------- --------------------

2 3625 178777 0.98 SELECT a.emp_no, ...

This SQL statement looks very effective. However, when we look closely, things are not the case. The hit rate did not reveal that there were five table connections, and that more than 3,600 physical disk reads were performed per execution. Is that too much? Is it valid? These two questions cannot be answered without further study. In fact, in this instance, one of the five tables incorrectly performs a full table scan. By re constructing SQL, we can reduce physical disk I/O to less than 50, while also significantly reducing logical disk I/O. Coincidentally, the hit rate dropped to less than 70%.

Our preferred V$sqlarea query is a true report of the physical disk I/O that each statement performs. The hit rate is informational, but sometimes misleading. Few logical I/O related. If the statement executes 1,000,000 logical I/O, but it takes less than one-tenth seconds, no one cares. This is total physical I/O, consumes almost all of the time, and identifies potentially incorrect SQL. For example:

The code is as follows
Sql> SELECT sql_text, executions,

ROUND (Disk_reads/executions, 2) Reads_per_run,

Disk_reads, Buffer_gets,

ROUND ((buffer_gets-disk_reads)

/Buffer_gets, 2) Hit_ratio,

Sql_text

From V$sqlarea

WHERE executions > 0

and buffer_gets > 0

and (buffer_gets-disk_reads)/Buffer_gets < 0.80

ORDER BY 3 Desc;

The first two statements will report more illuminating results:

The code is as follows
Executions Reads_per_run disk_reads buffer_gets hit_ratio sql_text

---------- ------------- ---------- ----------- --------- ------------

2 3 6 0.68 SELECT ...

2 1812.5 3625 178777 0.98 SELECT ...

From view V$sqlarea, we can immediately isolate all statements that have high physical reads. These statements may not necessarily be inefficient or poorly written, but they need to be investigated or adjusted further.