How to determine SQL statements with poor performance in oracle

In oracle, how does one determine SQL statements with poor performance? When SQL statements are involved, there are two aspects of poor performance: CPU-intensive statements (CPU-intensivestatements) and IO-intensive statements (IO-intensivestatements ).

How to determine SQL statements with poor performance in oracle? When SQL statements are involved, the performance is poor in two ways: CPU-intensive statements (CPU-intensive statements) and I/O-intensive statements (I/O-intensive statements ).

How to determine SQL statements with poor performance in oracle? When SQL statements are involved, the performance is poor in two ways: CPU-intensive statements (CPU-intensive statements) and I/O-intensive statements (I/O-intensive statements ).

The former is easy to locate. All operating systems allow us to view 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 SQL statements with poor performance. Once the module is determined, you must try to make it more efficient. One possible solution is to remove some processing programs and let the database process (clever SQL, storage objects, inline functions, array processing, etc ).

The second is an I/O-intensive SQL statement. These statements cause a large number of database I/O (full table scan, sorting, update, etc.) and run for several hours at a high cost. SQL recognition has been solved since Oracle 7. By querying the database sharing pool area, we can easily identify most I/O-intensive SQL statements.

The following SQL statement demonstrates how to determine an SQL statement with an I/O hit rate lower than 80%. This hit rate is that since the SQL statement is parsed to the shared pool for the first time, the overall I/O is reflected through all the executed statements. The following may be the results 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 10 order by 4 desc; EXECUTIONS DISK_READS BUFFER_GETS HIT_RATIO SQL _TEXT ---------------------------------------------------------------------------------------------------------------- 16 180 369. 51 select sku, PREPACK_IND, CASE_ID, TRANSFER_QTY, UNIT_COST, UNIT_RETAIL, ROWID FROM TSF_DETAIL WHERE transfer =: 1 order by sku 16 30 63. 52 select transfer, TO_STORE, TO_WH from tsfhead where transfer =: b1 AND TRANSFER_STATUS = 'A' 2 3 7. 57 select sku from UPC_EAN where upc =: b1 12 14 35. 60 select substr (DESC_UP, 1, 30), DEPT, SYSTEM_IND FROM DESC_LOOK WHERE SKU =: b1 14 13 35. 63 SELECT UNIT_COST, UNIT_RETAIL, subclass from WIN_SKUS where sku =: b1 In fact, we found that the preceding data is somewhat misleading for specific SQL statements. In fact, the statements are correct. Consider the following v $ sqlarea output: Executions Disk_Reads Buffer_Gets Hit_Ratio SQL _Text ------------------------------------------------------------ 2 6 19 0.68 select a. EMP_NO ,...

The hit rate of this statement is very low, but in fact it is very effective. Because SQL is operated through the UNIQUE index, the number of physical disk reads is almost the same as that of logical reads. The UNIQUE index significantly reduces the total number of physical and logical disk I/O, leading to a misleading low hit rate.

In the following example, the hit rate is 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 at it carefully, this is not the case. The hit rate is not exposed. The statement has five table connections and more than 3600 physical disks are read each time. Is this too much? Valid? These two questions cannot be answered without further research. In fact, one of the five tables in this instance mistakenly performs a full table scan. By re-constructing SQL statements, we can reduce the number of physical disk I/O to less than 50, while also significantly reducing the Logical Disk I/O. Coincidentally, the hit rate also drops to less than 70%.

The V $ SQLAREA query is the real Report of the physical disk I/O executed by each statement. The hit rate is informative, but sometimes misleading. Logical I/O is rarely related. If the statement executes 1,000,000 logic I/O, but it takes less than seconds, no one cares. This is the total physical I/O, which consumes almost all the time and identifies potentially incorrect SQL statements. 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 enlightening results:

The Code is as follows:
Executions Reads_Per_Run Disk_Reads Buffer_Gets Hit_Ratio SQL _Text ----------------------------------------------------------------- 2 3 6 19 0.68 SELECT... 2 1812.5 3625 178777 0.98 SELECT...

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