explain plan assistance needed

From: pfa <peter_at_falson.net>
Date: Mon, 04 Jun 2007 10:03:09 -0700
Message-ID: <1180976589.084103.87290@q19g2000prn.googlegroups.com>

I'm struggling to find information that will explain the results of my explain plan. What I'm trying to do is write a verification routine which shows the benfits of adding an index. From past experience it is accepted that just creating an index isn't (necessarily) enough but an analyze table ... compute statistics or a dbms_stats.gather... process be run. But in my test process the explain is showing an index scan without the stats and after the stats the only difference is the output of the rows/bytes.

Can anybody shed some light on this or point me to a url with some reading material that explains this?

Here's my script and the output I'm getting:

drop index NIX_QARUNSTATS_FILE
/

select count(RECID) from qarunstats_file x
/

select count(RECID) from qarunstats_file x where NVL(NUMCAST(extractValue(x.xmlcol,'/row/path')),0) = 5
/

explain plan for select count(RECID) from qarunstats_file x where NVL(NUMCAST(extractValue(x.xmlcol,'/row/path')),0) = 5
/

select plan_table_output from
table(dbms_xplan.display('plan_table',null,'all'))
/

CREATE INDEX nix_QARUNSTATS_FILE ON QARUNSTATS_FILE x (NVL(NUMCAST(extractValue(x.xmlcol,'/row/path')),0))
/

explain plan for select count(RECID) from qarunstats_file x where NVL(NUMCAST(extractValue(x.xmlcol,'/row/path')),0) = 5
/

select plan_table_output from
table(dbms_xplan.display('plan_table',null,'all'))
/

begin dbms_stats.gather_table_stats('SCOTT','QARUNSTATS_FILE'); end;
/

explain plan for select count(RECID) from qarunstats_file x where NVL(NUMCAST(extractValue(x.xmlcol,'/row/path')),0) = 5
/

select plan_table_output from
table(dbms_xplan.display('plan_table',null,'all'))
/

begin dbms_stats.gather_table_stats('SCOTT','QARUNSTATS_FILE'); end;
/

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 4 09:54:20 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Index dropped.

PL/SQL procedure successfully completed.

COUNT(RECID)

---

      100000

COUNT(RECID)

---

        4508

Explained.

PLAN_TABLE_OUTPUT

---

Plan hash value: 37407764

---

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

     |

---

---

|   0 | SELECT STATEMENT   |                 |     1 |   185 |   572

(14)| 00:0
0:07 |

|   1 |  SORT AGGREGATE    |                 |     1 |   185
|            |
     |

|* 2 | TABLE ACCESS FULL| QARUNSTATS_FILE | 992 | 179K| 572 (14)| 00:0
0:07 |

---

---

Query Block Name / Object Alias (identified by operation id):

---

   1 - SEL$1
   2 - SEL$1 / X_at_SEL$1 Predicate Information (identified by operation id):

---

   2 -
filter(NVL("NUMCAST"(EXTRACTVALUE(SYS_MAKEXML("X"."SYS_NC00003$"),'/ row

              /value')),0)=5)

Column Projection Information (identified by operation id):

---

   1 - (#keys=0) COUNT(*)[22]

26 rows selected.

Index created.

Explained.

PLAN_TABLE_OUTPUT

---

Plan hash value: 1724735628

---

---

| Id  | Operation         | Name                   | Rows  | Bytes |
Cost (%CPU)
| Time     |

--------------------------------------------------------------------------------

---

|   0 | SELECT STATEMENT  |                        |     1 |   185
|     1   (0)

| 00:00:01 |

|   1 |  SORT AGGREGATE   |                        |     1 |   185 |
|          |

|*  2 |   INDEX RANGE SCAN| NIX_QARUNSTATS_FILE    |   992 |
179K|     1   (0)

| 00:00:01 |

---

---

Query Block Name / Object Alias (identified by operation id):

---

   1 - SEL$1
   2 - SEL$1 / X_at_SEL$1 Predicate Information (identified by operation id):

---

   2 -
access(NVL("SCOTT"."NUMCAST"(EXTRACTVALUE(SYS_MAKEXML("SYS_NC00003$"),'/ row
/value'

              )),0)=5)

Column Projection Information (identified by operation id):

---

   1 - (#keys=0) COUNT(*)[22]

26 rows selected.

PL/SQL procedure successfully completed.

Explained.

PLAN_TABLE_OUTPUT

---

Plan hash value: 1724735628

---

---

| Id  | Operation         | Name                   | Rows  | Bytes |
Cost (%CPU)
| Time     |

--------------------------------------------------------------------------------

---

|   0 | SELECT STATEMENT  |                        |     1 |   185
|     1   (0)

| 00:00:01 |

|   1 |  SORT AGGREGATE   |                        |     1 |   185 |
|          |

|*  2 |   INDEX RANGE SCAN| NIX_QARUNSTATS_FILE    |  4456 |
805K|     1   (0)

| 00:00:01 |

---

---

Query Block Name / Object Alias (identified by operation id):

---

   1 - SEL$1
   2 - SEL$1 / X_at_SEL$1 Predicate Information (identified by operation id):

---

   2 -
access(NVL("SCOTT"."NUMCAST"(EXTRACTVALUE(SYS_MAKEXML("SYS_NC00003$"),'/ row
/value'

              )),0)=5)

Column Projection Information (identified by operation id):

---

   1 - (#keys=0) COUNT(*)[22]

26 rows selected.

PL/SQL procedure successfully completed.

Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> show parameters statistic

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
statistics_level                     string      TYPICAL
timed_os_statistics                  integer     0
timed_statistics                     boolean     FALSE

SQL> NUMCAST function

---

create function numcast(value in VARCHAR) return NUMBER deterministic as
rtnval NUMBER;
begin
  rtnval := TO_NUMBER(value);
  return rtnval;
exception
when others then
  return NULL;
end;
/
Received on Mon Jun 04 2007 - 12:03:09 CDT