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: logical tuning

RE: logical tuning

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Fri, 29 Nov 2002 11:49:19 -0800
Message-ID: <F001.0050F212.20021129114919@fatcity.com> 





Madhu - Here is what I understand you to be asking: "How do I understand how
my 3rd-party application works with Oracle". Here would be my tips:



    
  
  1.   Search for any books besides the vendor documentation.
  
  2.   Read the vendor documentation VERY carefully. Often the vendor explains
very clearly how they work with Oracle, but it may take a few reads to
understand what they are saying.
  
  3.   Search V$SQL, V$SQL_TEXT. Pull out the SQL text and run explain plan on
it.
  
  4.   Search the Internet for others like yourselves. Today you can often
locate email lists like this one that relate to your product. Maybe you'll
have to start one. Pooled knowledge is a powerful tool. This forum is also a
good place to make contacts. Often vendor developers will participate in
these groups, as long as you don't embarrass them by saying stuff like "why
do you work for such a stupid company?".
  
  5.   My best tip. Try to understand WHY the vendor designed their Oracle
interface the way they did. Don't just assume they are a bunch of idiots
because you are so smart you'd have designed it better. Often the vendor
must work under severe limitations like porting to several databases, or
can't rewrite their whole product overnight just to suit Oracle, or they
assume that most of their sites won't have an Oracle DBA, or they don't want
to make all their programmers PL/SQL experts, etc.






Hope that helps. 


Dennis Williams


DBA, 40%OCP



Lifetouch, Inc.


dwilliams_at_lifetouch.com 



-----Original Message-----


Sent: Friday, November 29, 2002 5:34 AM


To: Multiple recipients of list ORACLE-L




Dennis & Ferenc,


Your discussion is a good read ...



You guys are able to understand how your applications are working WITH
Oracle, like using RULE/COST optimizer , Table Scans and also how it is
using the Oracle capabilities. I also wanted to know more about the
application running on top of Oracle . Would you guys GUIDE me with some
steps ( may be top 10 and how to do that ) , or you have any document which
you have prepared in the past will be great help for guys like me who wanted
to know more :))-



This LIST is always been a great HELP for me... Happy Thanks giving to YOU
ALL.


Thanks


Madhu
 



-----Original Message-----


To: Multiple recipients of list ORACLE-L
Sent: 11/27/2002 4:28 PM



Ferenc 


   Thanks so much for providing an insight into what you do. Lawson uses
Oracle in quite a simpler method. No joins, just individual table
access. No


table scans, each access is hinted to use a specific index. Crude but
effective. The first issue is that it doesn't use all of Oracle's
capabilities. The second issue is that it provides little opportunity
for


Oracle tuning experts such as yourself. But customers keep pressing for
better use of Oracle, so there is hope yet. ;-)


   Based on what I've seen out of Lawson and wait statistics, I'm
applying


my efforts to reducing physical I/O. I just configured several tables
for


the KEEP and RECYCLE pools.



Dennis Williams


DBA, 40%OCP



Lifetouch, Inc.


dwilliams_at_lifetouch.com 




-----Original Message-----


Sent: Wednesday, November 27, 2002 2:20 PM
To: Multiple recipients of list ORACLE-L




Dennis



as you know, there is no 'follow these steps to get a better performing 
application' guide when it comes to tuning. An intimate knowledge of
what 


the application does is a must. I sell myself (tried the street corners
but 


was not getting much intrest) as a Siebel performance tuning specialist,
so 


when customers say 'Oh, you are an Oracle DBA !', I respond with 'No, 
Oracle DBA is just one of the things I do in order to get my job done'. 
there are plenty of DBA's out there, (and DBB's too), but understnading
how 


the application (in my case Siebel) works and what it is trying to 
accomplish from a functional perspective helps me to know immediately
what 


is the framework of limitations I can work in. For instance, Siebel is 
written for RBO, so when someone comes spouting partitions and bitmap 
indexes, I buzz them out on try 1.



now for Siebel specific EIM (Enterprise Integration Manager) type tuning
, 


when I see that index range scans are killing me, I try to reduce the
batch 


size first so that it will not have to go through as many records per
value 


(think of a batch size of 20,000 records where it is doing a correlated 

subquery on just the batch_id). Now change this into 100 batches of 200 
rows each, and immediately you have a huge saving in logical IO, since
each 


time excpet the first iteration, the index blocks and table blocks
should 


