Exadata Smart Scan feature and fast full scan on indexes

I spent some time analyzing the problem, why some queries, despite a good execution plan and use of the Exadata (smart scan) functionality, are slow.

In execution plan ( what is most important for database) we see that full scan table WITH SMART SCAN functionality is used on table TABLE and for rest is used join with indexes (with index fast full scan).

------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                     |       |       |       |   933K(100)|          |
|   1 |  SORT ORDER BY                   |                     |    22M|   917M|  1113M|   933K (38)| 00:05:02 |
|   2 |   HASH JOIN RIGHT SEMI           |                     |    22M|   917M|    23M|   720K (45)| 00:03:53 |
|   3 |    VIEW                          | ViEW_1              |  1316K|  9000K|       |   201K (49)| 00:01:05 |
|   4 |     HASH JOIN                    |                     |  1316K|    77M|    84M|   201K (49)| 00:01:05 |
|   5 |      TABLE ACCESS STORAGE FULL   | TABLE_1             |  1316K|    69M|       |   182K (48)| 00:00:59 |
|   6 |      INDEX STORAGE FAST FULL SCAN| PK_TABLE_2          |    28M|   191M|       |  5398  (83)| 00:00:02 |
|   7 |    INDEX FAST FULL SCAN          | NPI_INC_COMPRESS_1  |   488M|    16G|       |   162K (63)| 00:00:53 |
------------------------------------------------------------------------------------------------------------

Reality looks a bit different. During execution of this query with default execution plan (with smart scan) I saw that smart scan is not used!!!

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

Active% | SQL_ID          | SQL_CHILD | EVENT                               | WAIT_CLASS

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

    58% | 2rvn92mjxr7pk   | 0         | ON CPU                              | ON CPU

    42% | 2rvn92mjxr7pk   | 0         | cell multiblock physical read       | User I/O

Lack of WAIT cell smart table scan

Why? If in the execution plan is smart scan but smart scan is not used!

This part of my analysis took most of the time: reason of this behavior

When I forced this query to use only full scans on all tables (without indexes) then on session I saw :

