Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: explain plan assistance needed

Re: explain plan assistance needed

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Mon, 04 Jun 2007 19:05:08 -0700
Message-ID: <1181009108.951310.164820@w5g2000hsg.googlegroups.com> 





On Jun 4, 9:10 pm, pfa <p..._at_falson.net> wrote:


> Ok, my lack of knowledge is shining through here :-)....

>

> I turned off the auto-tune and set the stats level to BASIC. Still

> getting the IRS as opposed to FTS before gathering stats. I tried:

>

> sqlplus system/<passwd>

> ...

> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1'

> /

> ALTER SESSION SET CURRENT_SCHEMA="SCOTT"


> /

> ...<rest of script>

>

> However, I'm lost looking at the trace.

>

> I realize that ideally you'd want to see the index being used but I'm

> actually trying to show that a gather_stats is needed at least on some

> occasions and I'm struggling to produce a simple example.

>

> I also tried on 9.2.0.4.0 (again, I changed the statistics_level to

> 'BASIC')


>

> If there's a simple explanation of what I can change to get the

> results I'd appreciate it (e.g. init.ora settings perhaps).

>

> If it's a matter of interpretting the resulting trace file to get the

> answers I need then I'll accept I have some reading to do.




Here is a quick example:


Create a sample table:


CREATE TABLE T6(



  C1 VARCHAR2(10),



  C2 VARCHAR2(10),



  C3 DATE,



  PRIMARY KEY (C1));


Create an index on the date column:


CREATE INDEX IND_T6 ON T6(C3);


See if the indexes have been analyzed:


SELECT



  INDEX_NAME,



  LAST_ANALYZED



FROM



  USER_INDEXES



WHERE



  TABLE_NAME='T6';



INDEX_NAME                     LAST_ANAL
------------------------------ ---------


SYS_C008022



IND_T6


Insert 60,000 rows into the sample table:
INSERT INTO



  T6


SELECT



  TO_CHAR(ROWNUM),



  TO_CHAR(3000-ROWNUM),



  TRUNC(SYSDATE+ROWNUM/50)



FROM



  DUAL



CONNECT BY



  LEVEL<=60000;



COMMIT;


Let's run a simple query with 10053 trace enabled:
SELECT



  *


FROM



  T6


WHERE



  C3<TRUNC(SYSDATE+2);



The DBMS_XPLAN:





| Id  | Operation                   | Name   | Starts | E-Rows | A-


Rows |   A-Time   | Buffers |



|   1 |  TABLE ACCESS BY INDEX ROWID| T6     |      1 |     59 |
59 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN          | IND_T6 |      1 |     59 |
59 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------





Predicate Information (identified by operation id):





   2 - access("C3"<TRUNC(SYSDATE@!+2))



Note






>From the 10053 trace file, we can see the work that the optimizer went

through to collect statistics dynamically:
SINGLE TABLE ACCESS PATH



*** 2007-06-04 19:16:41.404


** Performing dynamic sampling initial checks. **
  Column (#3): C3(DATE)  NO STATISTICS (using defaults)
    AvgLen: 7.00 NDV: 623 Nulls: 0 Density: 0.0016056

** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated index stats.: IND_T6, blocks=244
** Dynamic sampling index access candidate : IND_T6
** Dynamic sampling updated table stats.: blocks=244
*** 2007-06-04 19:16:41.406
** Generated dynamic sampling query:




    query text :


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("T6")

FULL("T6") NO_PARALLEL_INDEX("T6") */ 1 AS C1, CASE WHEN

"T6"."C3"<TRUNC(SYSDATE@!+2) THEN 1 ELSE 0 END AS C2 FROM "T6" SAMPLE
BLOCK (25.819672 , 1) SEED (1) "T6") SAMPLESUB

*** 2007-06-04 19:16:41.497


** Executed dynamic sampling query:


    level : 2


    sample pct. : 25.819672


    actual sample size : 17020


    filtered sample card. : 59


    orig. card. : 19930

    block cnt. table stat. : 244
    block cnt. for sampling: 244
    max. sample block cnt. : 64




    sample block cnt. : 63


    min. sel. est. : 0.05000000

** Using recursive dynamic sampling card. est. : 65918.730159
*** 2007-06-04 19:16:41.497
** Generated dynamic sampling query:




    query text :


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("T6") INDEX("T6" IND_T6) NO_PARALLEL_INDEX("T6") */ 1 AS

