| query waits [message #152219] |
Wed, 21 December 2005 14:06  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I have a very simple query running very fast with "no cost" but this is the first one on the waiting list in my statpack.
SELECT MAX(NAME_ID)
FROM rr391.NAME
WHERE DSRC_ACCT_ID = :1
1 1 SELECT STATEMENT
1 SORT AGGREGATE
1 1 TABLE ACCESS BY INDEX ROWID RR391.NAME
3 1 INDEX RANGE SCAN RR391.IX_NAME_ACCT_ID
As there's nothing I can do with the query itself, what are my options to troubleshoot this situation?
elapsed_time cpu_time user_io_time_wait
15178075950 6482466111 14207100000
Thanks a lot,mj
|
|
|
|
| Re: query waits [message #152270 is a reply to message #152219] |
Thu, 22 December 2005 01:45  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Create an index on DSRC_ACCT_ID and NAME_ID in that order. Depending on your database version, it should produce a plan something like:
SELECT STATEMENT
SORT (AGGREGATE)
FIRST ROW
INDEX (RANGE SCAN (MIN/MAX)) of 'MY_NEW_INDEX'
_____________
Ross Leishman
[Updated on: Thu, 22 December 2005 01:45] Report message to a moderator |
|
|
|
| Re: query waits [message #152600 is a reply to message #152219] |
Sat, 24 December 2005 10:53 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
Thanks a lot. Your idea saved me a bunch.
Everything runs much better as the IO is less - skipping the table access...
But now there's a problem because the guys think that making the index over 2 fields will cause a BIG slow down in the app...
I cannot see that. I looked at the IO and CPU intensive statements and did not see this one again.
Any idea how I can track down if there are any overhead of the 2 column versus 1 in the index?
Thanks again and Merry Christmas!
mj
|
|
|
|
| Re: query waits [message #152603 is a reply to message #152600] |
Sun, 25 December 2005 01:35 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Maybe you could ask 'the guys' why it would cause such a BIG slow.
It might influence some queries 'at the other end' of the application that you haven't taken into account.
hth
|
|
|
|
| Re: query waits [message #152755 is a reply to message #152219] |
Mon, 26 December 2005 23:22 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
Well, the projected (there's no prove!) "slow down" will be a result from the 2 columns in the index instead of 1, because the application is insert intensive - about 25000 a minute per table, for 12 tables. I have traced single application instance, multiple application instances, and I have not seen any slowing into the insert rate. Something more - on the selects, this statement is no longer into the highest wait IO statements.
My question was general for the best way to track index changes that could impact the performance.
Thanks a lot,mj
|
|
|
|
| Re: query waits [message #152880 is a reply to message #152219] |
Tue, 27 December 2005 07:52 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
In general you have to weigh the benefits an index will get you in doing a select vs the penalty you'll pay doing inserts and deletes to maintain that index. People often exagerate (when they don't measure things to actually find out) the impacts in both of those operations.
So you have some timings, 25000 inserts a minute. So now time it with and without the extra column in the index and confirm whether or not the extra column really adds an overhead that is meaningful to your system, and counteracts the benefit you saw in the speedup of your query.
My guess (but it is only a guess, and so rather meaningless) is that you've already paid most of the overhead in creating the index in the first place. Adding one more column, a small column at that, probably won't do too much damage.
You can also consider (and test both ways) whether doing index key compression would be a benefit to you, if for nothing else but the space savings in the total index size. I doubt it would help you with your current query, but it may help in other activities, and we don't have enough information to be able to even guess either way. But of course test it and get some hard fact numbers to indicate one way or the other. It is one of those general things where you are already testing and measuring with or without the index, might as well add a third case of with key compressed index.
And by the way, those aren't the only two columns in your table are they? Should you make the table index organized?
|
|
|
|
| Re: query waits [message #152894 is a reply to message #152219] |
Tue, 27 December 2005 09:57 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
Thanks a lot.
It's absolutely true that the people usualy are scared from too many indexes or component ones in OLTPs, but all need to be tested... and proven. It depends on so many other things...
I cannot use compression on this particular index as it's updatable - this will cause decompression + compression, and will take longer time. I have another indexes with compression working good for me.
The table is 21 columns/334 row lenght - kind of longer for IOT. There are 4 indexes 2 single column, 1 on 5 columns - covered index for selects, and the new one I changed to 2 columns.
The overflow segment will be big...Do you think this is a good case for IOT? I can try but don't think it'll benefit me a lot... not too sure...
My question is also what of the system views will help me to discover the index overhead?
Thanks a lot,
mj
|
|
|
|
| Re: query waits [message #152902 is a reply to message #152219] |
Tue, 27 December 2005 12:24 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
No, probably not IOT then, I was just thinking if these were the only two columns in the table then it might be a great candidate for it, but that isn't the case.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
