top 10 expensive queries

Printable View

12-23-2004, 07:29 AM
parijat67

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
12-23-2004, 07:37 AM
gajananpkini

expensive queries -? u mean money wise ......
12-23-2004, 08:19 AM
parijat67

resource wise.

Paul
12-23-2004, 10:09 AM
davey23uk

Quote:

Originally posted by parijat67
resource wise.

Paul

run statspack, they will be listed for you if you do a snap at the right level
12-23-2004, 10:47 AM
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
12-23-2004, 10:52 AM
davey23uk

Quote:

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
12-23-2004, 11:07 AM
hacketta1

Quote:

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
12-23-2004, 12:06 PM
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
12-27-2004, 10:00 AM
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
12-27-2004, 10:17 AM
pando

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