I have a really bad performance on the database as more users got into the system (> 50 users) or after the bulk load or bulk import. I'm not a pure DBA, I'd need help on tuning the server (at least with buffer cache, shared pool size, and pga).
Below is my AWR Report
Per Second Per Transaction
Redo size: 1,131.67 3,025.06
Logical reads: 169.48 453.03
Block changes: 6.54 17.49
Physical reads: 0.18 0.49
Physical writes: 0.39 1.04
User calls: 9.65 25.79
Parses: 3.22 8.61
Hard parses: 0.17 0.45
Sorts: 1.01 2.71
Logons: 0.05 0.13
Executes: 6.09 16.28
Transactions: 0.37
% Blocks changed per Read: 3.86 Recursive Call %: 60.19
Rollback per transaction %: 18.47 Rows per Sort: 40.95
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.07 In-memory Sort %: 100.00
Library Hit %: 97.80 Soft Parse %: 94.82
Execute to Parse %: 47.09 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 95.10 % Non-Parse CPU: 91.53
Shared Pool Statistics
Begin End
Memory Usage %: 75.62 79.10
% SQL with executions>1: 95.39 93.05
% Memory for SQL w/exec>1: 93.56 91.78
Top 5 Timed Events
Event Waits Time(s) Percent Total DB Time Wait Class
class slave wait 122 624 3,490.44 Other
CPU time 11 64.05
control file parallel write 1,180 3 19.57 System I/O
control file sequential read 544 2 13.80 System I/O
log file parallel write 1,358 2 10.52 System I/O
SGA Memory Summary
SGA regions Size in Bytes
Database Buffers 1,853,882,368
Fixed Size 792,964
Redo Buffers 5,242,880
Variable Size 556,000,892
Take a look at Jeff Hunter's blog (Marist89), http://marist89.blogspot.com/2005/07/trace-it_04.html. He has a post about doing SQL tracing. Then start doing SQL tracing and see what your database is really doing. Also look at this page, http://www.psoug.org/reference/trace_tkprof.html. I would use this command to turn on tracing though, "ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';"
Start by clearing out your $ORACLE_ADMIN/udump directory, turn on tracing during the busy part of the day, let it run for an hour, then turn it off and run tkprof on each of the trace files according to Jeff's post. Then look through each of the trace files.
Finally find the poorest performing SQL, tune, then start the process all over again. Continue until you have fixed the worst performing SQL. This might require adding indexes, changing SQL, changing table layouts and foreign keys or some mix of the above.
Please run query below, then post results in between code tags.
HTML Code:
set pages 999
set lines 90
column c1 heading 'Event|Name' format a30
column c2 heading 'Total|Waits' format 999,999,999,999
column c3 heading 'Seconds|Waiting' format 999,999,999
column c4 heading 'Total|Timeouts' format 999,999,999
column c5 heading 'Average|Wait|(in secs)' format 99.999
ttitle 'System-wide Wait Analysis|for current wait events'
select
event c1,
total_waits c2,
time_waited / 100 c3,
total_timeouts c4,
average_wait /100 c5
from
sys.v_$system_event
where
event not in (
'dispatcher timer',
'lock element cleanup',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'pmon timer',
'rdbms ipc message',
'slave wait',
'smon timer',
'SQL*Net break/reset to client',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data to client',
'virtual circuit status',
'WMON goes to sleep'
)
AND
event not like 'DFS%'
and
event not like '%done%'
and
event not like '%Idle%'
AND
event not like 'KXFX%'
order by
c3 desc
;
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.
Please run query below, then post results in between code tags.
So I ran the query against one of my databases that has had performance issues, and it looks like a statspack report. I was wondering what specific things do you suggest people look at in order to diagnose performance problems? Do the following two stats tell me that I am doing too many full tablescans?
Code:
db file scattered read 33,168,651 478,165 0 .014
db file sequential read 69,640,302 407,351 0 .006
Do you spool to a file and grep for the word contention or the word wait? If I see that the longest average wait is less than 4 seconds, what does that really mean? I thought tuning by ratios was for the most part debunked.
I thought tuning by ratios was for the most part debunked.
I'm not looking at ratios but at wait events.
Query shows which wait events are affecting the most your system so you can drill down on them.
As per posted "db file scatered/sequential read" I don't know the answer to your question... and I don't know it for two reasons. 1) I really don't care about ratios but individual wait events and, 2) correct ratio would depend on specific application. May be that ratio is fantastic, who knows?
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.
Query shows which wait events are affecting the most your system so you can drill down on them.
As per posted "db file scatered/sequential read" I don't know the answer to your question... and I don't know it for two reasons. 1) I really don't care about ratios but individual wait events and, 2) correct ratio would depend on specific application. May be that ratio is fantastic, who knows?
So given the waits that we are seeing, does it seem like the waits are caused by replication to or from another database? And perhaps there is some bottleneck in the replication process?
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.
Yes, the system was just rebooted due to slowness and database hanging.
I would say the bad performance is the result of both general performance issue
and bad SQL queries/code. That's what I'm trying to find the root cause. Sorry, I'm not a dba.
Please describe the host system e.g. processors and memory, please also let us know if there is anything else other than this Oracle instance running on the particular host.
Please describe database in terms of size - total space allocation, size of the five larger tables.
You mentioned performance gets hurt "after the bulk load or bulk import". In regards to this item... 1) how many tables are getting loaded?... 2) how many rows are involved in each load?... 3) What are the size of load target tables in number of rows?
Is there any process gathering performance statistics?
You also mention database was shutdown/startup because of performance/hanging issues... did shutdown/startup the instance solved those issues?
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.
Reply With Quote
Originally Posted by PAVB
Bookmarks