C1, 1 AS C2, 1 AS C3  FROM "T6" "T6" WHERE "T6"."C3"<TRUNC(SYSDATE@!
+2) AND ROWNUM <= 2500) SAMPLESUB


*** 2007-06-04 19:16:41.499


** Executed dynamic sampling query:


    level : 2


    sample pct. : 100.000000


    actual sample size : 65919


    filtered sample card. : 59


    filtered sample card. (index IND_T6): 59
    orig. card. : 65919

    block cnt. table stat. : 244
    block cnt. for sampling: 244
    max. sample block cnt. : 4294967295




    sample block cnt. : 244


    min. sel. est. : 0.05000000


    index IND_T6 selectivity est.: 0.00089504

** Using dynamic sampling card. : 65919
** Dynamic sampling updated table card.
** Using single table dynamic sel. est. : 0.00089504


  Table: T6  Alias: T6


    Card: Original: 65919  Rounded: 59  Computed: 59.00  Non Adjusted:
59.00


  Access Path: TableScan


    Cost:  65.74  Resp: 65.74  Degree: 0

      Cost_io: 50.00  Cost_cpu: 63701289
      Resp_io: 50.00  Resp_cpu: 63701289
  Access Path: index (RangeScan)




    Index: IND_T6


    resc_io: 3.00  resc_cpu: 22534


    ix_sel: 8.9504e-004  ix_sel_with_filters: 8.9504e-004
    Cost: 3.01  Resp: 3.01  Degree: 1


  Best:: AccessPath: IndexRange  Index: IND_T6


         Cost: 3.01  Degree: 1  Resp: 3.01  Card: 59.00  Bytes: 0



The best access plan is the IND_T6 index with a cost of 3.01.
Remember those values while we set up for a second test.



TRUNCATE TABLE T6;


INSERT INTO



  T6


SELECT



  TO_CHAR(ROWNUM),



  TO_CHAR(3000-ROWNUM),



  TRUNC(SYSDATE+ROWNUM/50)



FROM



  DUAL



CONNECT BY



  LEVEL<=3000;



COMMIT;


ALTER SYSTEM FLUSH SHARED_POOL;


Let's run a simple query with 10053 trace enabled:
SELECT



  *


FROM



  T6


WHERE



  C3<TRUNC(SYSDATE+2);







| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-
Time   | Buffers |



|*  1 |  TABLE ACCESS FULL| T6   |      1 |     58 |     58 |
00:00:00.01 |      16 |
------------------------------------------------------------------------------------





Predicate Information (identified by operation id):





   1 - filter("C3"<TRUNC(SYSDATE@!+2))



Note






>From the 10053 trace file:



SINGLE TABLE ACCESS PATH



*** 2007-06-04 19:42:45.659


** Performing dynamic sampling initial checks. **
  Column (#3): C3(DATE)  NO STATISTICS (using defaults)
    AvgLen: 7.00 NDV: 33 Nulls: 0 Density: 0.030132

** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated index stats.: IND_T6, blocks=13
** Dynamic sampling index access candidate : IND_T6
** Dynamic sampling updated table stats.: blocks=13
*** 2007-06-04 19:42:45.661
** Generated dynamic sampling query:




    query text :


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("T6")

FULL("T6") NO_PARALLEL_INDEX("T6") */ 1 AS C1, CASE WHEN

"T6"."C3"<TRUNC(SYSDATE@!+2) THEN 1 ELSE 0 END AS C2 FROM "T6" "T6")
SAMPLESUB



*** 2007-06-04 19:42:45.677


** Executed dynamic sampling query:


    level : 2


    sample pct. : 100.000000


    actual sample size : 3000


    filtered sample card. : 58


    orig. card. : 1062

    block cnt. table stat. : 13
    block cnt. for sampling: 13
    max. sample block cnt. : 64




    sample block cnt. : 13


    min. sel. est. : 0.05000000

** Using recursive dynamic sampling card. est. : 3000.000000
*** 2007-06-04 19:42:45.678
** Generated dynamic sampling query:




    query text :


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("T6") INDEX("T6" IND_T6) NO_PARALLEL_INDEX("T6") */ 1 AS

C1, 1 AS C2, 1 AS C3  FROM "T6" "T6" WHERE "T6"."C3"<TRUNC(SYSDATE@!
+2) AND ROWNUM <= 2500) SAMPLESUB


