How to Use awr_set_report_thresholds to control the number of SQL statements in the AWR report

How to Use awr_set_report_thresholds to control the number of SQL statements in the AWR report

The sections related to SQL statements in the AWR report include SQL ordered by Elapsed Time, SQL ordered by CPU Time, SQL ordered by User I/O Wait Time, and SQL ordered by Gets. To view more SQL statements in the preceding section when analyzing a database with high load, we use DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (topnsql => 50) adjust the maximum number of SQL statements captured by AWR to 50. You can check the Adjusted Results from dba_hist_wr_control:
Col SNAP_INTERVAL format a30
Col RETENTION format a30
Set linesize 150
Select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
--------------------------------------------------------------------------------
617151977 + 00000 01:00:00. 0 + 00035 00:00:00. 0 50

However, in the generated AWR Report, the number of SQL statements in sections such as "SQL ordered by Elapsed Time" and "SQL ordered by CPU Time" is still 10, not changed to 50. DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS can be used to control the number of SQL statements in the AWR report. The usage is as follows:
DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS (
Top_n_events in number default null,
Top_n_files in number default null,
Top_n_segments in number default null,
Top_n_services in number default null,
Top_n_ SQL IN NUMBER DEFAULT NULL,
Top_n_ SQL _max IN NUMBER DEFAULT NULL,
Top_ SQL _pct IN NUMBER DEFAULT NULL,
Shmem_threshold in number default null,
Versions_threshold in number default null );

The parameters related to the number of SQL statements are top_n_ SQL, top_n_ SQL _max, and top_ SQL _pct. If we want to include 50 statements in the generated AWR report, we can first execute
Exec DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS (top_n_ SQL => 50), and then use @? /Rdbms/admin/awrrpt generates a report so that the top 50 SQL statements are displayed in the Report. Remember that the execution result of DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS takes effect only at the session level.
Yes @? The execution process of/rdbms/admin/awrrpt is 10046 trace, and an SQL statement that generates "SQL ordered by Elapsed Time" is extracted from the trace result:

