Re: how to find the most resource intensive sql?

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.

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
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
-----------------------------------------------------------------