I just saw MOS Note
ORA-4031 After Upgrade From 11.2.0.1 To 11.2.0.3 With Leak in ‘KTC latch subh’ Subheaps [ID 1398984.1]
giving very little information about this bug. At the time of this writing, there is no patch available but I expect it to be in a few days. I have checked some 11.2.0.3 instances and found only one with > 60 MB for this subheap.
At one of my customers, we wondered about some entries in v$datafile.unrecoverable_change# for the most critical tablespace of the application. Certainly no application components were allowed to perform nologging operations for segments inside this tablespace and yet there was evidence that “something” was done with nologging.
CREATE TABLE "MYOWNER".DBMS_TABCOMP_TEMP_UNCMP tablespace "DAT_MYOWNER" nologging AS SELECT /*+ FULL("MYOWNER"."MYTAB") */ * FROM "MYOWNER"."MYTAB" sample block( 3.55) mytab ; CREATE TABLE "MYOWNER".DBMS_TABCOMP_TEMP_CMP ORGANIZATION HEAP TABLESPACE "DAT_MYOWNER" COMPRESS FOR ALL OPERATIONS NOLOGGING AS SELECT * FROM "MYOWNER".DBMS_TABCOMP_TEMP_UNCMP MYTAB; DROP TABLE "MYOWNER".DBMS_TABCOMP_TEMP_UNCMP PURGE; DROP TABLE "MYOWNER".DBMS_TABCOMP_TEMP_CMP purge; |
Summary:
I find it suboptimal that Compression Advisor is executed on a daily basis during the maintenance window and that the Compression Advisor can not be seperately disabled without also disabling the Segment Advisor. (Although there is supposed to be an enhancement request open for this.)
In addition to that, it is more than problematic that the is using the user tablespace, which could lead to tablespace pressure and that the operations are performed as unrecoverable and therefore logged in v$datafile.unrecoverable_time, overwriting potential important information of real “user” operations performing with NOLOGGING.
More information about Compression Advisor can be found in MOS Note: How Does Compression Advisor Work? [ID 1284972.1]
From time to time we hit Errors like ORA-600 or ORA-7445, which are triggered by specific SQL statements. For these cases, Oracle developed the SQL Repair Advisor. This is a short demonstration of utilizing the SQL Repair Advisor to avoid ORA-7445 caused by a SQL statement.
SQL and Error:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | SELECT EXTRACT(YEAR FROM date_new) jahr, EXTRACT(MONTH FROM date_new) monat, COUNT(DISTINCT TRUNC(date_new)) arbeitstage, SUM(CASE WHEN STRING1 IN (SELECT STRING1 FROM MD.MD1 WHERE STRING1_GROUP = 'CUST') THEN DECODE(FLAG, 'J', 1, 0) ELSE 0 END) cust_flag FROM SCHEMA.TAB1 WHERE date_new BETWEEN TO_DATE(201101, 'yyyymm') AND ADD_MONTHS(TO_DATE(201107, 'yyyymm'), 1) GROUP BY EXTRACT(YEAR FROM date_new), EXTRACT(MONTH FROM date_new) ORDER BY 1,2; ERROR at line 6: ORA-03113: end-of-file ON communication channel Process ID: 7746 SESSION ID: 737 Serial NUMBER: 9823 Tue Jul 26 15:19:41 2011 Exception [TYPE: SIGSEGV, Address NOT mapped TO object] [ADDR:0xA] [PC:0x888A3C9, xtyqbcb()+413] ... ORA-07445: exception encountered: core dump [xtyqbcb()+413] [SIGSEGV] [ADDR:0xA] [PC:0x888A3C9]... |
Execution of SQL Repair Advisor:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 | DECLARE rep_out CLOB; t_id VARCHAR2(50); BEGIN T_ID := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK( sql_text => q'#SELECT EXTRACT(YEAR FROM date_new) jahr, EXTRACT(MONTH FROM date_new) monat, COUNT(DISTINCT TRUNC(date_new)) arbeitstage, SUM(CASE WHEN STRING1 IN (SELECT STRING1 FROM MD.MD1 WHERE STRING1_GROUP = 'CUST') THEN DECODE(FLAG, 'J', 1, 0) ELSE 0 END) cust_flag FROM SCHEMA.TAB1 WHERE date_new BETWEEN TO_DATE(201101, 'yyyymm') AND ADD_MONTHS(TO_DATE(201107, 'yyyymm'), 1) GROUP BY EXTRACT(YEAR FROM date_new), EXTRACT(MONTH FROM date_new) ORDER BY 1,2#', task_name => 'ORA7445-xtyqbcb', problem_type =>DBMS_SQLDIAG.PROBLEM_TYPE_COMPILATION_ERROR ); DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK (t_id); rep_out := DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK (t_id, DBMS_SQLDIAG.TYPE_TEXT); DBMS_OUTPUT.PUT_LINE ('Report : ' || rep_out); END; / <strong>Output:</strong> Report : GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : ORA7445-xtyqbcb Tuning Task Owner : SYS Workload TYPE : Single SQL Statement Scope : COMPREHENSIVE TIME LIMIT(seconds): 1800 Completion STATUS : COMPLETED Started at : 07/26/2011 15:46:38 Completed at : 07/26/2011 15:46:38 ------------------------------------------------------------------------------- Schema Name: SYS SQL ID : 39ac71hjcn0rt ... ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- SQL Patch Finding (see EXPLAIN plans SECTION below) ------------------------------------------------------ A potentially better execution plan was found FOR this statement. Recommendation -------------- - Consider accepting the recommended SQL patch. EXECUTE dbms_sqldiag.accept_sql_patch(task_name => 'ORA7445-xtyqbcb', task_owner => 'SYS', REPLACE => TRUE); Rationale --------- Recommended plan WITH hash VALUE 1503213169 has NUMBER OF ROWS 7, CHECK SUM 13925076401, execution TIME 264 AND 1972 buffer gets ------------------------------------------------------------------------------- PL/SQL PROCEDURE successfully completed. |
Activation:
1 2 3 4 5 6 | BEGIN dbms_sqldiag.accept_sql_patch( task_name =>'ORA7445-xtyqbcb', task_owner => 'SYS', REPLACE => TRUE); END; / |
Test:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | SELECT EXTRACT(YEAR FROM date_new) jahr, EXTRACT(MONTH FROM date_new) monat, COUNT(DISTINCT TRUNC(date_new)) arbeitstage, SUM(CASE WHEN STRING1 IN (SELECT STRING1 FROM MD.MD1 WHERE STRING1_GROUP = 'CUST') THEN DECODE(FLAG, 'J', 1, 0) ELSE 0 END) cust_flag FROM SCHEMA.TAB1 WHERE date_new BETWEEN TO_DATE(201101, 'yyyymm') AND ADD_MONTHS(TO_DATE(201107, 'yyyymm'), 1) GROUP BY EXTRACT(YEAR FROM date_new), EXTRACT(MONTH FROM date_new) ORDER BY 1,2; JAHR MONAT ARBEITSTAGE CUST_FLAG ---------- ---------- ----------- ---------------- 2011 1 20 0 7 ROWS selected. SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ADVANCED')); PLAN_TABLE_OUTPUT --------------------- SQL_ID 5wdztx81x0r8f, child NUMBER 0 ------------------------------------- Plan hash VALUE: 1503213169 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes |TempSpc| Cost (%CPU)| TIME | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 591 (100)| | |* 1 | TABLE ACCESS FULL | MD1 | 1 | 12 | | 3 (0)| 00:00:01 | | 2 | SORT GROUP BY | | 27402 | 294K| 2776K| 591 (12)| 00:00:02 | |* 3 | TABLE ACCESS FULL| TAB1 | 117K| 1260K| | 369 (10)| 00:00:01 | ----------------------------------------------------------------------------------------------- ... Note ----- - SQL patch "SYS_SQLPTCH_013166b4fb2a0001" used FOR this statement 66 ROWS selected. |
Removal of SQL Patch:
1 | EXEC DBMS_SQLDIAG.DROP_SQL_PATCH ('SYS_SQLPTCH_013166b4fb2a0001'); |
Deletion of SQL Diag Task:
1 | EXEC DBMS_SQLDIAG.DROP_DIAGNOSIS_TASK ('ORA7445-xtyqbcb'); |
I have been troubleshooting a performance issue in a DWH environment, which is quite interesting. It was a query on DBA_SEGMENTS in 11gR2 with lots of partitions and it was taking almost 10 minutes versus only a few seconds in 10gR2. The problem could be stripped downt to this SQL query:
select bytes from dba_segments
The dba_segment view can be seen in DBA_VIEWS:
SELECT ... decode(bitand(segment_flags, 131072), 131072, blocks, (decode(bitand(segment_flags,1),1, <strong>dbms_space_admin.segment_number_blocks</strong>(tablespace_id, relative_fno, header_block, segment_type_id, buffer_pool_id, segment_flags, segment_objd, blocks), blocks)))*blocksize, ... FROM sys_dba_segs |
The response time was dominated by Waits for “db file sequential reads” taking almost all of the response time.
11gR2:
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 12563 23.17 546.74 157447 580414 1 188421 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12565 23.17 546.74 157447 580414 1 188421 |
10gR2:
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.06 0.06 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 5104 1.77 2.54 0 96688 0 76542 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5106 1.83 2.61 0 96688 0 76542 |
The 10046 Trace File contains more data regarding those waits:
WAIT #4: nam='db file sequential read' ela= 134 file#=21 block#=2313482 blocks=1 obj#=-1 WAIT #4: nam='db file sequential read' ela= 120 file#=15 block#=2128019 blocks=1 obj#=-1 WAIT #4: nam='db file sequential read' ela= 128 file#=21 block#=2313490 blocks=1 obj#=-1 WAIT #4: nam='db file sequential read' ela= 372 file#=21 block#=2313498 blocks=1 obj#=-1 WAIT #4: nam='db file sequential read' ela= 108 file#=21 block#=2313506 blocks=1 obj#=-1 WAIT #4: nam='db file sequential read' ela= 130 file#=21 block#=2313514 blocks=1 obj#=-1 WAIT #4: nam='db file sequential read' ela= 132 file#=21 block#=2313522 blocks=1 obj#=-1 WAIT #4: nam='db file sequential read' ela= 121 file#=21 block#=2313530 blocks=1 obj#=-1 WAIT #4: nam='db file sequential read' ela= 158 file#=15 block#=2128003 blocks=1 obj#=-1 WAIT #4: nam='db file sequential read' ela= 146 file#=21 block#=2313538 blocks=1 obj#=-1 WAIT #4: nam='db file sequential read' ela= 116 file#=21 block#=2313546 blocks=1 obj#=-1 (Output modified for formatting) |
Strange thing that obj# is -1. I made some block dumps of those blocks and found out that all those blocks (file#/block#) have something in common: They were all segment header blocks. Another strange thing was that when the query was executed a second time, all those I/O requests were performed again – so no caching.
At that point, I opened a Support service request. After a couple of weeks, Support suggested running this for each tablespace of the user segments:
EXEC dbms_space_admin.TABLESPACE_FIX_SEGMENT_EXTBLKS('<tablespace_name>'); |
Finally this fixed the problem. Now, we are trying to find out why this was necessary for a database, that was freshly created with dbca 11.2.0.2 from the seeded template and filled with data pump import.
Murphy said: if problems arise, they all come at the same time. After several weeks of stability, this week brought hell of a lot of Oracle problems with 11.2.0.2 and RAC. To list the worst:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 8667 ora11 25 0 8423m 33m 28m R 99.7 0.1 358:27.78 ora_pz98_MDDB
I then checked different databases and found that almost all of our 11.2.0.2 databases have several hundreds or even thousands of child cursors.
SELECT sql_id, is_shareable, MIN(child_number), MAX(child_number), COUNT(*) FROM gv$sql GROUP BY sql_id, is_shareable HAVING COUNT(*) >100 ORDER BY 6 DESC SQL_ID IS_SHAREABLE MIN(CHILD_NUMBER) MAX(CHILD_NUMBER) COUNT(*) ------------- ------------ ----------------- ------------------ --------- c7kt3njhnmtkm Y 0 5097 1397 c7kt3njhnmtkm N 3 3544 1836 1vnhgmpc17vv0 Y 0 3022 2697 1vnhgmpc17vv0 N 6 2185 444 93qh89pxuxzuw Y 0 1949 1522 93qh89pxuxzuw N 2 1625 428 5fk0v8km2f811 Y 0 1281 1763 4f3ufvfcgfqsg Y 0 792 794 cjbwk0ww7j5rv Y 0 627 1251 dyqdzgxcv4d3t Y 0 626 1252 5fk0v8km2f811 N 3 543 260 f0jxh8d6b5af2 Y 0 494 564 f0jxh8d6b5af2 N 0 290 130 dbvkky621gqtr Y 0 266 267 32rqmpqpfv0gm Y 0 255 257 g9uwxs7pr8tjm Y 0 254 257 40k6jjt90n4fa Y 3 199 129 |
I suspect this to be Bug 10182051 Extended cursor sharing generates many shareable child cursors and there is a workaround:
alter system set “_optimizer_extended_cursor_sharing_rel”=none;
SQL> select count(*) from gv$session 2 ; select count(*) from gv$session * ERROR at line 1: ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 1 allocated ORA-12801: error signaled in parallel query server P001, instance 3599
Currently no other known workaround than bouncing all the RAC instances.
ARC7: Creating remote archive destination LOG_ARCHIVE_DEST_2: 'HAMDB' (thread 2 SEQUENCE 4044) (MUCDB2) ARC7: Archive log rejected (thread 2 SEQUENCE 4044) at host 'HAMDB' FAL[server, ARC7]: FAL archive failed, see trace file. ARCH: FAL archive failed. Archiver continuing ORACLE Instance MUCDB2 - Archival Error. Archiver continuing. |
On standby side, it says:
Media Recovery Waiting for thread 2 sequence 4044 (in transit)
The standby database never recovers from this problem, except when standby database is bounced. The problem appears with and without broker configuration. Currently there is no known workaround.
Maybe some of this issues will be addressed in upcoming PSU April, which will be released this week.
Just a short note:
If you are using 11.2.0.2 and 11.2.0.2.1, chances are high that database stability is endangered because of massive ORA-600 [ktcdso-1], unless you have recyclebin deactived with init.ora recyclebin=off.
Oracle is currently working on this issue with Bug 10427260: ORA-00600 [KTCDSO-1], [], [], [] WHEN PURGE RECYCLE BIN.
Unfortunately, the instance has to be bounced in order to deactivate recyclebin.
When you create an ACFS Filesystem in Grid Infrastructure 11.2.0.1 or 11.2.0.2, the filesystem root directory group ownership is set to Group SS_ASM_GRP, e.g. asmadmin.
1 2 3 4 5 6 7 8 9 10 11 12 | [grid@md1 ~]$ cd /opt/oracle/gridbase/acfsmounts [grid@md1 acfsmounts]$ ls -al total 12 drwxr-xr-x 3 grid oinstall 4096 Jan 10 09:44 . drwxr-xr-x 10 grid oinstall 4096 Jan 10 09:43 .. drwxrwx--- 4 root asmadm 4096 Jan 10 09:44 data_testvol SQL> select * from v$asm_filesystem where fs_name = '/opt/oracle/gridbase/acfsmounts/data_testvol' FS_NAME AVAILABLE BLOCK_SIZE STATE CORRU NUM_VOL TOTAL_SIZE TOTAL_FREE TOTAL_SNAP_SPACE_USAGE ---------------------------------------------- --------- ---------- ------------- ----- ---------- ---------- ---------- ---------------------- /opt/oracle/gridbase/acfsmounts/data_testvol 10-JAN-11 4 AVAILABLE FALSE 1 256 119.769531 0 |
If – for whatever reason – you change the group ownership from asmadm to a different group, ASM can not populate the views v$asm_filesystem and v$asm_acfsvolumes which in turn means that you can not monitor the filesystem with Oracle Enterprise Manager Grid Control because it uses those 2 views for monitoring.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [root@md1 data_testvol]# chgrp myapp . [root@md1 data_testvol]# ls -la total 80 drwxrwx--- 4 root myapp 4096 Jan 10 09:45 . drwxr-xr-x 3 grid oinstall 4096 Jan 10 09:44 .. drwxr-xr-x 5 root root 4096 Jan 10 09:44 .ACFS -rw-r--r-- 1 root asmadm 610 Jan 10 09:45 .fslimit drwx------ 2 root root 65536 Jan 10 09:44 lost+found SQL> select * from v$asm_filesystem where fs_name = '/opt/oracle/gridbase/acfsmounts/data_testvol' 2 ; no rows selected |
From my point of view, this is a severe limitation. ACFS Filesystems, should like any other filesystem, be able to allow any user/group ownership and still be able to monitor it. However, I could not convince my oracle support engineer to see it the same way…
Just a short information: I will be holding a presentation at DOAG Conference 2010 in Nürnberg about “Minimal Downtime Oracle Database 11g Upgrades“. The presentation will be on Tuesday, 16.11.2010 at 10:00. The presentation will be in german. This is a short preview in german:
Nachdem der Premier Support für das Datenbank-Release 10gR2 im Juli 2010 endet und nur das erste Jahr des Extended Supports kostenfrei ist, stehen viele Kunden vor dem Problem des Upgrades von Oracle 10g nach 11gR1 oder 11gR2 bis spätestens Juli 2011. Dieser Vortrag zeigt Möglichkeiten, den Oracle-Upgrade mittels Logical Standby sowie mittels Transportable Tablespaces mit minimaler Downtime durchzuführen. Es wird aufgezeigt, welche Voraussetzungen für die beiden Alternativen jeweils benötigt werden sowie die potentiellen Probleme bei der Durchführung. Der Vortrag enthält zudem einen praktischen Teil, bei dem die beiden Varianten demonstriert werden.
You can find the presentation materials (including online demo) on the presentations page.
The addition of multicast with 11.2.0.2 caused a lot of troubles and confusion. See the following articles regarding that:
I just saw that Oracle has published a note on October 25th, which says that there will be a patch 9974223 (not yet public), which changes the multicast address from 230.0.1.0 to 224.0.0.251.
See: How to Validate Network and Name Resolution Setup for the Clusterware and RAC [ID 1054902.1]
I found it very interesting that in our current environment, the test script mcast1 fails for 230.0.1.0, but succeeds for 224.0.0.251. Other than that, the suspected host route for multicast is not even necessary at all.
So, the suggested method from Blog Article http://www.ora-solutions.net/web/2010/09/29/upgradeinstall-of-grid-infrastructure-to-11-2-0-2-is-failing/ is not necessary. It simply turned out that the Java test script is not appropriate for testing, but mcast1.c is.
Update – 10.11.2010
Patch 9974223 is officially available for Linux x86-64.
At a customer site, I came across a problem of the Oracle cost based optimizer with selectivity calculation with a composite index with one column sorted descending. The following testcase describes the issue. Currently Oracle development is working on the issue. During testing on 11.2.0.1, I realized that the new cardinality feedback issue takes care of the problem and subsequent executions are using the correct plan.
-- drop table if exists DROP TABLE ADMDECKER.DUMMY purge; -- create table CREATE TABLE ADMDECKER.DUMMY ( a NUMBER NOT NULL, B NUMBER NOT NULL, C NUMBER NOT NULL, D NUMBER NOT NULL, E NUMBER ) NOLOGGING; -- fill table INSERT /*+ APPEND */ INTO ADMDECKER.DUMMY SELECT rownum AS a, MOD(rownum,100) AS B, MOD(rownum,10000) AS C, 1 AS D, 234 AS E FROM ( SELECT rownum r FROM (SELECT rownum r FROM dual CONNECT BY rownum <= 100) a, (SELECT rownum r FROM dual CONNECT BY rownum <= 100) b, (SELECT rownum r FROM dual CONNECT BY rownum <= 100) c WHERE rownum <= 1000000 ); commit; -- create indexes CREATE INDEX ADMDECKER.DUMMY_IDX1 ON ADMDECKER.DUMMY(C,B) nologging; CREATE INDEX ADMDECKER.DUMMY_IDX2 ON ADMDECKER.DUMMY(B DESC,a) nologging; CREATE INDEX ADMDECKER.DUMMY_IDX3 ON ADMDECKER.DUMMY(C,B,D) nologging; CREATE INDEX ADMDECKER.DUMMY_IDX4 ON ADMDECKER.DUMMY(B,a) nologging; -- gather statistics (including tab stats for virtual hidden column for "B desc" column. BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME=>'ADMDECKER', TABNAME=>'DUMMY', METHOD_OPT=>'FOR ALL COLUMNS SIZE 1', cascade=>TRUE, ESTIMATE_PERCENT=>100); END; / SELECT * FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 'DUMMY'; SELECT * FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'DUMMY'; -- note min/max/density values for system generated virtual hidden column SELECT * FROM DBA_TAB_COLS WHERE TABLE_NAME = 'DUMMY'; SELECT * FROM dba_ind_statistics WHERE TABLE_NAME = 'DUMMY'; |
I enable 10053 tracing and execute the query. Afterwards I display the execution plan.
SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1'; SESSION altered. SELECT /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) FROM ADMDECKER.DUMMY WHERE C = 9592 AND B >= 93 AND D=1; COUNT(E) ---------- 0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +COST')); PLAN_TABLE_OUTPUT --------------------- SQL_ID f5mkaprk7fzt8, child NUMBER 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) FROM ADMDECKER.DUMMY WHERE C = 9592 AND B >= 93 AND D=1 Plan hash VALUE: 1903509519 ------------------------------------------------------------------------------------------------ | Id |Operation |Name |E-ROWS| Cost (%CPU)|A-ROWS| A-TIME | Buffers | ------------------------------------------------------------------------------------------------ | 0|SELECT STATEMENT | | | 4 (100)| 1|00:00:00.07 | 23437 | | 1| SORT AGGREGATE | | 1| | 1|00:00:00.07 | 23437 | |* 2| TABLE ACCESS BY INDEX ROWID|DUMMY | 7| 4 (0)| 0|00:00:00.07 | 23437 | |* 3| INDEX RANGE SCAN |DUMMY_IDX2| 1| 3 (0)| 70000|00:00:00.03 | 197 | ------------------------------------------------------------------------------------------------ Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 2 - FILTER(("C"=9592 AND "D"=1)) 3 - access("DUMMY"."SYS_NC00006$"<=HEXTORAW('3EA1FF') ) FILTER(SYS_OP_UNDESCEND("DUMMY"."SYS_NC00006$")>=93) |
The complete 10053 can be viewed here: 10053 Trace of bad plan.
It can be seen that the execution plan contains the following statement and uses a very, very low density.
Using prorated density: 0.000002 of col #6 as selectvity of out-of-range/non-existent value pred
So, instead of using Index DUMMY_IDX3, which contains all three predicate columns, especially more selective equality predicate “C = 9592”, it uses quite unselective index DUMMY_IDX2, only because density is calculated wrongly.
If the statement is executed a second time, the right plan is used. Cardinality feedback feature kicks in here. Please note the higher costs and higher cardinality for index range scan at row source operation id 3.
CONNECT ADmdecker Enter password: Connected. ALTER SESSION SET EVENTS='10053 trace name context forever, level 1'; SESSION altered. SELECT /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) FROM ADMDECKER.DUMMY WHERE C = 9592 AND B >= 93 AND D=1; COUNT(E) ---------- 0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +COST')); PLAN_TABLE_OUTPUT ---------------------- SQL_ID f5mkaprk7fzt8, child NUMBER 1 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) FROM MDECKER.DUMMY WHERE C = 9592 AND B >= 93 AND D=1 Plan hash VALUE: 1171978535 ------------------------------------------------------------------------------------------------- | Id |Operation |Name |E-ROWS| Cost (%CPU)|A-ROWS | A-TIME |Buffers | ------------------------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | | 11 (100)| 1 |00:00:00.01 | 3 | | 1| SORT AGGREGATE | | 1 | | 1 |00:00:00.01 | 3 | | 2| TABLE ACCESS BY INDEX ROWID|DUMMY | 7 | 11 (0)| 0 |00:00:00.01 | 3 | |* 3| INDEX RANGE SCAN |DUMMY_IDX3 | 7 | 3 (0)| 0 |00:00:00.01 | 3 | ------------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 3 - access("C"=9592 AND "B">=93 AND "D"=1) FILTER("D"=1) Note ----- - cardinality feedback used FOR this statement |
The complete 10053 of the new plan (using cardinality feedback feature) can be viewed here: 10053 Trace of good plan using cardinality feedback. More information about this little documented new feature can be found at Dion Cho´s blog and from this Oracle paper.
Update: 06.10.2011
Oracle Support has provided Patch 11072246, which fixes a bug in DBMS_STATS on how min/max values are set for hidden desc columns. After installing the patch, the bug is gone and the execution plan is optimal.
