| Very Bad Performance After ReOrg [message #525664] |
Tue, 04 October 2011 09:21  |
 |
drovnik
Messages: 34
Registered: January 2011
Location: Gaborone, Botswana
|
Member |
|
|
I am experiencing very bad performance after completing a reorg.
All indexes are valid after rebuilding and stats are up to date.
STATPACK top statement by gets shows the following query being run 35 times with high gets per execution; I cannot seem to locate the query session and kill it as its not related to the application in any way.
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
1,170,910,588 35 33,454,588.2 58.2 ######## 12255.50 4175502449
Module: JDBC Thin Client
select round((100 * (sum(nvl((d.bytes - nvl(s.bytes,0))/(1024*10
24),0))) / sum(NVL(d.bytes / 1024 / 1024, 0))), 2) DiskUtilizati
on from sys.dba_data_files d, (select file_id, sum(bytes) bytes
from sys.dba_free_space group by file_id) s where (s.file_id (+)
= d.file_id)
STATPACK also shows top waits of db sequential file and cpu, also shows latch free and buffer busy. I understand latch free waits relate to cache buffers chains.
Is there some way I can reduce these wait events also to better my performance.
|
|
|
|
| Re: Very Bad Performance After ReOrg [message #525675 is a reply to message #525664] |
Tue, 04 October 2011 10:47  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hi - do you have statspack level 6 or 7 snapshots from before your reorg? If so, you can get the execution plan that was used before and compare it with what is being used now. That will make tuning the statement much easier. And that is what you have to do: adjust the statement/database/instance/environment to influence the optimizer towards a better exec plan.
(btw, I see you are in Gabarone, do you work for RPC Data, by any chance?)
|
|
|
|
|
|
| Re: Very Bad Performance After ReOrg [message #525704 is a reply to message #525664] |
Tue, 04 October 2011 12:18 |
 |
LNossov
Messages: 318
Registered: July 2011
Location: Germany
|
Senior Member |
|
|
|
Could you please upload the whole statspack report. If you have an old one (before reorg), upload it also. Performance issues after reorg arise mostly because of changing of execution plans.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
