| SQL Needs to be tuned [message #325190] |
Thu, 05 June 2008 04:55  |
virmahi
Messages: 266
Registered: April 2008
Location: India
|
Senior Member |
|
|
Hi,
I have to tune the below query as this is taking around 4 seconds when the record when there are only 200 record and one user connected. There can be more than 1000 users connected and more records.
Please suggest me something which can make this query run little more faster. Will any hint help if yes, which one...I tried some randomly but couldn't get much help.
SELECT COUNT (ryg), basedata.ryg
FROM (SELECT (SELECT SUBSTR (vrygbs, 3, 1)
FROM vwsrygbs v1
WHERE v1.vrygbs =
(SELECT MIN (v2.vrygbs)
FROM vwsrygbs v2
WHERE v1.vwkor_id_pk = v2.vwkor_id_pk)
AND ROWNUM = 1
AND owo.wkor_id_pk = v1.vwkor_id_pk) ryg,
owo.wkor_id_pk workorderid, owo.wkor_order_id_fk orderid,
vupdateddate currentstatustime,
vwostate.vstatunkey stateuniqueid,
owo.wkor_required_quantity orderquantity,
oli.oli_sku_number
|| '-'
|| oli.oli_skurevision_number skunumber,
oo.ord_deliverydatetime deliverydatetime,
oo.ord_deliverydatetime duedate, owsi.wsi_name workstepname,
asti.stai_state_name currentstatus,
asti.stai_state_name status,
ast.sta_state_id_pk currentstatusid,
owwm.wowsm_bom_id_fk bomid,
owo.wkor_parent_wo_id parentworkorderid,
owo.wkor_master_wo_id masterworkorderid,
owo.wkor_status_code workorderstatusid,
ows.wrs_id_pk workstepid
FROM ord_orders oo,
vworkorder_states vwostate,
ord_wkor_ws_mapping owwm,
ord_work_steps ows,
ord_lineitems oli,
ord_work_steps_in owsi,
adm_states ast,
adm_states_in asti,
ord_work_order owo
WHERE wkor_order_id_fk = oo.ord_id_pk
AND vwostate.vwoid = owo.wkor_id_pk
AND vwostate.vupdateddate = (
SELECT MAX (vwostate1.vupdateddate)
FROM vworkorder_states vwostate1
WHERE vwostate1.vwoid = owo.wkor_id_pk
AND vwostate1.voprid = ows.wrs_operation_type_id_fk
AND vwostate1.vwsid = ows.wrs_id_pk)
AND vwostate.voprid = ows.wrs_operation_type_id_fk
AND owwm.wowsm_wo_id = owo.wkor_id_pk
AND owwm.wowsm_ws_id = ows.wrs_id_pk
AND oli.oli_order_id_fk = owo.wkor_order_id_fk
AND wkor_ord_lineitem_id_fk = oli_id_pk
AND owsi.wsi_id_fk = ows.wrs_id_pk
AND ast.sta_state_id_pk = asti.stai_state_id_fk
AND vwostate.vstatunkey = ast.sta_state_key_un
AND oo.ord_completed_date IS NULL
AND NVL (oo.ord_type, 0) != 'D'
AND NVL (oli.oli_type, 0) != 'P'
AND NVL (owo.wkor_type, 0) NOT IN ('P', 'X')
AND owsi.wsi_language_id_fk = 1
AND owsi.wsi_language_id_fk = asti.stai_language_id_fk
AND owwm.wowsm_ws_id = vwostate.vwsid
AND owo.wkor_status_code != 129
AND owo.wkor_is_complete != 1
AND vwostate.vupdateddate = (
SELECT MAX (vwostate1.vupdateddate)
FROM vworkorder_states vwostate1
WHERE vwostate1.vwoid =
owo.wkor_id_pk)) basedata,
adm_states as1,
adm_states as2
WHERE as1.sta_state_id_pk = basedata.currentstatusid
AND as2.sta_state_id_pk = basedata.workorderstatusid
AND ( ( (ryg = 'B' OR ryg = 'S')
AND (as1.sta_operation_type_id_fk = as2.sta_operation_type_id_fk
)
)
OR (ryg = 'R' OR ryg = 'Y' OR ryg = 'G')
)
AND basedata.ryg != 'S'
GROUP BY ryg
I am also attaching the explain_plan which doesn't look in a good format. I copied but its not coming formatted...don't know how to do this.
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 110
COUNT STOPKEY
NESTED LOOPS 1 36 24
VIEW VW_SQ_1 1 18 12
SORT GROUP BY 1 44 12
HASH JOIN OUTER 3 132 12
MERGE JOIN CARTESIAN 1 38 8
NESTED LOOPS 1 32 5
NESTED LOOPS 1 20 3
TABLE ACCESS BY INDEX ROWID ORD_WORK_ORDER 1 12 2
INDEX UNIQUE SCAN ORD_WORK_ORDER_PK 1 1
TABLE ACCESS BY INDEX ROWID ORD_ORDERS 360 2 K 1
INDEX RANGE SCAN ORDER_ID 1 0
TABLE ACCESS BY INDEX ROWID ORD_WKOR_WS_MAPPING 1 12 2
INDEX RANGE SCAN IDX_ORD_WKOR_WS_MAPPING_WO_ID 1 1
BUFFER SORT 1 6 6
TABLE ACCESS FULL ADM_SITE_CONFIGURATION 1 6 3
TABLE ACCESS FULL ADM_STATES 75 450 3
VIEW VWSRYGBS 1 18 13
SORT ORDER BY 3 141 13
HASH JOIN OUTER 3 141 12
MERGE JOIN CARTESIAN 1 41 8
NESTED LOOPS 1 35 5
NESTED LOOPS 1 23 3
TABLE ACCESS BY INDEX ROWID ORD_WORK_ORDER 1 15 2
INDEX UNIQUE SCAN ORD_WORK_ORDER_PK 1 1
TABLE ACCESS BY INDEX ROWID ORD_ORDERS 360 2 K 1
INDEX RANGE SCAN ORDER_ID 1 0
TABLE ACCESS BY INDEX ROWID ORD_WKOR_WS_MAPPING 1 12 2
INDEX RANGE SCAN IDX_ORD_WKOR_WS_MAPPING_WO_ID 1 1
BUFFER SORT 1 6 6
TABLE ACCESS FULL ADM_SITE_CONFIGURATION 1 6 3
TABLE ACCESS FULL ADM_STATES 75 450 3
COUNT STOPKEY
NESTED LOOPS 1 36 24
VIEW VW_SQ_1 1 18 12
SORT GROUP BY 1 44 12
HASH JOIN OUTER 3 132 12
MERGE JOIN CARTESIAN 1 38 8
NESTED LOOPS 1 32 5
NESTED LOOPS 1 20 3
TABLE ACCESS BY INDEX ROWID ORD_WORK_ORDER 1 12 2
INDEX UNIQUE SCAN ORD_WORK_ORDER_PK 1 1
TABLE ACCESS BY INDEX ROWID ORD_ORDERS 360 2 K 1
INDEX RANGE SCAN ORDER_ID 1 0
TABLE ACCESS BY INDEX ROWID ORD_WKOR_WS_MAPPING 1 12 2
INDEX RANGE SCAN IDX_ORD_WKOR_WS_MAPPING_WO_ID 1 1
BUFFER SORT 1 6 6
TABLE ACCESS FULL ADM_SITE_CONFIGURATION 1 6 3
TABLE ACCESS FULL ADM_STATES 75 450 3
VIEW VWSRYGBS 1 18 13
SORT ORDER BY 3 141 13
HASH JOIN OUTER 3 141 12
MERGE JOIN CARTESIAN 1 41 8
NESTED LOOPS 1 35 5
NESTED LOOPS 1 23 3
TABLE ACCESS BY INDEX ROWID ORD_WORK_ORDER 1 15 2
INDEX UNIQUE SCAN ORD_WORK_ORDER_PK 1 1
TABLE ACCESS BY INDEX ROWID ORD_ORDERS 360 2 K 1
INDEX RANGE SCAN ORDER_ID 1 0
TABLE ACCESS BY INDEX ROWID ORD_WKOR_WS_MAPPING 1 12 2
INDEX RANGE SCAN IDX_ORD_WKOR_WS_MAPPING_WO_ID 1 1
BUFFER SORT 1 6 6
TABLE ACCESS FULL ADM_SITE_CONFIGURATION 1 6 3
TABLE ACCESS FULL ADM_STATES 75 450 3
HASH GROUP BY 1 136 110
FILTER
TABLE ACCESS BY INDEX ROWID ORD_ORDERS 1 8 1
NESTED LOOPS 3 408 68
HASH JOIN 3 384 65
TABLE ACCESS FULL ORD_WKOR_WS_MAPPING 1 K 14 K 9
NESTED LOOPS 4 K 560 K 55
HASH JOIN 4 K 536 K 54
TABLE ACCESS FULL ORD_WORK_STEPS 345 1 K 3
NESTED LOOPS 333 35 K 51
NESTED LOOPS 333 34 K 50
NESTED LOOPS 333 32 K 49
NESTED LOOPS 333 30 K 48
HASH JOIN 333 28 K 46
TABLE ACCESS FULL ORD_WORK_STEPS 345 1 K 3
HASH JOIN 333 26 K 43
TABLE ACCESS FULL ORD_LINEITEMS 356 3 K 5
NESTED LOOPS 724 51 K 37
MERGE JOIN 724 48 K 35
TABLE ACCESS BY INDEX ROWID ORD_WORK_ORDER 537 10 K 10
INDEX FULL SCAN ORD_WORK_ORDER_PK 39 3
COUNT STOPKEY
NESTED LOOPS 1 36 24
VIEW VW_SQ_1 1 18 12
SORT GROUP BY 1 44 12
HASH JOIN OUTER 3 132 12
MERGE JOIN CARTESIAN 1 38 8
NESTED LOOPS 1 32 5
NESTED LOOPS 1 20 3
TABLE ACCESS BY INDEX ROWID ORD_WORK_ORDER 1 12 2
INDEX UNIQUE SCAN ORD_WORK_ORDER_PK 1 1
TABLE ACCESS BY INDEX ROWID ORD_ORDERS 360 2 K 1
INDEX RANGE SCAN ORDER_ID 1 0
TABLE ACCESS BY INDEX ROWID ORD_WKOR_WS_MAPPING 1 12 2
INDEX RANGE SCAN IDX_ORD_WKOR_WS_MAPPING_WO_ID 1 1
BUFFER SORT 1 6 6
TABLE ACCESS FULL ADM_SITE_CONFIGURATION 1 6 3
TABLE ACCESS FULL ADM_STATES 75 450 3
VIEW VWSRYGBS 1 18 13
SORT ORDER BY 3 141 13
HASH JOIN OUTER 3 141 12
MERGE JOIN CARTESIAN 1 41 8
NESTED LOOPS 1 35 5
NESTED LOOPS 1 23 3
TABLE ACCESS BY INDEX ROWID ORD_WORK_ORDER 1 15 2
INDEX UNIQUE SCAN ORD_WORK_ORDER_PK 1 1
TABLE ACCESS BY INDEX ROWID ORD_ORDERS 360 2 K 1
INDEX RANGE SCAN ORDER_ID 1 0
TABLE ACCESS BY INDEX ROWID ORD_WKOR_WS_MAPPING 1 12 2
INDEX RANGE SCAN IDX_ORD_WKOR_WS_MAPPING_WO_ID 1 1
BUFFER SORT 1 6 6
TABLE ACCESS FULL ADM_SITE_CONFIGURATION 1 6 3
TABLE ACCESS FULL ADM_STATES 75 450 3
SORT JOIN 1 K 48 K 25
VIEW 1 K 48 K 24
SORT UNIQUE 1 K 39 K 24
UNION-ALL
TABLE ACCESS FULL ORD_WO_STATES_HISTORY 270 8 K 11
TABLE ACCESS FULL ORD_WORK_ORDER 774 30 K 11
TABLE ACCESS BY INDEX ROWID ADM_STATES 1 5 1
INDEX UNIQUE SCAN SYS_C0091764 1 0
TABLE ACCESS BY INDEX ROWID ADM_STATES 1 6 1
INDEX UNIQUE SCAN SYS_C0091764 1 0
TABLE ACCESS BY INDEX ROWID ADM_STATES 1 6 1
INDEX UNIQUE SCAN CONS_STA_STATE_KEY_UN 1 0
TABLE ACCESS BY INDEX ROWID ADM_STATES 1 5 1
INDEX UNIQUE SCAN SYS_C0091764 1 0
INDEX UNIQUE SCAN SYS_C0091767 1 5 0
INDEX UNIQUE SCAN ORD_WORK_STEPS_IN_PK 1 5 0
INDEX RANGE SCAN ORDER_ID 1 0
COUNT STOPKEY
NESTED LOOPS 1 36 24
VIEW VW_SQ_1 1 18 12
SORT GROUP BY 1 44 12
HASH JOIN OUTER 3 132 12
MERGE JOIN CARTESIAN 1 38 8
NESTED LOOPS 1 32 5
NESTED LOOPS 1 20 3
TABLE ACCESS BY INDEX ROWID ORD_WORK_ORDER 1 12 2
INDEX UNIQUE SCAN ORD_WORK_ORDER_PK 1 1
TABLE ACCESS BY INDEX ROWID ORD_ORDERS 360 2 K 1
INDEX RANGE SCAN ORDER_ID 1 0
TABLE ACCESS BY INDEX ROWID ORD_WKOR_WS_MAPPING 1 12 2
INDEX RANGE SCAN IDX_ORD_WKOR_WS_MAPPING_WO_ID 1 1
BUFFER SORT 1 6 6
TABLE ACCESS FULL ADM_SITE_CONFIGURATION 1 6 3
TABLE ACCESS FULL ADM_STATES 75 450 3
VIEW VWSRYGBS 1 18 13
SORT ORDER BY 3 141 13
HASH JOIN OUTER 3 141 12
MERGE JOIN CARTESIAN 1 41 8
NESTED LOOPS 1 35 5
NESTED LOOPS 1 23 3
TABLE ACCESS BY INDEX ROWID ORD_WORK_ORDER 1 15 2
INDEX UNIQUE SCAN ORD_WORK_ORDER_PK 1 1
TABLE ACCESS BY INDEX ROWID ORD_ORDERS 360 2 K 1
INDEX RANGE SCAN ORDER_ID 1 0
TABLE ACCESS BY INDEX ROWID ORD_WKOR_WS_MAPPING 1 12 2
INDEX RANGE SCAN IDX_ORD_WKOR_WS_MAPPING_WO_ID 1 1
BUFFER SORT 1 6 6
TABLE ACCESS FULL ADM_SITE_CONFIGURATION 1 6 3
TABLE ACCESS FULL ADM_STATES 75 450 3
SORT AGGREGATE 1 56
NESTED LOOPS OUTER 1 56 8
NESTED LOOPS 1 53 8
TABLE ACCESS BY INDEX ROWID ORD_WORK_STEPS 1 5 1
INDEX UNIQUE SCAN SYS_C0092089 1 0
VIEW 1 48 7
SORT UNIQUE 2 72 7
UNION-ALL
TABLE ACCESS BY INDEX ROWID ORD_WO_STATES_HISTORY 1 31 3
INDEX RANGE SCAN IDX_ORD_WO_ST_HIST_WO_ID_FK 4 1
TABLE ACCESS BY INDEX ROWID ORD_WORK_ORDER 1 41 2
INDEX UNIQUE SCAN ORD_WORK_ORDER_PK 1 1
INDEX UNIQUE SCAN SYS_C0091764 1 3 0
SORT AGGREGATE 1 54
NESTED LOOPS 5 270 7
NESTED LOOPS OUTER 5 255 7
VIEW 5 240 7
SORT UNIQUE 5 165 7
UNION-ALL
TABLE ACCESS BY INDEX ROWID ORD_WO_STATES_HISTORY 4 124 3
INDEX RANGE SCAN IDX_ORD_WO_ST_HIST_WO_ID_FK 4 1
TABLE ACCESS BY INDEX ROWID ORD_WORK_ORDER 1 41 2
INDEX UNIQUE SCAN ORD_WORK_ORDER_PK 1 1
INDEX UNIQUE SCAN SYS_C0091764 1 3 0
INDEX UNIQUE SCAN SYS_C0092089 1 3 0
Sorry for the bad format of the explain_plan given above as I do not know how to copy in a better formatted way from Toad. If anyone knows that please let me know.
Thanks in advance,
Mahi
[Updated on: Thu, 05 June 2008 05:18] Report message to a moderator |
|
|
|
| Re: SQL Needs to be tuned [message #325383 is a reply to message #325190] |
Fri, 06 June 2008 01:45  |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
Remember following Points:-
1) Use less joins of tables
when you are joining tables oracle first get the data in
buffer and then apply join conditions on the data.
use inline views to eliminate rows from each table.
2) First ask yourself what data you require from each table.
Their can be cases where you are accessing only one field
from a particular table and that can be easily get by using
user functions.
Try to remove this one:- MERGE JOIN CARTESIAN??
|
|
|
|
| Re: SQL Needs to be tuned [message #325431 is a reply to message #325383] |
Fri, 06 June 2008 03:32 |
virmahi
Messages: 266
Registered: April 2008
Location: India
|
Senior Member |
|
|
Thanks for your answer Raja,
Can you please give me a small example of removing extra joins as my query... just a example with refernece to my code...so that I can eliminate Merge cartesian as I am not very comfortable with tuning queries. Your help would let me learn things and tune queries in future.
Thanks in advance,
Mahi
|
|
|
|
| Re: SQL Needs to be tuned [message #325444 is a reply to message #325431] |
Fri, 06 June 2008 04:05 |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
Please study Explain Plan first.Then you can understand the logic how Oracle process a query.
How to eliminate join check this out:-
Example:-
Suppose you have two tables Table1 and Table2.
Now your requirement is that you need data from Table1 and need
to eliminate rows returned on basis of certain conditions based on Table2.
You use JOIN for this.
Select Table1.* from Table1,Table2
where Table1.<Column1>=Table2.<Column1>
and Table2.<Column2>=<Certain Value>
But This will access Table1 and Table2 and then eliminate the rows.Oracle takes time to fetch data from both tables and then
eliminate the data returned.
You can use EXISTS to eliminate this:-
Select Table1.* from Table1
Where EXISTS(select 1 from Table2 where Table1.<Column1>=Table2.<Column1>
and Table2.<Column2>=<Certain Value>)
|
|
|
|
| Re: SQL Needs to be tuned [message #325446 is a reply to message #325444] |
Fri, 06 June 2008 04:10 |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
Regarding Merge Join CARTESIAN:-
you are missing some join conditions while accessing your tables.
For Example:-
if we join two tables without a condition then it will result in CARTESIAN product.
select Table1.* from Table1,Table2
Output:--CARTESIAN product
|
|
|
|
| Re: SQL Needs to be tuned [message #325470 is a reply to message #325446] |
Fri, 06 June 2008 05:20 |
virmahi
Messages: 266
Registered: April 2008
Location: India
|
Senior Member |
|
|
Thank you so much Raja for explaining the basics to me. Now I really got some idea by your useful easy example.
I am looking into my query and trying to find if it takes less time by changing the code.....
I will come back to you in case I face any more problem.
Thanks,
Mahi
|
|
|
|
| Re: SQL Needs to be tuned [message #327084 is a reply to message #325190] |
Fri, 13 June 2008 12:46 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Hi rajatratewal, I do not wish to contradict you. Your description of fewer table joins was accurate many years ago for the Oracle Database.
But these days there would be no difference between these two versions of the query. The real thing I would focus on would be to see if the right steps had been taking for the database:
1) are the proper indexes and constraints in place?
2) have statistics been gathered?
3) is this the correct query?
In particular, with #3 above, should the query actually need to retrieve data from the second table, then it is not possible to construct the exists version of the query anyway.
Also, an index on the second table TALBLE2 (COLUMN1,COLUMN2), would likely mean be no access to table2 at all, only its index.
If you believe there is a difference between how these query versions would be executed, can you please provide the details of the process oracle would follow when retrieving the data? A simple psuedo code example would be fine. I am certainly not above believing that I am not familiar with something as Oracle changes every day.
But.. I am pretty sure both versions of this query would be executed the same way on a 8i, 9i, 10g, or 11g database.
I mention all this only because I feel that it would not benefit the OP to spend time re-writing his queries with this approach in mind. They should be focusing on something else.
What do you think?
Thanks, Kevin
|
|
|
|
| Re: SQL Needs to be tuned [message #327279 is a reply to message #327084] |
Sun, 15 June 2008 23:06 |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
Mr. Kevin my main concern was that pick only required data.
Their is no advantage of unnecesaary joins.
Yes the points that you have mentioned are equally important.
But my friend their is no Hard and fast rule for SQL tuning.
I take this approach.I eliminate unnecesary joins first.Check stats & indexes are available.
And it works for me.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
