| |
Awr report sql detail some of the basic part of the must-see, especially SQL Order by Elapsed time this section, can clearly see what sql statement taking up more DB time, the proportion. This can serve as an important reference when tuning, which can be targeted to run sql need particular attention.
For instance, we got a awr report, Elapsed time the contents of this section are as follows. As is apparent sql_id as dfb15m5s2uwmc of sql need special attention, because it takes up nearly half of DB time.
Elapsed Time (s) Executions per Exec (s)% Total% CPU% IO SQL Id SQL Module SQL Text
3,601.90 0 48.52 99.77 0.16 dfb15m5s2uwmc JDBC Thin Client SELECT: 1, machinecode, cn, co ...
1,612.04 21 76.76 21.71 99.97 0.00 8tmf11fvxy09j JDBC Thin Client SELECT ROUND (AVG (SUM (END_TIME ...
1,593.80 20 79.69 21.47 99.97 0.00 cy55p6nrd31db JDBC Thin Client SELECT MAX (USER_CLASS) FROM S ...
298.34 20 14.92 4.02 99.96 0.00 29tdwfv5d9s4f JDBC Thin Client SELECT NVL (SUM (OTAL), ...
Awr feature set provided is complete, if we are able to more quickly locate out which sql taking up more DB time to go and do not always generate a awr report, in fact, can be achieved, we can customize.
Part log is as follows:
.. Exported "SYS". "WRH $ _SQL_PLAN" 432.1 KB 1089 rows
.. Exported "SYS" "WRH $ _LATCH":. "WRH $ _LATCH_3645037571_0" 198.6 KB 3871 rows
.. Exported "SYS". "WRH $ _SYSMETRIC_HISTORY" 180.1 KB 3600 rows
You can see awr base table is wrh $ beginning, and we used the data dictionary tables are closely related.
For example sqlstat, data dictionary of historical data can be put wrh $ transposition dba_hist.
Our view dba_hist_sqlstat base table, in fact, such a finding is wrh $ base table.
$ 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
So we can see directly from the data dictionary table history information they need to go and do not always regenerate a awr report.
Of course, the process of implementation also slightly With some difficulty, the script a little processing, it becomes a shell version.
sqlplus -s $ DB_CONN_STR @ $ SH_DB_SID < < EOF
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 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) / 1000000,0) || '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;
Content output results are as follows, and html format compared, it is still very clear, only need to enter the number to the end of the snapshot.
$ Ksh showsnapsql.sh 57584
Current Instance
~~~~~~~~~~~~~~~~
DBID DB_NAME INST_NUM INST_NAME
---------- --------- ---------- ----------------
2534640677 TESTDB2 1 xxxxx
SNAP_ID SQL_ID EXECUTIONS_DELTA ELAPSED_TI PER_TOTAL
---------- ------------- ---------------- ------------ ---------
57494 dfb15m5s2uwmc 0 3601s 48%
57494 8tmf11fvxy09j 21 1612s 21%
57494 cy55p6nrd31db 20 1593s 21%
57494 29tdwfv5d9s4f 20 298s 4%
57494 c7k4g2urpu1sc 0 175s 2%
This time you can easily grab the problem sql, directly to determine whether more information is needed. |
|
|
|