Re: how to find the most resource intensive sql?

  • From: Tim Johnston <tjohnston@xxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 11 Mar 2004 20:05:27 -0500

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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------

Other related posts: