Database Reference
In-Depth Information
FROM ash_gc a, cdb_objects o
WHERE (a.current_obj#=o.object_id (+))
AND a.current_obj# >=1
AND a.con_id =o.con_id (+)
UNION
SELECT inst_id,0, '', '', 'Undo Header/Undo block', cnt
FROM ash_gc a WHERE a.current_obj#=0
UNION
SELECT inst_id,0, '', '', 'Undo Block', cnt
FROM ash_gc a
WHERE a.current_obj#=-1
)
ORDER BY cnt DESC
/
Inst Cont OWNER OBJECT_NAME OBJECT_TYPE CNT
---- ---- ---- ------------------------------ --------------- ---
2 3 RS HUGETABLE TABLE 21
2 3 RS HUGETABLE_HASH TABLE PARTITION 9
...
Identify SQL Statement
ASH data can also be queried to identify the SQL statement associated with events. Listing 10-5 shows the script
ash_gcwait_to_sql_id.sql to identify the SQL statement associated with the wait event. The output in Listing 10-5
shows that some SQL statements were inducing waits for these events. Further review of these SQL statements, access
plans, and performance metrics would be required to diagnose the performance problem.
Listing 10-5.
Script: ash_gcwait_to_sql_id.sql
WITH ash_gc AS
(SELECT /*+ materialize */ inst_id, event, sql_id, COUNT(*) cnt
FROM gv$active_session_history
WHERE event=lower('&event')
GROUP BY inst_id, event, sql_id
HAVING COUNT (*) > &threshold )
SELECT inst_id, sql_id, cnt FROM ash_gc
ORDER BY cnt DESC
/
Enter value for event: gc current block 2-way
Enter value for threshold: 100
INST_ID SQL_ID CNT
---------- ------------- ----------
3 26shktr5f1bqk 2717
3 4rfpqz63y34rk 2332
2 4rfpqz63y34rk 2294
...
With the PDB feature, the script shown in Listing 10-6 must also be modified to retrieve container_id of the PDB.
Column con_id can be used to identify PDB executing costly SQL statements.
