Home » RDBMS Server » Performance Tuning » query tuning (10.2.0.2.)
query tuning [message #382959] Mon, 26 January 2009 14:53 Go to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Hi Experts,
Following query is using lot of CPU:-
SELECT qv.friendly_name AS VALUE 
FROM   bv_category c, 
       bv_content_ref cr, 
       bv_ep_qual_value qv, 
       bv_ep_uprof_qval uq 
WHERE  c.content_type = 315 
       AND c.NAME = 'GenericRole' 
       AND c.store_id IN (SELECT s.store_id 
                          FROM   bv_store s 
                          WHERE  s.store_name = 'CLIFT') 
       AND c.oid = cr.parent_oid 
       AND qv.oid = cr.oid 
       AND qv.status = 1 
       AND qv.deleted = 0 
       AND uq.user_id = 10082863 
       AND uq.qvid = qv.oid 

The explain plan for the query is:-
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=21 Card=1 Bytes=81
          )

   1    0   NESTED LOOPS (Cost=21 Card=1 Bytes=81)
   2    1     HASH JOIN (Cost=21 Card=108 Bytes=7560)
   3    2       MERGE JOIN (CARTESIAN) (Cost=8 Card=108 Bytes=4752)
   4    3         NESTED LOOPS (Cost=4 Card=2 Bytes=66)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'BV_STORE' (TABLE
          ) (Cost=1 Card=1 Bytes=9)

   6    5             INDEX (UNIQUE SCAN) OF 'BV_STORE_NAME_INDEX' (IN
          DEX (UNIQUE)) (Cost=0 Card=1)

   7    4           TABLE ACCESS (BY INDEX ROWID) OF 'BV_CATEGORY' (TABLE) (Cost=3 Card=2 Bytes=48)

   8    7             INDEX (RANGE SCAN) OF 'BV_CATEGORY_KEY_IDX' (IND
          EX (UNIQUE)) (Cost=2 Card=2)

   9    3         BUFFER (SORT) (Cost=5 Card=57 Bytes=627)
  10    9           INDEX (RANGE SCAN) OF 'BV_EP_UPROF_QVAL_PK' (INDEX
           (UNIQUE)) (Cost=2 Card=57 Bytes=627)

  11    2       TABLE ACCESS (FULL) OF 'BV_EP_QUAL_VALUE' (TABLE) (Cos
          t=12 Card=1147 Bytes=29822)

  12    1     INDEX (UNIQUE SCAN) OF 'BV_CONTENT_REF_PK' (INDEX (UNIQU
          E)) (Cost=0 Card=1 Bytes=11)

The rowcounts for tables involved are:-
select count(*) from bv_category--44181
select count(*) from bv_content_ref--56472
select count(*) from bv_ep_qual_value--2294
select count(*) from bv_ep_uprof_qval--5238265

