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: Please help, comment required urgently

RE: Please help, comment required urgently

From: Leonard, George <george.leonard_at_farnell.com>
Date: Sun, 20 Oct 2002 03:38:25 -0800
Message-ID: <F001.004EE4FA.20021020033825@fatcity.com>


Hi all,  

Ok I am having the people look at the sql in question. For those interested the system is Siebel. So for those that know there is normally not a lot you can do regarding the sql it generates. I am going to need to figure out if we can possible generate a different index to cause it to follow a faster path, but as you know Siebel uses hints to indexes so hoping for the best.  

Initially I missed the logical reads, what did catch my eye was db file sequential read (index scans) having a high wait value and also high wait/txn 160, would you also consider this high for a txn ? Log file sync also seemed a bit high for me, comment?  

I noticed a table scan (short tables) with a total of 144,476 and a per second of 33.1 what got my interest though was the 20.4 per trans, what do you think of this.  

Also the avg waits (ms) for the table spaces, had a 400+'s avg wait and a couple of 100-200, this worried me, do.  

Architecture wise for those interested,  

This is a E10K with 6 GB ram, 8 CPU's 2 Storage 5200 each with 7 disks (14 total) striped as 7 together mirror to the 2nd 7 disk (Raid 0+1) being humiliated.  

PS still waiting for oraperf.com to send me my password so that I can scan the Statspack report. If Jared you have the output handy pls forward it to me,  

Thx for the help    

George



George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:george.leonard_at_za.didata.com
Web: http://www.didata.co.za <http://www.didata.co.za>  

You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit!
-----Original Message-----
Sent: 20 October 2002 07:48 AM
To: Multiple recipients of list ORACLE-L  

I think the main performance bottleneck of your system is that your system is doing a lot of logical read than necessary. So, your cpu usage is rather high.
And you get a lot of latch free event for your wait event.

To get your system work better, i suggest you tune the sql with the most buffer_gets, which you can see from the statspack sql part. Tune the sql, use index scan etc.

This should help. I once met an exact case and finally tuned it via the sql

And your log buffer is huge, i think 256-3M is enough for most systems.

Good luck and hope your give back the feedback.

> Hi guys, I need a second opinion on the following Statspack output, I got
my
> suspicions but my manager and the client is not buying what I am say,
>
> Not knowing anything of the system architecture please look at the output
> and say what would concern you. What assumptions/recommendations you would
> make.
>
> Thx>
>
>
> George
> ________________________________________________
> George Leonard
> Oracle Database Administrator
> Dimension Data (Pty) Ltd
> (Reg. No. 1987/006597/07)
> Tel: (+27 11) 575 0573
> Fax: (+27 11) 576 0573
> E-mail:george.leonard_at_za.didata.com
> Web: http://www.didata.co.za
>
> You Have The Obligation to Inform One Honestly of the risk, And As a
Person
> You Are Committed to Educate Yourself to the Total Risk In Any Activity!
> Once Informed & Totally Aware of the Risk, Every Fool Has the Right to
Kill
> or Injure Themselves as They See Fit!
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Leonard, George
  INET: george.leonard_at_farnell.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Sun Oct 20 2002 - 06:38:25 CDT

Original text of this message

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