Re: sql problem

From: Kerry Osborne <kerry.osborne_at_enkitec.com>
Date: Tue, 16 Mar 2010 15:47:11 -0500
Message-Id: <B0ED4E9F-C7EE-4FA3-BEDB-55683E840FF3_at_enkitec.com>



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 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:47:11 CDT

Original text of this message