RE: Database sloowww...trying to figure out why...
- From: DENNIS WILLIAMS <DWILLIAMS@xxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 30 Mar 2004 15:38:04 -0600
Go to Cary's web site http://www.hotsos.com <http://www.hotsos.com> . There
is a tool available that lets you upload a trace file and have it analyzed.
You may be allowed a free trial.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams@xxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Potluri, Venu (CT Appl Suppt)
Sent: Tuesday, March 30, 2004 3:32 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Database sloowww...trying to figure out why...
Optimizing Oracle Performance by Cary Millsap explains how to interpret the
raw trace data.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[SMTP:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Chris Stephens
Sent: Tuesday, March 30, 2004 4:24 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Database sloowww...trying to figure out why...
Well, what can I say...more performance problems in our apps
environment. The outsourcing company is suggesting things like increasing
buffer cache (seriously!).
This time I've successfully campaigned to alteast get perfstat
installed on the prod db. Here is what I've found so far.
After taking several snapshots it appears we have a cpu issue (sort
of...it's weird because the slowaris box doesn't look stressed for cpa AT
ALL!). As much as 80% of the response time is due to cpu usage. ...of that
cpu usage the majority of it is 'other cpu'. Absolutely everything I try to
do in there is slow (mostly look ups on the data dictionary to try and
figure out what's wrong). Without the ability to trace user sessions, I
thought I would trace my own. Executing statspack.snap takes as much as 25
seconds!!! So I enable 10046 trace and formatted the trace file through
tkprof. The database is 9203. The biggest waits in the trace file are due to
the execute phase. Here is an example:
INSERT into stats$sql_summary
( snap_id
, dbid
, instance_number
, text_subset
, sharable_mem
, sorts
, module
, loaded_versions
, fetches
, executions
, loads
, invalidations
, parse_calls
, disk_reads
, buffer_gets
, rows_processed
, command_type
, address
, hash_value
, version_count
, cpu_time
, elapsed_time
, outline_sid
, outline_category
, child_latch
)
select :b9
, :b8
, :b7
, substrb(sql_text,1,31)
, sharable_mem
, sorts
, module
, loaded_versions
, fetches
, executions
, loads
, invalidations
, parse_calls
, disk_reads
, buffer_gets
, rows_processed
, command_type
, address
, hash_value
, version_count
, cpu_time
, elapsed_time
, outline_sid
, outline_category
, child_latch
from stats$v$sqlxs
where is_obsolete = 'N'
and ( buffer_gets > :b6
or disk_reads > :b5
or parse_calls > :b4
or executions > :b3
or sharable_mem > :b2
or version_count > :b1
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.07 0.07 0 0 0 0
Execute 1 5.27 5.27 0 398 2738 6041
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 2 5.34 5.35 0 398 2738 6041
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 266 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
6041 VIEW (cr=0 r=0 w=0 time=4815086 us)
6041 FILTER (cr=0 r=0 w=0 time=4805641 us)
30284 SORT GROUP BY (cr=0 r=0 w=0 time=4760583 us)
33809 FIXED TABLE FULL X$KGLCURSOR (cr=0 r=0 w=0 time=3106069 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ----------
------------
control file sequential read 24 0.00 0.00
db file sequential read 4 0.00 0.00
async disk IO 2 0.00 0.01
db file single write 2 0.00 0.00
control file parallel write 4 0.00 0.00
rdbms ipc reply 2 0.00 0.00
I'm not so good at looking at raw trace files so I'm not sure what
part of that is relevant and what's not.
I guess what I'm asking is that now that I know that much of our
database time is spent on cpu and it appears (at least in my case) that much
of that is the execute phase where should I go from hear? ...I would imagine
there's a plethora of info in the raw trace file that I'm not privi to due
to ignorance. ...in my defense, I did try getting the miracle 10046
doohickey to work but have given up because I'm on hp not red hat and I
can't figure out what the equivalent of 'ls -l --time=atime *' is in ksh.
...and then it's not guaranteed to work because of possible differences in
trace file format between red hat and hp.
If anyone would like to see the full trace file, I have attached
that.
Thanks for any help.
Anyone know of a 10046 parser that works in an hp environment?
Chris
<< File: prod_ora_26083.trc >>
_____
If you are not an intended recipient of this e-mail, please notify the
sender, delete it and do not read, act upon, print, disclose, copy, retain
or redistribute it. Click here <http://www.ml.com/email_terms/> for
important additional terms relating to this e-mail.
http://www.ml.com/email_terms/ <http://www.ml.com/email_terms/>
_____
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
