Home » SQL & PL/SQL » SQL & PL/SQL » Different plans on select query (Oracle 10g 10.2.0.1.0)
Different plans on select query [message #572135] Thu, 06 December 2012 07:20 Go to next message
haimbeyhan
Messages: 10
Registered: December 2012
Location: Israel
Junior Member
We're using Oracle 10g for development purposes.
I have 2 same schemas with approximately same data.
I'm running same query on 2 schemas and I see that the first schema runs the query around 20sec and the 2.schema less than 1 sec. I thought first that there may be missing constraints or indexes but all are the same.
I checked the plan for the 2 schemas and I see that the plan is different.

Here is the query:
SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747)

I'm sending also the 2 output of PLAN results from 2 different schemas.

Pls. help me what should I do to fix the problem with the NAFBCA schema.

Thanks,

Haim Beyhan
Re: Different plans on select query [message #572136 is a reply to message #572135] Thu, 06 December 2012 07:41 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please read and follow How to use [code] tags and make your code easier to read?

Please get the explain plans from sqlplus using the following method and post them as text here, it's a lot easier to read and some people won't download images:
SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL> 
Re: Different plans on select query [message #572137 is a reply to message #572136] Thu, 06 December 2012 07:42 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
In addtion give details of the indexes on the table.
Re: Different plans on select query [message #572138 is a reply to message #572136] Thu, 06 December 2012 08:04 Go to previous messageGo to next message
haimbeyhan
Messages: 10
Registered: December 2012
Location: Israel
Junior Member
SQL> connect NAFBCA/NAFBCA@ORCLUTF8
Connected.
SQL> explain plan for SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp
INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1315124850

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

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

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 65293 | 9118K|
| 47932 (2)| 00:09:36 |

|* 1 | HASH JOIN | | 65293 | 9118K|
5744K| 47932 (2)| 00:09:36 |

| 2 | INLIST ITERATOR | | | |
| | |

| 3 | TABLE ACCESS BY INDEX ROWID| CATALOGUECOMPONENT | 65293 | 4973K|
| 523 (1)| 00:00:07 |

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

|* 4 | INDEX RANGE SCAN | CATALOGUECOMPONENT_PK | 26117 | |
| 61 (2)| 00:00:01 |

| 5 | TABLE ACCESS FULL | CATALOGUECOMPONENT | 6529K| 404M|
| 23103 (2)| 00:04:38 |

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



PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("CCP"."COMPONENTID"="CCC"."PARENTCOMPONENTID")
4 - access("CCC"."COMPONENTID"=1594747 OR "CCC"."COMPONENTID"=20934777)

18 rows selected.


SQL> connect NAFBCA2/NAFBCA2@ORCLUTF8
Connected.
SQL> explain plan for SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp
INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 192373925

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

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

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 176 |
9 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 2 | 176 |
9 (0)| 00:00:01 |

| 2 | INLIST ITERATOR | | | |
| |

| 3 | TABLE ACCESS BY INDEX ROWID| CATALOGUECOMPONENT | 2 | 94 |
5 (0)| 00:00:01 |

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

|* 4 | INDEX RANGE SCAN | CATALOGUECOMPONENT_PK | 2 | |
4 (0)| 00:00:01 |

| 5 | TABLE ACCESS BY INDEX ROWID | CATALOGUECOMPONENT | 1 | 41 |
2 (0)| 00:00:01 |

|* 6 | INDEX UNIQUE SCAN | CATALOGUECOMPONENT_PK | 1 | |
1 (0)| 00:00:01 |

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

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


Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("CCC"."COMPONENTID"=1594747 OR "CCC"."COMPONENTID"=20934777)
6 - access("CCP"."COMPONENTID"="CCC"."PARENTCOMPONENTID")

19 rows selected.
Re: Different plans on select query [message #572139 is a reply to message #572138] Thu, 06 December 2012 08:22 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Different plans on select query [message #572143 is a reply to message #572139] Thu, 06 December 2012 08:43 Go to previous messageGo to next message
haimbeyhan
Messages: 10
Registered: December 2012
Location: Israel
Junior Member
NAFBCA schema
--------------

Explain PLAN FOR

SELECT ccc.componentid AS "ComponentId",
ccp.code AS "ParentCode",
ccc.code AS "ChildCode"
FROM cataloguecomponent ccp
INNER JOIN cataloguecomponent ccc
ON ccp.componentid = ccc.parentcomponentid
WHERE ccc.componentid IN ( 20934777, 1594747 );

SQL> select * from table(dbms_xplan.display);

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

Plan hash value: 1315124850

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 65293 | 9118K| | 47932 (2)| 00:09:36 |
|* 1 | HASH JOIN | | 65293 | 9118K| 5744K| 47932 (2)| 00:09:36 |
| 2 | INLIST ITERATOR | | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| CATALOGUECOMPONENT | 65293 | 4973K| | 523 (1)| 00:00:07 |
|* 4 | INDEX RANGE SCAN | CATALOGUECOMPONENT_PK | 26117 | | | 61 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL | CATALOGUECOMPONENT | 6529K| 404M| | 23103 (2)| 00:04:38 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("CCP"."COMPONENTID"="CCC"."PARENTCOMPONENTID")
4 - access("CCC"."COMPONENTID"=1594747 OR "CCC"."COMPONENTID"=20934777)


Same query with NAFBCA2 schema
------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------

Plan hash value: 192373925

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 176 | 9 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 176 | 9 (0)| 00:00:01 |
| 2 | INLIST ITERATOR | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| CATALOGUECOMPONENT | 2 | 94 | 5 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | CATALOGUECOMPONENT_PK | 2 | | 4 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | CATALOGUECOMPONENT | 1 | 41 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | CATALOGUECOMPONENT_PK | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("CCC"."COMPONENTID"=1594747 OR "CCC"."COMPONENTID"=20934777)
6 - access("CCP"."COMPONENTID"="CCC"."PARENTCOMPONENTID")

19 rows selected.
Re: Different plans on select query [message #572145 is a reply to message #572143] Thu, 06 December 2012 09:00 Go to previous messageGo to next message
haimbeyhan
Messages: 10
Registered: December 2012
Location: Israel
Junior Member
NAFBCA schema

Explain PLAN FOR 

SELECT ccc.componentid AS "ComponentId", 
       ccp.code        AS "ParentCode", 
       ccc.code        AS "ChildCode" 
FROM   cataloguecomponent ccp 
       INNER JOIN cataloguecomponent ccc 
               ON ccp.componentid = ccc.parentcomponentid 
WHERE  ccc.componentid IN ( 20934777, 1594747 );

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                                       
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1315124850                                                                                             
                                                                                                                        
---------------------------------------------------------------------------------------------------------------         
| Id  | Operation                     | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |         
---------------------------------------------------------------------------------------------------------------         
|   0 | SELECT STATEMENT              |                       | 65293 |  9118K|       | 47932   (2)| 00:09:36 |         
|*  1 |  HASH JOIN                    |                       | 65293 |  9118K|  5744K| 47932   (2)| 00:09:36 |         
|   2 |   INLIST ITERATOR             |                       |       |       |       |            |          |         
|   3 |    TABLE ACCESS BY INDEX ROWID| CATALOGUECOMPONENT    | 65293 |  4973K|       |   523   (1)| 00:00:07 |         
|*  4 |     INDEX RANGE SCAN          | CATALOGUECOMPONENT_PK | 26117 |       |       |    61   (2)| 00:00:01 |         
|   5 |   TABLE ACCESS FULL           | CATALOGUECOMPONENT    |  6529K|   404M|       | 23103   (2)| 00:04:38 |         
   
                                                                                                                        
Predicate Information (identified by operation id):                                                                     
---------------------------------------------------                                                                     
                                                                                                                        
   1 - access("CCP"."COMPONENTID"="CCC"."PARENTCOMPONENTID")                                                            
   4 - access("CCC"."COMPONENTID"=1594747 OR "CCC"."COMPONENTID"=20934777)                                              

18 rows selected.



NAFBCA2 Schema

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 192373925

-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       |     2 |   176 |     9   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |                       |     2 |   176 |     9   (0)| 00:00:01 |
|   2 |   INLIST ITERATOR             |                       |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| CATALOGUECOMPONENT    |     2 |    94 |     5   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | CATALOGUECOMPONENT_PK |     2 |       |     4   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID | CATALOGUECOMPONENT    |     1 |    41 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
|*  6 |    INDEX UNIQUE SCAN          | CATALOGUECOMPONENT_PK |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("CCC"."COMPONENTID"=1594747 OR "CCC"."COMPONENTID"=20934777)
   6 - access("CCP"."COMPONENTID"="CCC"."PARENTCOMPONENTID")

19 rows selected.
Re: Different plans on select query [message #572147 is a reply to message #572145] Thu, 06 December 2012 09:10 Go to previous messageGo to next message
BlackSwan
Messages: 21955
Registered: January 2009
Senior Member
noticeable different number of rows reported between the two plans

does NAFBCA2 Schema have current & valid statistics for both tables & indexes?
Re: Different plans on select query [message #572148 is a reply to message #572147] Thu, 06 December 2012 09:13 Go to previous messageGo to next message
haimbeyhan
Messages: 10
Registered: December 2012
Location: Israel
Junior Member
Hi,

Actually I don't know how to check this as I know only basic management in Oracle.

Thanks,

Haim Beyhan
Re: Different plans on select query [message #572149 is a reply to message #572148] Thu, 06 December 2012 09:22 Go to previous messageGo to next message
BlackSwan
Messages: 21955
Registered: January 2009
Senior Member
>Actually I don't know how to check this as I know only basic management in Oracle.

like doing as below?

SELECT COUNT(*) FROM CATALOGUECOMPONENT;
Re: Different plans on select query [message #572150 is a reply to message #572149] Thu, 06 December 2012 09:25 Go to previous messageGo to next message
haimbeyhan
Messages: 10
Registered: December 2012
Location: Israel
Junior Member
SQL> SELECT COUNT(*) FROM CATALOGUECOMPONENT;

COUNT(*)
----------
6539241
Re: Different plans on select query [message #572151 is a reply to message #572150] Thu, 06 December 2012 09:28 Go to previous messageGo to next message
BlackSwan
Messages: 21955
Registered: January 2009
Senior Member
so why does CBO report only 2 rows for this table?

consider to collect statistics for this schema
Re: Different plans on select query [message #572152 is a reply to message #572151] Thu, 06 December 2012 09:47 Go to previous messageGo to next message
haimbeyhan
Messages: 10
Registered: December 2012
Location: Israel
Junior Member
I ran the following through the management

begin

dbms_stats.gather_schema_stats(
ownname=> '"NAFBCA"' ,
options=> 'GATHER AUTO');

dbms_stats.gather_schema_stats(
ownname=> '"NAFBCA2"' ,
options=> 'GATHER AUTO');

end;
Re: Different plans on select query [message #572154 is a reply to message #572152] Thu, 06 December 2012 10:00 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove AUTO.

Regards
Michel
Re: Different plans on select query [message #572155 is a reply to message #572154] Thu, 06 December 2012 10:20 Go to previous messageGo to next message
haimbeyhan
Messages: 10
Registered: December 2012
Location: Israel
Junior Member
Ok. I ran it now without AUTO.

SQL> exec dbms_stats.gather_schema_stats(ownname=> '"NAFBCA"' , options=> 'GATHER')

PL/SQL procedure successfully completed.
Re: Different plans on select query [message #572156 is a reply to message #572155] Thu, 06 December 2012 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, now go back the beginning and provide the new execution plan.

Regards
Michel
Re: Different plans on select query [message #572157 is a reply to message #572155] Thu, 06 December 2012 10:24 Go to previous messageGo to next message
haimbeyhan
Messages: 10
Registered: December 2012
Location: Israel
Junior Member
SQL> select * from table(dbms_xplan.display);

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

Plan hash value: 192373925

-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       |     2 |   176 |     9   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |                       |     2 |   176 |     9   (0)| 00:00:01 |
|   2 |   INLIST ITERATOR             |                       |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| CATALOGUECOMPONENT    |     2 |    94 |     5   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | CATALOGUECOMPONENT_PK |     2 |       |     4   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID | CATALOGUECOMPONENT    |     1 |    41 |     2   (0)| 00:00:01 |

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

|*  6 |    INDEX UNIQUE SCAN          | CATALOGUECOMPONENT_PK |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("CCC"."COMPONENTID"=1594747 OR "CCC"."COMPONENTID"=20934777)
   6 - access("CCP"."COMPONENTID"="CCC"."PARENTCOMPONENTID")

19 rows selected.
Re: Different plans on select query [message #572158 is a reply to message #572157] Thu, 06 December 2012 10:32 Go to previous messageGo to next message
haimbeyhan
Messages: 10
Registered: December 2012
Location: Israel
Junior Member
I ran the query again and it's very fast now.

Can you pls. explain this?

Thanks

Haim
Re: Different plans on select query [message #572166 is a reply to message #572158] Thu, 06 December 2012 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, only YOU have the information for this.

Regards
Michel

[Updated on: Thu, 06 December 2012 11:38]

Report message to a moderator

Re: Different plans on select query [message #572188 is a reply to message #572166] Fri, 07 December 2012 02:30 Go to previous message
Roachcoach
Messages: 1125
Registered: May 2010
Location: UK
Senior Member
Off topic, but your query may be a candidate for a hierarchical query.

You may also wish to consider reading this and this if you're managing the server...
Previous Topic: Update same column with different values
Next Topic: Schema name
Goto Forum:
  


Current Time: Mon Apr 21 01:34:02 CDT 2014

Total time taken to generate the page: 0.06130 seconds