Re: High Resource consuming SQL in Statspack ?
- From: "Nigel Thomas" <nigel.cl.thomas@xxxxxxxxxxxxxx>
- To: VIVEK_SHARMA@xxxxxxxxxxx
- Date: Thu, 27 Nov 2008 08:53:09 +0000
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@xxxxxxxxxxx>
> 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***
>
>
Other related posts:
