Find the query statement with the worst performance in the Oracle database

Finding the query statements "V $ SQLAREA" and "V $ SQL" with the worst performance in Oracle databases is a very useful view, you can find the SQL statements that require optimization in the worst execution efficiency. The value in the "DISK_READS" column indicates the number of disks read by executing this statement in the system. This is combined with execution (DISK_READS/EXECUTIONS) and returns the SQL statement that executes each statement to achieve the best disk hit rate. Any statement at the top of the list is most likely to be a problematic query and needs to be optimized. The AWR report or Statspack report also lists resource-intensive queries. The query statement under www.2cto.com can be used to find the worst-performing query in your database: select B. username,. disk_reads reads, a.exe cutions exec,. disk_reads/decode (a.exe cutions, 0, 1, a.exe cutions) rds_exec_ratio,. SQL _text Statement from V $ sqlarea a, dba_users B where. parsing_user_id = B. user_id and. disk_reads & gt; 100000 order by. disk_reads desc; username reads exec RDS_EXEC_RATIO STATEMENT --------------------------------- -- --------------------- Explain 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, 314159) = '000000' in the preceding statement www.2cto.com, the "DISK_READS" column can be replaced by the "BUFFER_GETS" column to find out the SQL statement that requires the maximum amount of memory. Now let's consider the output of the second example. This example calculates the number of rows in a table with billions of rows (EMP3, the number of rows in the table (EMP2) with the original 0.13 billion rows of data but only the first 15 rows of data are retained after all the data is deleted. Please note that Oracle has been counting based on the high level (HWM) of EMP2 (it will read more than 0.8 million blocks, 8 K blocks, even though only less than 1 block of data is left ). The following list shows you the error query for table EMP2. Since there are only 15 rows left in the table, the query needs to be adjusted (the analysis on the table will not improve this ). Www.2cto.com 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 clear the table (truncate) to solve this problem. However, because the table still has 15 rows of data, you have several solutions. The method you choose depends on your situation. You can: Export/clear/import at www.2cto.com; CREATE temporary TABLE emp2b (create table emp2b as select * FROM emp2 ); delete the table and rename the temporary table (I need to care about indexes and related objects ). Execute alter table emp2 move tablespace new1 to recreate the index. If a primary key exists, use "DBMS_REDEFINITION.CAN_REDEF_TABLE" to verify that the table can be redefined online. Check the Oracle document to understand the syntax, advantages, disadvantages, and conventions of each option (all listed here ), in this way, you can apply the best options for your actual situation (each of these options has some major drawbacks, including the inability of users to access tables and deleted objects, depending on which option you use, so be careful ). Once I reorganize the table, the next "count (*)" will only read one fast, not 800,065 blocks (this problem is worth processing ). Note that in this query, I changed the table "emp2" to "emP2", so that I can find the cursor in the cache. Alter table emp2 move; -- You can specify a tablespace select count (*) from emP2; select B. username,. disk_reads reads, a.exe cutions exec,. disk_reads/decode (a.exe cutions, 0, 1, a.exe cutions) rds_exec_ratio,. SQL _text Statement from V $ sqlarea a, dba_users B where. parsing_user_id = B. user_id and. SQL _text like '% emP2 %' order by. disk_reads desc; username reads exec RDS_EXEC_RATIO ST ATEMENT -------- ------- ----- --------------- ------------------- SCOTT 1 1 1 select count (*) from emP2 www.2cto.com you can also contract tables, index organization tables, indexes, partitions, subpartitions, materialized View or materialized view log space. You can use the statement "alter table, alter index, alter materialized view or alter materialized view log" to add the "shrink space" clause. Finally, if you want to use the "alter table name move tablespace name" command, consider using a TABLESPACE of the same size (or if possible, a smaller TABLESPACE) to MOVE data back and forth, this will not waste much space.