What can I do to reduce the cpu utilization from this query.
Re: query tuning [message #382961 is a reply to message #382959] Mon, 26 January 2009 14:59 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>What can I do to reduce the cpu utilization from this query.
SELECT qv.friendly_name AS VALUE
FROM   bv_category c, 
       bv_content_ref cr, 
       bv_ep_qual_value qv, 
       bv_ep_uprof_qval uq 


Eliminate C, CR, & UQ tables out of the FROM clause; because they contribute no data to SELECT clause.

Subordinate them into the WHERE clause.

Post results from each of these queries:
SELECT count(*) FROM bv_ep_qual_value qv WHERE qv.status = 1;
SELECT count(*) FROM bv_ep_qual_value qv WHERE qv.deleted = 0;

Do qv.status & qv.deleted have indexes on them?


[Updated on: Mon, 26 January 2009 15:18]

Report message to a moderator

Re: query tuning [message #382963 is a reply to message #382961] Mon, 26 January 2009 15:22 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Thanks Blackswan for you help.
I am posting the results for following queries:-
SELECT count(*) FROM bv_ep_qual_value qv WHERE qv.status = 1;---2294
SELECT count(*) FROM bv_ep_qual_value qv WHERE qv.deleted = 0;--1983

Please give me some idea about:-
Eliminate C, CR, & UQ tables out of the FROM clause; because they contribute no data to SELECT clause.

Subordinate them into the WHERE clause.

How can we subordinate them into where clause.

Regards,
Varun Punj
Re: query tuning [message #382964 is a reply to message #382963] Mon, 26 January 2009 15:28 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>How can we subordinate them into where clause.
by EXISTS or IN as part of the WHERE clause

>SELECT count(*) FROM bv_ep_qual_value qv WHERE qv.status = 1;---2294
>select count(*) from bv_ep_qual_value--2294

What the benefit of WHERE qv.status = 1 when every row is returned?


Re: query tuning [message #382967 is a reply to message #382964] Mon, 26 January 2009 15:45 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Hi BlackSwan,
Sorry for being dumb but I still could not understand:-
How can we subordinate them into where clause.
by EXISTS or IN as part of the WHERE clause

Regards,
Varun Punj,
Re: query tuning [message #382968 is a reply to message #382959] Mon, 26 January 2009 15:50 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/conditions012.htm#sthref2845

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/conditions013.htm#sthref2852

When all else fails, Read The Fine Manual!
Re: query tuning [message #383239 is a reply to message #382968] Tue, 27 January 2009 20:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Your optimiser thinks that there are 57 rows in bv_ep_uprof_qval that have
AND qv.status = 1 
AND qv.deleted = 0 

Is that true? Or is there a lot more?

If there is a lot more, gather statistics on this table with DBMS_STATS.GATHER_TABLE_STATS().

This should avoid the CARTESIAN JOIN, which is probably the source of your problem.

Ross Leishman
Re: query tuning [message #383391 is a reply to message #383239] Wed, 28 January 2009 11:38 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Thanks for replying Ross..
AND qv.status = 1---2294
AND qv.deleted = 0 ---1983
How did you come to know that my optimizer thinks that there
are 57 rows that have
AND qv.status = 1 
AND qv.deleted = 0 


Regards,
Varun Punj
Re: query tuning [message #383430 is a reply to message #383391] Wed, 28 January 2009 20:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Quote:
10 9 INDEX (RANGE SCAN) OF 'BV_EP_UPROF_QVAL_PK' (INDEX
(UNIQUE)) (Cost=2 Card=57 Bytes=627)


I'm not interested in how many rows match each predicate. How many match BOTH?

And while you're at it, how many rows in BV_CATEGORY match
       AND c.store_id IN (SELECT s.store_id 
                          FROM   bv_store s 
                          WHERE  s.store_name = 'CLIFT') 


Ross Leishman
Re: query tuning [message #383437 is a reply to message #383430] Wed, 28 January 2009 21:30 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Hi Ross,
select count(*) from bv_ep_qual_value where status=1 and deleted=0--1983


select * from bv_category where store_id in(SELECT s.store_id 

FROM   bv_store s WHERE  s.store_name = 'CLIFT')--254


Regards,
Varun Punj,
Re: query tuning [message #383440 is a reply to message #382959] Wed, 28 January 2009 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
SELECT qv.friendly_name AS VALUE 
FROM   bv_category c, 
       bv_content_ref cr, 
       bv_ep_qual_value qv       
WHERE  c.content_type = 315 
       AND c.NAME = 'GenericRole' 
       AND c.store_id IN (SELECT s.store_id 
                          FROM   bv_store s 
                          WHERE  s.store_name = 'CLIFT') 
       AND c.oid = cr.parent_oid 
       AND qv.oid = cr.oid 
       AND qv.status = 1 
       AND qv.deleted = 0 
       AND qv.oid in ( select uq.qvid from bv_ep_uprof_qval uq
                       where  uq.user_id = 10082863)



Now do likewise for bv_category c & bv_content_ref cr
Re: query tuning [message #383772 is a reply to message #383440] Fri, 30 January 2009 01:08 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
OK. So the following cartesian join:
  3    2       MERGE JOIN (CARTESIAN) (Cost=8 Card=108 Bytes=4752)

that Oracle thinks will return 108 rows, actually reurns 1983 x 254 = 503,682 rows. That's a bit different.

- Gather statistics on all tables using DBMS_STATS.GATHER_TABLE_STATS.
- Make sure you have an index on bv_content_ref(cr.parent_oid)
- Make sure you have an index on bv_content_ref(cr.oid)
- Make sure you have an index on bv_ep_uprof_qval(user_id)
- Make sure you have an index on bv_ep_uprof_qval(qvid)
- Make sure you have an index on bv_ep_qual_value(oid)
- Make sure you have an index on bv_category(oid)

Ross Leishman


Re: query tuning [message #383774 is a reply to message #382959] Fri, 30 January 2009 01:24 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Why the difference?
Re: query tuning [message #383817 is a reply to message #383774] Fri, 30 January 2009 06:41 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
BlackSwan wrote on Fri, 30 January 2009 18:24
Why the difference?

Could be bad statistics.

But it just may be bad luck:

- The estimate of rows from bv_category (2 instead of 254) is based on a foreign key IN sub-query. At best, Oracle can go on the mean cardinality of that foreign key. However if the mean cardinality is 2, then a single sample of 254 is fairly skewed. This degree of skewness is possible, but so are bad/no statistics.

I made a mistake on the other one. I should have asked how many rows matched:
AND uq.user_id = 10082863 

because that is the other side of the cartesian merge. But it's probably immaterial - the first example has shown the optimiser to be way out. If it thinks it is getting one or two rows, it is happy to cartesian join to a larger row source. 254 is a different story.

Quote:
- Gather statistics on all tables using DBMS_STATS.GATHER_TABLE_STATS.
- Make sure you have an index on bv_content_ref(cr.parent_oid)
- Make sure you have an index on bv_content_ref(cr.oid)
- Make sure you have an index on bv_ep_uprof_qval(user_id)
- Make sure you have an index on bv_ep_uprof_qval(qvid)
- Make sure you have an index on bv_ep_qual_value(oid)
- Make sure you have an index on bv_category(oid)

And also (possibly most importantly):
- Make sure you have an index on bv_category(content_type,name, store_id) in any order
Re: query tuning [message #383865 is a reply to message #383817] Fri, 30 January 2009 16:24 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Thanks Ross and Blackswan for all your help,
I gathered the statistics on all tables and it worked.
Ross,
I have few basic question now to make my concepts solid:-
I have following indexes on the tables:-
There is already concatenate index on(name,content_type,parent_oid,store_oid) in bv_category;
there is concatenate index on (user_id,qid,qvid)in bv_ep_uprof_qval
There is index on OID in bv_ep_qual_value.
there is index on oid and parent oid in bv_content_ref

As you told me to create separate index on bv_ep_uprof_qval like this:-
Make sure you have an index on bv_ep_uprof_qval(user_id)
Make sure you have an index on bv_ep_uprof_qval(qvid)

So I have concatenated index on these two columns but requirement was to use separate index.So it means Oracle
automatically use concatenated indexes as separate index when
required.Is It right?

2,Do I need to gather statisics on these tables everyday
or Oracle wiil take care of it.

3,The new explain plan is:-
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=1 Bytes=89
          )

   1    0   NESTED LOOPS (Cost=11 Card=1 Bytes=89)
   2    1     NESTED LOOPS (Cost=9 Card=1 Bytes=76)
   3    2       NESTED LOOPS (Cost=8 Card=1 Bytes=49)
   4    3         NESTED LOOPS (Cost=4 Card=1 Bytes=37)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'BV_STORE' (TABLE
          ) (Cost=1 Card=1 Bytes=9)

   6    5             INDEX (UNIQUE SCAN) OF 'BV_STORE_NAME_INDEX' (IN
          DEX (UNIQUE)) (Cost=0 Card=1)

   7    4           TABLE ACCESS (BY INDEX ROWID) OF 'BV_CATEGORY' (TA
          BLE) (Cost=3 Card=1 Bytes=28)

   8    7             INDEX (RANGE SCAN) OF 'BV_CATEGORY_KEY_IDX' (IND
          EX (UNIQUE)) (Cost=2 Card=1)

   9    3         TABLE ACCESS (BY INDEX ROWID) OF 'BV_CONTENT_REF' (T
          ABLE) (Cost=4 Card=1 Bytes=12)

  10    9           INDEX (RANGE SCAN) OF 'BV_CNTRF_IDX' (INDEX) (Cost
          =2 Card=25)

  11    2       TABLE ACCESS (BY INDEX ROWID) OF 'BV_EP_QUAL_VALUE' (T
          ABLE) (Cost=1 Card=1 Bytes=27)

  12   11         INDEX (UNIQUE SCAN) OF 'BV_EP_QUAL_VALUE_PK' (INDEX
          (UNIQUE)) (Cost=0 Card=1)

  13    1     INDEX (RANGE SCAN) OF 'BV_EP_UPROF_QVAL_PK' (INDEX (UNIQ
          UE)) (Cost=2 Card=1 Bytes=13)

There is lot of reduction in cost,card,bytes.
On what factors CPU is dependent out of these three.
Please provide me some detailed document(If you have any)to
understand Explain Plan.
Once again thanks a lot for all you help.
Regards,
Varun Punj


Re: query tuning [message #383871 is a reply to message #383865] Fri, 30 January 2009 16:49 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Having a concatenated index means you do not need to have a separate index on the first column of the concatenated index. Oracle can use the LEADING columns of an index just like it uses the whole index.

I asked about the other indexes because it is a good rule of thumb to index:
- Every join condition
- Every selective filter


Oracle 10g has a feature to automatically gather statistics. I think it is switched on by default, but you would need to check - someone may have switched it off.

If statistics are not automatically gathered, you need to do it yourself whenever the distribution of data changes significantly in the table/index (ie. more than a few percent)


Take a look at the Sticky thread at the top of this forum. There are several good resources there including the Oracle Performance Tuning Manual (which has a chapter devoted to Explain Plan) and my own guide on this site: http://www.orafaq.com/tuningguide

Your problem - no/bad statistics - is number one in the "quick fix" guide on that link.

Ross Leishman
Previous Topic: Exchange Partition
Next Topic: what is "count stopkey"?
Goto Forum:
  


Current Time: Fri Dec 02 18:28:18 CST 2016

Total time taken to generate the page: 0.35333 seconds