RE: sql problem
- From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
- To: <kerry.osborne@xxxxxxxxxxx>, <Chris.Stephens@xxxxxxx>
- Date: Mon, 29 Mar 2010 08:15:11 -0500
For what it's worth, I couldn't get this query to work under 10.2.0.2. I had
to replace "value(d)" with "value(d.column_value)", but then it worked
wonderfully!
VARIABLE SQL_ID VARCHAR2(13);
VARIABLE CHILD_NUM NUMBER;
EXEC :SQL_ID := '<sql id>';
EXEC :CHILD_NUM := <child number>;
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(d.column_value, '/bind/@nam') as bind_name
, EXTRACTVALUE(d.column_value, '/bind/@dty') as bind_type
, EXTRACTVALUE(d.column_value, '/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_NUM
AND other_xml IS NOT NULL)) d
);
Dave Herring | DBA, Global Technology Services
A c x i o m C o r p o r a t i o n
630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax
1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. | www.acxiom.com
Service Desk: 888-243-4566, https://servicedesk.acxiom.com, GSCA@xxxxxxx
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Kerry Osborne
Sent: Tuesday, March 16, 2010 3:38 PM
To: Chris.Stephens@xxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: sql problem
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/@nam') as bind_name,
extractvalue(value(d), '/bind/@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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