With sqt as (select elap, cput, exec, iowt, norm_val, SQL _id, module, rnum from (select SQL _id, module, elap, norm_val, cput, exec, iowt, rownum rnum from (select SQL _id, max (module) module, sum (elapsed_time_delta) elap, (100 * (sum (elapsed_time_delta)/nullif (: dbtime, 0) norm_val, sum (cpu_time_delta) cput, sum (executions_delta) exec, sum (iowait_delta) iowt from dba_hist_sqlstat where dbid =: dbid and instance_number =: inst_num and: bid <snap_id and snap_id <=: eid group by SQL _id order by nvl (sum (elapsed_time_delta),-1) desc, SQL _id) where rnum <: tsql_max and (rnum <=: tsql_min or norm_val>: top_pct_ SQL )) select/* + NO_MERGE (sqt) */nvl (sqt. elap/ipv00133, to_number(null1_limit, sqt.exe c,
Decode(sqt.exe c, 0, to_number (null), (sqt. elap/sqt.exe c/1000000), sqt. norm_val, decode (sqt. elap, 0, to_number (null), (100 * (sqt. cput/sqt. elap), decode (sqt. elap, 0, to_number (null), (100 * (sqt. iowt/sqt. elap), sqt. SQL _id, to_clob (decode (sqt. module, null, null, 'module: '| sqt. module), nvl (st. SQL _text, to_clob ('** SQL Text Not Available **') from sqt, dba_hist_sqltext st where st. SQL _id (+) = sqt. SQL _id and st. dbid (+) =: dbid order by sqt. rnum

It can be seen that there is a section where rnum <: tsql_max and (rnum <=: tsql_min or norm_val>: top_pct_ SQL) which filters AWR_SET_REPORT_THRESHOLDS results, dba_hist_sqlstat and dba_hist_sqltext are the main sources of SQL statistics. Explain the usage of the three parameters top_n_ SQL, top_n_ SQL _max, and top_ SQL _pct in AWR_SET_REPORT_THRESHOLDS. Abstract: top_ SQL _pct indicates the percentage of resources consumed by SQL statements corresponding to a SQL _id to the total database resources;
Specifically,
For SQL statements listed in SQL ordered by elapsed time, top_ SQL _pct indicates the percentage of time consumed by each SQL statement to db time: top_ SQL _pct_for_elapsed_time % = (SQL _elapse_time/db_time) * 100%;
For SQL statements listed in SQL ordered by cpu time, top_ SQL _pct indicates the percentage of cpu time consumed by each SQL statement to the db cpu: top_ SQL _pct_for_cpu_time % = (SQL _cpu_time/db_cpu) * 100%;
Similarly, % Total is used in AWR to represent this percentage value.

Top_n_ SQL and top_n_ SQL _max indicate the number of SQL statements returned by Elapsed Time, CPU Time, Gets, and other indicators in descending order, use top_ SQL _pct to explain the functions of top_n_ SQL and top_n_ SQL _max in the following scenarios (taking SQL ordered by elapsed time as an example)

(1) If top_n_ SQL = top_n_ SQL _max = N
Returns the N-1 SQL with the longest elapsed time, And the top_ SQL _pct value is ignored

(2) If top_n_ SQL = N, top_n_ SQL _max = M, and meet N + 1 = M
Returns the N SQL statements with the longest elapsed time. The top_ SQL _pct value is ignored.

(3) If top_n_ SQL = N, top_n_ SQL _max = M, and meet N + 1 <M
The returned SQL statement is
The longest N SQL statements of elapsed time
+
Select the (M-N) SQL statement with the minimum elasped time from the M SQL statement with the longest elapsed time and further filter out the N1 SQL statement with the % total> top_pct_ SQL value, here M-N => N1> = 0
In this case, the number of returned SQL statements is N + N1.

(4) If top_n_ SQL = N, top_n_ SQL _max = M, and N> M
Returns the longest M-1 SQL statement of elapsed time, And the top_pct_ SQL value is ignored

(5) If top_n_ SQL = N and top_n_ SQL _max are empty;
Returns N records with the longest elapsed time, And the top_pct_ SQL value is ignored.

(6) If top_n_ SQL _max = N, top_n_ SQL is empty;
Returns the longest 10 records of elapsed time.

From the above 3rd cases, the SQL statements returned by AWR can be used by the specified topn and total % values as the filtering methods.

Now, we know how to use AWR_SET_REPORT_THRESHOLDS to specify the number of SQL statements listed in the AWR report.
In fact, the number of SQL statements in the AWR report depends on two factors.
1. How many sqls are flushed from memory to awr by the mmon Process
2. How many SQL statements are filtered out from AWR by AWR_SET_REPORT_THRESHOLDS and generated in the AWR report.

In contrast, the former is the basis. We know the value of the statistics_level parameter from the knowledge we have gained. The value of topnsql in DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS determines the number of SQL statements from memory Flush to AWR:
This number is 30 when statistics_level = typical
When statistics_level = ALL, this number is 100.
Topnsql has the highest priority. You can specify the number of SQL statements directly, and overwrite the statistics_level function.

However, in 11gR2, the actual situation is quite different from the above viewpoint. The above three constraints do not seem to work. The test result is that no matter whether statistics_level = typical or statistics_level = all (as long as it is not Basic), mmon will flush as many SQL statements as possible into AWR regardless of the value of topnsql, I verified it through the following methods. If you are interested, please try it. I will not go into details about the verification process here.

-- When statistics_level = typical, the following result is> 30; When statistics_level = ALL, the following result is> 100; When topnsql = N, the following result is> N
Select count (distinct (SQL _id) from dba_hist_sqlstat where snap_id <: end_snap_id and snap_id >=: begin_snap_id; -- The end_snap_id and begin_snap_id represent two consecutive snapids, with no gap in the middle

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Generate and view Oracle AWR reports

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Steps for generating an Oracle AWR report