Re: High Resource consuming SQL in Statspack ?

Vivek

You can look into V$SESSION at columns MACHINE and PROGRAM which should identify the source of the SQL - ie is it from the application server. You can also check whether the statement is recursive (by tracing - I don't think this is recorded in statspack or in V$ tables). It could be called by a stored proc/function itself called from JDBC.

Regards Nigel

2008/11/27 VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>

> Folks
>
>
>
> Qs 1 Is the following SQL being fired by the Application (& NOT internally
> by the Oracle Database)? … Does the message *"Module: JDBC Thin Client" *prove
> this?
>
>
>
> Qs 2 Why is the *"% Total" so High i.e. 49.7* ? Any approaches for
> handling this issue?
>
>
>
> *Config:-*
>
> Oracle *10.2.0.1 *(NON-RAC)
>
> $ uname -a
>
> *HP-UX* SPEHPPA1 *B.11.11* U 9000/800 480485360 unlimited-user license
>
>
>
> Cheers & Thanks V much
>
>
>
> *P.S. Bad SQL:-*
>
>
>
> SQL ordered by CPU DB/Inst: LOSDB/LOSDB Snaps: 552-554
>
> . . .
>
>
>
> CPU CPU per Elapsd Old
>
> Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash
> Value
>
> ---------- ------------ ---------- ------ ---------- ---------------
> ----------
>
> 60.09 13,016 0.00 *49.7* 60.10 0
> 1702556642
>
> *Module: JDBC Thin Client*
>
> SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER
> ='NLS_DATE_FORMAT'
>
>
>
> **************** CAUTION - Disclaimer *****************
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
> for the use of the addressee(s). If you are not the intended recipient, please
> notify the sender by e-mail and delete the original message. Further, you are not
> to copy, disclose, or distribute this e-mail or its contents to any other person and
> any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
> every reasonable precaution to minimize this risk, but is not liable for any damage
> you may sustain as a result of any virus in this e-mail. You should carry out your
> own virus checks before opening the e-mail or attachment. Infosys reserves the
> right to monitor and review the content of all messages sent to or from this e-mail
> address. Messages sent to or from this e-mail address may be stored on the
> Infosys e-mail system.
> ***INFOSYS******** End of Disclaimer ********INFOSYS***
>
>

--
http://www.freelists.org/webpage/oracle-l