-
Performance Related Issues
Hi All,
This is regarding performance related issue.
1)In the process of tunning sql i am just gathering the SQL which is bad(disk_reads>50 * executions).I got some sql statements.
I was quite surprised with a SQL statement when i was checking for the query execution plan,I could see it uses proper index.Can any one give me a hint how efficent can we tunning more to reduce the disk_reads and the large number of buffer gets.
2)When i was checking the modules & sid which are consuming more CPU using
select rownum as rank, a.*
from (
SELECT v.sid,sess.status, module,program, v.value / (100 * 60) CPUMins
FROM v$statname s , v$sesstat v, v$session sess
WHERE s.name = 'CPU used by this session'
and sess.sid = v.sid
and v.statistic#=s.statistic#
and v.value>0
ORDER BY v.value DESC) a
where rownum < 1
I got top 10 session consuming more CPU resources.I got some of the SQLs which are run by the SID & did explain plan,for me it seems ok.But why does it consumes more CPU.
For eg 10 to 15 mins of CPU which is not acceptable.
Regards,
M.G.Vinod Kumar
-
Re: Performance Related Issues
Originally posted by vinodkmg
Hi All,
This is regarding performance related issue.
1)In the process of tunning sql i am just gathering the SQL which is bad(disk_reads>50 * executions).I got some sql statements.
I was quite surprised with a SQL statement when i was checking for the query execution plan,I could see it uses proper index.Can any one give me a hint how efficent can we tunning more to reduce the disk_reads and the large number of buffer gets.
You need to look at your database cache hit ratio, if it is < 95% then increase it. Moreover, you may need to use multiple buffer pools and/or cache frequently used tables (alter table ... cache)
HTH.
-
Hi,
Thanx for your reply,The hit ratio is very well >=95%.I dont see any problems in that...Please check my query once again.
Regards,
Vinod
-
Your Physical reads may not be the problem, hence a good Buffer Hit Ratio. I'd recommend monitoring queries in your v$sql looking closely at the buffer_gets column. Then pin-point poorly preforming SQL and re-writing it. Physical reads are not neccessarily as slow as Logical reads (i.e. from cache). Therefore increasing your buffer cache potentially will not make an ounce of difference.
Cheers,
OCP 8i, 9i DBA
Brisbane Australia
-
Hi,
To tune the query , try using the hint "FIRST_ROWS".
By doing so, oracle uses a CBO with best response time.
I am sure , your count of buffer gets would improve tremendously.
Thanks,
Sumit
-
As stated by sumit, you may obtain a great improvement using the CBO, but first assure you have your init.ora properly configured and your tables and indexes analyzed, or else you may get a negative impact.
Regards,
ltabora@bancopromerica.com
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|