Problem Diagnosis - Expert Oracle RAC Performance Diagnostics and Tuning - page 624

■ either way, the trace file will have to be checked to determine the actual query. however, if we use the V$ views
as the method, this would could help us get to the exact trace files.

Note

Querying the V$DIAG_INCIDENT_FILE will get us the list of trace files associated with this incident. From the trace
files, the SQL statement can be obtained.

Step 3
We have determined that the incident is associated with an SQL statement, and the SQL statement is part of the
following trace file:

/app/oracle/diag/rdbms/prddb/PRDDB_1/trace/PRDDB_1_ora_3956.trc

The next step is to create a diagnosis task using the PL/SQL procedure DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK.
This will help build the SQL repair advisor.

Step 4
When creating the diagnosis task, we assign the task a name for easy identification and also associate the problem
type with the task:

DECLARE
rep_out CLOB;
MV_id VARCHAR2(50);
BEGIN
MV_id := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK(
sql_text =>
'SELECT DISTINCT T537387.SRV_REQ_NUM AS c1 ,
T537387.INCIDENT_TYPE_CD AS c2 ,
T534609.DAY_DT AS c3 ,
T501320.NAME AS c4 ,
T512643.INSTANCE_NUM AS c5 ,
T512643.SERIAL_NUM AS c6 ,
T32069.X_PROD_HIER8_NAME AS c7 ,
T595543.NOTE_TYPE_NAME AS c8 ,
T595543.NOTES AS c9 ,
T595543.NOTES_DETAIL AS c10,
T595543.CREATED_ON_DT AS c11,
SUM(T537323.ROW_WID / NULLIF( T537323.ROW_WID, 0)) AS c12
FROM W_ORG_D T501320 ,
W_CUSTOMER_LOC_D T501866 ,
W_DAY_D T534609 ,
W_PRODUCT_D T32069 ,
X_SRV_INSTALL_BASE_D T512643 ,
X_SRV_REQ_F T537323 ,
X_SRV_REQ_D T537387 left outer join X_SRV_NOTES_V T595543 On T537387.ROW_WID = T595543.
PARENT_WID