Oops, just realized that script uses a function call display_raw (I think I got that from Tim Gorman somewhere along the way). Anyway, here's the definition of that function.
create or replace function display_raw (rawval raw, type varchar2)
return varchar2
is
cn number;
cv varchar2(32);
cd date;
cnv nvarchar2(32);
cr rowid;
cc char(32);
begin
if (type = 'NUMBER') then
dbms_stats.convert_raw_value(rawval, cn);
return to_char(cn);
elsif (type = 'VARCHAR2') then
dbms_stats.convert_raw_value(rawval, cv);
return to_char(cv);
elsif (type = 'DATE') then
dbms_stats.convert_raw_value(rawval, cd);
return to_char(cd,'dd-mon-yyyy');
elsif (type = 'NVARCHAR2') then
dbms_stats.convert_raw_value(rawval, cnv);
return to_char(cnv);
elsif (type = 'ROWID') then
dbms_stats.convert_raw_value(rawval, cr);
return to_char(cnv);
elsif (type = 'CHAR') then
dbms_stats.convert_raw_value(rawval, cc);
return to_char(cc);
else
return 'UNKNOWN DATATYPE';
end if;
end;
/
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 LinuxI 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 WHEREt_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.
