Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: explain plan assistance needed
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
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
** 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
** Using dynamic sampling card. : 65919 ** Dynamic sampling updated table card. ** Using single table dynamic sel. est. : 0.00089504Table: T6 Alias: T6
Cost_io: 50.00 Cost_cpu: 63701289 Resp_io: 50.00 Resp_cpu: 63701289 Access Path: index (RangeScan)
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);
|* 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
** 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
** Using dynamic sampling card. : 3000 ** Dynamic sampling updated table card. ** Using single table dynamic sel. est. : 0.01933333Table: T6 Alias: T6
Cost_io: 4.00 Cost_cpu: 2912579 Resp_io: 4.00 Resp_cpu: 2912579 Access Path: index (RangeScan)
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:
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)
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