Re: sql problem

From: Kerry Osborne <kerry.osborne_at_enkitec.com>
Date: Tue, 16 Mar 2010 15:37:40 -0500
Message-Id: <994A5099-3608-44A9-B8FB-AF4FA77C081F_at_enkitec.com>

I don't think you can force a statement to reevaluate binds every time it's executed. A hint to do that would be nice!

Peeked binds are in v$sql_plan.other_xml. You can use an XML query like this:

col bind_name for a20
col bind_type for a20
col value for a40
select
bind_name,
decode(bind_type,1,'VARCHAR2',2,'NUMBER',bind_type) bind_type, decode(bind_type,1,display_raw(bind_data,'VARCHAR2'),2,display_raw (bind_data,'NUMBER'),bind_data) value
from (
select

extractvalue(value(d), '/bind/_at_nam') as bind_name,
extractvalue(value(d), '/bind/_at_dty') as bind_type,
extractvalue(value(d), '/bind') as bind_data

from
xmltable('/*/*/bind'
passing (
select
xmltype(other_xml) as xmlval
from
v$sql_plan
where
sql_id like nvl('&sql_id',sql_id)
and child_number = '&child_no'
and other_xml is not null

)
) d
)

;

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com

On Mar 16, 2010, at 2:58 PM, Stephens, Chris wrote:

> 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.
>
>

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

Received on Tue Mar 16 2010 - 15:37:40 CDT