Home » RDBMS Server » Performance Tuning » Query taking 10 seconds to fetch only 300 records (Oracle 9i)
Query taking 10 seconds to fetch only 300 records [message #322037] Thu, 22 May 2008 04:29 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have to optimize the below query. Currently it takes around 10 seconds and fetches only 300 records. The records can increase upto lacs. Please advice a way to tune this query as its taking long time to execute.


SELECT   BaseData.*
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,
                 ar.Role_Id_pk RoleId
          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,
                 Adm_Roles ar
          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 ows.wrs_Operation_Type_Id_fk = ar.Role_opr_Type_Id_fk
                 AND ar.Role_Id_pk = 19
                 AND owo.wkOr_Status_Code != 129
                 AND Ast.sta_State_Key_un NOT IN (SELECT v_sta_State_Key_un
                                                  FROM   vwStatesStartComplete vsc
                                                  WHERE  vsc.v_Start_Complete = 0
                                                         AND vsc.v_sta_Operation_Type_Id_fk = ows.wrs_Operation_Type_Id_fk)) 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'
         AND BaseData.ryg != 'B'
ORDER BY DueDate,
         BaseData.OrderId ASC,
         BaseData.WorkOrderId ASC


Below is the explain for the above query...
Plan
1 Rows were retrieved using the unique index OCECAT.ORD_WORK_ORDER_PK .
2 Rows from table OCECAT.ORD_WORK_ORDER  were accessed using rowid got from an index.
3 One or more rows were retrieved using index OCECAT.ORDER_ID .  The index was scanned in ascending order..
4 Rows from table OCECAT.ORD_ORDERS  were accessed using rowid got from an index.
5 For each row retrieved by step 2, the operation in step 4 was performed to find a matching row.
6 One or more rows were retrieved using index OCECAT.IDX_ORD_WKOR_WS_MAPPING_WO_ID .  The index was scanned in ascending order..
7 Rows from table OCECAT.ORD_WKOR_WS_MAPPING  were accessed using rowid got from an index.
8 For each row retrieved by step 5, the operation in step 7 was performed to find a matching row.
9 Every row in the table OCECAT.ADM_SITE_CONFIGURATION  is read.
10 BUFFER SORT
11 Every row in step 8 was joined to every row in step 10.
12 Every row in the table OCECAT.ADM_STATES  is read.
13 Rows from step 11 which matched rows from step 12 were returned (hash join).
14 The rows were sorted in order to be grouped.
15 A view definition was processed, either from a stored view SYS.VW_SQ_1  or as defined by steps 14.
16 Rows were retrieved using the unique index OCECAT.ORD_WORK_ORDER_PK .
17 Rows from table OCECAT.ORD_WORK_ORDER  were accessed using rowid got from an index.
18 One or more rows were retrieved using index OCECAT.ORDER_ID .  The index was scanned in ascending order..
19 Rows from table OCECAT.ORD_ORDERS  were accessed using rowid got from an index.
20 For each row retrieved by step 17, the operation in step 19 was performed to find a matching row.
21 One or more rows were retrieved using index OCECAT.IDX_ORD_WKOR_WS_MAPPING_WO_ID .  The index was scanned in ascending order..
22 Rows from table OCECAT.ORD_WKOR_WS_MAPPING  were accessed using rowid got from an index.
23 For each row retrieved by step 20, the operation in step 22 was performed to find a matching row.
24 Every row in the table OCECAT.ADM_SITE_CONFIGURATION  is read.
25 BUFFER SORT
26 Every row in step 23 was joined to every row in step 25.
27 Every row in the table OCECAT.ADM_STATES  is read.
28 Rows from step 26 which matched rows from step 27 were returned (hash join).
29 The results were sorted to support the ORDER BY clause.
30 A view definition was processed, either from a stored view OCECAT.VWSRYGBS  or as defined by steps 29.
31 The result sets from steps 15, 30 were joined (hash).
32 Processing was stopped when the specified number of rows from step 31 were processed.
33 Rows were retrieved using the unique index OCECAT.SYS_C0091722 .
34 Rows from table OCECAT.ADM_ROLES  were accessed using rowid got from an index.
35 One or more rows were retrieved using index OCECAT.IDX_ORD_WRK_STPS_OP_TYPE_ID_FK .  The index was scanned in ascending order..
36 Rows from table OCECAT.ORD_WORK_STEPS  were accessed using rowid got from an index.
37 For each row retrieved by step 34, the operation in step 36 was performed to find a matching row.
38 Every row in the table OCECAT.ORD_WKOR_WS_MAPPING  is read.
39 The result sets from steps 37, 38 were joined (hash).
40 Rows were retrieved using the unique index OCECAT.ORD_WORK_STEPS_IN_PK .
41 Rows from table OCECAT.ORD_WORK_STEPS_IN  were accessed using rowid got from an index.
42 For each row retrieved by step 39, the operation in step 41 was performed to find a matching row.
43 One or more rows were retrieved using index OCECAT.IDX_ORD_WRK_STPS_OP_TYPE_ID_FK .  The index was scanned in ascending order..
44 Rows from table OCECAT.ORD_WORK_STEPS  were accessed using rowid got from an index.
45 For each row retrieved by step 42, the operation in step 44 was performed to find a matching row.
46 Rows were retrieved using the unique index OCECAT.ORD_WORK_ORDER_PK .
47 Rows from table OCECAT.ORD_WORK_ORDER  were accessed using rowid got from an index.
48 One or more rows were retrieved using index OCECAT.ORDER_ID .  The index was scanned in ascending order..
49 Rows from table OCECAT.ORD_ORDERS  were accessed using rowid got from an index.
50 For each row retrieved by step 47, the operation in step 49 was performed to find a matching row.
51 One or more rows were retrieved using index OCECAT.IDX_ORD_WKOR_WS_MAPPING_WO_ID .  The index was scanned in ascending order..
52 Rows from table OCECAT.ORD_WKOR_WS_MAPPING  were accessed using rowid got from an index.
53 For each row retrieved by step 50, the operation in step 52 was performed to find a matching row.
54 Every row in the table OCECAT.ADM_SITE_CONFIGURATION  is read.
55 BUFFER SORT
56 Every row in step 53 was joined to every row in step 55.
57 Every row in the table OCECAT.ADM_STATES  is read.
58 Rows from step 56 which matched rows from step 57 were returned (hash join).
59 The rows were sorted in order to be grouped.
60 A view definition was processed, either from a stored view SYS.VW_SQ_1  or as defined by steps 59.
61 Rows were retrieved using the unique index OCECAT.ORD_WORK_ORDER_PK .
62 Rows from table OCECAT.ORD_WORK_ORDER  were accessed using rowid got from an index.
63 One or more rows were retrieved using index OCECAT.ORDER_ID .  The index was scanned in ascending order..
64 Rows from table OCECAT.ORD_ORDERS  were accessed using rowid got from an index.
65 For each row retrieved by step 62, the operation in step 64 was performed to find a matching row.
66 One or more rows were retrieved using index OCECAT.IDX_ORD_WKOR_WS_MAPPING_WO_ID .  The index was scanned in ascending order..
67 Rows from table OCECAT.ORD_WKOR_WS_MAPPING  were accessed using rowid got from an index.
68 For each row retrieved by step 65, the operation in step 67 was performed to find a matching row.
69 Every row in the table OCECAT.ADM_SITE_CONFIGURATION  is read.
70 BUFFER SORT
71 Every row in step 68 was joined to every row in step 70.
72 Every row in the table OCECAT.ADM_STATES  is read.
73 Rows from step 71 which matched rows from step 72 were returned (hash join).
74 The results were sorted to support the ORDER BY clause.
75 A view definition was processed, either from a stored view OCECAT.VWSRYGBS  or as defined by steps 74.
76 The result sets from steps 60, 75 were joined (hash).
77 Processing was stopped when the specified number of rows from step 76 were processed.
78 Rows were retrieved using the unique index OCECAT.ORD_WORK_ORDER_PK .
79 Rows from table OCECAT.ORD_WORK_ORDER  were accessed using rowid got from an index.
80 For each row retrieved by step 45, the operation in step 79 was performed to find a matching row.
81 Rows were retrieved using the unique index OCECAT.SYS_C0092027 .
82 Rows from table OCECAT.ORD_LINEITEMS  were accessed using rowid got from an index.
83 For each row retrieved by step 80, the operation in step 82 was performed to find a matching row.
84 One or more rows were retrieved using index OCECAT.ORDER_ID .  The index was scanned in ascending order..
85 Rows from table OCECAT.ORD_ORDERS  were accessed using rowid got from an index.
86 For each row retrieved by step 83, the operation in step 85 was performed to find a matching row.
87 Rows were retrieved using the unique index OCECAT.SYS_C0091764 .
88 Rows from table OCECAT.ADM_STATES  were accessed using rowid got from an index.
89 For each row retrieved by step 86, the operation in step 88 was performed to find a matching row.
90 Every row in the table OCECAT.ADM_STATES_IN  is read.
91 The result sets from steps 89, 90 were joined (hash).
92 Rows were retrieved using the unique index OCECAT.SYS_C0091764 .
93 Rows from table OCECAT.ADM_STATES  were accessed using rowid got from an index.
94 For each row retrieved by step 91, the operation in step 93 was performed to find a matching row.
95 Rows were retrieved using the unique index OCECAT.SYS_C0091764 .
96 Rows from table OCECAT.ADM_STATES  were accessed using rowid got from an index.
97 For each row retrieved by step 94, the operation in step 96 was performed to find a matching row.
98 Rows were retrieved using the unique index OCECAT.CONS_STA_STATE_KEY_UN .
99 Rows from table OCECAT.ADM_STATES  were accessed using rowid got from an index.
100 For each row retrieved by step 97, the operation in step 99 was performed to find a matching row.
101 One or more rows were retrieved using index OCECAT.IDX_ORD_WO_ST_HIST_WO_ID_FK .  The index was scanned in ascending order..
102 Rows from table OCECAT.ORD_WO_STATES_HISTORY  were accessed using rowid got from an index.
103 For the rows returned by step 102, filter out rows depending on filter criteria.
104 Rows were retrieved using the unique index OCECAT.ORD_WORK_ORDER_PK .
105 Rows from table OCECAT.ORD_WORK_ORDER  were accessed using rowid got from an index.
106 For the rows returned by step 105, filter out rows depending on filter criteria.
107 UNION ALL PUSHED PREDICATE 
108 The rows from step 107 were sorted to eliminate duplicate rows.
109 A view definition was processed, either from a stored view OCECAT.  or as defined by steps 108.
110 For each row retrieved by step 100, the operation in step 109 was performed to find a matching row.
111 Rows were retrieved using the unique index OCECAT.ORD_WORK_ORDER_PK .
112 Rows from table OCECAT.ORD_WORK_ORDER  were accessed using rowid got from an index.
113 One or more rows were retrieved using index OCECAT.ORDER_ID .  The index was scanned in ascending order..
114 Rows from table OCECAT.ORD_ORDERS  were accessed using rowid got from an index.
115 For each row retrieved by step 112, the operation in step 114 was performed to find a matching row.
116 One or more rows were retrieved using index OCECAT.IDX_ORD_WKOR_WS_MAPPING_WO_ID .  The index was scanned in ascending order..
117 Rows from table OCECAT.ORD_WKOR_WS_MAPPING  were accessed using rowid got from an index.
118 For each row retrieved by step 115, the operation in step 117 was performed to find a matching row.
119 Every row in the table OCECAT.ADM_SITE_CONFIGURATION  is read.
120 BUFFER SORT
121 Every row in step 118 was joined to every row in step 120.
122 Every row in the table OCECAT.ADM_STATES  is read.
123 Rows from step 121 which matched rows from step 122 were returned (hash join).
124 The rows were sorted in order to be grouped.
125 A view definition was processed, either from a stored view SYS.VW_SQ_1  or as defined by steps 124.
126 Rows were retrieved using the unique index OCECAT.ORD_WORK_ORDER_PK .
127 Rows from table OCECAT.ORD_WORK_ORDER  were accessed using rowid got from an index.
128 One or more rows were retrieved using index OCECAT.ORDER_ID .  The index was scanned in ascending order..
129 Rows from table OCECAT.ORD_ORDERS  were accessed using rowid got from an index.
130 For each row retrieved by step 127, the operation in step 129 was performed to find a matching row.
131 One or more rows were retrieved using index OCECAT.IDX_ORD_WKOR_WS_MAPPING_WO_ID .  The index was scanned in ascending order..
132 Rows from table OCECAT.ORD_WKOR_WS_MAPPING  were accessed using rowid got from an index.
133 For each row retrieved by step 130, the operation in step 132 was performed to find a matching row.
134 Every row in the table OCECAT.ADM_SITE_CONFIGURATION  is read.
135 BUFFER SORT
136 Every row in step 133 was joined to every row in step 135.
137 Every row in the table OCECAT.ADM_STATES  is read.
138 Rows from step 136 which matched rows from step 137 were returned (hash join).
139 The results were sorted to support the ORDER BY clause.
140 A view definition was processed, either from a stored view OCECAT.VWSRYGBS  or as defined by steps 139.
141 The result sets from steps 125, 140 were joined (hash).
142 Processing was stopped when the specified number of rows from step 141 were processed.
143 Rows were retrieved using the unique index OCECAT.SYS_C0092089 .
144 Rows from table OCECAT.ORD_WORK_STEPS  were accessed using rowid got from an index.
145 One or more rows were retrieved using index OCECAT.IDX_ORD_WO_ST_HIST_WO_ID_FK .  The index was scanned in ascending order..
146 Rows from table OCECAT.ORD_WO_STATES_HISTORY  were accessed using rowid got from an index.
147 Rows were retrieved using the unique index OCECAT.ORD_WORK_ORDER_PK .
148 Rows from table OCECAT.ORD_WORK_ORDER  were accessed using rowid got from an index.
149 Return all rows from steps 146, 148 - including duplicate rows.
150 The rows from step 149 were sorted to eliminate duplicate rows.
151 A view definition was processed, either from a stored view OCECAT.  or as defined by steps 150.
152 For each row retrieved by step 144, the operation in step 151 was performed to find a matching row.
153 Rows were retrieved using the unique index OCECAT.SYS_C0091764 .
154 For each row returned by step 152 get the matching row from step 153 If there are not matching rows from step 153 return nulls for those columns.
155 The rows were sorted to support a group operation (MAX,MIN,AVERAGE, SUM, etc).
156 Rows were retrieved using the unique index OCECAT.CONS_STA_STATE_KEY_UN .
157 Rows from table OCECAT.ADM_STATES  were accessed using rowid got from an index.
158 One or more rows were retrieved using index OCECAT.SYS_C0091767 .  The index was scanned in ascending order..
159 For each row retrieved by step 157, the operation in step 158 was performed to find a matching row.
160 For the rows returned by step 110, filter out rows depending on filter criteria.
161 The results were sorted to support the ORDER BY clause.
162 Rows were returned by the SELECT statement.


