Re: Performance Issue

I imagine. A job(s) starts working too intesively and other users can't live.
I suspect, several things:

- SQLs are made badly in that Job.
- SQLs allocate too much resources to be faster.
- a Parallel degree is too much, if there.

So. You should check and tune SQL(s) in this Job(s). If native code is inside a wrapped package body you have to trace one. After this you have to tune especially excessive SQLs. If code wrapped, you can impact on performance of this by creating/deleting indexes and/or by setting an appropriate optimizer mode.
One of my suspections is you have OPTIMIZER_MODE=CHOOSE (by default). In this case, especially schema is analyzed or Cost Optimizer hints used inside Job, optimizer can choose hashed/merged joins. This joins needs more memory in comparison with nested loops. Try to set OPTIMIZER_MODE=FIRST_ROWS this will make Job less agressive. Try to split Job on several subjobs and "spread" a load of system. Tune a pulsing snapshots - probably several snapshots started in one time. At last - revise business rules to do Job in not busy time, nightly. Or consider more powerful hardware, disks, memory, processors, Parallel Server....

As you can see it is too difficult to give advices without whole picture what happening in your system. Make utlbstat.sql before and utlestat after your job. Turn on a timing statistics before...

Mark Malakanov,
OraDBA
Sapience, Toronto

Tapan Trivedi <tapan.trivedi_at_abbnm.com> wrote in message news:380340A5.1A302F6C_at_abbnm.com...
> Thanks for asking Mark. The problem is that whenever that functionality
> is evoked nothing else can occur on that box. As soon as the job is over
> (which is every hour on the hour) the system goes back to normal. The
> happenings at that particular time are updates, deletes etc to the table
> in question and the replication job that pushes the changes from one
> machine to the other. Let me know if you can help.
> Tapan
>
> Mark Malakanov wrote:
> >
> > Don warry about chained rows. You have only 0.003% chained rows.
> >
> > What is "a performance problem associated with a particular
functionality"?
> > Access time? Long selects? Long changies? Locks? Space shortage? Errors
like
> > "Unable to create (sort/rollback) extent"?....
> >
> > Mark Malakanov,
> > OraDBA,
> > Sapience, Toronto
> >
> > Tapan Trivedi <tapan.trivedi_at_abbnm.com> wrote in message
> > news:3801F953.C8CD2010_at_abbnm.com...
> > > 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
Received on Wed Oct 13 1999 - 00:10:04 CDT