Trace - set max_dump_file_size=unlimited

 Trace 

ACTION PLAN

-----------------

1. Execute SQL alter system set max_dump_file_size=unlimited; (If you dont set it to unlimited the trace could be truncated)

2. Perform steps to enable trace for the concurrent request:

a. In System Administrator responsibility Navigate to: Concurrent -> Program -> Define, query up the concurrent program "Federal Activity Attributes Creation" to trace. Make sure that the "enable trace" checkbox is checked.

3. Submit the Concurrent Request "Federal Activity Attributes Creation"

4. After the "Federal Activity Attributes Creation" request starts / wait for couple of hours (3-4 hours) and move further (DO NOT CANCEL THE REQUEST).

5. Execute tkprof on the trace files collected. (NOTE: TKPROF should be executed from DB node as DB Owner OS User).

a. Issue a below command to create a TKPROF of the trace file. This command sorts the results with the longest running queries first:

tkprof <filename.trc> <output_filename_SORT.txt> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)'

b. Additionally, please provide the following TKPROF that limits the results to the top ten queries:

tkprof <filename.trc> <output_filename_TOP10.txt> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)' print=10

c. Additionally, please provide the following TKPROF that shows all the sql statements executed for that concurrent request.

tkprof <filename.trc> <output_filename_ALL.txt> sys=no explain=apps/<password>

d. The above 3 commands should be executed on each and every trace file generated by the concurrent request.

6. Execute SQL alter system set max_dump_file_size=<ACTUAL VALUE>;

7. Upload a copy of the "Federal Activity Attributes Creation" request log and output.

8. Execute the following SQL statement and upload the output in an EXCEL SHEET:

SQL> select owner, table_name, status, num_rows, sample_size, last_analyzed from dba_tables where table_name like '%_GT' and num_rows is not null order by table_name;

SQL> select owner, table_name, status, num_rows, sample_size, last_analyzed from dba_tables where table_name = 'MO_GLOB_ORG_ACCESS_TMP' and num_rows is not null;

SQL> select * from v$parameter where name like 'optimizer%' order by name;

SQL> select SIGNATURE, SQL_HANDLE, SQL_TEXT, PLAN_NAME, CREATOR, ORIGIN FROM dba_sql_plan_baselines;

9. Collect AWR Report for the below snaps when "Invoice Validation" was running and upload the report:

Begin Snap ID = <Start time of Federal Activity Attributes Creation>

End Snap ID = <Start time of Federal Activity Attributes Creation + 1 Hour>

10. Run SQLT 

# sqlplus / as sysdba
SQL> START sqlhc.sql "T" djkbyr8vkc64h

RESTRICTIONS

1. Please note the SQL_ID cannot be a SQL_ID of a PL/SQL package. This will produce no useful results.
2. This routine will run against ONE SQL_ID at a time, only. You cannot use a list of SQL_ID statements as a parameter list.
3. You cannot run this routine against ALL SQL statements run by a schema.

Ref:

Concurrent Processing - bde_request.sql - Process and Session info for one Concurrent Request (11.5) (Doc ID 187504.1)

SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)