Home » SQL & PL/SQL » SQL & PL/SQL » Query taking 6 sec to return 2 rows (Oracle 10g)
Query taking 6 sec to return 2 rows [message #334600] Thu, 17 July 2008 05:42 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have a query that is taking around 6 seconds to return only 2 records.

Please have a look at the query and advice me as what changes can be done to this for faster processing of the query.

SELECT DISTINCT oli.oli_order_id_fk orderid,
                oo.ord_salesordernumber orderrefnumber,
                oli.oli_id_pk lineitemnumber, oli.oli_ordered_qty quantity,
                (SELECT COUNT (1)
                   FROM ord_lineitems oli1
                  WHERE oli1.oli_order_id_fk =
                                            oli.oli_order_id_fk)
                                                                lineitemcount
           FROM ord_orders oo, ord_lineitems oli, ord_lineitems oli1
          WHERE oo.ord_id_pk = oli.oli_order_id_fk
            AND NVL (oli.oli_type, 0) != 'D'
            AND oo.ord_id_pk NOT IN (
                           SELECT DISTINCT wkor_order_id_fk
                                      FROM ord_wo_states_history hist,
                                           ord_work_order owo
                                     WHERE hist.wsh_workorder_id_fk =
                                                                owo.wkor_id_pk)


Below is the explain plan of the above query :

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS		112 K	 	970  	 	      	 	 
  SORT AGGREGATE		1  	4  	 	 	      	 	 
    INDEX RANGE SCAN	IDX_ORD_LINEITEMS_ORDER_ID_FK	1  	4  	1  	 	      	 	 
  PX COORDINATOR		  	 	 	 	      	 	 
    PX SEND QC (RANDOM)	:TQ10006	112 K	3 M	970  	:Q1006	 P->S 	 	 
      HASH UNIQUE		112 K	3 M	970  	:Q1006	 PCWP 	 	 
        PX RECEIVE		112 K	3 M	64  	:Q1006	 PCWP 	 	 
          PX SEND HASH	:TQ10005	112 K	3 M	64  	:Q1005	 P->P 	 	 
            HASH JOIN		112 K	3 M	64  	:Q1005	 PCWP 	 	 
              BUFFER SORT		  	 	 	:Q1005	 PCWC 	 	 
                PX RECEIVE		405  	5 K	5  	:Q1005	 PCWP 	 	 
                  PX SEND BROADCAST	:TQ10001	405  	5 K	5  	 	 S->P 	 	 
                    TABLE ACCESS FULL	ORD_LINEITEMS	405  	5 K	5  	 	      	 	 
              MERGE JOIN CARTESIAN		102 K	1 M	57  	:Q1005	 PCWP 	 	 
                SORT JOIN		  	 	 	:Q1005	 PCWP 	 	 
                  HASH JOIN ANTI		253  	3 K	23  	:Q1005	 PCWP 	 	 
                    BUFFER SORT		  	 	 	:Q1005	 PCWC 	 	 
                      PX RECEIVE		363  	4 K	8  	:Q1005	 PCWP 	 	 
                        PX SEND HASH	:TQ10002	363  	4 K	8  	 	 S->P 	 	 
                          TABLE ACCESS FULL	ORD_ORDERS	363  	4 K	8  	 	      	 	 
                    PX RECEIVE		110  	440  	14  	:Q1005	 PCWP 	 	 
                      PX SEND HASH	:TQ10004	110  	440  	14  	:Q1004	 P->P 	 	 
                        VIEW	VW_NSO_1	110  	440  	14  	:Q1004	 PCWP 	 	 
                          NESTED LOOPS SEMI		110  	1 K	14  	:Q1004	 PCWP 	 	 
                            BUFFER SORT		  	 	 	:Q1004	 PCWC 	 	 
                              PX RECEIVE		  	 	 	:Q1004	 PCWP 	 	 
                                PX SEND ROUND-ROBIN	:TQ10000	  	 	 	 	 S->P 	 	 
                                  TABLE ACCESS FULL	ORD_WORK_ORDER	843  	6 K	11  	 	      	 	 
                            INDEX RANGE SCAN	IDX_ORD_WO_ST_HIST_WO_ID_FK	57  	228  	1  	:Q1004	 PCWP 	 	 
                BUFFER SORT		405  	 	57  	:Q1005	 PCWP 	 	 
                  BUFFER SORT		  	 	 	:Q1005	 PCWC 	 	 
                    PX RECEIVE		405  	 	2  	:Q1005	 PCWP 	 	 
                      PX SEND BROADCAST	:TQ10003	405  	 	2  	 	 S->P 	 	 
                        INDEX FAST FULL SCAN	SYS_C0092027	405  	 	2  	 	      	 	 

I tried to make little change to remove DISTINCT but it returned no good result for faster processing :
SELECT DISTINCT oli.oli_order_id_fk orderid,
                oo.ord_salesordernumber orderrefnumber,
                oli.oli_id_pk lineitemnumber, oli.oli_ordered_qty quantity,
                (SELECT COUNT (1)
                   FROM ord_lineitems oli1
                  WHERE oli1.oli_order_id_fk =
                                            oli.oli_order_id_fk)
                                                                lineitemcount
           FROM ord_orders oo, ord_lineitems oli, ord_lineitems oli1
          WHERE oo.ord_id_pk = oli.oli_order_id_fk
            AND NVL (oli.oli_type, 0) != 'D'
            AND oo.ord_id_pk NOT IN (
                           SELECT wkor_order_id_fk
                                      FROM ord_work_order owo
                            WHERE EXISTS
                                 (
                                   SELECT 1 FROM   ord_wo_states_history hist
                                    WHERE  hist.wsh_workorder_id_fk =
                                                                owo.wkor_id_pk                                      
                                )
               


Please help me tune this query.

Thanks,
Mahi
Re: Query taking 6 sec to return 2 rows [message #334604 is a reply to message #334600] Thu, 17 July 2008 05:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You didn't remove the DISTINCT - you replaced it with a much more complex subqyery.
Replace this code:
AND oo.ord_id_pk NOT IN (
                           SELECT DISTINCT wkor_order_id_fk
                                      FROM ord_wo_states_history hist,
                                           ord_work_order owo
                                     WHERE hist.wsh_workorder_id_fk =
                                                                owo.wkor_id_pk)
with this:
AND oo.ord_id_pk NOT IN (
                           SELECT wkor_order_id_fk
                                      FROM ord_wo_states_history hist,
                                           ord_work_order owo
                                     WHERE hist.wsh_workorder_id_fk =
                                                                owo.wkor_id_pk)


check that you've got an index on ord_lineitems.oli_order_id_fk.

Some of your tables look like they're views, so there's not a lot we can do without knowing more details of them,

Re: Query taking 6 sec to return 2 rows [message #334606 is a reply to message #334604] Thu, 17 July 2008 06:09 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks for your reply JRow.
I didn't remove DISTINCT because it is NULL column and not unique. There is an index on the column ord_lineitems.oli_order_id_fk but its not Unique.

They are all tables and not views and they all have some indexes but none are unique indexes except the Primary keys.
Any primary key column is having the suffix "_pk" in their columns.

The query is getting slow only because of the last part of the query which consist of the NOT IN clause.

[Updated on: Thu, 17 July 2008 06:19]

Report message to a moderator

Re: Query taking 6 sec to return 2 rows [message #334608 is a reply to message #334606] Thu, 17 July 2008 06:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It doesn't matter whether it's unique or not. Both versions of the subquery will fetch the same amount of data from the database, but the one with the Distinct will have an extra processing step of removing the distinct values.

Given that you're trying to get the number of values sharing the same oli_order_id_fk value, I'd hardly expected a unique index.

You've got a duplicate table in your where clause, which is not joined to anything - that will be slowing you down a bit.
Replace this:
FROM ord_orders oo, ord_lineitems oli, ord_lineitems oli1
with this:
FROM ord_orders oo, ord_lineitems oli
Re: Query taking 6 sec to return 2 rows [message #334618 is a reply to message #334608] Thu, 17 July 2008 06:59 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi JRow,
I have come to the root of the problem now.

The code below is only taking 4 seconds to run when returning only one record.

SELECT ord_id_pk
  FROM ord_orders oo, ord_work_order wo , ord_wo_states_history his
 WHERE oo.ord_id_pk = wo.wkor_order_id_fk
   AND his.wsh_workorder_id_fk = wo.wkor_id_pk


The two joins are taking time. If I run it with only one join it runs in Milliseconds. Please advice as what change I need to make to the code above to make it faster.

Re: Query taking 6 sec to return 2 rows [message #334619 is a reply to message #334618] Thu, 17 July 2008 07:06 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
The two joins are taking time. If I run it with only one join it runs in Milliseconds

No, I don't think it does.
It may well return the first rows quicker, but it's returning a totally different and larger set of rows.

This query:
SELECT ord_id_pk
  FROM ord_orders oo, ord_work_order wo , ord_wo_states_history his
 WHERE oo.ord_id_pk = wo.wkor_order_id_fk
   AND his.wsh_workorder_id_fk = wo.wkor_id_pk
has to examin pretty much every row in all 3 tables.
If you want to speed it up, find some additional clauses that you can use to restrict the set of rows.

What effect did removing the duplicate table have on the speed?
Previous Topic: scheduler
Next Topic: os file modified or not through PL/SQL (urgent)
Goto Forum:
  


Current Time: Fri Dec 09 23:18:45 CST 2016

Total time taken to generate the page: 0.10563 seconds