top 10 expensive queries

[parijat67]

Hi,

Pls help me find out the top 10 currently running SQL queries which are the most expensive queries.

thanks
Parijat Paul

[gajananpkini]

expensive queries -? u mean money wise ......

[parijat67]

resource wise.

Paul

[davey23uk]

Originally posted by parijat67
resource wise.

Paul

run statspack, they will be listed for you if you do a snap at the right level

[parijat67]

okk. I would run statspack to gather the sql queries. What if I need to find out the expensive queries while running a particular application process and the statspack is not configured?

Suppose, I have run a process thru the application which does huge amount of calculations on huge set of tables and while the calculations are being done I want to check the queries according to the resources they are utilising. The purpose of this is to fine tune those expensive queries to speed up the process that I had run.

thanks

Paul

[davey23uk]

Originally posted by parijat67
okk. I would run statspack to gather the sql queries. What if I need to find out the expensive queries while running a particular application process and the statspack is not configured?

Suppose, I have run a process thru the application which does huge amount of calculations on huge set of tables and while the calculations are being done I want to check the queries according to the resources they are utilising. The purpose of this is to fine tune those expensive queries to speed up the process that I had run.

thanks

Paul

statspack is still the answer, not hard to set up and then you dont have to worry about querying the database for yourself

as sysdba

@?/rdbms/admin/spcreate.sql

done statspack installed

[hacketta1]

statspack is still the answer

There is a parameter, i_session_id I believe, that allows you to produce a statspack report for an individual session

[DaPi]

I think I'd start by putting in some "instrumentation" into the job - just writing out start & end times for each query to a table or log file - that will show you the slow ones. The advantage is that you can leave it there for all time and it will immediately indicate which query is responsible if things start going slow again.

Tracing the session and using tkprof or trace analyser might be the next step - note that this can have a significant overhead. It has the advantage if being 100% focused on the job you want to optimise.

Never seen statspack for a session - is this new in 9i? http://download-west.oracle.com/docs...spac.htm#22046

[tamilselvan]

You can get the SQL statements by querying V%SQL view.
The execution and disk_reads col values tell you how expensive the SQLs are.

Tamil

[pando]

For statspack at session level check spdoc.txt (8.1.7) section 4.5

4.5.  Specifying a Session Id

  If you would like to gather session statistics and wait events for a
  particular session (in addition to the instance statistics and wait events),
  it is possible to specify the session id in the call to Statspack.  The
  statistics gathered for the session will include session statistics,
  session events and lock activity.  The default behaviour is to not to
  gather session level statistics.

    SQL>  execute statspack.snap(i_session_id=>3);