Database Reference
In-Depth Information
FROM ash_gc a
WHERE a.current_obj#=-1
)
ORDER BY cnt DESC
/
Enter value for event: gc current block 2-way
Enter value for threshold: 30
INST_ID OWNER OBJECT_NAME OBJECT_TYPE CNT
------- -------------------- -------------------------------- ------------- ----------
3 PO RCV_SHIPMENT_LINES TABLE 2228
2 PO RCV_SHIPMENT_LINES TABLE 2199
1 PO RCV_SHIPMENT_LINES TABLE 2197
3 PO PO_REQUISITION_LINES_ALL TABLE 2061
2 PO PO_REQUISITION_LINES_ALL TABLE 1985
3 Undo Block 120
...
The output of Listing 10-3 shows the tables suffering from waits for the
gc current block 2-way
wait event. You
can supply any event name to identify the objects suffering from that event. If the object_id is 0 or -1, then it indicates
undo block or undo segment header block. If the object was dropped, then the object_name will be marked as
non-existent
.
Also, to improve the performance of this query, set a higher threshold limit such as 100 or 1,000 in a busy
database.
■
ash data is sampled, so the number of samples must be high enough for the data to be accurate. also, you will
need a Diagnostic & tuning pack license to access ash data in release 12c and earlier.
Note
In version 12c, the Pluggable Database (PDB) feature is introduced. As the user objects can be in a PDB instead of
the CDB, the code shown in Listing 10-3 needs to be adjusted to access cdb_objects view. Listing 10-4 shows the modified
script, and in the output below, con_id column indicates the PDB container_id and object inducing global cache wait
events. You can identify the PDB name by joining cdb_pdbs view. In my test cluster, container_id of hrdb1 PDB is 3.
Listing 10-4.
Script: ash_gcwait_to_obj_12c.sql
col owner format a30
col object_name format a30
set lines 160
WITH ash_gc AS
(SELECT inst_id, event, current_obj#,con_id, COUNT(*) cnt
FROM gv$active_session_history
WHERE event=lower('&event')
GROUP BY inst_id, event, current_obj#, con_id
HAVING COUNT (*) > &threshold )
SELECT * FROM
(SELECT inst_id, a.con_id, nvl( owner,'Non-existent') owner ,
nvl ( object_name,'Non-existent') object_name,
nvl ( object_type, 'Non-existent') object_type,
cnt
