Capture problematic SQL statements in AWR reports using Shell scripts

The SQL details section in the awr report is basically mandatory, especially the SQLOrderbyElapsedtime section, which clearly shows which SQL statements occupy a large number of databases

The SQL details section in the awr report is basically mandatory, especially the SQL Order by Elapsed time section, which clearly shows which SQL statements occupy a large number of databases

The SQL details section in the awr report is basically mandatory, especially the SQL Order by Elapsed time section, which clearly shows which SQL statements occupy a large amount of DB time, proportion. This can serve as an important reference for tuning, and can be used to find out which sqls require special attention.

For example, we get an awr report. The content of Elapsed time is as follows. It can be seen that SQL _id is dfb15m5s2uwmc SQL requires special attention, because it occupies nearly half of the DB time.

Elapsed Time (s) Executionsper Exec (s) % Total % CPU % IOSQL IdSQL ModuleSQL Text

3,601.900 48.5299.770.16dfb15m5s2uwmcJDBC Thin ClientSELECT: 1, machinecode, cn, co...

1, 612.042176.7621.7199.970.008tmf11fvxy09jJDBC Thin ClientSELECT ROUND (AVG (SUM (END_TIME...

1, 593.802079.6921.4799.970.00cy55p6nrd31dbJDBC Thin ClientSELECT MAX (USER_CLASS) from s...

298.342014.924385299.960.0029tdwfv5d9s4fjdbc Thin ClientSELECT NVL (SUM (OTAL ),...

Awr provides a complete set of functions. If we can quickly find out which SQL statements occupy a large amount of DB time rather than generating an awr report each time, we can actually implement this, we can customize it.

Some logs are as follows:
.. Exported "SYS". "WRH $ _ SQL _PLAN" 432.1 KB 1089 rows
.. Exported "SYS". "WRH $ _ LATCH": "WRH $ _ latch_361_37571_0" 198.6 KB 3871 rows
.. Exported "SYS". "WRH $ _ SYSMETRIC_HISTORY" 180.1 KB 3600 rows

We can see that the base table of awr starts with wrh $, which is closely related to our commonly used data dictionary tables.
For example, sqlstat, wrh $ can be changed to dba_hist for historical data in the data dictionary.
We can view the base table of dba_hist_sqlstat and find that it is a base table like wrh $.
$ Ksh showdict. sh DBA_HIST_SQLSTAT
Object_details
OWNER OBJECT_ID DATA_OBJECT_ID OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------------------------------
SYS 9409 DBA_HIST_SQLSTAT VIEW
PUBLIC 9410 DBA_HIST_SQLSTAT SYNONYM
Synonym_details
OWNER SYNONYM_NAME
------------------------------------------------------------
PUBLIC DBA_HIST_SQLSTAT

View_details
VIEW_NAME TEXT
--------------------------------------------------------------------------------------------------------------
DBA_HIST_SQLSTAT select SQL. snap_id, SQL. dbid, SQL. instance_number,
Xxxx from WRM $ _ SNAPSHOT sn, WRH $ _ SQLSTAT SQL
Where sn. snap_id = SQL. snap_id
And sn. dbid = SQL. dbid
And sn. instance_number = SQL. instance_number
And sn. status = 0

Then we can view the required information directly from these data dictionary history tables without generating an awr report every time.
Of course, the implementation process also takes some twists and turns into a shell version by slightly processing the script.
Sqlplus-s $ DB_CONN_STR @ $ SH_DB_SID < Break on db_name
Set pages 50
Set linesize 100
Col elapsed_time format a10
Col per_total format a10
Prompt
Prompt Current Instance
Prompt ~~~~~~~~~~~~~~~~
Select d. dbid
, D. name db_name
, I. instance_number inst_num
, I. instance_name inst_name
From v \ $ database d,
V \ $ instance I;
Select snap_id, SQL _id, EXECUTIONS_DELTA, max_elapsed elapsed_time, per_total | '%' per_total from
(Select distinct snap_id, SQL _id, EXECUTIONS_DELTA, trunc (max (ELAPSED_TIME_DELTA) OVER (partition by snap_id, SQL _id)/limit) |'s 'max_elapsed,
Trunc (max (ELAPSED_TIME_DELTA) OVER (partition by snap_id, SQL _id)/(SUM (ELAPSED_TIME_DELTA) OVER (PARTITION BY snap_id), 2) * 100 per_total
From dba_hist_sqlstat where snap_id = $1
Order by 5 desc
) Where rownum <= 5;