be found in DBBC (Also see Cary's paper on www.hotsos.com which goes
into 


deeper details on the latches needed and the recursive calls for buffer 
hits.) Other things include looking at SQL where you can see it is using
an 


index to look up a row in the table to get a single value (column). In
this 


case, for a large load, it may be beneficial to recreate this same index



with the column concatenated on the end, and avoid the table lookup 
altogether. Also knowing EXACTLY how RBO works (there are only about 20 
rules and in reality only 5 or 6 get used in an application), will help
you 


to know when it may even be beneficial to DROP an index (gasp ! can he
be 


serious ? Youbetcha ! ). anyway, that is it for today, class dismissed.



Have a great day !



Ferenc Mantfeld



-----Original Message-----

From:	DENNIS WILLIAMS [SMTP:DWILLIAMS_at_LIFETOUCH.COM]
Sent:	Thursday, November 28, 2002 3:40 AM
To:	Multiple recipients of list ORACLE-L
Subject:	RE: Using RECYCLE pool?





Thanks Denny, Connor, and Ferenc for your helpful suggestions.



Ferenc - I particularly appreciated your insights. This is also a
packaged


app where I can't tune the SQL. It does no table scans (long story, but 
that


is the way this app works). My logic is that the biggest wait (85% of
wait)


is "db file sequential read", and the BHR is fairly low, about 80%. So
my


thought is to increase the buffer, and while I was at it, thought I
would


try the KEEP and RECYCLE pools.


   But I find your comment about logical tuning very interesting. Can
you


explain more, in case I'm missing something basic? Thanks.



Dennis Williams


DBA, 40%OCP



Lifetouch, Inc.


dwilliams_at_lifetouch.com




-----Original Message-----


Sent: Tuesday, November 26, 2002 7:29 PM
To: Multiple recipients of list ORACLE-L




Hi Dennis



I try to not think of the pool names as being descriptive of what they
should be allocated for. I regard them as pool 1 (default), of which I
can


configure two other pools, (pool 2 and pool 3).



For Siebel applications (probably works similar for PSOFT [Joe, you in
on


this thread ?] and SAP), knowing the application and what it does, the
repository tables, like the tables that define position based access,
views, responsibilities, position relationships (team-based visibility
in


Siebel), broadcast messages, workflow rules and rule items, I put them
into 


a separate smaller but very frequently accessed pool, knowing they are
going to get hit at least a few times every minute with a few hundred
users 


logged on.



Then I try to identify those tables that DO get FTS, and if I cannot
tune


the query by placing relevant indices (sometimes it is better to have
FTS



than large index range scan to reduce logical IO, the big performance
killer), put these into a separate pool, and leave the rest in default.
Alternatively, the hot smaller tables go into one pool, the indices in
another and the rest of the tables stay in default. There are various
tricks for this. Oracle 9 makes things easier because you can identify
which indexes are beig used, and then not waste your time with the
others.



Just remember, you will get much further distance from reducing logical
IO's than playing with various buffer pools, though there is a minimal
argument for playing with buffer pools, once logical IO's have been
decreased.



Real-life example : using Siebel EIM, by placing EIM tables into
separate


buffer pools, I saw a small advantage, say 5 - 10 % in buffer cache
latch


reduction and more efficient use of cached IO. But after tuning the
structures so that I reduced logical IO's, I saw a 2000% throughput
improvement of EIM, to the amazement of all skeptics on the project


(also



bumped up initrans and ran multiple parallel streams). So prioritize
where


you spend your tuning efforts. Reduction of logical IO = biggest bang
for


buck !



Getting off my soapbox now. Lots to do.


Ciao :



Ferenc Mantfeld



-----Original Message-----

From:	DENNIS WILLIAMS [SMTP:DWILLIAMS_at_LIFETOUCH.COM]
Sent:	Wednesday, November 27, 2002 8:30 AM
To:	Multiple recipients of list ORACLE-L
Subject:	Using RECYCLE pool?





Is anyone using the Oracle RECYCLE buffer pool? What was your criteria
to


select tables? The application I am considering RECYCLE for doesn't
perform


table scans, so that eliminates one common suggestion.



Dennis Williams


DBA



Lifetouch, Inc.


dwilliams_at_lifetouch.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.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).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: mantfield
  INET: mantfield_at_connexus.net.au

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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: mantfield
  INET: mantfield_at_connexus.net.au

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reddy, Madhusudana
  INET: Madhusudana.Reddy_at_bestbuy.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.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 Fri Nov 29 2002 - 13:49:19 CST












Original text of this message













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