RE: sql problem
- From: "Michael Dinh" <mdinh@xxxxxxxxx>
- To: <Chris.Stephens@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 16 Mar 2010 13:11:39 -0700
check out this post
http://jonathanlewis.wordpress.com/2008/07/24/bind-capture/
<http://jonathanlewis.wordpress.com/2008/07/24/bind-capture/>
NOTICE OF CONFIDENTIALITY - This material 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 laws. BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN
PROTECTED HEALTH INFORMATION (PHI). BY ACCEPTING THIS MESSAGE, YOU
ACKNOWLEDGE THE FOREGOING, AND AGREE AS FOLLOWS: YOU AGREE TO NOT
DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN, EXCEPT AS
EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO PERFORM YOUR
OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE. If the reader of
this email (and attachments) is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited. Please notify the sender of the
error and delete the e-mail you received. Thank you.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Stephens, Chris
Sent: Tuesday, March 16, 2010 12: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.
Other related posts:
