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 Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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 #343245 is a reply to message #343230] |
Tue, 26 August 2008 14:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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 #343264 is a reply to message #343230] |
Tue, 26 August 2008 15:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 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 #343349 is a reply to message #343230] |
Wed, 27 August 2008 00:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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 #343568 is a reply to message #343230] |
Wed, 27 August 2008 09:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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 #343585 is a reply to message #343230] |
Wed, 27 August 2008 10:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 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 #344048 is a reply to message #343230] |
Thu, 28 August 2008 12:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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 #344050 is a reply to message #343230] |
Thu, 28 August 2008 12:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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 #344104 is a reply to message #344079] |
Thu, 28 August 2008 15:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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 #344112 is a reply to message #344079] |
Thu, 28 August 2008 15:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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 #344130 is a reply to message #343230] |
Thu, 28 August 2008 16:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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 #345504 is a reply to message #344137] |
Wed, 03 September 2008 21:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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 #345743 is a reply to message #343230] |
Thu, 04 September 2008 12:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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 #346056 is a reply to message #343230] |
Fri, 05 September 2008 14:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to previous message](/forum/theme/orafaq/images/down.png) |
varunvir
Messages: 389 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,
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:26:49 CDT 2024
|