How to Interpret TOP SQL

[ssekhar]

Hi,

I'm using this query in our Prodn database to find the Most Resource Intensive SQL(TOP SQL).

SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 2 DESC;

Following is the Output of above query -

(select /*+ALL_ROWS */ s . final_cust , g . locid,
1224149,
1169483,
6120749,
5,
21,
238FA568

select /*+ ALL_ROWS*/ s . final_cust , g . locid ,
4687,
369533,
4687,
1,
3,
237EE8E8

But I'm unable to interpret the output. Can some advice how to understand this data...


Thanks.

[Dilippatel]

Hello Soma,

From Qsoft ?

You can use Enterprise manager tool, and can use top session utility, where u will find good details, and sorting options.

Dilip, ATUL LTD.

[ssekhar]

Hi Moderator,

Basically I want to know what exactly these values mean and is there any bench mark to say the resources consumed is high, moderate or low etc.,

Also on what basis we infer that some queries require fine tuning and some not required etc.,

Any advice..


Regards