OraFAQ Forum: Performance Tuning » Oracle10g Performance question

Home » RDBMS Server » Performance Tuning » Oracle10g Performance question

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

Oracle10g Performance question [message #226124]

Thu, 22 March 2007 10:48

bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam

Member

Hi All,
i have a runing query that cost 1.20 second to execute and the smae query but with the use of 'union all' which reduce the execution duration time to 06 milsecond.
the question is Why??

the query:
===========
select count(distinct nvl(t.derivedFrom,t.nr)) from vwContent c , ContentTypes t
where c.ContentType=t.nr
and ( ( c.nr IN (69760,69700,69397,69339,69244,69185,69149,69146,68960,68931))
or (c.Nr IN (Select Nr from vwContent start with Parent in (69760,69700,69397,69339,69244,69185,69149,69146,68960,68931)
connect by prior Nr=Parent and prior Folder=1))
);
COUNT(DISTINCTNVL(T.DERIVEDFROM,T.NR))
--------------------------------------
2

Elapsed: 00:01:21.72
Statistics
----------------------------------------------------------
22 recursive calls
0 db block gets
870952 consistent gets
0 physical reads
0 redo size
441 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
45046 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from
(
select count(distinct nvl(t.derivedFrom,t.nr)) from vwContent c , ContentTypes t
where c.ContentType=t.nr
and c.nr IN (69760,69700,69397,69339,69244,69185,69149,69146,68960,68931)
union all
select count(distinct nvl(t.derivedFrom,t.nr)) from vwContent c , ContentTypes t
where c.Nr IN (Select Nr from vwContent start with Parent in (69760,69700,69397,69339,69244,691
85,69149,69146,68960,68931)
connect by prior Nr=Parent and prior Folder=1)
);

COUNT(*)
----------
2

Elapsed: 00:00:00.06
Statistics
----------------------------------------------------------
29 recursive calls
0 db block gets
123 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

The 'consistent gets' is 123 in the second query while it is '870952' in the first query?..
i need to optimize the first query ..what must i do,any suggestins??
thanks in Advance,

Report message to a moderator

Re: Oracle10g Performance question [message #226131 is a reply to message #226124]

Thu, 22 March 2007 11:14

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Post XPLAIN plan for both cases, however I suspect that in first case the optimizer used FULL TABLE scan to access the data vs index scans in second case.

HTH.

Report message to a moderator

Re: Oracle10g Performance question [message #226135 is a reply to message #226131]

Thu, 22 March 2007 11:24

bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam

Member

Thanks for your replay,
this is the first query XPLAIN:
-------------------------------

select count(distinct nvl(t.derivedFrom,t.nr)) from vwContent c , ContentTypes t
where c.ContentType=t.nr
and ( ( c.nr IN (69760,69700,69397,69339,69244,69185,69149,69146,68960,68931))
or (c.Nr IN (Select Nr from vwContent start with Parent in (69760,69700,69397,69339,69244,69185,69149,69146,68960,68931)
connect by prior Nr=Parent and prior Folder=1))
);
  



COUNT(DISTINCTNVL(T.DERIVEDFROM,T.NR))
--------------------------------------
                                     2

Elapsed: 00:01:21.72

Execution Plan
----------------------------------------------------------
Plan hash value: 3948575723

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

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

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

|   0 | SELECT STATEMENT                  |                    |     1 |    14 |    30   (7)| 00:00:
01 |

|   1 |  SORT GROUP BY                    |                    |     1 |    14 |            |
   |

|*  2 |   FILTER                          |                    |       |       |            |
   |

|*  3 |    HASH JOIN                      |                    | 12490 |   170K|    30   (7)| 00:00:
01 |

|   4 |     TABLE ACCESS FULL             | CONTENTTYPES       |    52 |   260 |    15   (0)| 00:00:
01 |

|*  5 |     INDEX FAST FULL SCAN          | IDX_1018           | 12490 |   109K|    15  (14)| 00:00:
01 |

|*  6 |    FILTER                         |                    |       |       |            |
   |

|*  7 |     CONNECT BY WITH FILTERING     |                    |       |       |            |
   |

|*  8 |      TABLE ACCESS BY INDEX ROWID  | CONTENTS           |       |       |            |
   |

|*  9 |       INDEX FAST FULL SCAN        | IDX_706            |    50 |   300 |    16  (13)| 00:00:
01 |

|  10 |      NESTED LOOPS                 |                    |       |       |            |
   |

|  11 |       BUFFER SORT                 |                    |       |       |            |
   |

|  12 |        CONNECT BY PUMP            |                    |       |       |            |
   |

|* 13 |       FILTER                      |                    |       |       |            |
   |

|* 14 |        TABLE ACCESS BY INDEX ROWID| CONTENTS           |     5 |    60 |     6   (0)| 00:00:
01 |

|* 15 |         INDEX RANGE SCAN          | IX_CONTENTS_PARENT |     6 |       |     1   (0)| 00:00:
01 |

|* 16 |      TABLE ACCESS FULL            | CONTENTS           |     5 |    60 |     6   (0)| 00:00:
01 |

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


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

   2 - filter("NR"=68931 OR "NR"=68960 OR "NR"=69146 OR "NR"=69149 OR "NR"=69185 OR "NR"=69244
              OR "NR"=69339 OR "NR"=69397 OR "NR"=69700 OR "NR"=69760 OR  EXISTS (SELECT 0 FROM
              VL52."CONTENTS" "CONTENTS" WHERE NULL=1 AND "PARENT"=NULL AND "STATUS"<>2 AND ("NR"=:B
1)))

   3 - access("CONTENTTYPE"="T"."NR")
   5 - filter("STATUS"<>2)
   6 - filter("NR"=:B1)
   7 - filter("PARENT"=69760 OR "PARENT"=69700 OR "PARENT"=69397 OR "PARENT"=69339 OR
              "PARENT"=69244 OR "PARENT"=69185 OR "PARENT"=69149 OR "PARENT"=69146 OR "PARENT"=68960
 OR

              "PARENT"=68931)
   8 - filter("STATUS"<>2)
   9 - filter("STATUS"<>2 AND ("PARENT"=68931 OR "PARENT"=68960 OR "PARENT"=69146 OR
              "PARENT"=69149 OR "PARENT"=69185 OR "PARENT"=69244 OR "PARENT"=69339 OR "PARENT"=69397
 OR

              "PARENT"=69700 OR "PARENT"=69760))
  13 - filter(NULL=1)
  14 - filter("STATUS"<>2)
  15 - access("PARENT"=NULL)
  16 - access("PARENT"=NULL)
       filter("STATUS"<>2)


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

and this is for the second query:
=====================

select count(*) from 
  2  (
  3  select count(distinct nvl(t.derivedFrom,t.nr)) from vwContent c , ContentTypes t
  4  where c.ContentType=t.nr
  5  and c.nr IN (69760,69700,69397,69339,69244,69185,69149,69146,68960,68931)
  6  union all 
  7  select count(distinct nvl(t.derivedFrom,t.nr)) from vwContent c , ContentTypes t
  8  where c.Nr IN (Select Nr from vwContent start with Parent in (69760,69700,69397,69339,69244,691
85,69149,69146,68960,68931)
  9  connect by prior Nr=Parent and prior Folder=1)
 10  );

  COUNT(*)
----------
         2

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 2555688925

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

| Id  | Operation                             | Name               | Rows  | Bytes | Cost (%CPU)| Ti
me     |

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

|   0 | SELECT STATEMENT                      |                    |     1 |       |    88   (0)| 00
:00:02 |

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

|   2 |   VIEW                                |                    |     2 |       |    88   (0)| 00
:00:02 |

|   3 |    UNION-ALL                          |                    |       |       |            |
       |

|   4 |     SORT GROUP BY                     |                    |     1 |    14 |            |
       |

|   5 |      NESTED LOOPS                     |                    |     8 |   112 |    10   (0)| 00
:00:01 |

|   6 |       INLIST ITERATOR                 |                    |       |       |            |
       |

|*  7 |        INDEX RANGE SCAN               | IDX_1018           |     8 |    72 |     9   (0)| 00
:00:01 |

|   8 |       TABLE ACCESS BY INDEX ROWID     | CONTENTTYPES       |     1 |     5 |     1   (0)| 00
:00:01 |

|*  9 |        INDEX UNIQUE SCAN              | PK_CONTENTTYPES    |     1 |       |     0   (0)| 00
:00:01 |

|  10 |     SORT GROUP BY                     |                    |     1 |    24 |            |
       |

|  11 |      MERGE JOIN CARTESIAN             |                    |   260 |  6240 |    78   (0)| 00
:00:01 |

|  12 |       NESTED LOOPS                    |                    |     5 |    95 |    11   (0)| 00
:00:01 |

|  13 |        VIEW                           | VW_NSO_1           |     5 |    65 |     6   (0)| 00
:00:01 |

|* 14 |         CONNECT BY WITH FILTERING     |                    |       |       |            |
       |

|* 15 |          TABLE ACCESS BY INDEX ROWID  | CONTENTS           |       |       |            |
       |

|* 16 |           INDEX FAST FULL SCAN        | IDX_706            |    50 |   300 |    16  (13)| 00
:00:01 |

|  17 |          NESTED LOOPS                 |                    |       |       |            |
       |

|  18 |           BUFFER SORT                 |                    |       |       |            |
       |

|  19 |            CONNECT BY PUMP            |                    |       |       |            |
       |

|* 20 |           FILTER                      |                    |       |       |            |
       |

|* 21 |            TABLE ACCESS BY INDEX ROWID| CONTENTS           |     5 |    60 |     6   (0)| 00
:00:01 |

|* 22 |             INDEX RANGE SCAN          | IX_CONTENTS_PARENT |     6 |       |     1   (0)| 00
:00:01 |

|* 23 |          TABLE ACCESS FULL            | CONTENTS           |     5 |    60 |     6   (0)| 00
:00:01 |

|* 24 |        INDEX RANGE SCAN               | IDX_1012           |     1 |     6 |     1   (0)| 00
:00:01 |

|  25 |       BUFFER SORT                     |                    |    52 |   260 |    77   (0)| 00
:00:01 |

|  26 |        TABLE ACCESS FULL              | CONTENTTYPES       |    52 |   260 |    13   (0)| 00
:00:01 |

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


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

   7 - access("NR"=68931 OR "NR"=68960 OR "NR"=69146 OR "NR"=69149 OR "NR"=69185 OR "NR"=69244 OR
              "NR"=69339 OR "NR"=69397 OR "NR"=69700 OR "NR"=69760)
       filter("STATUS"<>2)
   9 - access("CONTENTTYPE"="T"."NR")
  14 - filter("PARENT"=69760 OR "PARENT"=69700 OR "PARENT"=69397 OR "PARENT"=69339 OR
              "PARENT"=69244 OR "PARENT"=69185 OR "PARENT"=69149 OR "PARENT"=69146 OR "PARENT"=68960
 OR

              "PARENT"=68931)
  15 - filter("STATUS"<>2)
  16 - filter("STATUS"<>2 AND ("PARENT"=68931 OR "PARENT"=68960 OR "PARENT"=69146 OR "PARENT"=69149
              OR "PARENT"=69185 OR "PARENT"=69244 OR "PARENT"=69339 OR "PARENT"=69397 OR "PARENT"=69
700 OR

              "PARENT"=69760))
  20 - filter(NULL=1)
  21 - filter("STATUS"<>2)
  22 - access("PARENT"=NULL)
  23 - access("PARENT"=NULL)
       filter("STATUS"<>2)
  24 - access("NR"="$nso_col_1")
       filter("STATUS"<>2)


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

Thanks in Advance,

[Updated on: Fri, 23 March 2007 21:39] by Moderator

Report message to a moderator

Re: Oracle10g Performance question [message #226136 is a reply to message #226124]

Thu, 22 March 2007 11:24

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

I wonder why there is no join (c.ContentType=t.nr) between those two tables in the second select of your second sql.
Have you missed that while po(a)sting here?

By
Vamsi

Report message to a moderator

Re: Oracle10g Performance question [message #226138 is a reply to message #226136]

Thu, 22 March 2007 11:29

bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam

Member

This is the first query:
======================
select count(distinct nvl(t.derivedFrom,t.nr)) from vwContent c , ContentTypes t
where c.ContentType=t.nr
and ( ( c.nr IN (69760,69700,69397,69339,69244,69185,69149,69146,68960,68931))
or (c.Nr IN (Select Nr from vwContent start with Parent in (69760,69700,69397,69339,69244,69185,69149,69146,68960,68931)
connect by prior Nr=Parent and prior Folder=1))
);

and this is the second query:
============================
select count(distinct nvl(t.derivedFrom,t.nr)) from vwContent c , ContentTypes t
where c.ContentType=t.nr
and c.nr IN (69760,69700,69397,69339,69244,69185,69149,69146,68960,68931)
union all
select count(distinct nvl(t.derivedFrom,t.nr)) from vwContent c , ContentTypes t
where c.Nr IN (Select Nr from vwContent start with Parent in (69760,69700,69397,69339,69244,691
85,69149,69146,68960,68931)
connect by prior Nr=Parent and prior Folder=1);
--------------------------------
you can see that the 'where c.ContentType=t.nr' are there in both of them.

thanks in advance,

Report message to a moderator

Re: Oracle10g Performance question [message #226140 is a reply to message #226138]

Thu, 22 March 2007 11:34

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Quote:

union all
select count(distinct nvl(t.derivedFrom,t.nr)) from vwContent c , ContentTypes t
where c.Nr IN (Select Nr from vwContent start with Parent in (69760,69700,69397,69339,69244,691
85,69149,69146,68960,68931)
connect by prior Nr=Parent and prior Folder=1)

By
Vamsi

Report message to a moderator

Re: Oracle10g Performance question [message #226299 is a reply to message #226140]

Fri, 23 March 2007 03:57

bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam

Member

Thanks for your replay,
the question is :Why the first query return such long execution time?
the query again:
----------------
select count(distinct nvl(t.derivedFrom,t.nr)) from vwContent c , ContentTypes t
where c.ContentType=t.nr
and ( ( c.nr IN (69760,69700,69397,69339,69244,69185,69149,69146,68960,68931))
or (c.Nr IN (Select Nr from vwContent start with Parent in (69760,69700,69397,69339,69244,69185,69149,69146,68960,68931)
connect by prior Nr=Parent and prior Folder=1))
);

Elapsed: 00:01:22.38

So the second query was just as demonstration for the first query problem.
any suggestions?
thanks in Advance,

Report message to a moderator

Re: Oracle10g Performance question [message #226469 is a reply to message #226299]

Fri, 23 March 2007 22:02

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

Senior Member

In the faster version, it is able to run the CONNECT BY query first, and then use the results to probe into vwContent using an indexed lookup. The key is Step 12 of the plan - a NESTED LOOPS join that uses the CONNECT BY (Step 14) as a driving step, and an Index scan (Step 24) as the iterative step.

As vasmi pointed out, you forgot the join predicate (c.ContentType=t.nr) resulting in a catesian join, but there mustn't be many rows because this was quick.

In the slower version, the OR statement stopped Oracle from driving off the CONNECT BY. Instead, it performed the join between vwContent and Content Types, and then it ran the CONNECT BY as a filter for every row returned - possibly 100s or 1000s of runs.

The key to this one is the FILTER on Step 2 of the plan. A FILTER with two child nodes is like a nested loops join: for each row returned by the first child step, execute the second child step. The first child step (Step 3) is the join, and for each row returned by the join, Step 6 is executed (the CONNECT BY).

This type of thing (filtering) is discussed in this article.

Ross Leishman

Report message to a moderator

Re: Oracle10g Performance question [message #226489 is a reply to message #226124]

Sat, 24 March 2007 02:25

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

Senior Member
Account Moderator

select count(*) from (
<select that returns 1 one, for instance a count>
union all
<select that returns 1 one, for instance a count>
)
always returns 2 as the first subquery returns one row and the second one also, so the outer query returns the count of rows of the union all that 1+1 = 2.

SQL> select count(*) from 
  2  (select count(*) from dual union all select count(*) from dual)
  3  /
  COUNT(*)
----------
         2

1 row selected.

SQL> select count(*) from 
  2   (select count(*) from user_objects union all select count(*) from user_synonyms)
  3  /
  COUNT(*)
----------
         2

1 row selected.

So your second query always returns 2.
And I think the optimizer is aware of this and the cost is only the cost of your recursive calls.

Regards
Michel

Report message to a moderator

Re: Oracle10g Performance question [message #226653 is a reply to message #226489]

Mon, 26 March 2007 02:11

bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam

Member

Thank you for your Reply,
This is a great answer..Thanks.

Thanks In Advance,

Report message to a moderator

Previous Topic:	Cost based optimizer
Next Topic:	list all active connections with sql statement used?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 01:38:56 CST 2024