data corruption between x$ash and its fixed index (v11.2)
- From: "Henry Poras" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "hrp@xxxxxxxxxx" for DMARC)
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 4 Aug 2014 15:23:37 -0400
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 @hya1db2 > select count(*) from x$ash a, x$kewash k where
a.need_awr_sample='Y';
COUNT(*)
--------------
0
SYS @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 @hya1db2 > explain plan for select count(*) from x$ash a, x$kewash k
where a.need_awr_sample='Y';
Explained.
SYS @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 @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 @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 @hya1db2 > explain plan for select count(*) from x$ash a where
a.need_awr_sample='Y';
Explained.
SYS @hya1db2 >
SYS @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 @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
Other related posts:
- » data corruption between x$ash and its fixed index (v11.2) - Henry Poras
