Home » RDBMS Server » Performance Tuning » Oracle10g Performance question
| 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,
|
|
|
|
|
|
| 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 #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,
|
|
|
|
| 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
|
|
|
|
| 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,
|
|
|
|
| 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
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu May 16 01:25:40 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
