Re: Effects of framentation

"Murat Balkas" <murat.balkas_at_o2.com.tr> wrote in message news:b1148377.0401280704.16ecd1fe_at_posting.google.com...
> Hi,
>
> my platform is : RedHat, Oracle 8.1.7
>
> We're hosting an application server and the database of our
> customer. We're not responsible of the database users's objects.
> Nowadays the site is terribly slow. I've noticed that all the tables
> and indexes of the user are put in one tablespace and almost all
> objects ( including tables,indexes,... ) in this tablesapce are
> fragmented over %60.
>
> I thnik the problem is about these fragmented objects and some of
> their queries.
>
> I need to prove that the cause of the slowness is the fragmented
> objects and will advise rebuilding them. They'll do that work.
>
> How can I do that? How can I prove that queries answer too slow
> because of the fragmentation.

What a great approach - try to prove what the problem is before fixing it. I very much doubt that the issue is fragmentation, though if everything is not only in one tablespace but on one disk as well then IO contention might be a suspect.

Oracle provides this handy (but not especially well known) utility called tracing. If you enable tracing appropriately you can collect timing information and information about what the system is waiting for. Troubleshooting when you have this information becomes a lot easier.

What you need to do is to enable event 10046 at level 8 or 12 (I'd suggest 8 to start with, 12 collects binds as well as wait info), for your sessions, collect trace data by running the slow part of the application, stop tracing and run the resulting trace file through an analyzer. You collect trace data for a session in a number of ways, but either by

1. issuing the sql statement "alter session set events '10046 trace name context forever, level 8';" at the start of the problem code and "alter session set events '10046 trace name context off';" at the end of the code.

OR

2. if you don't have source code access create a logon trigger that fires when that user connects and issue the alter session command above.

OR

3. manually use the DBMS_SUPPORT.START_TRACE_IN_SESSION and DBMS_SUPPORT.STOP_TRACE_IN_SESSION functions with waits (and optionally) binds parameters set to TRUE

once you have your trace file run it thru TKPROF with sort=(exeela,prsela,fchela) to sort the most time consuming statements at the top of the file.

I bet it will be fewer than 5 sql statements that cause the problems.

--
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
******************************************