Re: What accounts for performance issues?

Stephane Viau <viaust_at_rmoc.on.ca> wrote in message <4ehD2.52$N62.3590587_at_nnrp1.tor.metronet.ca>...
> What accounts for performance issues with Oracle 804 and NT4 SP3. We are
> running queries that are running for a very long time. We are trying to
> pinpoint the cause of severe performance issues. We had a Oracle DBA look
> at and reconfigure our database, but did not improve performance.

Lots of things impact performance: database parameters, hardware resources, distribution of data across discs, database design, existence of indexes, structure of the query and how it is optimised....

Nine times out of 10 it's the structure of the query and the way it's optimised that has the biggest impact. It sounds like you might not have got value for money from your Oracle DBA. A good DBA would not have reconfigured your database without at least trying to discover if that was the problem. ( Or was he/she told "changing the code is out-of-bounds - do what you can without changing the queries"? )

Try posting a typical bad-performing query on the newsgroup, together with an explain plan (tkprof) output and see what we can do for you. Or just email it to me if you prefer.

I guess you're not experienced with tkprof and explain plan? Is it possible to isolate your queries and execute them under SQL*Plus? If so, do the following:

   In SQL*Plus:

        alter session set sql_trace true;

        <run your query>

        exit

This will create a trace file. You need to find it. The default location for trace files is $ORACLE_HOME/rdbms/log, but it might be somewhere else ( see the init.ora parameter user_dump_dest ).

Run tkprof against the trace file. In Unix the syntax will be ..

     tkprof <trace_file> <output_file> explain=<userid>/<password>
            sort="fchela,exeela"

Not sure what the NT equivalent is. The sort parameter is optional, but brings the most expensive statement to the top ( as measured by fetch elapse time + exec elapse time ). Userid and password are those of the person who executed the session which is being analysed.

If you can't isolate the query, you can switch on sql_trace for the whole database: set sql_trace = true in your init.ora and then restart your database. But this will create a lot of trace files, so it's a good idea to do it when the system is quiet, run just one session, switch off sql_trace and restart your database again.

Post or email the output from tkprof.

( In SQL*Plus you can also use the SET AUTOTRACE ON to display explain plans and statistics online immediately after executing a query, but I find it gives slightly less information than using tkprof ).

Dave.
--
Remove "nospam" from my address to reply by email Received on Thu Mar 04 1999 - 12:35:31 CST