top 10 expensive queries
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: top 10 expensive queries

  1. #1
    Join Date
    Dec 2001
    Posts
    120

    top 10 expensive queries

    Hi,

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

    thanks
    Parijat Paul

  2. #2
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132
    expensive queries -? u mean money wise ......

  3. #3
    Join Date
    Dec 2001
    Posts
    120
    resource wise.

    Paul

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Originally posted by parijat67
    resource wise.

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

  5. #5
    Join Date
    Dec 2001
    Posts
    120
    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

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  7. #7
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    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

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    For statspack at session level check spdoc.txt (8.1.7) section 4.5

    Code:
    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);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width