OraFAQ Forum: Server Administration » Different execution plans for same query ???

Home » RDBMS Server » Server Administration » Different execution plans for same query ??? (9.2.0.7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Different execution plans for same query ??? [message #337264]

Wed, 30 July 2008 06:02

nazbrian
Messages: 36
Registered: July 2008

Member

Hi,

Following is the output.

SQL>  explain plan for
  2   select * from fem_term_deposits where as_of_date = to_date('16-Jul-2008','DD-Mon-YY') and data_source = 'CB' ;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------
| Id  | Operation                         |  Name              | Rows  | Bytes | Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                    |     1 |   579 |     2 |       |       |
|   1 |  TABLE ACCESS BY LOCAL INDEX ROWID| FEM_TERM_DEPOSITS  |     1 |   579 |     2 |    21 |    21 |
|   2 |   INDEX RANGE SCAN                | FEM_TD_AOD         |     1 |       |     1 |    21 |    21 |
--------------------------------------------------------------------------------------------------------

Note: cpu costing is off, PLAN_TABLE' is old version

10 rows selected.

SQL> explain plan for
  2   select * from fem_term_deposits where as_of_date = to_date('16-Jul-2008','DD-Mon-YY') and data_source = 'FN' ;

Explained.

SQL>  @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------
| Id  | Operation            |  Name              | Rows  | Bytes | Cost  | Pstart| Pstop |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    |  4859K|  2683M| 53037 |       |       |
|   1 |  TABLE ACCESS FULL   | FEM_TERM_DEPOSITS  |  4859K|  2683M| 53037 |    21 |    21 |
-------------------------------------------------------------------------------------------

Note: cpu costing is off, PLAN_TABLE' is old version

9 rows selected.

SQL>

The table is analyzed!

In the above 2 query's I have just one column value is different, My doubt is how oracle will know FTS/Indexes Scan?

I mean, Which table the statisics are stored for column values ?

Brian

Report message to a moderator

Re: Different execution plans for same query ??? [message #337266 is a reply to message #337264]

Wed, 30 July 2008 06:07

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> desc DBA_TAB_HISTOGRAMS
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 OWNER                                     VARCHAR2(30 CHAR)
 TABLE_NAME                                VARCHAR2(30)
 COLUMN_NAME                               VARCHAR2(4000)
 ENDPOINT_NUMBER                           NUMBER
 ENDPOINT_VALUE                            NUMBER
 ENDPOINT_ACTUAL_VALUE                     VARCHAR2(1000)

Regards
Michel

Report message to a moderator

Re: Different execution plans for same query ??? [message #337290 is a reply to message #337264]

Wed, 30 July 2008 08:04

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

If your question is WHY are there different execution plans, the answer is because there are many more rows in your table with data_source = 'FN', making a full table scan more appropriate than an index scan.

Report message to a moderator

Re: Different execution plans for same query ??? [message #337484 is a reply to message #337264]

Thu, 31 July 2008 01:11

nazbrian
Messages: 36
Registered: July 2008

Member

Hi Michel,

I have following output from DBA_TAB_HISTOGRAMS.

SQL> l
  1  select * from DBA_TAB_HISTOGRAMS
  2  where table_name = 'FEM_TERM_DEPOSITS' and column_name in ('AS_OF_DATE','DATA_SOURCE')
  3* order by column_name
SQL> /

OWNER                          TABLE_NAME                     COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
------------------------------ ------------------------------ -------------------- --------------- -------------- --------------------
BOIDW                          FEM_TERM_DEPOSITS              AS_OF_DATE                         0        2453430
BOIDW                          FEM_TERM_DEPOSITS              AS_OF_DATE                         1        2454191
BOIDW                          FEM_TERM_DEPOSITS              DATA_SOURCE                        0     3.4922E+35
BOIDW                          FEM_TERM_DEPOSITS              DATA_SOURCE                        1     3.6504E+35

SQL>

Based on the above output, I'm not clear why there is different execution plans based on values in the column "DATA_SOURCE".

I understand that Oracle makes its own judgement to use index scan or FTS. But as a research I want to how know oracle make s the execution Plan.

Brian.

Report message to a moderator

Previous Topic:	PCTUSED not permited with index
Next Topic:	Time Change

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 19:38:10 CST 2024