Home » SQL & PL/SQL » SQL & PL/SQL » tuining sqlquery
tuining sqlquery [message #219421] Wed, 14 February 2007 05:44 Go to next message
donind
Messages: 95
Registered: February 2007
Member
hi

This portion of the procedure is taking on an average 1 hour 30 minutes I am sure it’s the second loop which is taking so much time. Can we improve it by either improving the sql or indexing any table etc?

UPDATE MST_DO_APPROVE OUTER SET AUTO_APPROVE_FLAG = 0,AUTO_APPROVE_WINDOW = 7,AUTO_APPROVE_FLAG_SYS=0,
USE_SYSTEM_LOGIC = 0 ,SYS_LAST_MODIFIED_DATE = SYSDATE WHERE AUTO_APPROVE_WINDOW is null and sys_ent_state = 'ACTIVE';

COMMIT;

UPDATE MST_DO_APPROVE OUTER SET AUTO_APPROVE_FLAG = 1,AUTO_APPROVE_WINDOW = 7,AUTO_APPROVE_FLAG_SYS=0,
USE_SYSTEM_LOGIC = 0 ,SYS_LAST_MODIFIED_DATE = SYSDATE WHERE (SCENARIO_ID,ITEM_ID,LOCATION_ID) =
(SELECT 0,MSTITEM.ITEM_ID,LOCATION_ID FROM MST_LOCATION ,MST_ITEM mstitem WHERE
(CATEGORY = 'SPARES' OR CATEGORY = 'ACCESSORIES') AND
LOCATION_CATEGORY in ('RETAIL','DIRECT','INSURANCE','DHRP','PRDC')

AND LOCATION_ID = OUTER.LOCATION_ID AND
OUTER.ITEM_ID = mstitem.ITEM_ID AND
mstitem.SCENARIO_ID = 0 AND
(
(is_rp_initialized = '0' or is_rp_initialized is null ) OR (is_bod_initialized = '0' or is_bod_initialized is null ))
) and sys_ent_state = 'ACTIVE';

UPDATE MST_DO_APPROVE OUTER SET AUTO_APPROVE_FLAG = 0,AUTO_APPROVE_WINDOW = 1,AUTO_APPROVE_FLAG_SYS=0,
USE_SYSTEM_LOGIC = 1,SYS_LAST_MODIFIED_DATE = SYSDATE WHERE (SCENARIO_ID,ITEM_ID) in
(SELECT 0,ITEM_ID FROM MST_ITEM WHERE
(CATEGORY = 'HANDSETS' OR CATEGORY = 'REFURBS')) and AUTO_APPROVE_WINDOW != 1 and sys_ent_state = 'ACTIVE';
COMMIT



Any help really appreciated

Thanks in advance

[Updated on: Wed, 14 February 2007 05:47]

Report message to a moderator

Re: tuining sqlquery [message #219427 is a reply to message #219421] Wed, 14 February 2007 06:04 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Any Explain really appreciated
Re: tuining sqlquery [message #219433 is a reply to message #219421] Wed, 14 February 2007 06:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How many rows are impacted? 10? 10 billion?
Re: tuining sqlquery [message #219434 is a reply to message #219427] Wed, 14 February 2007 06:28 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
How to get a explain for that?????
Can you give me a sqeuence of steps
when I tried like this

explain_plan for update......................................

Its giving error message as explain_plan table doesnt exist?
Re: tuining sqlquery [message #219436 is a reply to message #219434] Wed, 14 February 2007 06:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
google for the error message.
Re: tuining sqlquery [message #219587 is a reply to message #219427] Thu, 15 February 2007 01:17 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
HI,

what are the columns you need from plan_table for above mentioned querries to tune.

As I found so many columns in plan_table. If you need all. I will export data into execel sheet and upload over here.

Thanks
Re: tuining sqlquery [message #219589 is a reply to message #219587] Thu, 15 February 2007 01:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use dbms_xplan to extract the data from the plan table.
Re: tuining sqlquery [message #219598 is a reply to message #219589] Thu, 15 February 2007 01:48 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
sorry for my ignorance. how to use dbms_xplan to extract data?

Thanks
Re: tuining sqlquery [message #219601 is a reply to message #219598] Thu, 15 February 2007 02:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
google?
Really, you have to get used to use Google. Almost every oracle related question can be answered, especially questions related to the use of standard packages, error messages etc.

[Updated on: Thu, 15 February 2007 02:01]

Report message to a moderator

icon14.gif  Re: tuining sqlquery [message #219609 is a reply to message #219434] Thu, 15 February 2007 02:41 Go to previous messageGo to next message
vino4ever
Messages: 11
Registered: July 2006
Location: Chennai
Junior Member
Hi,

Try to use the AUTOTRACE option.
For example,

SQL> Set autotrace on;
SQL> <your update query>
SQL> set autotrace off;

The above command will generate the statistics and execution plan of the update query.

Note:
1. You can use this option only in SQL Editor.
2. The user from which you are firing the query should be grated with PLUSTRACE role.

Thanks
Vinod V
Re: tuining sqlquery [message #219611 is a reply to message #219609] Thu, 15 February 2007 02:50 Go to previous message
donind
Messages: 95
Registered: February 2007
Member
Hi,

Here is my actual querry.


UPDATE MST_DO_APPROVE OUTER SET AUTO_APPROVE_FLAG = 1,AUTO_APPROVE_WINDOW = 7,AUTO_APPROVE_FLAG_SYS=0,
USE_SYSTEM_LOGIC = 0 ,SYS_LAST_MODIFIED_DATE = SYSDATE WHERE (SCENARIO_ID,ITEM_ID,LOCATION_ID) =
(SELECT 0,MSTITEM.ITEM_ID,LOCATION_ID FROM MST_LOCATION ,MST_ITEM mstitem WHERE
(CATEGORY = 'SPARES' OR CATEGORY = 'ACCESSORIES') AND
LOCATION_CATEGORY in ('RETAIL','DIRECT','INSURANCE','DHRP','PRDC')

AND LOCATION_ID = OUTER.LOCATION_ID AND
OUTER.ITEM_ID = mstitem.ITEM_ID AND
mstitem.SCENARIO_ID = 0 AND
(
(is_rp_initialized = '0' or is_rp_initialized is null ) OR (is_bod_initialized = '0' or is_bod_initialized is null ))
) and sys_ent_state = 'ACTIVE';


I tuned it to below querry

UPDATE MST_DO_APPROVE OUTER SET AUTO_APPROVE_FLAG = 1,AUTO_APPROVE_WINDOW = 7,AUTO_APPROVE_FLAG_SYS=0,
USE_SYSTEM_LOGIC = 0 ,SYS_LAST_MODIFIED_DATE = SYSDATE WHERE (SCENARIO_ID,ITEM_ID,LOCATION_ID) =
(SELECT 0,MSTITEM.ITEM_ID,MSTLOCATION.LOCATION_ID FROM (Select scenario_id, item_id, category, IS_BOD_INITIALIZED from mst_item where scenario_id=0 and category in (‘SPARES’,’ACCESSORIES’) ) MSTITEM , (select location_id, location_category, is_rp_initialized from mst_location where LOCATION_CATEGORY in ('RETAIL','DIRECT','INSURANCE','DHRP','PRDC') ) mstlocation
WHERE
AND mstlocation.LOCATION_ID = OUTER.LOCATION_ID AND
OUTER.ITEM_ID = mstitem.ITEM_ID AND
(
(mstlocation.is_rp_initialized = '0' or mstlocation.is_rp_initialized is null ) OR (mstitem.is_bod_initialized = '0' or mstitem.is_bod_initialized is null ))
) and sys_ent_state = 'ACTIVE';

Is there any possibilty of still tuning this querry?

Thanks

[Updated on: Thu, 15 February 2007 23:33]

Report message to a moderator

Previous Topic: change data to lower
Next Topic: Running a procedure on time
Goto Forum:
  


Current Time: Fri Dec 02 13:50:55 CST 2016

Total time taken to generate the page: 0.28250 seconds