Oracle Database Experts

SET AUTOTRACE OFF – Turn off AUTOTRACE This is the default.
SET AUTOTRACE ON EXPLAIN – Shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS – Shows only the SQL statement execution statistics.
SET AUTOTRACE ON – Shows both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY – Similar to “SET AUTOTRACE ON”, but suppresses the printing of the user’s query output. Great for checking execution times for queries without looking at the query output.

You can use the following snippet of code to setup autotrace in SQL*Plus:

col PLAN_PLUS_EXP            for a122
set lines 500
set autot trace exp
set time on timing on

Posted by Charles Kim, Oracle ACE

Enable GUARD:

Guard the standby database from writes being able to modify tables that are being replicated:

Stop Redo Apply first

alter database stop logical standby apply
SQL> /
Database altered.

SQL> alter database guard standby;
Database altered.

Skipping transactions for troubleshooting:
In general, you never want to skip a DML transaction with the fear of corrupting the logical standby data. If you do, it will most likely be DDL not DML.

1  SELECT XIDUSN, XIDSLT, XIDSQN FROM DBA_LOGSTDBY_EVENTS
2* WHERE EVENT_TIME = (SELECT MAX(EVENT_TIME) FROM DBA_LOGSTDBY_EVENTS)
    XIDUSN     XIDSLT     XIDSQN
---------- ---------- ----------
         9          2     732576
SQL> EXECUTE DBMS_LOGSTDBY.SKIP_TRANSACTION(9,2,732576);

Additional Performance Tuning Tips:
We will add to this section on an on-going basis. As we encounter performance issues, we will add to this section of our findings.

Increase MAX Servers

1* alter system set parallel_max_servers=27 scope=both sid='*'
SQL> /
System altered.

exec DBMS_LOGSTDBY.APPLY_SET('MAX_SERVERS', 27);

Increases LCR cache memory

execute dbms_logstdby.apply_set('MAX_SGA',2400);

Changes the number of apply processes. For a specific incident, you may have to change the following parameter up to 10 and then back down to 1:

exec dbms_logstdby.apply_set('APPLY_SERVERS',1);

Posted by Charles Kim, Oracle ACE