Home » RDBMS Server » Performance Tuning » tuning sql query (10.2.0.2)
tuning sql query [message #379456] Tue, 06 January 2009 10:25 Go to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Hi Experts,
The following query was executed 506 times in the production database and had an average
elapsed time of 12seconds.
update wm_pending_users_request pur set pur.REQUEST_STATUS = 'PROCESSED'  
where pur.USER_ALIAS = 'holljame'  and pur.REQUEST_STATUS NOT IN ('PROCESSED', 'INVALID').
There is no index on table.
Upto my knowledge I can create index on user_alis and request_status to tune the query.
Please let me know if this is the right way.
Regards,
Varun Punj,

[Updated on: Tue, 06 January 2009 10:32] by Moderator

Report message to a moderator

Re: tuning sql query [message #379462 is a reply to message #379456] Tue, 06 January 2009 10:52 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
How many records are in the table and how many will be updated by that statement?
Re: tuning sql query [message #379488 is a reply to message #379462] Tue, 06 January 2009 14:40 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Thnaks fro you reply.
Number of records in the table:-505394
This query run four times for given user alias.
But overall it runs more than 500 different user_aliases.
Regards,
Varun Punj,
Re: tuning sql query [message #379567 is a reply to message #379488] Wed, 07 January 2009 01:21 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
- What percentage of rows have REQUEST_STATUS of 'PROCESSED' or 'INVALID'
- What percentage of rows have other values for REQUEST_STATUS

Ross Leishman
Re: tuning sql query [message #379680 is a reply to message #379567] Wed, 07 January 2009 11:09 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Hi Reishman,
Total number of records are-507308
select count(*) from wm_pending_users_request  where  REQUEST_STATUS   IN ('PROCESSED', 'INVALID');
It gives 506659

select count(*) from wm_pending_users_request  where  REQUEST_STATUS   NOT IN ('PROCESSED', 'INVALID');
646

Moreover I checked the ADDM Report and it interpreted that
this query is not using bind variables leading to lot of hard
parses.
Can I set shared_cursor=forced so that it should to avoid use of
literals.
Regards,
Varun Punj


Re: tuning sql query [message #379754 is a reply to message #379680] Wed, 07 January 2009 19:55 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If this was a Data Warehouse type environment, Bitmap indexes would be the best bet - separate BITMAP indexes on USER_ALIAS and REQUEST_STATUS.

However, if this table is part of a Transaction Processing system (many users) then Bitmap Indexes might be inappropriate.

Your best approach would be to change the SQL:

and DECODE(pur.REQUEST_STATUS, 'PROCESSED', NULL, 'INVALID', NULL, 1) = 1


Then create a function-based index on:
ON wm_pending_users_request 
(USER_ALIAS, DECODE(REQUEST_STATUS, 'PROCESSED', NULL, 'INVALID', NULL, 1))


Alternatively, if you know ALL of the statuses other than PROCESSED and INVALID, you could list them in an
IN (..values..)
clause. That would allow using an index on (user_alias, request_status)

Ross Leishman
Re: tuning sql query [message #379959 is a reply to message #379754] Thu, 08 January 2009 11:33 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Thanks Ross for you response.
You mean I should edit the query as follow:-
update wm_pending_users_request pur set pur.REQUEST_STATUS = 'PROCESSED'  
where pur.USER_ALIAS = 'holljame'  and DECODE(pur.REQUEST_STATUS, 'PROCESSED', NULL, 'INVALID', NULL, 1) = 1



The other question is:-
Will Shared_force=forced improve the performance.
Regards,
Varun Punj,


[Updated on: Thu, 08 January 2009 16:52]

Report message to a moderator

Re: tuning sql query [message #380010 is a reply to message #379959] Thu, 08 January 2009 19:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You will also need to create the function-based index.

Cursor sharing wont significantly improve any one execution of any one query, but it MAY improve the load on the system as a whole.

Ross Leishman
Re: tuning sql query [message #380110 is a reply to message #379456] Fri, 09 January 2009 03:20 Go to previous message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Cursor_sharing = force can actually slow queries down.
See http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3696883368520#451098700346847257
for an example
Previous Topic: Select statement is causing performance issue
Next Topic: Oracle 9i
Goto Forum:
  


Current Time: Mon Dec 05 04:55:23 CST 2016

Total time taken to generate the page: 0.04457 seconds