Dynamic Sampling and Partitioning
January 7, 2010 1 Comment
A question on OTN asked about the interaction between dynamic sampling and partitioning, what difference does partitioning make to level 2 dynamic sampling?
To recap on dynamic sampling levels, level 2 says that dynamic sampling should be applied to all unanalysed tables with the number of sampled blocks being twice the default (32).
Asif Momen and Randolf Geist have two interesting studies showing how different variations on table, partition and subpartition statistics affect whether dynamic sampling is used at all – i.e. partitioning introduces a qualification/variation to that definition above of all unanalysed tables and affects whether dynamic sampling is eligible.
The other element to the question is, the above aside, once we’re in a situation where dynamic sampling is valid and eligible, is there any effect of partitioning on dynamic sampling? I.e. does dynamic sampling do anything special for a partitioned table?
The question is answered by looking at a 10046 trace which reveals how dynamic sampling does it’s stuff.
Taking, if he’ll excuse me, the table from Randolf’s post and the first query:
1 CREATE TABLE wr_test 2 ( test_id 3 , trade_date 4 , CONSTRAINT test_pk PRIMARY KEY (trade_date, test_id) using index local) 5 PARTITION BY RANGE (trade_date) 6 ( PARTITION p_jan VALUES LESS THAN (DATE '2009-02-01') 7 , PARTITION p_feb VALUES LESS THAN (DATE '2009-03-01') 8 , PARTITION p_mar VALUES LESS THAN (DATE '2009-04-01') ) 9 AS 10 SELECT ROWNUM AS test_id 11 , DATE '2009-02-01' + trunc(dbms_random.value(0, 59)) as trade_date 12 FROM dual 13* CONNECT BY LEVEL <=1000 / Table created. SQL>alter session set events '10046 trace name context forever, level 12'; Session altered. SQL> explain plan for 2 select * from wr_test where trade_date = date '2009-03-01'; Explained.
Then looking at the trace file (handy hint):
SQL> l 1 select value ||'/'||(select instance_name from v$instance) ||'_ora_'|| 2 (select spid||case when traceid is not null then '_'||traceid else null end 3 from v$process 4 where addr = (select paddr 5 from v$session 6 where sid = (select sid 7 from v$mystat 8 where rownum = 1))) || '.trc' tracefile 9 from v$parameter 10* where name = 'user_dump_dest' SQL> / TRACEFILE -------------------------------------------------------------------------------- /opt/oracle/admin/DOM10gR2/udump/DOM10gR2_ora_428.trc SQL>
If we run the trace file through TKPROF for readability then we can find the following queries from the dynamic sampling:
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0)
FROM
(SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("WR_TEST") FULL("WR_TEST")
NO_PARALLEL_INDEX("WR_TEST") */ 1 AS C1, CASE WHEN "WR_TEST"."TRADE_DATE"=
TO_DATE(' 2009-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') THEN 1 ELSE 0 END
AS C2 FROM "WR_TEST" "WR_TEST" WHERE "WR_TEST"."TRADE_DATE"=TO_DATE('
2009-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) SAMPLESUB
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 1 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 4 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=4 pr=0 pw=0 time=503 us)
542 PARTITION RANGE SINGLE PARTITION: 3 3 (cr=4 pr=0 pw=0 time=610 us)
542 TABLE ACCESS FULL WR_TEST PARTITION: 3 3 (cr=4 pr=0 pw=0 time=64 us)
********************************************************************************
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param('parallel_execution_enabled',
'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE
*/ NVL(SUM(C1),0), NVL(SUM(C2),0), NVL(SUM(C3),0)
FROM
(SELECT /*+ NO_PARALLEL("WR_TEST") INDEX("WR_TEST" TEST_PK)
NO_PARALLEL_INDEX("WR_TEST") */ 1 AS C1, 1 AS C2, 1 AS C3 FROM "WR_TEST"
"WR_TEST" WHERE "WR_TEST"."TRADE_DATE"=TO_DATE(' 2009-03-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND ROWNUM <= 2500) SAMPLESUB
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.01 2 4 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.02 2 4 0 2
Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=2 pr=1 pw=0 time=15881 us)
24 VIEW (cr=2 pr=1 pw=0 time=15878 us)
24 COUNT STOPKEY (cr=2 pr=1 pw=0 time=15874 us)
24 PARTITION RANGE SINGLE PARTITION: 3 3 (cr=2 pr=1 pw=0 time=15849 us)
24 INDEX RANGE SCAN TEST_PK PARTITION: 3 3 (cr=2 pr=1 pw=0 time=15821 us)(object id 51602)
and you can repeat the steps with an unpartitioned table, e.g.
SQL> ed Wrote file afiedt.buf 1 CREATE TABLE wr_test 2 ( test_id 3 , trade_date 4 , CONSTRAINT test_pk PRIMARY KEY (trade_date, test_id) using index) 5 AS 6 SELECT ROWNUM AS test_id 7 , DATE '2009-02-01' + trunc(dbms_random.value(0, 59)) as trade_date 8 FROM dual 9* connect by level <= 1000 / Table created.
The point being that the queries are the same for both the partitioned and unpartitioned table – there’s no special treatment – although the access plans for these recursive queries are going to be different due to the partitioning.
As an aside, what prevents us from entering an infinite loop of dynamic sampling of the dynamic sampling queries?
Apologies – had some formatting issues