*** 2007-06-04 19:42:45.679


** Executed dynamic sampling query:


    level : 2


    sample pct. : 100.000000


    actual sample size : 3000


    filtered sample card. : 58


    filtered sample card. (index IND_T6): 58
    orig. card. : 3000

    block cnt. table stat. : 13
    block cnt. for sampling: 13
    max. sample block cnt. : 4294967295




    sample block cnt. : 13


    min. sel. est. : 0.05000000


    index IND_T6 selectivity est.: 0.01933333

** Using dynamic sampling card. : 3000
** Dynamic sampling updated table card.
** Using single table dynamic sel. est. : 0.01933333


  Table: T6  Alias: T6


    Card: Original: 3000  Rounded: 58  Computed: 58.00  Non Adjusted:
58.00


  Access Path: TableScan


    Cost:  4.72  Resp: 4.72  Degree: 0

      Cost_io: 4.00  Cost_cpu: 2912579
      Resp_io: 4.00  Resp_cpu: 2912579
  Access Path: index (RangeScan)




    Index: IND_T6


    resc_io: 18.00  resc_cpu: 147296


    ix_sel: 0.019333  ix_sel_with_filters: 0.019333
    Cost: 18.04  Resp: 18.04  Degree: 1


  Best:: AccessPath: TableScan


         Cost: 4.72  Degree: 1  Resp: 4.72  Card: 58.00  Bytes: 0



The best access plan is the full tablescan with a cost of 4.72.  Even
though we are retrieving one fewer row, the expected cost of the index
access increased from 3.01 to 18.04 - for exactly the same SQL
statement.



Maybe statistics would help?


Collect statistics and flush the shared pool.



With the same 3,000 rows:



SELECT



  *


FROM



  T6


WHERE



  C3<TRUNC(SYSDATE+2);



DBMS_XPLAN:






| Id  | Operation                   | Name   | Starts | E-Rows | A-


Rows |   A-Time   | Buffers |



|   1 |  TABLE ACCESS BY INDEX ROWID| T6     |      1 |     59 |
58 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN          | IND_T6 |      1 |     59 |
58 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------





Predicate Information (identified by operation id):





   2 - access("C3"<TRUNC(SYSDATE@!+2))





>From the 10053 trace file:





BASE STATISTICAL INFORMATION





Table Stats::


  Table: T6  Alias: T6


    #Rows: 3000  #Blks:  13  AvgRowLen:  17.00
Index Stats::


  Index: IND_T6  Col#: 3


    LVLS: 1  #LB: 8  #DK: 61  LB/K: 1.00  DB/K: 1.00  CLUF: 14.00

  Index: SYS_C008022  Col#: 1


    LVLS: 1  #LB: 10  #DK: 3000  LB/K: 1.00  DB/K: 1.00  CLUF: 544.00





SINGLE TABLE ACCESS PATH



  Column (#3): C3(DATE)


    AvgLen: 8.00 NDV: 61 Nulls: 0 Density: 1.6667e-004 Min: 2454256
Max: 2454316


    Histogram: Freq  #Bkts: 61  UncompBkts: 3000  EndPtVals: 61
  Table: T6  Alias: T6


    Card: Original: 3000  Rounded: 59  Computed: 58.50  Non Adjusted:
58.50


  Access Path: TableScan


    Cost:  4.72  Resp: 4.72  Degree: 0

      Cost_io: 4.00  Cost_cpu: 2912579
      Resp_io: 4.00  Resp_cpu: 2912579
  Access Path: index (RangeScan)




    Index: IND_T6


    resc_io: 3.00  resc_cpu: 44374


    ix_sel: 0.0195  ix_sel_with_filters: 0.0195
    Cost: 3.01  Resp: 3.01  Degree: 1


  Best:: AccessPath: IndexRange  Index: IND_T6


         Cost: 3.01  Degree: 1  Resp: 3.01  Card: 58.50  Bytes: 0



The best access plan is the IND_T6 index again with a cost of 3.01.
So, the cost of the index access changed from 3.01 to 18.04 and back
to 3.01 once we collected statistics for the table and indexes - for
exactly the same SQL statement and the same data set.



Charles Hooper


IT Manager/Oracle DBA


K&M Machine-Fabricating, Inc.
Received on Mon Jun 04 2007 - 21:05:08 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US