RE: sql problem
- From: Rajesh Rao <Rajesh.Rao@xxxxxxxxxxxx>
- To: "Chris.Stephens@xxxxxxx" <Chris.Stephens@xxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 17 Mar 2010 13:57:29 -0400
http://tech.e2sn.com/oracle/troubleshooting/how-to-read-errorstack-output
Tanel talks of reading bind variables using an errorstack trace.
Regards
Raj
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Stephens, Chris
Sent: Tuesday, March 16, 2010 3:58 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: sql problem
10.2.0.4 Red Hat Linux
I found a very simple query that is consuming a ton of I/O resources.
Depending on the bind value, the query should be using an index. I've set the
statistics_level to 'ALL' in the database since the sql is issued from a canned
application and I haven't been able to get a hold of the users to actually
trace a session. The column is a timestamp datatype. I was hoping to get the
value used for :1 with the following:
SQL:
select * from table(dbms_xplan.display_cursor('3wc8wxtk6cnjz',2,'allstats
+peeked_binds'));
SQL_ID 3wc8wxtk6cnjz, child number 2
-------------------------------------
SELECT t_stamp, DPT_102, PT_104, PT_102, PT_108, PT_115, PT_117, PT_106,
PT_110, DPT_101 FROM PGREACTOR2SEC WHERE
t_stamp >= :1 ORDER BY t_stamp
Plan hash value: 2198310677
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name
| Starts | E-Rows | A-Rows | A-Time | Buffers |
Reads |
----------------------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| PGREACTOR2SEC |
46 | 1 | 6220K|00:00:33.26 | 1410K| 13161 |
|* 2 | INDEX RANGE SCAN | PGREACTOR2SEC_T_STAMP_NDX | 46 |
1 | 6220K|00:00:07.46 | 634K| 959 |
----------------------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (TIMESTAMP): [Not Printable]
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T_STAMP">=:1)
Here the index is used but that isn't always the case. Is there any other way
other than 100046 trace to get the bind value?
Also, there isn't much SQL being executed in this database. Is there a way to
get Oracle to evaluate the value of the bind variable on each execution to
ensure an appropriate execution plan?
Thanks,
Chris
CONFIDENTIALITY NOTICE:
This message is intended for the use of the individual or entity to which it is
addressed and may contain information that is privileged, confidential and
exempt from disclosure under applicable law. If the reader of this message is
not the intended recipient or the employee or agent responsible for delivering
this message to the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this communication is strictly
prohibited. If you have received this communication in error, please notify us
immediately by email reply.
This communication is for informational purposes only. It is not
intended as an offer or solicitation for the purchase or sale of
any financial instrument or as an official confirmation of any
transaction. All market prices, data and other information are not
warranted as to completeness or accuracy and are subject to change
without notice. Any comments or statements made herein do not
necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
and affiliates.
This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and any
attachments are believed to be free of any virus or other defect
that might affect any computer system into which it is received and
opened, it is the responsibility of the recipient to ensure that it
is virus free and no responsibility is accepted by JPMorgan Chase &
Co., its subsidiaries and affiliates, as applicable, for any loss
or damage arising in any way from its use. If you received this
transmission in error, please immediately contact the sender and
destroy the material in its entirety, whether in electronic or hard
copy format. Thank you.
Please refer to http://www.jpmorgan.com/pages/disclosures for
disclosures relating to European legal entities.
Other related posts:
