Finding the worst Performance query statement in an Oracle database

"V$sqlarea" and "v$sql" are very useful views in which you can find the SQL statements that you find to be optimized for the least efficient execution. The value of the "disk_reads" column represents the amount of disk read by the statement in the system.

This, combined with execution (disk_reads/executions), returns the SQL statement that each statement performs to achieve the optimal disk hit ratio. Any statements at the top of this list are most likely to be problematic queries that need to be optimized. AWR reports or Statspack reports also list resource-intensive queries.

The following query statement can be used to find the worst performing queries in your database:

Select B.username Username, a.disk_reads reads,  
a.executions exec, A.disk_reads/decode  
(a.executions, 0, 1, a.executions) Rds_exec_ratio,  
a.sql_text Statement from  
V$sqlarea A, dba_users b  
where a.parsing_user_id = b.user_id and  
a.disk_reads > 100000 order by  
A.disk_reads desc;  
USERNAME reads EXEC rds_exec_ratio STATEMENT  
--------------------------------------------------------  
--- ------------------------------------------------  
ADHOC1 7281934 1 7281934 Select Custno, Ordno  
from Cust,  Orders  
ADHOC5 4230044 4 1057511 Select Ordno from  
orders where trunc (ordno) = 721305  
ADHOC1 801716 2 400858 Select Custno,  
ordno from Cust where substr (custno,1,6) = ' 314159 '

The "disk_reads" column in the preceding statement can be replaced by the "buffer_gets" column to find out about the SQL statement that requires the maximum amount of memory.

Now consider the output of the second example, which is a row count for a 1 billion-row table (EMP3), as well as a row count of 130 million rows of data, but only the first 15 data (EMP2) are left after all data has been deleted. Note that Oracle has been counting the EMP2 high water level (HWM) (it reads more than 800,000 blocks, 8k blocks, although all data is less than 1 blocks in size). The following list shows you the error query for the table EMP2, and now that the table has only 15 rows left, the query needs to be adjusted (analysis of the table will not improve this).

USERNAME reads EXEC rds_exec_ratio STATEMENT  
------------------------------------------------------------  
Scott 5875532 1 5875532 Select COUNT (*) from Emp3  
Scott 800065 1 800065 Select COUNT (*) from EMP2

For this problem, if the EMP2 table is completely empty, you can simply empty the table (truncate) to solve the problem. However, because the table still has 15 rows of data, you have several processing options. The choice of which method depends on your specific situation. You can:

Export/purge/import; Create temporary table emp2b (CREATE TABLE emp2b as SELECT * from EMP2); then delete the table and rename the temporary table (I need to care about indexes and related objects, and so on).

Executes the ALTER TABLE emp2 move tablespace new1 rebuild index.

If you have a primary key, use the dbms_redefinition. Can_redef_table Verify that the table can be redefined online.

Please check the Oracle documentation for the syntax of each option, pros, cons, and usage conventions (here for all), so you can apply the best options for your situation (each of these options has some major drawbacks, including the inability of users to access the table and deleted related objects, depending on which option you use, So be careful. Once I reorganize the table, the next "count (*)" reads only one quick, not 800,065 blocks (the problem is well worth dealing with). Note that in this query, I changed the table "EMP2" to "emP2" so that I could find the cursor in the cache.

ALTER TABLE EMP2 move; --can specify a tablespace  
select COUNT (*) from  
emP2;  
Select B.username Username, a.disk_reads reads,  
a.executions exec, A.disk_reads/decode  
(a.executions, 0, 1, a.executions) Rds_exec_ratio,  
a.sql_text Statement from  
V$sqlarea A, dba_users b  
where a.parsing_user_id = b.user_id and  
a.sql_text like '%emp2% ' ORDER by
a.disk_reads desc;  
USERNAME reads EXEC rds_exec_ratio STATEMENT  
--------------------------------------------------------  
SCOTT 1 1 1 Select COUNT (*) from emP2

You can also shrink the space for tables, index organization tables, indexes, partitions, sub partitions, materialized views, or materialized view logs. You can use the "Alter Table,alter index,alter materialized view or alter materialized view LOG" statement plus the "SHRINK space" clause to achieve this. Please see the Oracle Administrator's Guide for more information. Finally, if you want to use the "ALTER table name move tablespace table space name" command, consider using a table space of the same size (or, if you can, use a smaller table space) for moving data back and forth without wasting space.