Hi,
Today we had issue with one of the Query and we are using the dynamic sampling but when ever the query uses the AS of SCN then the dynamic sampling not kicked on.
No stats present on this Tables.
Database: tst_pre_eod/tst_pre_eod@mifextest3/mifex3
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 15 12:04:49 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.4.0 - Production
tst_pre_eod@MIFEX3> SELECT ParticipantID, BoardID, InstrumentID, MMListAction FROM
TIBEX_MEMMHybridAdmView as of scn 6148947776 WHERE MEGroupID = 'ME1'
ORDER BY Timestamp ASC 2 3
4 /
Execution Plan
----------------------------------------------------------
Plan hash value: 39089053
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 159 | 88 (3)| 00:00:02 |
| 1 | SORT ORDER BY | | 1 | 159 | 88 (3)| 00:00:02 |
| 2 | NESTED LOOPS | | 1 | 159 | 87 (2)| 00:00:02 |
|* 3 | HASH JOIN | | 60 | 3780 | 86 (2)| 00:00:02 |
|* 4 | TABLE ACCESS FULL | TIBEX_INSTRUMENT | 47 | 1598 | 59 (0)| 00:00:01 |
| 5 | SORT UNIQUE | | 2361 | 68469 | 26 (0)| 00:00:01 |
|* 6 | INDEX FAST FULL SCAN | XPKTIBEX_ADMINACK | 2361 | 68469 | 26 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| TIBEX_HYBRIDMMINSTRADMIN | 1 | 96 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | XPKTIBEX_HYBRIDMMINSTRADMIN | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."MEGROUPID"="C"."SERVERID")
4 - filter("B"."MEGROUPID"='ME1')
6 - filter("C"."SERVERID"='ME1')
7 - filter("A"."INSTRUMENTID"="B"."INSTRUMENTID")
8 - access("A"."ADMINID"="C"."ADMINID")
tst_pre_eod@MIFEX3> SELECT ParticipantID, BoardID, InstrumentID, MMListAction FROM
TIBEX_MEMMHybridAdmView WHERE MEGroupID = 'ME1'
ORDER BY Timestamp ASC; 2 3
Execution Plan
----------------------------------------------------------
Plan hash value: 1706923895
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 159 | 5 (20)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 159 | 5 (20)| 00:00:01 |
| 2 | NESTED LOOPS SEMI | | 1 | 159 | 4 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 130 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TIBEX_HYBRIDMMINSTRADMIN | 1 | 96 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| TIBEX_INSTRUMENT | 1 | 34 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | XPKTIBEX_INSTRUMENT | 1 | | 0 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | XPKTIBEX_ADMINACK | 134K| 3799K| 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("B"."MEGROUPID"='ME1')
6 - access("A"."INSTRUMENTID"="B"."INSTRUMENTID")
7 - access("A"."ADMINID"="C"."ADMINID" AND "C"."SERVERID"='ME1')
filter("B"."MEGROUPID"="C"."SERVERID")
Note
-----
- dynamic sampling used for this statement
Trace Generated
"mifex3_ora_17850_blksize.trc" 2110 lines, 89760 characters
/u01/app/oracle/admin/mifex3/udump/mifex3_ora_17850_blksize.trc
Oracle Database 10g Release 10.2.0.4.0 - Production
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db
System name: SunOS
Node name: mifextest3
Release: 5.10
Version: Generic_118855-36
Machine: i86pc
Instance name: mifex3
Redo thread mounted by this instance: 1
Oracle process number: 22
Unix process pid: 17850, image: oracle@mifextest3
*** 2010-09-15 13:42:39.879
*** ACTION NAME:() 2010-09-15 13:42:39.879
*** MODULE NAME:(SQL*Plus) 2010-09-15 13:42:39.879
*** SERVICE NAME:(mifex3) 2010-09-15 13:42:39.879
*** SESSION ID:(296.1403) 2010-09-15 13:42:39.879
Registered qb: SEL$1 0xfdaf5458 (PARSER)
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=5 objn=1028303 hint_alias="TIBEX_MEMMHYBRIDADMVIEW"@"SEL$1"
Registered qb: SEL$2 0xfdaf4908 (PARSER)
signature (): qb_name=SEL$2 nbfros=2 flg=0
fro(0): flg=4 objn=1027987 hint_alias="A"@"SEL$2"
fro(1): flg=4 objn=1027996 hint_alias="B"@"SEL$2"
Registered qb: SEL$3 0xfdaf3198 (PARSER)
signature (): qb_name=SEL$3 nbfros=1 flg=0
fro(0): flg=4 objn=1027815 hint_alias="C"@"SEL$3"
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM: Checking validity of predicate move-around in SEL$1 (#0).
CBQT: copy not possible because as-of table
in SEL$1 (#0)
CBQT bypassed for SEL$1 (#0): Cannot copy query block.
CBQT: Validity checks failed for du66qtvf1w957.
Query block (fffffd7ffdaf4908) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "A"."MMLISTACTION" "MMLISTACTION","A"."BOARDID" "BOARDID","A"."INSTRUMENTID" "INSTRUMENTID","A"."PARTICIPANTID" "PARTICIPANTID","A"."TIMESTAMP" "TIMESTAMP","B"."MEGROUPID
" "MEGROUPID" FROM TST_PRE_EOD."TIBEX_HYBRIDMMINSTRADMIN" AS OF SCN (:B1) "A",TST_PRE_EOD."TIBEX_INSTRUMENT" AS OF SCN (:B2) "B" WHERE "A"."INSTRUMENTID"="B"."INSTRUMENTID" AN
D EXISTS (SELECT 'X' FROM TST_PRE_EOD."TIBEX_ADMINACK" AS OF SCN (:B3) "C" WHERE "A"."ADMINID"="C"."ADMINID" AND "B"."MEGROUPID"="C"."SERVERID")
Query block (fffffd7ffdaf4908) unchanged
CVM: Considering view merge in query block SEL$1 (#0)
CVM: Checking validity of merging SEL$2 (#0)
CVM: Considering view merge in query block SEL$2 (#0)
CVM: Considering view merge in query block SEL$3 (#0)
CVM: Merging SPJ view SEL$2 (#0) into SEL$1 (#0)
Registered qb: SEL$F5BB74E1 0xfdaf5458 (VIEW MERGE SEL$1; SEL$2)
signature (): qb_name=SEL$F5BB74E1 nbfros=2 flg=0
fro(0): flg=0 objn=1027987 hint_alias="A"@"SEL$2"
/dynamic
0: invalid distribution method
sel - selectivity
ptn - partition
*******************************************
Peeked values of the binds in SQL statement
*******************************************
kkscoacd
Bind#0
oacdty=02 mxl=22(06) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0300 frm=00 csi=00 siz=24 off=0
kxsbbbfp=fffffd7ffda66090 bln=22 avl=06 flg=09
value=6148947776
Bind#1
oacdty=01 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0100 frm=01 csi=31 siz=32 off=0
kxsbbbfp=fffffd7ffda66048 bln=32 avl=03 flg=09
value="ME1"
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
cursor_sharing = force
optimizer_dynamic_sampling = 10
Query Block Registry:
*********************
SEL$3 0xfdaf3198 (PARSER)
SEL$1F949E82 0xfdaf5458 (SUBQUERY UNNEST SEL$F5BB74E1; SEL$3) [FINAL]
SEL$2 0xfdaf4908 (PARSER)
SEL$F5BB74E1 0xfdaf5458 (VIEW MERGE SEL$1; SEL$2)
SEL$1F949E82 0xfdaf5458 (SUBQUERY UNNEST SEL$F5BB74E1; SEL$3) [FINAL]
SEL$1 0xfdaf5458 (PARSER)
SEL$F5BB74E1 0xfdaf5458 (VIEW MERGE SEL$1; SEL$2)
...
Optimizer State Dump: call(in-use=53048, alloc=98184), compile(in-use=106840, alloc=157536)
Regards
NM