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

Home -> Community -> Usenet -> c.d.o.server -> Re: "Tuning" small tasks ...

Re: "Tuning" small tasks ...

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 20 Dec 2002 11:48:41 -0000
Message-ID: <3e030399$0$246$ed9e5944@reading.news.pipex.net>


"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:ats6vj$206uo$1_at_ID-152732.news.dfncis.de...
> Hello,
>
> it is not really a tuning question I think, but although the instance has
> been tuned, I sometimes observe strange things
> like the follwing in SQL*Plus:
>
> - selects on very small tables (100 rows or such) may take 20 seconds ...

Don't measure the size of the table in rows but in blocks below the HWM. A 100 row table that is heavily inserted/deleted could in fact be a 100mb table on disk.....

> - desc some_view or desc some_table may also tale 10 seconds ...
> on other times the responses come up at once, just as it should be ...

See if you can trace the session and find out what the session is spending its time doing. It will probably be waiting for something and a trace of the session will show this. If you have metalink access look for docid 21154.1 on level 10046 tracing with waits which you can enable from sqlplus with

DBMS_SUPPORT.START_TRACE(); for your own session and the unsurprisingly named
DBMS_SUPPORT.STOP_TRACE; to stop it. You may need to install dbms_support which you can do by running ?/rdbms/admin/dbmssupp WHEN CONNECTED AS SYSDBA

>
> I could not verify what may cause this behavior. It happens on normal
times
> or on heavy duty times (with much redo),
> with 300 sessions online, but only 10 active simultaniously at the peak. I
> (think that I) have appropriate hit ratios and so forth.

I can't resist commenting on your remark that 'the instance is tuned' which you seem to conclude because some ratios are above a certain value. The instance is probably never tuned (i.e. its an ongoing dba task) but certainly not when operations take too long. In general tune response time down. The various hit ratios kicking about the place merely tell you how efficiently a particular workload has been executed, what they cannot tell you is how efficient the workload itself is. For example updating a table tab2 from an indexed table tab1 can be done via

begin
v_id number;
v_desc char;
for i in 1..10000 loop
select id,desc into v_id,v_desc from tab1 where id = i; update tab2 set description = v_desc where id = v_id; commit;
loop;
end;

and this would probably get a pretty good hit ratio but is a terrible way to do the update because it does far far too much work.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************
Received on Fri Dec 20 2002 - 05:48:41 CST

Original text of this message

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