Please suggest what changes can help this query execute faster.
Thanks,
Mahi
Re: Query taking 10 seconds to fetch only 300 records [message #322043 is a reply to message #322037] Thu, 22 May 2008 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I love these fancy tools that want to help you to read execution plan and make it completly unusable.

Regards
Michel
Re: Query taking 10 seconds to fetch only 300 records [message #322046 is a reply to message #322043] Thu, 22 May 2008 04:52 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Michel,
Please suggest me something as the explain plan I got is really confusing and don't know what to do for the tuning.

Please give me your suggestion..

Thanks for looking into this,
Mahi
Re: Query taking 10 seconds to fetch only 300 records [message #322061 is a reply to message #322046] Thu, 22 May 2008 05:27 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Michel,
I have not used Hints often. Now I added the hint /*+ NO_QUERY_TRANSFORMATION */ and the query returns same rows and same data but in less than one second while without this Hint it takes around 10 seconds.

Please advice if usage of this Hint is fine in my code.


Thanks,
Mahi
Re: Query taking 10 seconds to fetch only 300 records [message #322068 is a reply to message #322061] Thu, 22 May 2008 05:48 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi All,
If Michel is busy replying other questions, can anyone please help me on the usage of this hint.

Thanks in advance,
Mahi
Re: Query taking 10 seconds to fetch only 300 records [message #322085 is a reply to message #322068] Thu, 22 May 2008 07:48 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Read the Performance Tuning Guide for instructions on using Explain Plan.

Or , if you're really interested in getting good help, read the chapter on SQL Trace and TKPROF

Ross Leishman
Previous Topic: Performance issue
Next Topic: DMT
Goto Forum:
  


Current Time: Fri Dec 09 15:26:09 CST 2016

Total time taken to generate the page: 0.29277 seconds