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: <beijingshenao2008_at_yahoo.com.cn>
Date: Mon, 04 Jun 2007 22:57:36 -0700
Message-ID: <1181023056.870532.230650@o11g2000prd.googlegroups.com>


On 6 5 , 10 05 , Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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
> -----
> - dynamic sampling used for this statement
> ==============================================================================
>
> >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
> -----
> - dynamic sampling used for this statement
> ==============================================================================
>
> >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 ...
>
> - -
>
> - -

SHINE international trade company
Sorry to trouble you.
please read this mail. It's for your career
>From shine international trade Company of Beijing. China
Dear sir/madam:

        Thank you for read this mail. Our company in china. Like you think, china is ready To be world's factory. We have all kinds of things. Of course. We have best manpower resource , best price. More and more person, company like to build trade company in china. It is our predominance. Like light industry, Sinitic things. and a lot of other things need us to do. It's a chance for us. That's right.

        Until this, I want you know, our company now do business about like shoes, bags, belts, clothing, PSP, glasses jeans and so on. Our company want to look for a cooperate friend in your state. If you agree our company case. If you want to pay just a computer to do something. If you have a trade company in your state. Or if you have a shop(online shop)You can call us to talk about next step. We will give the best price, best service. The shine international shine trade will like to talk to you very much.

         You can go to our website http://www.6thshop.com/bbs.asp to leave massage
 MSN:evenbeijing_at_hotmail.com

	 yahoo:  shinechina_at_yahoo.com.cn
	 E-mail:   even-china_at_hotmail.com
	 http://www.6thshop.com
	 phone: 86-13811619431
	 FAX: 86-10-51670319
	             GOOD LUCK!
			        								       EVEN FROM CHINA
------- SHINE INTERNATIONAL TRADE COMPANY OF BEIJING.CHINA Received on Tue Jun 05 2007 - 00:57:36 CDT

Original text of this message

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