Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Database sloowww...trying to figure out why...

Re: Database sloowww...trying to figure out why...

From: Darrell Landrum <darrell_at_landrum.com>
Date: Mon, 12 Apr 2004 23:35:17 -0500
Message-ID: <003801c42110$b9ab6f70$6501a8c0@darrell1>


Chris,
Others may find me barking up the wrong tree (wouldn't be the first time), but at first glance, I have to wonder about IO contention. In this trace file there is a great deal of "control file sequential reads", which the number of them alone isn't typically an issue, but on a workload this small, the elapsed time of these waits add up. What's much, much worse though are the waits that are doing writing, "control file parallel write" and "db file single write". The elapsed of these waits (I don't recall if this is centiseconds or milliseconds) is lonnng. I've rarely seen reads/writes with elapsed of 3 digits and never of 4 digits. This might be an area to start troubleshooting and I'll look some more tomorrow, but I've got other work to do for now and I suspect you'll get some really good feedback from other folks on the list. One last train of thought, with control file parallel writes and db file single writes (desc in docs as a write to the file header), I'm wondering if this is indicative of a lot of check pointing / maybe too frequent log file switches. Just some quick guessing here, so I too will be interested in seeing some other responses.

Good luck!

  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



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Tue Apr 13 2004 - 00:09:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US