Re: how to find the most resource intensive sql?

Hi Mladen...

  I was focusing on his original request... Or, my interpretation of his problem... To me, the subject line is different then what he asked for in the body of his email... He said he wanted to find out why a particular user was encountering a problem at a particular point in the application... With that in mind, I think a sql trace is an excellent way of deterring what that user is doing and why he is waiting...

  The problem with using the v$sqlarea approach is he may identify 500 statements that use more resources then the statement which is causing him this issue... He could tune all of these statements and still not solve the original problem since slow is a relative thing...

  However, if his original post was "What's the most resource intensive SQL in my entire system", then a 10046 would not be the appropriate solution...

Tim

Mladen Gogala wrote:

>On 03/11/2004 07:22:32 PM, Tim Johnston wrote:
>
>
>>Can they start a sql trace from the app? If not, can you identify the
>>session (i.e. By looking at things like the machine, program and
>>username fields in v$session)? If so, have the user log on and start a
>>trace in their session (check out the dbms_system package)... Have the
>>user run through screens until the encounter the problem... Then
>>examine the resulting trace file for the problem SQL... Usually, I skip
>>straight to a 10046 at level 12 so I also get the bind variables and
>>wait events... oops... make that timed events...
>>
>>
>>
>
>The meaning of the phrase "most resource expensive SQL" depends on the resource
>we have in mind. If you want to save I/O bandwidth at the expense of everything else,
>you should take care of your buffer cache hit ratio and use so called "method C" (that
>"C" comes from "Cary", the name of the person who has made that method so very popular.
>Now I earned myself few rounds with a well known martial artist.)
>Other then that, there is a little table called V$SQLAREA, which contains the information
>about the consumption of various resources. The table is described below:
>SQL> desc v$sqlarea
> Name Null? Type
> ----------------------------------------- -------- ---------------------------- SQL_TEXT VARCHAR2(1000)
> SQL_ID VARCHAR2(13)
> SHARABLE_MEM NUMBER
> PERSISTENT_MEM NUMBER
> RUNTIME_MEM NUMBER
> SORTS NUMBER
> VERSION_COUNT NUMBER
> LOADED_VERSIONS NUMBER
> OPEN_VERSIONS NUMBER
> USERS_OPENING NUMBER
> FETCHES NUMBER
> EXECUTIONS NUMBER
> END_OF_FETCH_COUNT NUMBER
> USERS_EXECUTING NUMBER
> LOADS NUMBER
> FIRST_LOAD_TIME VARCHAR2(19)
> INVALIDATIONS NUMBER
> PARSE_CALLS NUMBER
> DISK_READS NUMBER
> DIRECT_WRITES NUMBER
> BUFFER_GETS NUMBER
> APPLICATION_WAIT_TIME NUMBER
> CONCURRENCY_WAIT_TIME NUMBER
> CLUSTER_WAIT_TIME NUMBER
> USER_IO_WAIT_TIME NUMBER
> PLSQL_EXEC_TIME NUMBER
> JAVA_EXEC_TIME NUMBER
> ROWS_PROCESSED NUMBER
> COMMAND_TYPE NUMBER
> OPTIMIZER_MODE VARCHAR2(25)
> PARSING_USER_ID NUMBER
> PARSING_SCHEMA_ID NUMBER
> KEPT_VERSIONS NUMBER
> ADDRESS RAW(4)
> HASH_VALUE NUMBER
> OLD_HASH_VALUE NUMBER
> MODULE VARCHAR2(64)
> MODULE_HASH NUMBER
> ACTION VARCHAR2(64)
> ACTION_HASH NUMBER
> SERIALIZABLE_ABORTS NUMBER
> CPU_TIME NUMBER
> ELAPSED_TIME NUMBER
> IS_OBSOLETE VARCHAR2(1)
> CHILD_LATCH NUMBER
> PROGRAM_ID NUMBER
>
>SQL>
>Event 10046 is not very useful to find the most intensive SQL. Ove very good gooey tool
>which could do that in a very easy and descriptive way is SQL*Lab by Quest Software.
>Generally speaking, quest has the best tools in the business.
>
>

-- 
Regards,
Tim Johnston
Tel: 978-322-4226
Fax: 978-322-4100


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------