Dynamic Sampling and Partitioning

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 &lt;= 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?

About these ads

One Response to Dynamic Sampling and Partitioning

  1. dombrooks says:

    Apologies – had some formatting issues

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 68 other followers

%d bloggers like this: