data corruption between x$ash and its fixed index (v11.2)
Date: Mon, 4 Aug 2014 15:23:37 -0400
Message-ID: <CAAVg4uKmzeRP9G_g=pQZ8p=VRtXr7d_jZpHipj1nN5+wpps0pA_at_mail.gmail.com>
I had a database which was giving me timeouts on its auto AWR snapshots. On researching this issue (cause was a non-default nls_sort and nls_comp setting) I found a different issue which appears to be corruption between x$ash and its fixed index. With standard nls settings this corruption won't be noticeable as the data is typically just accessed via the index.
Here is an easy way to see the problem (these queries came from stripped down versions of the query used to populate dba_hist_active_session_history from v$ash):
SYS _at_hya1db2 > select count(*) from x$ash a, x$kewash k where a.need_awr_sample='Y';
COUNT(*)
0
SYS _at_hya1db2 > select count(*) from x$ash a, x$kewash k where a.need_awr_sample='Y' and a.sample_id=k.sample_id and a.sample_addr=k.sample_addr;
COUNT(*)
178,709
Wait a minute!!! We add two join conditions (not outer join) and go from 0 to almost 200,000 records??? Let’s look at the execution plans.
SYS _at_hya1db2 > explain plan for select count(*) from x$ash a, x$kewash k where a.need_awr_sample='Y';
Explained.
SYS _at_hya1db2 > select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 4001314014
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 2 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
| 2 | MERGE JOIN CARTESIAN| | 100 | 200 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$ASH | 1 | 2 | 0 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 100 | | 0 (0)| 00:00:01 |
| 5 | FIXED TABLE FULL | X$KEWASH | 100 | | 0 (0)| 00:00:01 |
Predicate Information (identified by operation id):
3 - filter("A"."NEED_AWR_SAMPLE"='Y')
SYS _at_hya1db2 > explain plan for select count(*) from x$ash a, x$kewash k where a.need_awr_sample='Y' and a.sample_id=k.sample_id and a.sample_addr=k.sample_addr;
Explained.
SYS _at_hya1db2 > select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 2317177117
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 54 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 54 | | |
| 2 | NESTED LOOPS | | 1 | 54 | 0 (0)| 00:00:01 |
| 3 | FIXED TABLE FULL | X$KEWASH | 100 | 2600 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$ASH (ind:1) | 1 | 28 | 0 (0)| 00:00:01 |
Predicate Information (identified by operation id):
4 - filter("A"."NEED_AWR_SAMPLE"='Y' AND "A"."SAMPLE_ID"="K"."SAMPLE_ID"
AND
"A"."SAMPLE_ADDR"="K"."SAMPLE_ADDR")
SYS _at_hya1db2 > explain plan for select count(*) from x$ash a where
a.need_awr_sample='Y';
Explained.
SYS _at_hya1db2 >
SYS _at_hya1db2 > select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 2196106623
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 2 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | FIXED TABLE FULL| X$ASH | 1 | 2 | 0 (0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - filter("A"."NEED_AWR_SAMPLE"='Y')
SYS _at_hya1db2 > select * from v$indexed_fixed_column where table_name = 'X$ASH'; TABLE_NAME INDEX_NUMBER COLUMN_NAME COLUMN_POSITION
------------------------------ ------------ ------------------------------
X$ASH 1 NEED_AWR_SAMPLE 2 X$ASH 1 SAMPLE_ID 1 X$ASH 1 SAMPLE_ADDR 0
The fixed index is comprised of sample_addr, sample_id, need_awr_sample. We need the join conditions in order to hit the index. When we access data via the index, records are returned. When we access data via the x$ table, no records are returned.
The dba_hist_active_sess_history table remains empty even after a manual snapshot because of an underlying Oracle bug arising from data discrepency between an x$ table and its associated fixed index.
I have opened an SR with Oracle, but they are just plodding along.
Does anyone know how to get a dump of an x$ table and its index?
Henry
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Aug 04 2014 - 21:23:37 CEST