OraFAQ Forum: Performance Tuning » An observation

Home » RDBMS Server » Performance Tuning » An observation (Oracle 10.2.0.3)

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

An observation [message #468477]

Fri, 30 July 2010 03:38

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

SELECT   department_id
FROM     (SELECT department_id
          FROM   employees
          UNION
          SELECT department_id
          FROM   employees_old )
WHERE    department_id=100;

SELECT   department_id
FROM     (
          SELECT department_id
          FROM   employees
		  WHERE    department_id=100 
          UNION
          SELECT department_id
          FROM   employees_old
		  WHERE    department_id=100)

The index has been created on both depart_id for the two tables.

The only difference between the two I observed was the 1 recursive call for the 1st sql.
and also, one additional view in the plan.There is a little difference in bytes
sent over the network.

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
212 bytes sent via SQL*Net to client
279 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
215 bytes sent via SQL*Net to client
279 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

Is there any performance impact you find in those above two sqls if you compare?

Thanks for the suggesion in advance!

Regards
Ved

Report message to a moderator

Re: An observation [message #468479 is a reply to message #468477]

Fri, 30 July 2010 03:43

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

Have you ran this SQL in diffrent Sessions ?
Or one after another in Single seesion?

Report message to a moderator

Re: An observation [message #468482 is a reply to message #468477]

Fri, 30 July 2010 04:10

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

Quote:

The index has been created on both depart_id for the two tables.

Correction: department_id

I ran the sql in same session.

What I understand is it goes for 1 recursive calls in the 2nd query is its doing internal processing to build the view for the sub select. Am I wrong here?

Which one would you prefer and why?

Regards
Ved

[Updated on: Fri, 30 July 2010 04:13]

Report message to a moderator

Re: An observation [message #468485 is a reply to message #468482]

Fri, 30 July 2010 04:32

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

internal processing I understand as Soft Parse is that what you mean ?

Report message to a moderator

Re: An observation [message #468486 is a reply to message #468485]

Fri, 30 July 2010 04:33

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

Try Running same query in different Session you shoud get 1 recursive calls in both.

Report message to a moderator

Re: An observation [message #468487 is a reply to message #468477]

Fri, 30 July 2010 04:37

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

Quote:

The only difference between the two I observed was the 1 recursive call for the 1st sql.
and also, one additional view in the plan.There is a little difference in bytes
sent over the network.

Regards
Ved

[Updated on: Fri, 30 July 2010 04:38]

Report message to a moderator

Re: An observation [message #468488 is a reply to message #468487]

Fri, 30 July 2010 04:52

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

there is no Difference for me

 SQL>  select a from (
  2         select a from rb
  3         union
  4         select a from rb1
  5      ) where a = 5;

         A
----------
         5

Elapsed: 00:00:01.67

Execution Plan
----------------------------------------------------------
Plan hash value: 3301796248

--------------------------------------
| Id  | Operation           | Name   |
--------------------------------------
|   0 | SELECT STATEMENT    |        |
|   1 |  VIEW               |        |
|   2 |   SORT UNIQUE       |        |
|   3 |    UNION-ALL        |        |
|*  4 |     INDEX RANGE SCAN| IX_RB  |
|*  5 |     INDEX RANGE SCAN| IX_RB1 |
--------------------------------------

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

   4 - access("A"=5)
   5 - access("A"=5)

Note
-----
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        215  bytes sent via SQL*Net to client
        247  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

 SQL> select a from (
  2         select a from rb where a = 5
  3         union
  4         select a from rb1 where a = 5
  5      )
  6  /

         A
----------
         5

Elapsed: 00:00:01.65

Execution Plan
----------------------------------------------------------
Plan hash value: 3301796248

--------------------------------------
| Id  | Operation           | Name   |
--------------------------------------
|   0 | SELECT STATEMENT    |        |
|   1 |  VIEW               |        |
|   2 |   SORT UNIQUE       |        |
|   3 |    UNION-ALL        |        |
|*  4 |     INDEX RANGE SCAN| IX_RB  |
|*  5 |     INDEX RANGE SCAN| IX_RB1 |
--------------------------------------

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

   4 - access("A"=5)
   5 - access("A"=5)

Note
-----
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        218  bytes sent via SQL*Net to client
        247  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Version

SQL> @v

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

Elapsed: 00:00:01.67

Report message to a moderator

Re: An observation [message #468489 is a reply to message #468488]

Fri, 30 July 2010 04:56

cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester

Senior Member

You're using the RBO on 10g?!?!?!?
You do realise that's not supported?

Report message to a moderator

Re: An observation [message #468490 is a reply to message #468489]

Fri, 30 July 2010 04:57

cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester

Senior Member

Plus I assume Ved is using the CBO so that proves absolutely nothing.

Report message to a moderator

Re: An observation [message #468493 is a reply to message #468489]

Fri, 30 July 2010 05:00

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

cookiemonster wrote on Fri, 30 July 2010 04:56

You're using the RBO on 10g?!?!?!?
You do realise that's not supported?

Even I was surprise Its taking RBO Sad

Sad

SQL> show parameter mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      choose

Report message to a moderator

Re: An observation [message #468494 is a reply to message #468490]

Fri, 30 July 2010 05:03

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

Its CBO..

My bad!! the second sql was


          SELECT department_id
          FROM   employees
		  WHERE    department_id=100 
          UNION
          SELECT department_id
          FROM   employees_old
		  WHERE    department_id=100;

There was no need of another select at all as we are using predicates for the two tables.

So, it wont go for 1 recursive calls.

My question is what is the difference you find when you compare the above sql with the 1st one?
Which one would you prefer and why?
Does the sql above and the 1st one impact performance?

Regards,
Ved

[Updated on: Fri, 30 July 2010 05:04]

Report message to a moderator

Re: An observation [message #468495 is a reply to message #468494]

Fri, 30 July 2010 05:07

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

1/ I would prefer For Large Tables

 SELECT department_id
          FROM   employees
		  WHERE    department_id=100 
          UNION
          SELECT department_id
          FROM   employees_old
		  WHERE    department_id=100;

Why ?

your first query will get All Table Result and then Union and then Apply the Filter
as in Second query filters are directly applied to The tables.

Report message to a moderator

Re: An observation [message #468496 is a reply to message #468495]

Fri, 30 July 2010 05:08

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

and YES 1st one impact performace for LARGE Tables.

Report message to a moderator

Re: An observation [message #468501 is a reply to message #468495]

Fri, 30 July 2010 05:32

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

SELECT department_id
FROM   (SELECT department_id
        FROM   employees
        UNION
        SELECT department_id
        FROM   employees_old)
WHERE  department_id = 100;

Plan
SELECT STATEMENT ALL_ROWSCost: 8 Bytes: 91 Cardinality: 7
5 VIEW XXX. Cost: 8 Bytes: 91 Cardinality: 7
4 SORT UNIQUE Cost: 8 Bytes: 39 Cardinality: 7
3 UNION-ALL
1 INDEX RANGE SCAN INDEX EMP_INDX_FK01 Cost: 3 Bytes: 15 Cardinality: 3
2 INDEX RANGE SCAN INDEX EMP_O_INDX_FK01 Cost: 3 Bytes: 24 Cardinality: 4

	

SELECT department_id
FROM   employees
WHERE  department_id = 100
UNION
SELECT department_id
FROM   employees_old
WHERE  department_id = 100

Plan
SELECT STATEMENT ALL_ROWSCost: 8 Bytes: 29 Cardinality: 7
4 SORT UNIQUE Cost: 8 Bytes: 29 Cardinality: 7
3 UNION-ALL
1 INDEX RANGE SCAN INDEX EMP_INDX_FK01 Cost: 3 Bytes: 15 Cardinality: 3
2 INDEX RANGE SCAN INDEX EMP_O_INDX_FK01 Cost: 3 Bytes: 24 Cardinality: 4

Seems that my observation was wrong.It was all a mess while reading the plan in Toad. SQLPLUS gives me the clear picture
about the hierarchy order

Thanks to all!

Regards
Ved

[Updated on: Fri, 30 July 2010 06:02]

Report message to a moderator

Re: An observation [message #468589 is a reply to message #468501]

Fri, 30 July 2010 22:01

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

With inline views like this, Oracle has 3 choices:
- MERGE - Transform the query into one without the inline view. If Oracle merges, it will internally transform Query 1 into just the inline-view part of Query 2.
- PUSH PREDICATES - If a MERGE is not possible, it can often leave the inline view where it is, but add predicates from the outer query. This would be like having the WHERE clause both inside AND outside the inline view. This applies more to join predicates than constant predicates like you have here.
- Don't transform the query at all - it will run the inline view in its entirety and then apply predicates from the outer query.

Depending on your Oracle version and initialization parameters, it could do any one of these. Oracle 10.2 onwards will - I think - do the full merge automatically. With older versions, the UNION probably would have prevented automatic merging and you would need to provide a hint.

Ross Leishman

Report message to a moderator

Previous Topic:	Which application (software, utility) do I choose for report?
Next Topic:	Table Partition

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 04 14:17:24 CDT 2024