Re: Performance Issue
From: Sybrand Bakker <postmaster_at_sybrandb.demon.nl>
Date: 1999/10/12
Message-ID: <939751714.21292.0.pluto.d4ee154e_at_news.demon.nl>#1/1
Date: 1999/10/12
Message-ID: <939751714.21292.0.pluto.d4ee154e_at_news.demon.nl>#1/1
Hi Tapan,
We will need far more info to resolve this issue. The things you mention
have little impact on performance except if indexes are badly skewed. Most
likely the user is issuing offensive sql and we'll need to isolate that sql
to resolve the problem.
If you could identify the users sid when he is running that program (from
v$session)
you could use v$open_cursor, v$sqltext, v$sqlarea, v$sesstat and
v$session_io, and v$session_waits to see what is exactly happening.
Alternatively (knowing the sid and the serial#) you could use
dbms_system.set_sql_trace_in_session(sid,serial#,true) to isolate the sql in
a trace file.
Hth,
-- Sybrand Bakker, Oracle DBA Tapan Trivedi <tapan.trivedi_at_abbnm.com> wrote in message news:6EC14873F507D3119B8C0090273C43490A850C_at_jdasoftware.com...Received on Tue Oct 12 1999 - 00:00:00 CEST
> Hi Guys,
> I have a client who is screaming of a performance problem associated
> with a particular functionality. All the tables for that functionality
> are in a single tablespace. All the indexes in a different tablespace.
> All the tables for that functionality look ok except for one ESDATA.
> This is the main table which is the most used which has a next extent of
> close to 50m. The average next extent for all the objects is around 2m.
> Is there something I can do towards this. The table has around 5.7
> million rows and about 19000 chained rows. What can I do about the same
> ? I am concerned as this is a replicated environment and anything that I
> do has to go over to the next side. Any suggestions,comments,experiences
> are welcome.
>
> Thank you.
> Tapan H Trivedi
>
> SQL> select
> SEGMENT_NAME,BYTES,EXTENTS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS
> 2 from dba_segments where extents > 20;
>
> SEGMENT_NAME BYTES EXTENTS INITIAL_EXTENT NEXT_EXTENT
> MIN_EXTENTS
> -------------------- ---------- ---------- -------------- -----------
> -----------
> EVTAUG 75581440 37 10240
> 2097152 1
> ESDATA 195174400 69 2097152
> 52428800 1
> EVTMSG 100771840 49 10240
> 2099200 1
>
>