Ways to find Bad SQL

Ways to find Bad SQL

Find the running system bad SQL is an old topic, we should according to their own actual situation to analyze.

The methods described below must not be used in dogma.

When these SQL statements are used, the system tables are grouped and, of course, the load on the system is increased.

It is recommended that after the system has been activated for a period of time, in the middle of the night load lighter time (for example: one months) to check. Be sure to analyze specific problems.

Here are some of my favorite ways to find bad sql:

Column Sql_text format A80;
--questionable SQL comes fromhttp://www.ixora.com.au/
Select
substr (To_char (s.pct, ' 99.00 '), 2) | | '% ' load,
S.executions executes,
P.sql_text
From
(
Select
Address
Disk_reads,
Executions,
pct
Rank () Over (order by disk_reads DESC) ranking
From
(
Select
Address
Disk_reads,
Executions,
* Ratio_to_report (Disk_reads) over () pct
From
Sys.v_$sql
where
Command_type!= 47
)
where
Disk_reads > * Executions
) s,
Sys.v_$sqltext P
where
S.ranking <= 5 and
P.address = s.address
ORDER BY
1, s.address, p.piece
/

--Logical reading of more SQL
SELECT * FROM (select Buffer_gets, Sql_text
From V$sqlarea
where Buffer_gets > 500000
ORDER BY buffer_gets Desc) where rownum<=30;

--The SQL that executes more times
Select Sql_text,executions from
(select Sql_text,executions from v$sqlarea to executions desc)
where rownum<81;

--Read more SQL on the hard drive
Select Sql_text,disk_reads from
(select Sql_text,disk_reads from V$sqlarea to Disk_reads desc)
where rownum<21;

--Sort of multiple SQL
Select Sql_text,sorts from
(select Sql_text,sorts from V$sqlarea to sorts Desc)
where rownum<21;

--Too many times to parse, too few to execute, write SQL with the method of binding variables
Set pagesize 600;
Set Linesize 120;
Select substr (sql_text,1,80) "SQL", count (*), SUM (executions) "Totexecs"
From V$sqlarea
where executions < 5
Group by substr (sql_text,1,80)
Having count (*) > 30
Order by 2;

--Observation of cursors
Set pages 300;
Select SUM (a.value), b.name
From V$sesstat A, v$statname b
where a.statistic# = b.statistic#
and B.name = ' opened Cursors current '
Group BY B.name;

Select COUNT (0) from V$open_cursor;

Select User_name,sql_text,count (0) from V$open_cursor
GROUP BY User_name,sql_text has count (0) >30;

--View the SQL executed by the current user &username
Select Sql_text from V$sqltext_with_newlines where (hash_value,address) in
(select Sql_hash_value,sql_address from v$session where username= ' &username ')
Order BY Address,piece;