| tuning sql query [message #379456] |
Tue, 06 January 2009 10:25  |
varunvir
Messages: 389
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').
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 #379488 is a reply to message #379462] |
Tue, 06 January 2009 14:40  |
varunvir
Messages: 389
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 |
rleishman
Messages: 3728
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 |
varunvir
Messages: 389
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 |
rleishman
Messages: 3728
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 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 |
varunvir
Messages: 389
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 |
rleishman
Messages: 3728
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
