TopNsql and awr

[V Raman]

List,

I adjusted the topnsql so when I query the dba_hist_wr_control i see 50.
Hoping to capture more than the default top 10 SQLs. However, even after
the setting I see only 10 SQLs listed in the AWR reports, in SQLs by
elapsed time, by CPU, reads, etc. Am I missing something.

We definitely had more than 10 SQLs that run in the 2 half hour awr sample
periods.

venky.

["Reen, Elizabeth " (Redacted sender "elizabeth.reen" for DMARC)]

My suggestion is to tune the top 10 and then the next 10 will bubble up.


Liz

Elizabeth Reen
CPB Database Group Manager
718.248.9930 (Office)
Service Now Group: CPB-ORACLE-DB-SUPPORT


From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of V Raman
Sent: Monday, July 02, 2018 5:39 PM
To: oracle-***@freelists.org
Subject: TopNsql and awr

List,

I adjusted the topnsql so when I query the dba_hist_wr_control i see 50. Hoping to capture more than the default top 10 SQLs. However, even after the setting I see only 10 SQLs listed in the AWR reports, in SQLs by elapsed time, by CPU, reads, etc. Am I missing something.

We definitely had more than 10 SQLs that run in the 2 half hour awr sample periods.

venky.

[Harmandeep Singh]

Hi Venky,

In the Section of SQLs.. at end there is line which mentions "It captures
x% of sqls ". It seems in your case 10 sqls are capturing more than 90% so
Oracle will not show you the another 10 even if you set topnsql to 50

Hope it helps

Regards
Harman

Post by V Raman
List,
I adjusted the topnsql so when I query the dba_hist_wr_control i see 50.
Hoping to capture more than the default top 10 SQLs. However, even after
the setting I see only 10 SQLs listed in the AWR reports, in SQLs by
elapsed time, by CPU, reads, etc. Am I missing something.
We definitely had more than 10 SQLs that run in the 2 half hour awr sample
periods.
venky.