May I open a second front?
SqlID=3dg8cdtp3b47f called my attention for two reasons.
1- Executed 2808 times, parsed 2808 times - even when using bind variables
Is this a Java application? if this is the case code may be issuing too many PrepareStatement/Close which causes Oracle to (soft) parse it as many times as it is executed.
2- statement hits table MAM_ASSET_ATTR_DOMAIN_VALUES which is your buffer_gets overall champion.
I would check indexing strategy and be sure query reads what's needed and nothing more.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
My Answers inline.
Originally Posted by mahajanakhil198
1. Yes, it is a large java application.
Originally Posted by PAVB
2. I cannot do anything with SQL tuning now except rebuilding the indexes. The development on this application stopped 3 years back.
I exactly know the problematic query i.e. SQL_ID is 3dg8cdtp3b47f. As already stated, it took more than 34 minutes to execute on SQLPLUS. This SQL was obtained after analyzing the application logs for the time when the application session failed and logged off.
Two things I want to know regarding I/O:
Originally Posted by malay_biswal
1. What should be the I/O speed of the system? From where do we find this out?
2. What is the current speed of I/O when I manually execute the problematic query through SQLPLUS?
It is Solaris box hosting Oracle and Application.
I do not know the answer.
It depends on what hardware architecture you are using.
Click Here to Expand Forum to Full Width