Insert statements cause table contention

Insert contention usually is caused by block level contention such as other session and sequential reads along with the indexes 

Troubleshooting steps:

• Run ASH reports
• Run AWR report when database is good performance v.s when database is bad performance
• Ensure tables have latest statistics
• Find the objects fragmented below high water mark
• Run SQL Tunning Health-check Script (SQLHC) -1366133.1
• Review OSW during bad time

Recommendations:

• De-fragment the table and related indexes.  It's is best to use ALTER TABLE move and ALTER INDEX REBUILD ONLINE and gather new statistics.
• It's best to change the PCTFREE to 20% and Indexes rebuilt with PCFREE 20%

Queries to run:

select index_name, INI_TRANS,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,NUM_ROWS,PCT_FREE from dba_indexes where table_name ='your-table-with-insert-issue'

select OWNER, SEGMENT_NAME, SEGMENT_TYPE from dba_extents where file_id = 106 and 50600154 between block_id and block_id + blocks - 1 and rownum = 1;

Latch Free Wait contention issue

Latch Free Wait contention issue

To troubleshoot this issue, following the following steps:

1. run AWR reports on each node RAC

2. run AWR diff report

3.  Patch 26436717 HIGH RESULT CACHE LATCHES AFTER MIGRATION TO 12.1.0.2

Apply the patch or use workaround:  

alter system set "_optimizer_dsdir_usage_control" = 0;

This parameter can be set dynamically

spool query_result.html

set serveroutput on

exec dbms_result_cache.memory_report;

set lines 500

set long 9999

set pages 999

set serveroutput on size 1000000

set feedback off

SET MARKUP HTML ON SPOOL ON HEAD "<TITLE>SQL*Plus Report</title><STYLE TYPE='TEXT/CSS'><!--BODY {background: ffffc6} --></STYLE>"

set echo off

select namespace, status, name,

count(*) number_of_results,

round(avg(scan_count)) avg_scan_cnt,

round(max(scan_count)) max_scan_cnt,

round(sum(block_count)) tot_blk_cnt

from v$result_cache_objects

where type = 'Result'

group by namespace, name, status

order by namespace, tot_blk_cnt;

select name, value from v$result_cache_statistics;

spool off

SET MARKUP HTML OFF

set echo on

Frequency v.s Heigh Balanced Histogram

Frequency

·         Each bucket has its own size (counts of rows with that value)

·         NDV <= Buckets request <=254

·         One distinct value per bucket

·         Bucket_size = rows with “that” particular value

  

Height Balanced

·         Bucket size = (num_rows – num_nulls) / num_buckets

·         Values are sorted before splitting them into buckets

·         One or more distinct values per bucket

·         Only values of end-points are stored in histogram

·         Bucket_size = (Orig_CarD – Nnulls) / Buckets_total

dbms_stats

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

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

                         31 => this is the default value

If you like to change it to 45 days, then use the ALTER_STATS_HISTORY_RETENTION procedure

SQL> exec dbms_stats.alter_stats_history_retention(45);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_availability from dual; => this query will return the date statistics have been purged up to. 

GET_STATS_HISTORY_AVAILABILITY

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

18-APR-13 07.20.37.099029000 PM -05:00

SQL> select table_name, stats_update_time from dba_tab_stats_history where owner = ‘GLS’;  è this query shows the time when  given tables’ statistics were re-gathered

  

TABLE_NAME                     STATS_UPDATE_TIME

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

GLS_CONTACT_NEW                 19-MAY-13 09.56.39.845243 PM -05:00

GLS_CONTACT_NEW                 06-MAY-13 05.22.14.338999 PM -05:00

To restore table statistics:   For example, for GLS_CONTACT_NEW table, the statistics on May 19 is bad but May 6^th is good, so you like to restore it to May 6^th.

SQL> execute dbms_stats.restore_table_stats('GLS','GLS_CONTACT_NEW','06-MAY-13 05.22.14.338999 PM -05:00');

PL/SQL procedure successfully completed.

To compare statistics for a table from two timestamps or date in the past

