Home » RDBMS Server » Performance Tuning » lobg running query (10.2.0.1)
lobg running query [message #343230] Tue, 26 August 2008 12:51 Go to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Hi Experts,
I am running following query taking 21 seconds to execute:-
SELECT CONCAT (UP.hcc_last_name,CONCAT (', ',CONCAT (UP.hcc_first_name,CONCAT (' [', CONCAT (u.user_alias, ']'))))), u.user_id FROM bv_user_profile UP, bv_user u, bv_ep_qual_value qv, bv_ep_uprof_qval uq, bv_category c, (SELECT u2.user_id FROM bv_user u2, bv_ep_uprof_qval uq2, bv_ep_qual_value qv2, bv_category c2 WHERE u2.user_id = uq2.user_id AND uq2.qvid = qv2.OID AND qv2.status = 1 AND qv2.deleted = 0 AND qv2.friendly_name ='Alice - Atlas CH' AND qv2.store_id = 107 AND uq2.qid = c2.OID AND c2.NAME = 'ATLAS LQA Districts' AND c2.store_id = 107 ) dis WHERE u.user_id = UP.user_id AND u.user_state = 0 AND u.user_id = dis.user_id AND u.user_id = uq.user_id AND uq.qvid = qv.OID AND qv.store_id = 107 AND qv.status = 1 AND qv.deleted = 0 AND qv.friendly_name = 'Grading Manager' AND uq.qid = c.OID AND c.store_id = 107 AND c.status = 1 AND c.deleted = 0 AND c.NAME = 'ATLAS LQA User Role' ORDER BY 1 ASC

Explain Plan for the query is:-

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2058 Card=1 Bytes=
192)

1 0 SORT (ORDER BY) (Cost=2058 Card=1 Bytes=192)
2 1 NESTED LOOPS (Cost=2057 Card=1 Bytes=192)
3 2 NESTED LOOPS (Cost=2056 Card=1 Bytes=175)
4 3 NESTED LOOPS (Cost=2056 Card=1 Bytes=169)
5 4 HASH JOIN (Cost=2055 Card=1 Bytes=147)
6 5 HASH JOIN (Cost=2052 Card=6 Bytes=732)
7 6 TABLE ACCESS (FULL) OF 'BV_EP_QUAL_VALUE' (TAB
LE) (Cost=9 Card=1 Bytes=30)

8 6 NESTED LOOPS (Cost=2042 Card=28253 Bytes=25992
76)

9 8 MERGE JOIN (CARTESIAN) (Cost=272 Card=883 By
tes=66225)

10 9 MERGE JOIN (CARTESIAN) (Cost=11 Card=1 Byt
es=51)

11 10 TABLE ACCESS (FULL) OF 'BV_EP_QUAL_VALUE
' (TABLE) (Cost=9 Card=1 Bytes=30)

12 10 BUFFER (SORT) (Cost=2 Card=1 Bytes=21)
13 12 TABLE ACCESS (BY INDEX ROWID) OF 'BV_C
ATEGORY' (TABLE) (Cost=2 Card=1 Bytes=21)

14 13 INDEX (RANGE SCAN) OF 'BV_CATEGORY_K
EY_IDX' (INDEX (UNIQUE)) (Cost=1 Card=1)

15 9 BUFFER (SORT) (Cost=270 Card=39455 Bytes=9
46920)

16 15 TABLE ACCESS (FULL) OF 'BV_USER' (TABLE)
(Cost=261 Card=39455 Bytes=946920)

17 8 INDEX (RANGE SCAN) OF 'BV_EP_UPROF_QVAL_PK'
(INDEX (UNIQUE)) (Cost=2 Card=32 Bytes=544)

18 5 TABLE ACCESS (BY INDEX ROWID) OF 'BV_CATEGORY' (
TABLE) (Cost=3 Card=1 Bytes=25)

19 18 INDEX (RANGE SCAN) OF 'BV_CATEGORY_KEY_IDX' (I
NDEX (UNIQUE)) (Cost=2 Card=1)

20 4 TABLE ACCESS (BY INDEX ROWID) OF 'BV_USER_PROFILE'
(TABLE) (Cost=1 Card=1 Bytes=22)

21 20 INDEX (UNIQUE SCAN) OF 'BV_USER_PROFILE_ID_IDX'
(INDEX (UNIQUE)) (Cost=0 Card=1)

22 3 INDEX (UNIQUE SCAN) OF 'BV_USER_PK' (INDEX (UNIQUE))
(Cost=0 Card=1 Bytes=6)

23 2 INDEX (UNIQUE SCAN) OF 'BV_EP_UPROF_QVAL_PK' (INDEX (U
NIQUE)) (Cost=1 Card=1 Bytes=17)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
177178 consistent gets
16889 physical reads
0 redo size
453 bytes sent via SQL*Net to client
280 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
5 rows processed
Can you suggest me how to tune the query.
Regards,
Varun Punj,
Re: lobg running query [message #343232 is a reply to message #343230] Tue, 26 August 2008 12:55 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
sorry for misspelling long.
Re: lobg running query [message #343237 is a reply to message #343232] Tue, 26 August 2008 13:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No problem with mispelling, on the other hand it should be great if you could read Forum guide and format your post.

Regards
Michel
Re: lobg running query [message #343245 is a reply to message #343230] Tue, 26 August 2008 14:33 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Sorry Michel not to read the guides before creating topic due to my busy schedule.I will do that before creating any new topic from next time onwards,
Re: lobg running query [message #343249 is a reply to message #343230] Tue, 26 August 2008 14:41 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
We are busy too.
We will respond after formatted post is provided.

You're On Your own (YOYO)!
Re: lobg running query [message #343264 is a reply to message #343230] Tue, 26 August 2008 15:40 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2058 Card=1 Bytes=192)

1 0 SORT (ORDER BY) (Cost=2058 Card=1 Bytes=192)

2 1 NESTED LOOPS (Cost=2057 Card=1 Bytes=192)

3 2 NESTED LOOPS (Cost=2056 Card=1 Bytes=175)

4 3 NESTED LOOPS (Cost=2056 Card=1 Bytes=169)

5 4 HASH JOIN (Cost=2055 Card=1 Bytes=147)

6 5 HASH JOIN (Cost=2052 Card=6 Bytes=732)

7 6 TABLE ACCESS (FULL) OF 'BV_EP_QUAL_VALUE' (TABLE)(Cost=Card=1 Bytes=30)

8 6 NESTED LOOPS (Cost=2042 Card=28253 Bytes=2599276)

9 8 MERGE JOIN (CARTESIAN) (Cost=272 Card=883 Bytes=66225)

10 9 MERGE JOIN (CARTESIAN) (Cost=11 Card=1 Bytes=51)

11 10 TABLE ACCESS (FULL) OF 'BV_EP_QUAL_VALUE' (TABLE) (Cost=9 Card=1 Bytes=30)

12 10 BUFFER (SORT) (Cost=2 Card=1 Bytes=21)
13 12 TABLE ACCESS (BY INDEX ROWID) OF 'BV_CATEGORY' (TABLE) (Cost=2 Card=1 Bytes=21)

14 13 INDEX (RANGE SCAN) OF 'BV_CATEGORY_KEY_IDX' (INDE (UNIQUE)) (Cost=1 Card=1)

15 9 BUFFER (SORT) (Cost=270 Card=39455 Bytes=946920)

16 15 TABLE ACCESS (FULL) OF 'BV_USER' (TABLE)(Cost=261 Card=39455 Bytes=946920)

17 8 INDEX (RANGE SCAN) OF 'BV_EP_UPROF_QVAL_PK' (INDEX (UNIQUE)) (Cost=2 Card=32 Bytes=544)

18 5 TABLE ACCESS (BY INDEX ROWID) OF 'BV_CATEGORY' (TABLE (Cost=3 Card=1 Bytes=25)

19 18 INDEX (RANGE SCAN) OF 'BV_CATEGORY_KEY_IDX' (INDEX (UNIQUE)) (Cost=2 Card=1)

20 4 TABLE ACCESS (BY INDEX ROWID) OF 'BV_USER_PROFILE'(TABLE) (Cost=1 Card=1 Bytes=22)

21 20 INDEX (UNIQUE SCAN) OF 'BV_USER_PROFILE_ID_IDX'(INDEX (UNIQUE)) (Cost=0 Card=1)

22 3 INDEX (UNIQUE SCAN) OF 'BV_USER_PK' (INDEX (UNIQUE) (Cost=0 Card=1 Bytes=6)

23 2 INDEX (UNIQUE SCAN) OF 'BV_EP_UPROF_QVAL_PK' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=17)
Re: lobg running query [message #343291 is a reply to message #343230] Tue, 26 August 2008 18:42 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Here is solution to your problem.

'SELECTCONCAT(UP.HCC_LAST_NAME,CONCAT('','',CONCAT(UP.HCC_FIRST_NAME,CONCAT(''['
--------------------------------------------------------------------------------
SELECTCONCAT(UP.hcc_last_name,CONCAT(',',CONCAT(UP.hcc_first_name,CONCAT('[',CON
CAT(u.user_alias,']')))))(((SELECTCONCAT(UP.hcc_last_name,CONCAT(',',CONCAT(UP.h
cc_first_name,CONCAT('[',CONCAT(u.user_alias,']'))))),u.user_idFROMbv_user_profi
leUP,bv_useru,bv_ep_qual_valueqv,bv_ep_uprof_qvaluq,bv_categoryc,(SELECTu2.user_
idFROMbv_useru2,bv_ep_uprof_qvaluq2,bv_ep_qual_valueqv2,bv_categoryc2WHEREu2.use
r_id=uq2.user_idANDuq2.qvid=qv2.OIDANDqv2.status=1ANDqv2.deleted=0ANDqv2.friendl
y_name='Alice-AtlasCH'ANDqv2.store_id=107ANDuq2.qid=c2.OIDANDc2.NAME='ATLASLQADi
stricts'ANDc2.store_id=107)disWHEREu.user_id=UP.user_idANDu.user_state=0ANDu.use
r_id=dis.user_idANDu.user_id=uq.user_idANDuq.qvid=qv.OIDANDqv.store_id=107ANDqv.
status=1ANDqv.deleted=0ANDqv.friendly_name='GradingManager'ANDuq.qid=c.OIDANDc.s
tore_id=107ANDc.status=1ANDc.deleted=0ANDc.NAME='ATLASLQAUserRole'ORDERBY1ASC)))
,u.user_idFROMbv_user_profileUP,bv_useru,bv_ep_qual_valueqv,bv_ep_uprof_qvaluq,b
v_categoryc,(SELECTCONCAT(UP.hcc_last_name,CONCAT(',',CONCAT(UP.hcc_first_name,C
ONCAT('[',CONCAT(u.user_alias,']'))))),u.user_idFROMbv_user_profileUP,bv_useru,b
v_ep_qual_valueqv,bv_ep_uprof_qvaluq,bv_categoryc,(SELECTu2.user_idFROMbv_useru2
,bv_ep_uprof_qvaluq2,bv_ep_qual_valueqv2,bv_categoryc2WHEREu2.user_id=uq2.user_i
dANDuq2.qvid=qv2.OIDANDqv2.status=1ANDqv2.deleted=0ANDqv2.friendly_name='Alice-A
tlasCH'ANDqv2.store_id=107ANDuq2.qid=c2.OIDANDc2.NAME='ATLASLQADistricts'ANDc2.s
tore_id=107)disWHEREu.user_id=UP.user_idANDu.user_state=0ANDu.user_id=dis.user_i
dANDu.user_id=uq.user_idANDuq.qvid=qv.OIDANDqv.store_id=107ANDqv.status=1ANDqv.d
eleted=0ANDqv.friendly_name='GradingManager'ANDuq.qid=c.OIDANDc.store_id=107ANDc
.status=1ANDc.deleted=0ANDc.NAME='ATLASLQAUserRole'ORDERBY1ASC)(SELECTu2.user_id
FROMbv_useru2,bv_ep_uprof_qvaluq2,bv_ep_qual_valueqv2,bv_categoryc2WHEREu2.user_
id=uq2.user_idANDuq2.qvid=qv2.OIDANDqv2.status=1ANDqv2.deleted=0ANDqv2.friendly_
name='Alice-AtlasCH'ANDqv2.store_id=107ANDuq2.qid=c2.OIDANDc2.NAME='ATLASLQADist
ricts'ANDc2.store_id=107)disWHEREu.user_id=UP.user_idANDu.user_state=0ANDu.user_
id=dis.user_idANDu.user_id=uq.user_idANDuq.qvid=qv.OIDANDqv.store_id=107ANDqv.st
atus=1ANDqv.deleted=0ANDqv.friendly_name='GradingManager'ANDuq.qid=c.OIDANDc.sto
re_id=107ANDc.status=1ANDc.deleted=0ANDc.NAME='ATLASLQAUserRole'ORDERBY1ASC

OH GEE, sorry about that but do not worry, I will be sure to format in future posts.

Kevin
Re: lobg running query [message #343305 is a reply to message #343230] Tue, 26 August 2008 22:05 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
sorry about that but dont get so annoyed

Regards,
Varun Punj,
Re: lobg running query [message #343328 is a reply to message #343230] Tue, 26 August 2008 23:49 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I am not annoyed, you are not the first to have forgotten to format code before you post it. But... we are all still waiting for you to do what you should, which is take that SQL statement you initially posted, send it through a code formatter (like the one on the home page of OraFAQ (that is why it is there)), and paste it into your next post on this thread.

I will be more civil I promise.

Kevin
Re: lobg running query [message #343349 is a reply to message #343230] Wed, 27 August 2008 00:41 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
SELECT CONCAT (UP.hcc_last_name,CONCAT (', ',CONCAT (UP.hcc_first_name,CONCAT (' [', CONCAT (u.user_alias, ']'))))), u.user_id FROM bv_user_profile UP, bv_user u, bv_ep_qual_value qv, bv_ep_uprof_qval uq, bv_category c, (SELECT u2.user_id FROM bv_user u2, bv_ep_uprof_qval uq2, bv_ep_qual_value qv2, bv_category c2 WHERE u2.user_id = uq2.user_id AND uq2.qvid = qv2.OID AND qv2.status = 1 AND qv2.deleted = 0 AND qv2.friendly_name ='Alice - Atlas CH' AND qv2.store_id = 107 AND uq2.qid = c2.OID AND c2.NAME = 'ATLAS LQA Districts' AND c2.store_id = 107 ) dis WHERE u.user_id = UP.user_id AND u.user_state = 0 AND u.user_id = dis.user_id AND u.user_id = uq.user_id AND uq.qvid = qv.OID AND qv.store_id = 107 AND qv.status = 1 AND qv.deleted = 0 AND qv.friendly_name = 'Grading Manager' AND uq.qid = c.OID AND c.store_id = 107 AND c.status = 1 AND c.deleted = 0 AND c.NAME = 'ATLAS LQA User Role' ORDER BY 1 ASC
Re: lobg running query [message #343354 is a reply to message #343349] Wed, 27 August 2008 00:46 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
sorry once again for lacking discipline and being irresponsible

[Updated on: Wed, 27 August 2008 00:47]

Report message to a moderator

Re: lobg running query [message #343359 is a reply to message #343354] Wed, 27 August 2008 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you still failed in 2 points:
- not using a SQL formater
- not keeping your lines in 80 characters.

Regards
Michel
Re: lobg running query [message #343500 is a reply to message #343359] Wed, 27 August 2008 07:39 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
varunvir

Quote:
Please go to the home page of OraFAQ.

One the right side of the page is a topic bar.

Travel down the bar about half way and you will see the sql formatter.

Click the link.

Paste your code into the formatter and press the format button.

Cut the code out of the box which now contains your formatted code and past that into your post.

Then highlight the code which should now look formatted (but still won't be) and click the {..} button at the top of the post under the smiley faces. This will wrap your code with the CODE html tags that will cause your formatted code to actually appear formatted in your post.
Kevin
Re: lobg running query [message #343568 is a reply to message #343230] Wed, 27 August 2008 09:35 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
SELECT   Concat(Up.hcc_Last_Name,Concat(', ',Concat(Up.hcc_First_Name,Concat(' [',Concat(u.User_Alias,']'))))),
         u.User_Id
FROM     bv_User_Profile Up,
         bv_User u,
         bv_ep_qual_Value qv,
         bv_ep_UprOf_qval uq,
         bv_Category c,
         (SELECT u2.User_Id
          FROM   bv_User u2,
                 bv_ep_UprOf_qval uq2,
                 bv_ep_qual_Value qv2,
                 bv_Category c2
          WHERE  u2.User_Id = uq2.User_Id
                 AND uq2.qvId = qv2.oId
                 AND qv2.Status = 1
                 AND qv2.Deleted = 0
                 AND qv2.Friendly_Name = 'Alice - Atlas CH'
                 AND qv2.Store_Id = 107
                 AND uq2.qId = c2.oId
                 AND c2.NAME = 'ATLAS LQA Districts'
                 AND c2.Store_Id = 107) dIs
WHERE    u.User_Id = Up.User_Id
         AND u.User_State = 0
         AND u.User_Id = dIs.User_Id
         AND u.User_Id = uq.User_Id
         AND uq.qvId = qv.oId
         AND qv.Store_Id = 107
         AND qv.Status = 1
         AND qv.Deleted = 0
         AND qv.Friendly_Name = 'Grading Manager'
         AND uq.qId = c.oId
         AND c.Store_Id = 107
         AND c.Status = 1
         AND c.Deleted = 0
         AND c.NAME = 'ATLAS LQA User Role'
ORDER BY 1 ASC
Re: lobg running query [message #343577 is a reply to message #343230] Wed, 27 August 2008 10:05 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Excellent, thanks.

So now we would ask some additional questions.

Quote:
1) Can you provide rowcounts for all the tables involved?

FROM     bv_User_Profile Up,
         bv_User u,
         bv_ep_qual_Value qv,
         bv_ep_UprOf_qval uq,
         bv_Category c,


2) What is your performance goal? How long do you expect this to take? What is the desired time frame?

Kevin
Re: lobg running query [message #343585 is a reply to message #343230] Wed, 27 August 2008 10:26 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
I want this query to take least time.I mean within 5-8 seconds.
Here are row counts for the tables:-
1.Row Count for BV_USER = 78663
2.Row Count for bv_user_profile = 58490
3.Row Count for bv_ep_qual_value =2074
4.Row Count for bv_ep_uprof_qval = 4398579
5.Row Count for bv_category = 43786


Regards,
Varun Punj,
Re: lobg running query [message #343632 is a reply to message #343230] Wed, 27 August 2008 12:37 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
5-8 seconds seems like a very reasonable expectation for these rowcounts. Looking at the query, the first thing I would try is setting up a good set of indexes for the query. I don't know how much experience you have with indexing so I will throw some work out at you and you take it from there:

The query comes in two parts: a) a main query, b) an inline subselect. You first consider indexing for the main query, and then indexing for the subselect taking into account the correlation between the main query and the subselect.

I come up with the following index sets:

Quote:
Main Query Optimal Indexes

bv_ep_qual_Value (Store_Id,Status,Deleted,Friendly_Name,oId)
bv_ep_UprOf_qval (qvId,qId,User_Id,oId)
bv_User (User_Id,User_State,User_Alias)
bv_User_Profile (User_Id,hcc_Last_Name,hcc_First_Name)
bv_Category (Store_Id,Status,Deleted,NAME,oId)

Quote:
INLINE QUERY Optimal Indexes

bv_User (User_Id)
bv_ep_UprOf_qval (User_Id,qvId,qId)
bv_ep_qual_Value (Store_Id,Status,Deleted,Friendly_Name,oId)
bv_Category (Store_Id,NAME,oId)

From which I might create these indexes for use:

Quote:
FINAL Indexes

bv_ep_qual_Value (Store_Id,Status,Deleted,Friendly_Name,oId)
bv_ep_UprOf_qval (qvId,qId,User_Id,oId)
bv_User (User_Id,User_State,User_Alias)
bv_User_Profile (User_Id,hcc_Last_Name,hcc_First_Name)
bv_Category (Store_Id,NAME,oId,Status,Deleted)

Some things to remember:

Quote:
1) indexing should always take into account the total workload whenever possible. Only tune for a specific query if it is absolutely essential.

2) simply adding indexes may not be the solution. Oracle may choose to ignore the indexes altogether based on his idea of statistics

3) always remember to collect statistics on your tables and your indexes before doing tuning, otherwish you can pretty much forget about good plans

4) which means... don't do any of this indexing until you are sure you statistics for the tables and indexes on the system currently are up to date and you have rerun your performance tests with these up to date stats.

5) never collect statistics unless you have the permission of you DBA. It is quite possible that collection of stats could change query plans and once in a while not for the better. So you need get you DBA in the loop so he/she can assisit you in case something rare should happen that negatively affects your system.

6) never tune on a production system until after you have reproduced and solved the problem on a test system. Tuning is dangerous too.

Good luck, Kevin
Re: lobg running query [message #343662 is a reply to message #343230] Wed, 27 August 2008 15:06 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Thank you Sir.Let me create all the indexes on the dev box and i will come back.

Regards,
Varun Punj,
Re: lobg running query [message #344036 is a reply to message #343632] Thu, 28 August 2008 12:02 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Hi Kevin,
I created all the indexes.Now query is taking 16 seconds.What can I do to tune it further
Regards,
Varun Punj
Re: lobg running query [message #344038 is a reply to message #343230] Thu, 28 August 2008 12:09 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Did you collect statistics on the tables and indexes before running you tests?

Please post the explain plan. Make sure your plan is formatted (indented), and that you use the CODE tags that the plan appears formatted on ORAFAQ in you post.

If you are testing in SQLPLUS, then do this:

Quote:
set autotrace traceonly

run your query

cut/paste the results from sqlplus (use the code tags)

If you are not testing in sqlplus using autotrace, then do so.

Also please post the results of the following query.

set verify off

break on index_name skip 1
col column_name format a30

select index_name,column_name
,(select uniqueness from dba_indexes b where b.owner = a.index_owner and b.index_name = a.index_name) uniqueness
,(select tablespace_name from dba_indexes b where b.owner = a.index_owner and b.index_name = a.index_name) tablespace_name
from dba_ind_columns a
where table_name = upper('&&2')
and table_owner = upper('&&1')
order by 1,column_position
/


Kevin
Re: lobg running query [message #344048 is a reply to message #343230] Thu, 28 August 2008 12:31 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2058 Card=1 Bytes=
          192)

   1    0   SORT (ORDER BY) (Cost=2058 Card=1 Bytes=192)
   2    1     NESTED LOOPS (Cost=2057 Card=1 Bytes=192)
   3    2       NESTED LOOPS (Cost=2056 Card=1 Bytes=175)
   4    3         NESTED LOOPS (Cost=2056 Card=1 Bytes=169)
   5    4           HASH JOIN (Cost=2055 Card=1 Bytes=147)
   6    5             HASH JOIN (Cost=2052 Card=6 Bytes=732)
   7    6               TABLE ACCESS (FULL) OF 'BV_EP_QUAL_VALUE' (TAB
          LE) (Cost=9 Card=1 Bytes=30)

   8    6               NESTED LOOPS (Cost=2042 Card=28253 Bytes=25992
          76)

   9    8                 MERGE JOIN (CARTESIAN) (Cost=272 Card=883 By
          tes=66225)

  10    9                   MERGE JOIN (CARTESIAN) (Cost=11 Card=1 Byt
          es=51)

  11   10                     TABLE ACCESS (FULL) OF 'BV_EP_QUAL_VALUE
          ' (TABLE) (Cost=9 Card=1 Bytes=30)

  12   10                     BUFFER (SORT) (Cost=2 Card=1 Bytes=21)
  13   12                       TABLE ACCESS (BY INDEX ROWID) OF 'BV_C
          ATEGORY' (TABLE) (Cost=2 Card=1 Bytes=21)

  14   13                         INDEX (RANGE SCAN) OF 'BV_CATEGORY_K
          EY_IDX' (INDEX (UNIQUE)) (Cost=1 Card=1)

  15    9                   BUFFER (SORT) (Cost=270 Card=39455 Bytes=9
          46920)

  16   15                     TABLE ACCESS (FULL) OF 'BV_USER' (TABLE)
           (Cost=261 Card=39455 Bytes=946920)

  17    8                 INDEX (RANGE SCAN) OF 'BV_EP_UPROF_QVAL_PK'
          (INDEX (UNIQUE)) (Cost=2 Card=32 Bytes=544)

  18    5             TABLE ACCESS (BY INDEX ROWID) OF 'BV_CATEGORY' (
          TABLE) (Cost=3 Card=1 Bytes=25)

  19   18               INDEX (RANGE SCAN) OF 'BV_CATEGORY_KEY_IDX' (I
          NDEX (UNIQUE)) (Cost=2 Card=1)

  20    4           TABLE ACCESS (BY INDEX ROWID) OF 'BV_USER_PROFILE'
           (TABLE) (Cost=1 Card=1 Bytes=22)

  21   20             INDEX (UNIQUE SCAN) OF 'BV_USER_PROFILE_ID_IDX'
          (INDEX (UNIQUE)) (Cost=0 Card=1)

  22    3         INDEX (UNIQUE SCAN) OF 'BV_USER_PK' (INDEX (UNIQUE))
           (Cost=0 Card=1 Bytes=6)

  23    2       INDEX (UNIQUE SCAN) OF 'BV_EP_UPROF_QVAL_PK' (INDEX (U
          NIQUE)) (Cost=1 Card=1 Bytes=17)





Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     177178  consistent gets
      17296  physical reads
          0  redo size
        453  bytes sent via SQL*Net to client
        279  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          5  rows processed
Re: lobg running query [message #344049 is a reply to message #343230] Thu, 28 August 2008 12:34 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Please repost without the line wrapping. Use this to fix it.

col PLAN_PLUS_EXP format a400
set linesize 999
set trimspool on
set trimout on



Kevin
Re: lobg running query [message #344050 is a reply to message #343230] Thu, 28 August 2008 12:36 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
I took the statistics with the help of dbms_stats before testing it
please let me know if it is not fine.I tried formatting this in sql formatter but got errors,then i put the ouptput in code/code and sent it.

Regards
Varun Punj,
Re: lobg running query [message #344079 is a reply to message #344050] Thu, 28 August 2008 13:28 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
sqlformatter is only for formatting sql statements and plsql code.

Use the set and column commands I gave you to remove the line wraps, then repost.

Failing that, edit the file yourself and put the line back together.

Kevin
Re: lobg running query [message #344104 is a reply to message #344079] Thu, 28 August 2008 15:19 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2058 Card=1 Bytes=192)
   1    0   SORT (ORDER BY) (Cost=2058 Card=1 Bytes=192)
   2    1     NESTED LOOPS (Cost=2057 Card=1 Bytes=192)
   3    2       NESTED LOOPS (Cost=2056 Card=1 Bytes=175)
   4    3         NESTED LOOPS (Cost=2056 Card=1 Bytes=169)
   5    4           HASH JOIN (Cost=2055 Card=1 Bytes=147)
   6    5             HASH JOIN (Cost=2052 Card=6 Bytes=732)
   7    6               TABLE ACCESS (FULL) OF 'BV_EP_QUAL_VALUE' (TABLE) (Cost=9 Card=1 Bytes=30)
   8    6               NESTED LOOPS (Cost=2042 Card=28253 Bytes=2599276)
   9    8                 MERGE JOIN (CARTESIAN) (Cost=272 Card=883 Bytes=66225)
  10    9                   MERGE JOIN (CARTESIAN) (Cost=11 Card=1 Bytes=51)
  11   10                     TABLE ACCESS (FULL) OF 'BV_EP_QUAL_VALUE' (TABLE) (Cost=9 Card=1 Bytes=30)
  12   10                     BUFFER (SORT) (Cost=2 Card=1 Bytes=21)
  13   12                       TABLE ACCESS (BY INDEX ROWID) OF 'BV_CATEGORY' (TABLE) (Cost=2 Card=1 Bytes=21)
  14   13                         INDEX (RANGE SCAN) OF 'BV_CATEGORY_KEY_IDX' (INDEX (UNIQUE)) (Cost=1 Card=1)
  15    9                   BUFFER (SORT) (Cost=270 Card=39455 Bytes=946920)
  16   15                     TABLE ACCESS (FULL) OF 'BV_USER' (TABLE) (Cost=261 Card=39455 Bytes=946920)
  17    8                 INDEX (RANGE SCAN) OF 'BV_EP_UPROF_QVAL_PK' (INDEX (UNIQUE)) (Cost=2 Card=32 Bytes=544)
  18    5             TABLE ACCESS (BY INDEX ROWID) OF 'BV_CATEGORY' (TABLE) (Cost=3 Card=1 Bytes=25)
  19   18               INDEX (RANGE SCAN) OF 'BV_CATEGORY_KEY_IDX' (INDEX (UNIQUE)) (Cost=2 Card=1)
  20    4           TABLE ACCESS (BY INDEX ROWID) OF 'BV_USER_PROFILE' (TABLE) (Cost=1 Card=1 Bytes=22)
  21   20             INDEX (UNIQUE SCAN) OF 'BV_USER_PROFILE_ID_IDX' (INDEX (UNIQUE)) (Cost=0 Card=1)
  22    3         INDEX (UNIQUE SCAN) OF 'BV_USER_PK' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=6)
  23    2       INDEX (UNIQUE SCAN) OF 'BV_EP_UPROF_QVAL_PK' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=17)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     177178  consistent gets
      17749  physical reads
          0  redo size
        453  bytes sent via SQL*Net to client
        279  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          5  rows processed
Re: lobg running query [message #344106 is a reply to message #343230] Thu, 28 August 2008 15:31 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You might be able to get a performance boost by eliminating out of any FROM clause those tables which contribute no actual data to the SELECT clause.

In the past in selected cases, the SQL sped up by a factor of 50 times.
Re: lobg running query [message #344112 is a reply to message #344079] Thu, 28 August 2008 15:46 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
The indexes on bv_user are:-
INDEX_NAME                     COLUMN_NAME                    UNIQUENES TABLESPACE_NAME
------------------------------ ------------------------------ --------- ----------------------------
BV_USER_ALIAS_INDEX            USER_ALIAS                     UNIQUE    DEV07_PERM

BV_USER_PK                     USER_ID                        UNIQUE    DEV07_PERM

BV_USR_ACCTID_IDX              ACCOUNT_ID                     NONUNIQUE DEV07_PERM

BV_USR_STATE_IDX               USER_STATE                     NONUNIQUE DEV07_PERM

Re: lobg running query [message #344117 is a reply to message #343230] Thu, 28 August 2008 16:01 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
The indexes used by bv_category are:-
INDEX_NAME                     COLUMN_NAME                    UNIQUENES TABLESPACE_NAME
------------------------------ ------------------------------ --------- ----------------------------
BV_CATEGORY_DELETED_IDX        DELETED                        NONUNIQUE DEV07_PERM

BV_CATEGORY_KEY_IDX            NAME                           UNIQUE    DEV07_PERM
                               CONTENT_TYPE                   UNIQUE    DEV07_PERM
                               PARENT_OID                     UNIQUE    DEV07_PERM
                               STORE_ID                       UNIQUE    DEV07_PERM

BV_CATEGORY_PK                 OID                            UNIQUE    DEV07_PERM

BV_CATEGORY_STATUS_IDX         STATUS                         NONUNIQUE DEV07_PERM


INDEX_NAME                     COLUMN_NAME                    UNIQUENES TABLESPACE_NAME
------------------------------ ------------------------------ --------- ----------------------------
BV_CATEGORY_STR_IDX            STORE_ID                       NONUNIQUE DEV07_PERM

IDX_BV_CATEGORY_NAM_SID        SYS_NC00039$                   NONUNIQUE DEV07_PERM
                               STORE_ID                       NONUNIQUE DEV07_PERM
Re: lobg running query [message #344118 is a reply to message #343230] Thu, 28 August 2008 16:03 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Indexes for bv_ep_qual_value are:-
INDEX_NAME                     COLUMN_NAME                    UNIQUENES TABLESPACE_NAME
------------------------------ ------------------------------ --------- ----------------------------
BV_EP_QUAL_DELETED_IDX         DELETED                        NONUNIQUE DEV07_PERM

BV_EP_QUAL_STATUS_IDX          STATUS                         NONUNIQUE DEV07_PERM

BV_EP_QUAL_VALUE_KEY_IDX       QV_NAME                        UNIQUE    DEV07_PERM
                               STORE_ID                       UNIQUE    DEV07_PERM

BV_EP_QUAL_VALUE_PK            OID                            UNIQUE    DEV07_PERM

BV_EP_QUAL_VALUE_STR_IDX       STORE_ID                       NONUNIQUE DEV07_PERM


INDEX_NAME                     COLUMN_NAME                    UNIQUENES TABLESPACE_NAME
------------------------------ ------------------------------ --------- ----------------------------
IDX_BV_EP_QUAL_VALU_FRN_NAM    SYS_NC00010$                   NONUNIQUE DEV07_PERM
Re: lobg running query [message #344119 is a reply to message #343230] Thu, 28 August 2008 16:06 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Indexes used for bv_user_profile are:-
INDEX_NAME                     COLUMN_NAME                    UNIQUENES TABLESPACE_NAME
------------------------------ ------------------------------ --------- ----------------------------
BV_USER_PROFILE_ID_IDX         USER_ID                        UNIQUE    DEV07_PERM

BV_USER_PROF_LAST_IDX          HCC_LAST_NAME                  NONUNIQUE DEV07_PERM

USER_PROF_HCC_FIRST_NAME       HCC_FIRST_NAME                 NONUNIQUE DEV07_PERM
Re: lobg running query [message #344121 is a reply to message #343230] Thu, 28 August 2008 16:11 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Indexes for bv_ep_uprof_qual:-
INDEX_NAME                     COLUMN_NAME                    UNIQUENES TABLESPACE_NAME
------------------------------ ------------------------------ --------- ----------------------------
BV_EP_UPROF_QVAL_KEY_IDX       USER_ID                        NONUNIQUE DEV07_PERM

BV_EP_UPROF_QVAL_PK            USER_ID                        UNIQUE    DEV07_PERM
                               QID                            UNIQUE    DEV07_PERM
                               QVID                           UNIQUE    DEV07_PERM


Re: lobg running query [message #344125 is a reply to message #343230] Thu, 28 August 2008 16:34 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Nice job on the formatting and data collection.

OK first I do not see any of the indexes I suggested. You do not have to use the indexes I suggested. Let me make a couple of comments for you to think about:

Quote:
1) these indexed I gave were designed to optimize this particular statement by eliminating the TABLE ACCESS BY ROWID steps in the query plan, but since they do not exist and/or are not being used this did not happen.

2) when you collect statistics I suggest you use the cascade option for each table to make sure stats are collected for all indexes.

dbms_stats.gather_table_status(...,...,cascade=>true);

3) this query plan says it expects one row to be return in the final result. Is this true. If so, this query should be executing in a fraction of a second I think.

You may need to modify some existing indexes to get the best performance for this particular query. But I would suggest you create the indexes I offered and test with that. In some cases, primary keys may give you trouble. Take for instance the table USER. It has a PK on USER_ID. This PK and its associated unique index will mean that the index I offered will not be used. If I really wanted this query to go fast I would do the following:

Quote:
1) drop the PK on user.
2) create my own (NON-UNIQUE) index on all three of these columns (User_Id,User_State,User_Alias) (it must be non-unique).
3) then recreate the PK constraint on USER (which will bind to this non-unique index INSTEAD of creating its own).

Then the index will get used as I intended in this query.

So... make sure your stats are collected correctly, then build the indexes like I suggested. Then do the tests again. I can walk you through the PK issues if we need to.

Kevin
Re: lobg running query [message #344130 is a reply to message #343230] Thu, 28 August 2008 16:56 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
This is what I used to gather statisics before:-
begin
 dbms_stats.gather_schema_stats( 
ownname          => 'DEV07', 
options          => 'GATHER AUTO', 
estimate_percent => dbms_stats.auto_sample_size, 
method_opt       => 'for all columns size repeat', 
cascade          => true
);
end;
Re: lobg running query [message #344131 is a reply to message #343230] Thu, 28 August 2008 17:00 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Sir
you said you did not see the indexes.But all the new indexes created are there.
Regards,
Varun Punj
Re: lobg running query [message #344137 is a reply to message #343230] Thu, 28 August 2008 18:06 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
You seem to have mis-interpreted my index descriptions.

I said

create index i1 on bv_User (User_Id,User_State,User_Alias);

It looks like you did:

create index i1 on bv_User (User_Id);
create index i2 on bv_User (User_State);
create index i3 on bv_User (User_Alias);

You do understand the difference between single column indexes and concatenated indexes right?

Kevin
Re: lobg running query [message #345504 is a reply to message #344137] Wed, 03 September 2008 21:55 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Hi Kevin,
I changed the query and the i retrieved data in 4-5 seconds.But another issue came up,this query is taking 5 seconds in one schema and 35 seconds in other schema.I gathered statisics on the new schema but still it is taking the lot of time.The new query is:-
SELECT   Up.hcc_Last_Name,
         Up.hcc_First_Name,
         u.User_Alias,
         dIs.User_Id,
         dIs.Friendly_Name AS District,
         qv.Friendly_Name
FROM     bv_User_Profile Up,
         bv_User u,
         bv_ep_qual_Value qv,
         bv_ep_UprOf_qval uq,
         bv_Category c,
         (SELECT u2.User_Id,
                 qv2.Friendly_Name
          FROM   bv_User u2,
                 bv_ep_UprOf_qval uq2,
                 bv_ep_qual_Value qv2,
                 bv_Category c2
          WHERE  u2.User_Id = uq2.User_Id
                 AND uq2.qvId = qv2.oId
                 AND qv2.Status = 1
                 AND qv2.Deleted = 0
                 AND qv2.Store_Id = 107
                 AND uq2.qId = c2.oId
                 AND c2.NAME = 'ATLAS LQA Districts'
                 AND c2.Store_Id = 107) dIs
WHERE    u.User_Id = Up.User_Id
         AND u.User_State = 0
         AND u.User_Id = dIs.User_Id
         AND u.User_Id = uq.User_Id
         AND uq.qvId = qv.oId
         AND qv.Store_Id = 107
         AND qv.Status = 1
         AND qv.Deleted = 0
         AND qv.Friendly_Name IN ('Grading Manager',
                                  'Reviewer')
         AND uq.qId = c.oId
         AND c.Store_Id = 107
         AND c.Status = 1
         AND c.Deleted = 0
         AND c.NAME = 'ATLAS LQA User Role'
ORDER BY dIs.Friendly_Name ASC

Regards,
Varun Punj,
Re: lobg running query [message #345690 is a reply to message #343230] Thu, 04 September 2008 08:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I suggest that you take a look at what is different between the two environment. Start with three things:

1) indexes
2) rowcounts
3) datatabase instance parameter settings (init.ora)

Good luck, Kevin
Re: lobg running query [message #345743 is a reply to message #343230] Thu, 04 September 2008 12:42 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Hi Kevin,
Rowcounts are almost same.
Indexes are same.
Is there any specific parameter i need to compare.
Should i try using stored outlines??

Regards,
Varun Punj,
Re: lobg running query [message #345762 is a reply to message #345743] Thu, 04 September 2008 13:49 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Just do a compare for v$parameter on both systems. Make note to yourself of any values that are different. Then try to imagine why a different parameter might affect performance.

Kevin
Re: lobg running query [message #346056 is a reply to message #343230] Fri, 05 September 2008 14:47 Go to previous messageGo to previous message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Hi Kevin,
I did not see much difference in parameters.So now I created one stored outline for that query.But I dont know how to move stored outline from one schema to another.Can i user Dump utility to move stored outline.
Regards,
Varun Punj,
Previous Topic: help in tuning procedure
Next Topic: Why showing BITMAP in Plan while no bitmap exists for the table
Goto Forum:
  


Current Time: Sun Dec 11 00:27:43 CST 2016

Total time taken to generate the page: 0.10631 seconds