SQL Tuning

SQL Tuning
===========

Top SQLs in Oracle Database session
Finding Top rated SQL’s is the key process while working on Database performance. Sure we can find these SQL’s using AWR report but still we should know which view to access to find Top rated SQL’s. PFB SQL’s:-

1. TOP 10 SQL STATEMENT WITH LARGE NO. OF DISK READS :-

set lin 400
column “SQL_TEXT” format a100
column EXECUTIONS format 9999999
column DISK_READS format 999999999
column USER FORMAT a10
select substr(sql_text,0,100) as “SQL_TEXT”,PARSING_SCHEMA_NAME as “USER”,executions ,disk_reads from v$sqlarea where decode(executions ,0,disk_reads,disk_reads/executions)
> (select avg(decode(executions,0,disk_reads,disk_reads/executions)) + stddev(decode(executions,0,disk_reads,disk_reads/executions))
from v$sqlarea) and ROWNUM < 11 order by disk_reads desc / 2. TOP 10 SQL STATEMENT WITH FULL TABLE SCANS :- column "SQL_TEXT" format a100 column OPERATION format a12 column OPTIONS format a12 column USER FORMAT a10 select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",p.operation,p.options from v$sqlarea t, v$sql_plan p where t.hash_value=p.hash_value and p.operation='TABLE ACCESS' and p.options='FULL' and p.object_owner not in ('SYS','SYSTEM') and ROWNUM < 11 order by DISK_READS DESC, EXECUTIONS DESC / 3. TOP 10 SQL STATEMENT WITH MOST CPU UTILIZATION :- column "SQL_TEXT" format a100 column EXECUTIONS format 9999999 column CPU_TIME format 999999999 column USER FORMAT a10 select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS,t.CPU_TIME from v$sqlarea t where ROWNUM < 11 order by CPU_TIME DESC,EXECUTIONS DESC / 4. TOP 10 SQL STATEMENT WITH MOST BUFFER GETS :- column SQL_TEXT format a100 column EXECUTIONS format 9999999 column BUFFER_GETS format 999999999 column USER FORMAT a10 select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS,t.BUFFER_GETS from v$sqlarea t where ROWNUM < 11 order by BUFFER_GETS DESC,EXECUTIONS DESC / 5. TOP 10 SQL STATEMENT WITH MOST NO. OF EXECUTIONS :- column SQL_TEXT format a100 column EXECUTIONS format 9999999 column USER FORMAT a10 select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS from v$sqlarea t where ROWNUM < 11 order by EXECUTIONS DESC / 6. TOP 10 SQL STATEMENT WITH MOST NO. OF SORTS :- column SQL_TEXT format a100 column EXECUTIONS format 9999999 column SORTS format 99999999 column USER FORMAT a10 select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS,t.SORTS from v$sqlarea t where ROWNUM < 11 order by SORTS DESC,EXECUTIONS DESC / 7. TOP 10 SQL STATEMENT WITH MOST SHARABLE MEMORY :- column SQL_TEXT format a100 column EXECUTIONS format 9999999 column SHARABLE_MEM format 99999999 column USER FORMAT a10 select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS,t.SHARABLE_MEM from v$sqlarea t where ROWNUM < 11 order by SHARABLE_MEM DESC,EXECUTIONS DESC / 8. TOP 10 SQL STATEMENT WITH MOST NO. of PARSE CALLS :- column SQL_TEXT format a100 column EXECUTIONS format 9999999 column PARSE_CALLS format 99999999 column USER FORMAT a10 select substr(t.SQL_TEXT,0,100) as "SQL_TEXT",t.PARSING_SCHEMA_NAME as "USER",t.EXECUTIONS,t.PARSE_CALLS from v$sqlarea t where ROWNUM < 11 order by PARSE_CALLS DESC,EXECUTIONS DESC /


Betting site http://cbetting.co.uk more info for Coral Offers

Support Us?

Our only Source of Income via clicking Ads.Click our Web Ads to help our Community!




Like us in Facebook!

Love Our Articles?

Follow us in Facebook to find our Latest Stuff!

Powered by WordPress Popup