12c Query Against Dictionary Views slow

Doc ID 1292253.1

SQL> purge recyclebin; --if you have DBA access, purge dba_recyclebin;
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SYS', TABNAME => '$KTFBUE',ESTIMATE_PERCENT=>100);
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NO_INVALIDATE => FALSE);

Rman Duplicate database from Active database

From 11g above, you can use rman to duplicate database from an active database instead of from a backup.
Two big disadvantages of the ACTIVE database duplication method are:

·         Negative performance impact on the source database. This impact applies to the whole duplication time.

·         High network traffic on the connection between the source and target databases.

Test envorinment:

Source

     SID: testprd

     host:192.168.96.3

Target

     SID: testrman

      host: 192.168.96.4

Oracle 10/11 install on Centos 7

For 10.2.0.1:

If you get error
“Error in invoking target 'collector' of makefile '/orasw/app/oracle/product/10.2.0/sysman/lib/ins_emdb.mk'” at 83% can be ignored (Metalink 957982.1)

For 11.2.0.4:

If you get the following error:

PRVF-7532 : Package "pdksh" is missing on node "Hostname"
You can install ksh package (yum install ksh)then safely ignore it and  continue.

When getting error “Error in invoking target 'agent nmhs' of makefile '/orasw/app/oracle/product/11.2.0/sysman/lib/ins_emagent.mk'.”

vi $ORACLE_HOME/sysman/lib/ins_emagent.mk
Search for the line
$(MK_EMAGENT_NMECTL)
Change it to:
$(MK_EMAGENT_NMECTL) -lnnz11

And retry.

RAC: ORA-01410: invalid ROWID

Can try flushing the cache:
alter system flush buffer_cache;

ORA-03114 and ORA-03137

Oracle Version: 11.2.0.1
Client sessions disconnected due to error "ORA-03114: Not Connected to Oracle".
In Alert log ORA-03137 is detected: "ORA-03137: TTC protocol internal error : [12333] [8] [57] [48] [] [] [] [] ".

optimizer_index_cost_adj hint

Sometimes you want to use alter session set optimizer_index_cost_adj=<value> to change the parameter, but you can also use hint for the purpose, for example:

select /*+ opt_param('optimizer_index_cost_adj',20) */ col1, col2 . .
  

And optimizer_mode hint:

select /*+ opt_param('optimizer_mode','first_rows_10') */ col1, col2 . . .  

Session hang because 'gc cr request' and 'cr request retry'

In 11g Rac, during datapump import, session hangs at step of importing indexes or constraints.

If you check v$session_wait for the sid, you may notice  'gc cr request' and 'cr request retry':

SQL> select event from v$session_wait where sid=405;

EVENT

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

gc cr request

SQL> select event from v$session_wait where sid=405;

EVENT

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

cr request retry

Mostly it's caused by Redo log IO performance or the interconnect performance.

You can try:
1) Improve the interconnect performance.
2) Improve the Redo log I/o performance.
3) Set undersore parameter "_cr_server_log_flush" =false.