1558, SYS       , TIME, DB CPU                                                    ,       3798473,   759.69ms,    76.0%, [@@@@@@@@  ],          ,           ,

   1558, SYS       , TIME, sql execute elapsed time                                  ,       4004145,   800.83ms,    80.1%, [########  ],          ,           ,

   1558, SYS       , TIME, DB time                                                   ,       4004145,   800.83ms,    80.1%, [########  ],          ,           ,

   1558, SYS       , WAIT, cell smart table scan                                     ,        244890,    48.98ms,     4.9%, [W         ],       443,       88.6,    552.8us

And query finished 4 times faster than using default execution plan.

The reason of this behavior is little tricky. I found that if in the execution plan is used (INDEX FAST FULL SCAN) on compressed index then the smart scan cannot be used in all of this query!!!  Also if in execution plan database show that smart scan is used:

Smart Scans On Compressed Indexes (Doc ID 1561260.1)

And we have this situation for problematic query. Index NPI_INC_COMPRESS_1 is a compressed index and database in execution plan decide to use INDEX FAST FULL SCAN on this index and after that FULL_SCAN (SMART SCAN) on TABLE_1 is not used but execution plan shows that is .

Solution for that is add hints FULL on all tables used in this query or change this index to uncompressed . After that smart scan feature is used.

What is really interesting that if index fast full scan is used then is  more than 10 conditions when smart scan is not really used if the even execution plan says otherwise (details in 1561260.1)

RMAN - transfer archivelog from primary database to standby

rman target /@rman_prm catalog [catalog]@rmancat auxiliary /@rman_stb

backup as copy archivelog sequence between  5643  and 5683   thread 2  auxiliary format '+RECOC1';

Checking most heavy queries every snapshot and system statistics

Most important system statiscts/performance overview:

SELECT begin_time,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN 'SQL Service Response
Time (secs)'
    WHEN 'Response Time Per Txn'
    THEN 'Response Time Per Txn
(secs)'
    ELSE METRIC_NAME
  END METRIC_NAME,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((MINVAL / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((MINVAL / 100),2)
    ELSE MINVAL
  END MININUM,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((MAXVAL / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((MAXVAL / 100),2)
    ELSE MAXVAL
  END MAXIMUM,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((AVERAGE / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((AVERAGE / 100),2)
    ELSE AVERAGE
  END AVERAGE
FROM SYS.DBA_HIST_SYSMETRIC_SUMMARY
WHERE METRIC_NAME IN ('CPU Usage Per Sec', 'CPU Usage Per Txn', 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'Executions Per Sec', 'Executions Per Txn', 'Response Time Per Txn', 'SQL Service Response Time', 'User Transaction Per Sec','User Commits Per Sec')
AND BEGIN_TIME BETWEEN sysdate -1 and sysdate
ORDER BY 1;

Output:

Most heavy queries:

SELECT t.sql_id,
  dbms_lob.substr(q.SQL_TEXT,100,1),
  t.PARSING_SCHEMA_NAME username,
  t.executions_delta exec_count,
  begin_interval_time,
  ROUND(SUM(t.elapsed_time_delta/1000000)/SUM(t.executions_delta),4) time_exec
FROM dba_hist_sqlstat t,
  dba_hist_snapshot s,
  DBA_HIST_SQLTEXT q
WHERE t.snap_id           = s.snap_id
AND t.dbid                = s.dbid
AND q.sql_id              =t.sql_id
AND t.instance_number     = s.instance_number
AND t.executions_delta   IS NOT NULL
AND t.elapsed_time_delta IS NOT NULL
AND t.executions_delta    > 0
AND s.begin_interval_time BETWEEN TRUNC(sysdate)-1 AND TRUNC(sysdate)
AND t.PARSING_SCHEMA_NAME NOT                  IN ('SYS','SYSTEM','DBSNMP') -- yesterday's stats
GROUP BY t.sql_id,
  dbms_lob.substr(q.SQL_TEXT,100,1),
  PARSING_SCHEMA_NAME,
  t.executions_delta,
  s.begin_interval_time
ORDER BY 5,6 DESC;

Output:

 

RMAN-06004: ORACLE error from recovery catalog database: ORA-20999: internal error: found non-null and null site name

In metalink is only one solution. Recreate controlfile and this issue occurs for database with long db_unique_name ( 30 chars in length) and when catalog is in use.

RMAN-06004: ORACLE error from recovery catalog database: ORA-20999: internal error: found non-null and null site name

I found another solution. I set debug option for rman and in trace file was information:

DBGSQL:           RCVCAT> select count(*) into cnt from db
DBGSQL:              sqlcode = 905
DBGSQL:           error: ORA-00905: missing keyword (krmkosqlerr)

So. It's looks like that rman wrongly generate code. After that I decided to use:

upgrade catalog 

and now everything works ...

Probably is a small difference in catalog (for example after PSU) and all packages must be recreated.

What is strange that command "upgrade catalog" had been executed on newly created catalog... 

resmgr:pq queued

When trying to connect to database (11g,12c)  I  see wait events on "resmgr:pq queued" indefinitely.
What can be done to resolve the resmgr:pq queued wait message?

By changing the following parameter, this issue was resolved :

alter system set "_parallel_statement_queuing"=FALSE scope=both;

Clusterware out of sync

I had strange inconsistency in Clusterware 12c.

crsctl stat res -t -w "NAME = ora.dbname.db"

return information that db is configured on:
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.dbname.db
      1        ONLINE  ONLINE       node04              Open,Readonly,STABLE
      2        ONLINE  UNKNOWN      node01              Abnormal Termination
                                                             ,STABLE
--------------------------------------------------------------------------------

but when I checked configuration for this database:

srvctl config database -d dbname
...
Database instances: dbname1,dbname1
Configured nodes: node03,node04

 In documentation is a info that crsctl has a option "relocate resource" but dosn't work :/

To relocate resource I had to use:
srvctl modify instance -d dbname -i dbname1 -n node03


In one of document on metalink I found that only modify option force clusterware to synchronize information about resources

KTBCONVERTREDO

ORA-600 [KTBCONVERTREDO_1]

After ora-4030 errors and sequence of ora-600 ktbConvertRedo_1

and  led to an instance crash. Restarting database didn't help

(database was up ~ 5minutes).

We tried also switchover  to physical standby but unfortunately

we received the same error. 

Smon generated trace and if you carefully look in trace there is

 a object_id. When we tried 

run queries on this object then we received the same error. 

Bingo!!! Object had been corrupted.

luckily that was table with logs from application and we can

 easy drop table and recreate empty structure.   
 

In metalink I found information that  event 10153 should be set

 to see object_id but without that in alertlog this information was.

Shrink segments

Script to shrink segments:
SELECT 'alter table '
  ||OWNER
  ||'.'
  ||TABLE_NAME
  ||' enable row movement;' sql1
FROM DBA_TABLES
WHERE TABLESPACE_NAME IN ('USERS')
AND TABLE_NAME NOT    IN
  (SELECT TABLE_NAME
  FROM DBA_TAB_PARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  UNION ALL
  SELECT TABLE_NAME
  FROM DBA_TAB_SUBPARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  )
UNION
SELECT 'alter table '
  ||OWNER
  ||'.'
  ||TABLE_NAME
  ||' shrink space compact;' sql1
FROM DBA_TABLES
WHERE TABLESPACE_NAME IN ('USERS')
AND TABLE_NAME NOT    IN
  (SELECT TABLE_NAME
  FROM DBA_TAB_PARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  UNION ALL
  SELECT TABLE_NAME
  FROM DBA_TAB_SUBPARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  )
UNION
SELECT 'alter table '
  ||OWNER
  ||'.'
  ||TABLE_NAME
  ||' shrink space;' sql1
FROM DBA_TABLES
WHERE TABLESPACE_NAME IN ('USERS')
AND TABLE_NAME NOT    IN
  (SELECT TABLE_NAME
  FROM DBA_TAB_PARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  UNION ALL
  SELECT TABLE_NAME
  FROM DBA_TAB_SUBPARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  )
UNION
SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' enable row movement;' sql1
FROM DBA_TAB_PARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
AND SUBPARTITION_COUNT =0
UNION
SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' modify partition '
  ||PARTITION_NAME
  ||' SHRINK SPACE;' sql1
FROM DBA_TAB_PARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
AND SUBPARTITION_COUNT =0
UNION
SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' enable row movement;' sql1
FROM dba_tab_subpartitions
WHERE tablespace_name IN ('USERS')
UNION
SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' modify subpartition '
  ||SUBPARTITION_NAME
  ||' SHRINK SPACE;' sql1
FROM dba_tab_subpartitions
WHERE tablespace_name IN ('USERS')
UNION
SELECT 'alter table '
  ||owner
  ||'.'
  ||table_name
  ||' modify lob('
  ||column_name
  ||') (shrink space);' sql1
FROM dba_lobs
WHERE TABLESPACE_NAME IN ('USERS')
AND PARTITIONED        ='NO'
UNION
SELECT 'ALTER TABLE '
  ||TABLE_OWNER
  ||'."'
  || TABLE_NAME
  || '" MOVE SUBPARTITION '
  || SUBPARTITION_NAME
  ||'  TABLESPACE '
  ||TABLESPACE_NAME
  ||' LOB ('
  ||COLUMN_NAME
  ||') STORE AS (TABLESPACE '
  ||TABLESPACE_NAME
  ||');'
FROM DBA_LOB_SUBPARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
union
SELECT 'ALTER TABLE '
  ||TABLE_OWNER
  ||'."'
  || TABLE_NAME
  || '" MOVE PARTITION '
  || PARTITION_NAME
  ||'  TABLESPACE '
  ||TABLESPACE_NAME
  ||' LOB ('
  ||COLUMN_NAME
  ||') STORE AS (TABLESPACE '
  ||TABLESPACE_NAME
  ||');'
FROM DBA_LOB_PARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
UNION
SELECT 'ALTER INDEX '
  ||INDEX_OWNER
  ||'.'
  || INDEX_NAME
  ||' rebuild partition '
  || PARTITION_NAME
  ||' online;' sql1
FROM DBA_IND_PARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
AND SUBPARTITION_COUNT =0
UNION
SELECT 'ALTER INDEX '
  ||INDEX_OWNER
  ||'.'
  || INDEX_NAME
  ||' rebuild subpartition '
  || SUBPARTITION_NAME
  ||' online;' sql1
FROM DBA_IND_SUBPARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
UNION
SELECT 'ALTER INDEX '
  ||OWNER
  ||'.'
  || INDEX_NAME
  ||' rebuild online;' sql1
FROM DBA_INDEXES
WHERE TABLESPACE_NAME IN ('USERS')
AND PARTITIONED        ='NO' ;

RMAN - unregister not existed database from catalog

Normally when I tried unregister non-existed database in RMAN catalog I received error:

execute dbms_rcvcat.unregisterdatabase(818673443,879389610);
BEGIN dbms_rcvcat.unregisterdatabase(818673443,879389610); END;

*
ERROR at line 1:
ORA-02292: integrity constraint (RMAN.TSATT_F2) violated - child record found
ORA-06512: at "RMAN.DBMS_RCV

Probably some informations about backups are still in catalog for this database but without database I can't unregister this datbase.

Solution:

select name,DB_KEY,DBINC_KEY,dbid from rman.rc_database where name=<database_name>;
delete rman.tsatt where DBINC_KEY=<DBINC_KEY for db>;

commit;

execute dbms_rcvcat.unregisterdatabase(<DB_KEY for db>,<DBID for db>);

PL/SQL procedure successfully completed.


 

Changing database link definition in pl/sql

To change database link definition in Oracle database we have to connect as a owner of this database link. In most cases it's possible by using output from dba_db_links and connect from sqlplus as a owner.

But if we use undocumented function dbms_sys_sql.parse_as_user, then it's possible to change definition from pl/sql:

-- if we use specific password schema
define pass_prefix = 'pre'
define pass_postfix = 'post'

DECLARE
  sqltext  VARCHAR2(1000);
  l_result NUMBER;
  l_cursor INTEGER;
  l_cursors dbms_sql.Number_Table;
BEGIN
  -- modify db_links
  FOR cur IN
  (SELECT u.user_id,
    d.OWNER,
    d.DB_LINK,
    d.USERNAME,
    d.HOST
  FROM dba_db_links d,
    dba_users u
  WHERE d.owner=u.username
  )
  LOOP
    --parse the cursor only if we haven't already
    sqltext:= 'alter database link "'||cur.DB_LINK||'" connect to '||cur.USERNAME||' identified by &&pass_prefix'||cur.USERNAME||'&&pass_postfix';
    IF ( NOT l_cursors.exists(cur.user_id) ) THEN
      l_cursors(cur.user_id):=dbms_sys_sql.open_cursor;
      --parsing anonymous PL/SQL block as a job owner
      dbms_sys_sql.parse_as_user( c => l_cursors(cur.user_id), STATEMENT => sqltext, language_flag => dbms_sql.native, userid => cur.user_id );
    END IF;
    --bind the job number
    --remove the job by executing
    l_result:=sys.dbms_sys_sql.execute(l_cursors(cur.user_id));
  END LOOP;
END;
/

RMAN - restore process failed

On test server I had to restore database (3TB).
Due to network issue RESTORE (not recovery) process  failed. In normal situation I have to  start restore process from beginning once again but I already have almost 2.5 TB restored. How to use this data?

Database is in mount mode and in view V$DATAFILE_COPY we have information about file_id and datafile name (this information is needfull if OMF is used).

select 'set newname for datafile '||FILE#||' to '''||name||''';' from V$DATAFILE_COPY where name is not null order by file#;


This query generate set newname for all datafiles already restored.

run {

set until time "to_date('2015-01-05 00:11:00','yyyy-mm-dd hh24:mi:ss')";
<generated set newname> 

restore database;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
recover database DELETE ARCHIVELOG MAXSIZE 90G;
}

and if we run this script, rman continue restore process.

If in v$datafile_copy you don't have information for some datafiles, then RMAN not restored then already and for these datafiles you have to created fake name.

"ORA-01555: snapshot too old" after failover physical standby

Recently I received error on  test database:

ORA-01555: snapshot too old: rollback segment number 53 with name "_SYSSMU53_526582059$" too small

simple problem with a simple solution :)

but...

This  is a test database and almost nobody use this database. I checked a UNDO utilization and was on very low level.

I changed undo guarantee, undo retention and I added additional datafiles to UNDO tablespace.

Nothing help...

and now the background on how we prepare the test database.

This test database is prepared as I storage snapshot from standby. Before preparing snapshot recovery managed standby had been cancel

recover managed standby database cancel;

and stopped.
So everything should be OK. 

I thought that then problem can be with UNDO and I decided to recreate UNDO tablespace. I create new one and switched database to use new UNDO tablespace. After that I dropped old UNDO tablespace and I executed query once again.

and...

the same error but without indicating the UNDO segment.

ORA-01555: snapshot too old: rollback segment number  with name "" too small

In original query application use two sub-queries, I wanted to find exactly where is the problem.

In execution plan I found that optimizer for one of the sub-query use index. After eliminating this sub-query everything works :)

BINGO

I tried rebuild this index and once again ORA-01555, so I decided to drop index and create index once again. Successfully...

After that original query works perfectly

OMF and Physical Standby

In normal Data Guard configuration on primary and standby database I want use the same parameters, especially storage parameters eg "db_create_file_dest". This parameter determines default location
Oracle-managed datafiles.

Sometimes I need create tablespace with manually setting datafile name (diffrent directory) and if on standby database OMF is enabled then datafile is created in different directory than on primary - standby use  "db_create_file_dest" location and after that we have to disable recovery mode on standby and move datafile on OS level and rename datafile on standby.
But if before creating tablespace on primary I set  

alter system set db_create_file_dest="" scope=both;

then on standby, database create datafile in the same directory as on primary database.

ORA-08106: cannot create journal table

After killing the session, where rebuilding index was performed, sometimes get an error:
 
SQL> ALTER INDEX ind_example_1 rebuild partition DATA_PART1 online
*
ERROR at line 1:
ORA-08106: cannot create journal table TEST.SYS_JOURNAL_2918162


solution for this error is running procedure from DBMS_REPAIR package:


declare
isclean boolean;
begin
isclean := false;
while isclean = false
loop
isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN
(dbms_repair.all_index_id, dbms_repair.lock_wait);
dbms_lock.sleep (10);
end loop;
end;
/

Segment advisor - query

SELECT o.type AS object_type,
  o.attr1     AS schema,
  O.ATTR2     AS OBJECT_NAME,
  F.MESSAGE,
  ROUND(TO_NUMBER(SUBSTR(F.MORE_INFO,INSTR(F.MORE_INFO,':',1,1)+1,INSTR(F.MORE_INFO,':',1,2)-INSTR(F.MORE_INFO,':',1,1)-1))/(1024*1024)) ALLOCATED_SPACE,
  ROUND(TO_NUMBER(SUBSTR(F.MORE_INFO,INSTR(F.MORE_INFO,':',1,3)+1,INSTR(F.MORE_INFO,':',1,4)-INSTR(F.MORE_INFO,':',1,3)-1))/(1024*1024)) USED_SPACE,
  round(to_number(SUBSTR(f.more_info,INSTR(f.more_info,':',1,5)+1,INSTR(f.more_info,':',1,6)-INSTR(f.more_info,':',1,5)-1))/(1024*1024)) reclaim_space
FROM dba_advisor_findings f
JOIN DBA_ADVISOR_OBJECTS O
ON F.OBJECT_ID  = O.OBJECT_ID
AND F.TASK_NAME = O.TASK_NAME
where F.MESSAGE like '%shrink%'
ORDER BY 7 desc;

Monitoring RMAN backup

Query for EM 11 to monitoring all backups:

SELECT b.database_name                         AS "DB Name",
  t.type_qualifier1                            AS "Version",
  b.host                                       AS "Server Name" ,
  b.target_type                                AS "Type",
  TO_CHAR(b.start_time, 'YYYYMMDD-HH24:MI:SS') AS "Start Time",
  TO_CHAR(b.end_time, 'YYYYMMDD-HH24:MI:SS')   AS "End Time",
  b.input_type "Backup Info",
  b.time_taken_display   AS "Time taken",
  b.output_bytes_display AS "Final Size Bytes",
  status                 AS "Status"
FROM mgmt$ha_backup b,
  mgmt$target t
WHERE b.target_guid = t.target_guid
AND b.start_time    > sysdate -2
ORDER BY type_qualifier1,
  END_TIME ;

and progress restoring or backup from RMAN:

SELECT OPNAME,
  SOFAR                /TOTALWORK*100 PCT,
  TRUNC(TIME_REMAINING /60) MIN_RESTANTES,
  TRUNC(ELAPSED_SECONDS/60) MIN_ATEAGORA
FROM V$SESSION_LONGOPS
WHERE TOTALWORK>0
AND OPNAME LIKE '%RMAN%';

Purge materialized view log table

We found huge MV log (~14GB) on table which has 1G. The reason was that few months ago, after some maintenance we had to recreate materialized view (REFRESH FAST).

After recreating MV, log had two registered snapshots and leave data for first MV which was dropped. MV has been created to replicate data beatween two databases and inMV definition we using database links. After dropping MV,  information about registred MV had not been refreshed.

To purge log table from database which waiting for not existed MV I used dbms_mview.purge_mview_from_log procedure, but in first step I have to check which snapshot not exist (thanks Remek: http://remigium.blogspot.com/2013/08/the-orphaned-mview-registration-on.html)

SELECT s.mowner,
  s.master,
  s.snaptime,
  'exec dbms_mview.purge_mview_from_log('
  ||s.snapid
  ||');' fix1
FROM sys.slog$ s
WHERE NOT EXISTS
  (SELECT 1 FROM DBA_REGISTERED_SNAPSHOTS R WHERE S.SNAPID=R.SNAPSHOT_ID
  )
ORDER BY MOWNER,
  master;

and run purge_mview_from_log for not exited snapid.

After that all data for not existed MV will be remove, but only for this snap id, so we don't need run complete refresh for exited MV.

MV log table is a normall segment and it's no problem with shrinking segment by alter table MLOG$_EX_TABLE shrink space;

On our db after purging and shrinking, log table segment has 80MB.

SQL profile and invisible index

A few days ago we had a problem with the performance of several queries and we found that the problem is with queries which take different index than should be for some not bind variables.

So we decide set invisible parameter for this index. Before that I checked what queries use this index using v$sql_plan and I ran:

alter index index_owner.index_name INVISIBLE;

The list of queries that used this index there was one important query and after setting invisible for this index execution plan for this query start use different index and elapsed time increased almost 100 times. 

There was no possibility to add hint USE_INVISIBLE_INDEXES to this query directly, so I hat to use sql profile:

DECLARE
  clsql_text CLOB;
BEGIN
  SELECT sql_fulltext INTO clsql_text FROM v$sqlarea WHERE sql_id = '&SQL_ID';
  DBMS_SQLTUNE.IMPORT_SQL_PROFILE( sql_text => clsql_text, profile => sqlprof_attr('USE_INVISIBLE_INDEXES'), name => 'PROFILE_&SQL_ID', force_match => TRUE );
END;
/ 

Oracle File watcher - diagnostic

Filewatcher is a new scheduler object with job triggered by the arrival new file in specified location.
I don't want describe configuration, but how to get more information about how it's work.
Normally, there is no information why sometimes files are not processed. I have not found the table with logs.

For more information, we must enable trace level on database: 

alter system set events '27401 trace name context forever, level 262144';

After that our instance will create trace files for all new files in filewatcher location.

To diable this trace we have to do:

alter system set events '27401 trace name context off';

Audit queries on Read Only standby

I was asked about the possibility of audit queries on the physical standby database
 open in read mode.

Just turn on the audit select on primary  database:

AUDIT ALL BY TEST_USER BY ACCESS;
AUDIT SELECT TABLE BY TEST_USER BY ACCESS;

Standby is open in read only mode  and audit trails can't be written to the database since it's read only. So, where?

Audit trail automaticly switch to OS files when database is open in read only mode.

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
audit_trail                          string      OS
 

 Here is a example of audit trail file:

Thu May 16 11:57:09 2013 +03:00
LENGTH: "253"
SESSIONID:[10] "4294967295" ENTRYID:[2] "13" STATEMENT:[1] "8" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$N
AME:[4] "DUAL" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:30 2013 +03:00
LENGTH: "257"
SESSIONID:[10] "4294967295" ENTRYID:[2] "14" STATEMENT:[2] "11" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$
NAME:[7] "X$KCCDI" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:30 2013 +03:00
LENGTH: "258"
SESSIONID:[10] "4294967295" ENTRYID:[2] "15" STATEMENT:[2] "11" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$
NAME:[8] "X$KCCDI2" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:30 2013 +03:00
LENGTH: "262"
SESSIONID:[10] "4294967295" ENTRYID:[2] "16" STATEMENT:[2] "11" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$
NAME:[11] "GV$DATABASE" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:30 2013 +03:00
LENGTH: "261"
SESSIONID:[10] "4294967295" ENTRYID:[2] "17" STATEMENT:[2] "11" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$
NAME:[10] "V$DATABASE" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:30 2013 +03:00
LENGTH: "262"
SESSIONID:[10] "4294967295" ENTRYID:[2] "18" STATEMENT:[2] "11" USERID:[2] "TEST_USER" USERHOST:[12] "ro_standby" TERMINAL:[5] "pts/0" ACTION:[1] "3" RETURNCODE:[1] "0" OBJ$CREATOR:[3] "SYS" OBJ$
NAME:[11] "V_$DATABASE" OS$USERID:[6] "oracle" DBID:[10] "1234567890"

Thu May 16 11:57:32 2013 +03:00
LENGTH: "225"
SESSIONID:[10] "4294967295" ENTRYID:[1] "1" USERID:[2] "TEST_USER" ACTION:[3] "101" RETURNCODE:[1] "0" LOGOFF$PREAD:[1] "5" LOGOFF$LREAD:[3] "125" LOGOFF$LWRITE:[1] "0" LOGOFF$DEAD:[1] "0" DBID:[10
] "1234567890" SESSIONCPU:[1] "1"