select * from table(dbms_stats.diff_table_stats_in_history
(ownname => upper('&ownname'),
tabname => upper('&tabname'),
time1 => systimestamp,
time2 => to_timestamp('&time2','mm/dd/yyyy'),
pctthreshold => 0))

Whenever it is necessary, statistics can be restored from the history.  The dbms_stats provides:

Execute dbms_stats.restore_table_stats(‘owner’, ‘table’, date)

Execute dbms_stats.restore_database_stats(date)

Execute dbms_stats.restore_dictionary_stats(date)

Execute dbms_stats.restore_fixed_objects_stats(date)

Execute dbms_stats.restore_schemas_stats(‘owner’,date)

Execute dbms_stats.restore_system_stats(date)

To set table statistics, you can use dbms_stats.set_table_stats

SQL> exec dbms_stats.set_table_stats(ownname=>'GLS', tabname=>'TEST_ISSUE', numrows=>140);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_table_stats(ownname=>'GLS', tabname=>'TEST_ISSUE',numblks=>10);
PL/SQL procedure successfully completed.

To set index statistics, you can use dbms_stats.set_index_stats to set number rows in index, number of leaf blocks, distinct keys, clustering factor, heigh of index 

exec dbms_stats.set_index_stats (ownname=>'GLS',tabname=>'PROD_NEW',numdist=>1000);

DBMS_SQLTUNE package to Run the Sql Tuning Advisor (ID 262687.1)

Check out the SQL TUNING document is from metalink (ID 262687.1).  It’s very handy when you need to get some advices to tune a poor SQL query using dbms_sqltune package. 

DECLARE

  my_task_name VARCHAR2(30);

  my_sqltext CLOB;

BEGIN

  my_sqltext := 'SELECT * '   ||

                'FROM sales ' || 'WHERE prod_id = 10 AND ' || 'cust_id = 100 ';

  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

                           sql_text => my_sqltext,

                           user_name => 'SH',

                           scope => 'COMPREHENSIVE',

                           time_limit => 60,

                           task_name => ‘gls_sql_tuning_task’,

                           description => 'Task to tune a query on a specified PRODUCT');

END;

Execute the tuning task.:

Execute dbms_sqltune.Execute_tuning_task (task_name => ‘gls_sql_tuning_task’);

Check the status of the task using following query:

 select status from dba_advisor_log where task_name=‘gls_sql_tuning_task’;

View the Recommendations

set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task(‘gls_sql_tuning_task’) from dual;

The report can suggest to add index, or gather statistics, or SQL profile.  In my case, the sql_profile was recommended and to accept it, the performance was improved 99.9%

execute dbms_sqltune.accept_sql_profile(task_name =>‘gls_sql_tuning_task’, task_owner => 'SYSTEM', replace => TRUE);

Extended Statistics

Starting 11g, you can create statistics for two or more related columns or an expression on a column that are part of a join condition are corrected. 

Extended statistics on multi-column statistics

DECLARE
  exst_name VARCHAR2(30);
BEGIN
  exst_name := DBMS_STATS.CREATE_EXTENDED_STATS('HR','emp',
             '(last_name,department_id)');
END;
/

SELECT SYS.DBMS_STATS.SHOW_EXTENDED_STATS_NAME('hr','emp',
       '(last_name,department_id)') col_group_name
FROM DUAL
/

COL_GROUP_NAME
--------------------------------------------------------------------------------
SYS_STUAV0Z346SKNRSGSE2XA1MM6L

SQL> select dbms_stats.create_extended_stats('hr','emp','(last_name,department_id)') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS('HR','EMP','(LAST_NAME,DEPARTMENT_ID)')
--------------------------------------------------------------------------------
SYS_STUAV0Z346SKNRSGSE2XA1MM6L

Extended statistics on Expression:

SQL> execute dbms_stats.gather_table_stats('HR','EMP', method_opt =>'for all columns size skewonly for columns(lower(last_name)) size skewonly');

To drop extended stats:

SQL> exec dbms_stats.drop_extended_stats('hr','emp','(last_name,department_id)');

Gathering statistics on Column Groups:
Method_opt enables you to gather statistics on column group: 
FOR ALL COLUMNS SIZE AUTO:  optimizer gathers statistics on all existing column groups
FOR COLUMNS:  The column group is automatically created as part of statistics gathering

exec dbms_stats.gather_table_stats('HR','EMP',METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (LAST_NAME, DEPARTMENT_ID) SIZE SKEWONLY');

To monitor column groups about multi column or expression statstistics
SQL> select * from user_stat_extensions;

TABLE_NAME EXTENSION_NAME                 EXTENSION    CREATO DRO
---------- ------------------------------ ------------ ------ ---
EMP        SYS_STUAV0Z346SKNRSGSE2XA1MM6L ("LAST_NAME" USER   YES
                                          ,"DEPARTMENT
                                          _ID")

EMP        SYS_STU$OGQI#ACVJROJQ#33ZBENKN (LOWER("LAST USER   YES
                                          _NAME")

SELECT e.EXTENSION col_group, t.NUM_DISTINCT, t.HISTOGRAM
FROM   USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
WHERE  e.EXTENSION_NAME=t.COLUMN_NAME
AND    e.TABLE_NAME=t.TABLE_NAME
AND    t.TABLE_NAME='EMP';

SQL Monitor , /*+ MONITOR */, DBMS_SQLTUNE.REPORT_SQL_MONITOR

The SQL monitor is introduced in Oracle 11g database is a very effective way to monitor long running SQL statements.  By default, SQL monitoring automatically starts when a SQL statement runs parallel, or when it has consumed at least 5 seconds of CPU or I/O time in a single execution.  The /*+MONITOR*/ hint enables monitoring for statements and the DBMS_SQLTUNE.REPORT_SQL_MONITOR package creates a monitoring report for a specific SQL statement.  It's interactive with the Enterprise Manager and statistics performance metrics are capture (elapsed time, CPU time, I/O time, reads/writes related, etc. 

Below are few examples:

Example1 /*+ MONITOR */ hint
------------------------------------------------
SQLPLUS >

select /*+ MONITOR */ emp_nbr from DBALAB.EMPLOYEE where emp_nbr is not null and rownum <=200;

set long 1000000
set longchunksize 1000000
set linesize 600
set pagesize 0
set trim on
set trimspool on

select dbms_sqltune.report_sql_monitor (session_id=>sys_context('userenv','sid'),  report_level=>'ALL') as report from dual;
SQL Monitoring Report

SQL Text
------------------------------
select /*+ MONITOR */ emp_nbr from DBALAB.EMPLOYEE where emp_nbr is not null and rownum <=200
Global Information
------------------------------
Status              :  DONE (ALL ROWS)
Instance ID         :  1
Session             :  SYS (689:10593)
SQL ID              :  9hhhwr7ns2y9y
SQL Execution ID    :  16777216
Execution Started   :  05/04/2012 15:02:55
First Refresh Time  :  05/04/2012 15:02:55
Last Refresh Time   :  05/04/2012 15:02:55
Duration            :  .425631s
Module/Action       :  sqlplus@vmsrv01.la.com (TNS V1-V3)/-
Service             :  SYS$USERS
Program             :  sqlplus@vmsrv01.la.com (TNS V1-V3)
Fetch Calls         :  15
Global Stats
======================================================================================
| Elapsed |   Cpu   |    IO    | Cluster  |  Other   | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
======================================================================================
|    0.43 |    0.00 |     0.41 |     0.00 |     0.01 |    15 |     23 |    3 | 112KB |
======================================================================================
SQL Plan Monitoring Details (Plan Hash Value=238925293)
==========================================================================================================================================================
| Id |      Operation       |    Name    |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |      Activity Detail       |
|    |                      |            | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |        (# samples)         |
==========================================================================================================================================================
|  0 | SELECT STATEMENT     |            |         |      |         1 |     +0 |     1 |      200 |      |       |          |                            |
|  1 |   COUNT STOPKEY      |            |         |      |         1 |     +0 |     1 |      200 |      |       |          |                            |
|  2 |    TABLE ACCESS FULL | DBALAB.EMPLOYEE |     200 |    4 |         2 |     +0 |     1 |      200 |    3 | 112KB |   100.00 | db file scattered read (1) |
==========================================================================================================================================================



Example2:  DBMS_SQLTUNE.REPORT_SQL_MONITOR
-----------------------------------------------------------------
select sid, sql_id
from v$session
where status = 'ACTIVE';


 SID    SQL_ID
-------- -------------
     232 7fnqw0qww759d
     343
set long 1000000
set longchunksize 1000000
set linesize 600
set pagesize 0
set trim on
set trimspool on


select dbms_sqltune.report_sql_monitor(sql_id =>'7fnqw0qww759d', type=>'TEXT') as report from dual;
SQL Monitoring Report
SET LINESIZE 300
COLUMN sql_text FORMAT A100
SELECT sql_id, status, sql_text FROM v$sql_monitor;
acc988uzvjmmt DONE                delete from WRH$_SYSMETRIC_HISTORY tab where (:beg_snap <= tab.snap_id and         tab.snap_id <= :e
                                  nd_snap and         dbid = :dbid)    and not exists (select 1 from WRM$_BASELINE b
                                    where (tab.dbid = b.dbid) and                          (tab.snap_id >= b.start_snap_id) and
                                                     (tab.snap_id <= b.end_snap_id))
7fnqw0qww759d DONE                begin dbms_feature_usage_internal.exec_db_usage_sampling(:bind1); end;


SET LONG 1000000               
SET FEEDBACK OFF
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>'7fnqw0qww759d',type=> 'TEXT')
  2  AS report FROM dual;
SQL Monitoring Report
SQL Text
------------------------------
begin dbms_feature_usage_internal.exec_db_usage_sampling(:bind1); end;
Global Information
------------------------------
 Status              :  DONE
 Instance ID         :  1
 Session             :  (1600:2927)
 SQL ID              :  05s9358mm6vrr
 SQL Execution ID    :  16777218
 Execution Started   :  04/30/2012 21:37:39
 First Refresh Time  :  04/30/2012 21:37:43
 Last Refresh Time   :  04/30/2012 21:37:44
 Duration            :  5s
 Module/Action       :  MMON_SLAVE/0000010 FINISHED190
 Service             :  SYS$BACKGROUND
 Program             :  oracle@vmsrv01.la.com (M000)
Global Stats
==========================================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  |  Other   | Buffer | Read | Read  | Write | Write |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes |
==========================================================================================================================
|    4.22 |    2.16 |     1.19 |        0.01 |        0.03 |     0.07 |     0.77 |   204K | 1682 |  24MB |     4 | 32768 |
==========================================================================================================================
SQL>

Oracle Trace Analyzer --TRCANLZR or TRCA

Oracle Trace Analyzer, TRCANLZR or TRCA, is a SQL trace profiling tool for analyzing your trace files.  Here are the major sections of a TRCA report that provide you a lot of useful diagnostic information than tkprof.

·         Response Time Summary

·         Overall Time and Totals

·         Non-Recursive Time and Totals

·         Recursive Time and Totals

·         Top SQL

·         Non-Recursive SQL

·         SQL Genealogy

·         Individual SQL

·         Overall Segment I/O Wait Summary

·         Hot I/O Blocks

·         Gaps in Trace

·         ORA errors in Trace

·         Transactions Summary

·         Non-default Initialization Params

·         Trace Header

·         Tool Data Dictionary

·         Tool Execution Environment

·         Tool Configuration Parameters

You need to download the tool from Oracle Support.  Once you’ve downloaded it, the setup is very easy by executing the /trca/install/trcreate.sql.

SETUP:

cd /software/tools/trca/install

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> @tacreate.sql

Uninstalling TRCA, please wait

TADOBJ completed.

SQL>

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE;

SQL> REM If this DROP USER command fails that means a session is connected with this user.

SQL> DROP USER trcanlzr CASCADE;

SQL> WHENEVER SQLERROR CONTINUE;

SQL>

SQL> SET ECHO OFF;

TADUSR completed.

TADROP completed.

Specify optional Connect Identifier (as per Oracle Net)

Include "@" symbol, ie. @PROD

If not applicable, enter nothing and hit the "Enter" key

Optional Connect Identifier (ie: @PROD): @LABPOC1

Define the TRCANLZR user password (hidden and case sensitive).

Specify TRCANLZR password:

Re-enter password:

Set up TRCANLZR temporary and default tablespaces

Below are the list of online tablespaces in this database.

Decide which tablespace you wish to create the TRCANLZR tables

and indexes.  This will also be the TRCANLZR user default tablespace.

Specifying the SYSTEM tablespace will result in the installation

FAILING, as using SYSTEM for tools data is not supported.

Wait...

TABLESPACE_NAME                FREE_SPACE_MB

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

TOOLS                                     55

TOOLS_OLD                                 98

LAB_I                                   830

USERS                                   1130

LAB_D                                 65632

Above is the list of online tablespaces in this database.

Decide which tablespace you wish to create the TRCANLZR tables

and indexes.  This will also be the TRCANLZR user default tablespace.

Specifying the SYSTEM tablespace will result in the installation

FAILING, as using SYSTEM for tools data is not supported.

Tablespace name is case sensitive.

Default tablespace [UNKNOWN]: TOOLS_OLD

DEFAULT_TABLESPACE

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

TOOLS_OLD

Choose the TRCANLZR user temporary tablespace.

Specifying the SYSTEM tablespace will result in the installation

FAILING, as using SYSTEM for the temporary tablespace is not recommended.

Wait...

TABLESPACE_NAME

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

TEMP

Tablespace name is case sensitive.

Temporary tablespace [UNKNOWN]: TEMP

TEMPORARY_TABLESPACE

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

TEMP

Type of TRCA repository

Create TRCA repository as Temporary or Permanent objects?

Enter T for Temporary or P for Permanent.

T is recommended and default value.

Type of TRCA repository [T]:

TACUSR completed.

No errors.

SQLT$STAGE: write test file tasqdirset.txt

sqlt$_trca$_dir_set.open_write_close: ORA-29283: invalid file

operation

ORA-06512: at "SYS.UTL_FILE", line 536

ORA-29283: invalid file

operation

SQLT$STAGE: created

SQLT$STAGE: READ,WRITE access granted to SQLTXPLAIN

SQLT$STAGE: READ,WRITE access granted to TRCANLZR

SQLT$STAGE: write test file tasqdirset.txt

SQLT$STAGE: read test file tasqdirset.txt

…..

TRCA$INPUT2: read test file tasqdirset.txt

TRCA$INPUT2: get attributes for file tasqdirset.txt

TRCA$INPUT2: /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace

Connected.

TAUTLTEST completed.

no rows selected

TACOBJ completed.

tool_owner: "TRCANLZR"

role_name: "PUBLIC"

Creating Package Specs TRCA$G

No errors.

Creating Grants on Packages

Tool Version

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

11.4.4.2

Install Date

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

20120320

Directories

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

TRCA$INPUT1(VALID)      /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace

TRCA$INPUT2(VALID)      /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace

TRCA$STAGE(VALID)       /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace

user_dump_dest          /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace

background_dump_dest    /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace

Libraries

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

VALID PACKAGE TRCA$I /* $Header: 224270.1 tacpkgi.pks 11.4.4.1 2012/01/02 csierra $ */

VALID PACKAGE TRCA$E /* $Header: 224270.1 tacpkge.pks 11.4.2.7 2011/04/08 csierra $ */

VALID PACKAGE TRCA$G /* $Header: 224270.1 tacpkgg.pks 11.4.2.7 2011/04/08 csierra $ */

VALID PACKAGE TRCA$P /* $Header: 224270.1 tacpkgp.pks 11.4.4.1 2012/01/02 csierra $ */

VALID PACKAGE TRCA$R /* $Header: 224270.1 tacpkgr.pks 11.4.2.7 2011/04/08 csierra $ */

VALID PACKAGE TRCA$T /* $Header: 224270.1 tacpkgt.pks 11.4.3.1 2011/06/17 csierra $ */

VALID PACKAGE TRCA$X /* $Header: 224270.1 tacpkgx.pks 11.4.2.7 2011/04/08 csierra $ */

VALID PACKAGE BODY TRCA$I /* $Header: 224270.1 tacpkgi.pkb 11.4.4.1 2012/01/02 csierra $ */

VALID PACKAGE BODY TRCA$E /* $Header: 224270.1 tacpkge.pkb 11.4.2.7 2011/04/08 csierra $ */

VALID PACKAGE BODY TRCA$G /* $Header: 224270.1 tacpkgg.pkb 11.4.4.2 2012/02/02 csierra $ */

VALID PACKAGE BODY TRCA$P /* $Header: 224270.1 tacpkgp.pkb 11.4.4.1 2012/01/02 csierra $ */

VALID PACKAGE BODY TRCA$R /* $Header: 224270.1 tacpkgr.pkb 11.4.4.1 2012/01/02 csierra $ */

VALID PACKAGE BODY TRCA$T /* $Header: 224270.1 tacpkgt.pkb 11.4.4.1 2012/01/02 csierra $ */

VALID PACKAGE BODY TRCA$X /* $Header: 224270.1 tacpkgx.pkb 11.4.2.7 2011/04/08 csierra $ */

TACPKG completed.

Taking a snapshot of some Data Dictionary objects, please wait...

17:48:23 => refresh_trca$_dict_from_this

17:48:23 -> purge_trca$_dict

17:48:23 dict_state_before_purge

17:48:23 -----------------------

17:48:23 -> print_dict_state

17:48:23 dict_refresh_days :

17:48:23 dict_refresh_date :

17:48:23 dict_database_id  :

…..

17:48:45 <- print_dict_state

17:48:45 <= refresh_trca$_dict_from_this

PL/SQL procedure successfully completed.

Snapshot of some Data Dictionary objects completed.

TAUTLTEST completed.

TACREATE completed. Installation completed successfully.

SQL>

TRACING:

alter session set events ‘10046 trace name context forever, level 12’;

alter system set tracefile_identifier=’mytrace’;

<Your SQL statement here…>

alter session set events ‘10046 trace name context off’;

SQL> START trcanlzr.sql /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace/LABPOC1_ora_23740_mytrace.trc

Parameter 1:

Trace Filename or control_file.txt (required)

Value passed to trcanlzr.sql:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TRACE_FILENAME: /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace/LABPOC1_ora_23740_mytrace.trc

Analyzing /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace/LABPOC1_ora_23740_mytrace.trc

To monitor progress, login as TRCANLZR into another session and execute:

SQL> SELECT * FROM trca$_log_v;

... analyzing trace(s) ...

Trace Analyzer completed.

Review first trcanlzr_error.log file for possible fatal errors.

Review next trca_e43354.log for parsing messages and totals.

Copying now generated files into local directory

TKPROF: Release 11.2.0.3.0 - Development on Tue Mar 20 17:59:31 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

  adding: trca_e43354.html (deflated 89%)

  adding: trca_e43354.log (deflated 84%)

  adding: trca_e43354.tkprof (deflated 82%)

  adding: trca_e43354.txt (deflated 84%)

  adding: trcanlzr_error.log (deflated 81%)

test of trca_e43354.zip OK

deleting: trcanlzr_error.log

Archive:  trca_e43354.zip

  Length     Date   Time    Name

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

   107234  03-20-12 17:59   trca_e43354.html

    15891  03-20-12 17:59   trca_e43354.log

     8367  03-20-12 17:59   trca_e43354.tkprof

    52391  03-20-12 17:59   trca_e43354.txt

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

   183883                   4 files

File trca_e43354.zip has been created

TRCANLZR completed.