Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Murali.

Asked: October 27, 2002 - 5:35 pm UTC

Last updated: February 16, 2006 - 11:41 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Good Morning Tom

When I run the following querry with passing of variable l_start_date I am getting an error, If I dont pass l_start_Date
as an parameter instead if I hardcode with some value it works.

I think the way in which I am passing l_start_date as variable in l_ap_sql is wrong.
I dont know where I am doing wrong

ERROR at line 1:
ORA-00904: invalid column name
ORA-06512: at line


What I am looking is If User passes l_Start_date and l_end_date as parameters it should give
the count in the table ( AP_INVOICES_INTERFACE ) for those dates, otherwise it should give count for all the dates

=================================================================
SET SERVEROUTPUT ON SIZE 1000000

DECLARE

l_site VARCHAR2(5) := '&P_SITE' ;
l_start_date DATE := '&P_START_DATE' ;
show_my_link VARCHAR2(50) ;
l_ap_invoices NUMBER := 0 ;

BEGIN

show_my_link := get_db_link(l_site) ;

l_ap_sql := 'SELECT COUNT(*) FROM ap_invoices_interface'||'@'||show_my_link
||' '||'WHERE global_attribute18 IS NULL '|| '( AND creation_date between ' || l_start_date || 'AND'|| l_end_date||
' OR '|| l_start_date IS NULL || )';

EXECUTE IMMEDIATE (l_ap_sql ) INTO l_ap_invoices ;

dbms_output.Put_line(' The Number of AP Invoices IS : '|| (l_ap_invoices));
END;

===============================================================

Thanking you in advance

Murali

and Tom said...

You need to use BIND VARIABLES!!!! so very very very very important.

Never never never glue values in like that.

l_query :=
'select count(*)
from ap_invoices_interface@' || show_my_link || '
where global_attribute18 is null';

if ( l_start_date is NOT NULL )
then
l_query := l_query || ' and creation_date between :x and :y';
else
l_query := l_query || ' and (:x is null and :y is null)';
end;

execute immediate l_query into l_ap_invoices using l_start_date, l_end_date;




Rating

  (22 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

good

santosh, October 28, 2002 - 10:19 am UTC

it is very useful to me and my students also please send free books and .......querrys

what about dynamic where clause?

Sarah, November 14, 2002 - 8:08 am UTC

Tom,
This example assumed that the where clause always uses the same bind variables. What would you do if the where clause in this query is not always the same?

I want to be able to add it extra parts to the where clause depending on the user search criteria.
I have your book and I've got an example using SYS_CONTEXT working against a partitioned table. When I try to do this against a remote table using a db_link the query is trying to look at all partitions.

An example is:
INSERT /*+ APPEND */ INTO local_search_results
(SEARCH_KEY,
test1,
test2)
SELECT
SYS_CONTEXT('CTX_TRANS_SEARCH', 'pv_search_key'),
test1,
test2
FROM view_on_big_partitioned_table@remote.world
WHERE
partition_field >= TO_DATE(SYS_CONTEXT('CTX_TRANS_SEARCH', 'pd_date_from'), 'YYYYMMDD HH24MISS')
AND partition_field < TO_DATE(SYS_CONTEXT('CTX_TRANS_SEARCH', 'pd_date_to'), 'YYYYMMDD HH24MISS')
AND test_amount >= TO_NUMBER(SYS_CONTEXT('CTX_TRANS_SEARCH', 'pi_test_amount'))
AND ROWNUM <= TO_NUMBER(SYS_CONTEXT('CTX_TRANS_SEARCH', 'pi_get_rows'))

Is this the best way to do it?
Thanks you your help



Tom Kyte
November 14, 2002 - 7:21 pm UTC

two things

o partition elimination is still happening - it is just that the PLAN cannot reflect exactly WHICH ones will be eliminated. It is only looking at the data it needs to

o if you use literals -- the plan will reflect exactly what partitions are used since it can definitely tell at parse times.

So, here is an idea, let me say this (hold onto your hats)

insert /*+ append */

that indicates to me that this query is not executed over and over and over and over. Maybe -- once a day, once a year, once a month. It is a mass load -- infrequent.


So, maybe -- in this case -- you can get away without using binds - literals would be ok.

When you do the same query hundreds/thousands of times a day -- BINDS ARE A MUST.

When you do a query once a day or even less -- BINDS are something to consider.

Thanks

Sarah, November 15, 2002 - 5:00 am UTC

Tom,
Thanks for the quick reply.
This query is repeated many times a day/minute so I think I will need bind variables (but I will take out the /*+ APPEND */ hint).
The only way I can think to do it is as in your first suggestion calling one of a set of 'execute immediate' queries depending on the number of values I've built up.
If you can think of a cleaner way, that would be a big help.
Thanks


How can I use bind variable in dates like these

JAMES, August 29, 2003 - 12:08 pm UTC

IF p_date_created_from <> ' ' and p_date_created_to <> ' ' then

str := str || ' and trunc(batch_apis.date_created) between''' || v_date_created_from || '''';
str := str || ' and ''' || v_date_created_to || '''';


END IF;

Tom Kyte
August 29, 2003 - 12:35 pm UTC

hmm, that should be:

if ( p_date_created_from is not null and p_date_created_to is not null )
then
str := str || ' and batch_apis.date_created
between :x and trunc(:y)+1-1/24/60/60';
end if;


and then you open .... using p_date_created_from, p_date_created_to

if you cannot explicitly bind, use this technique:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>

using an application context

Please see

A reader, August 29, 2003 - 12:44 pm UTC

if ( p_hiredate is NOT NULL ) then
dbms_session.set_context( 'MY_CTX', 'HIREDATE',
to_char(p_hiredate,'yyyymmddhh24miss'));
l_query := l_query ||
' and hiredate >
to_date(
sys_context( ''MY_CTX'',
''HIREDATE'' ),
''yyyymmddhh24miss'') ';
end if;

but when I put the key word between is not working for me.


Tom Kyte
August 29, 2003 - 12:46 pm UTC

'not working for me' doesn't tell us very much.


between works great - assuming you build a VALID query...

still having problem using appication context in this dynamic sql

mike, September 03, 2003 - 11:06 am UTC


str := str || ' and batch_ccs.date_of_manuf between ''' || v_date_of_manuf_from|| '''';

str := str || 'and ''' || v_date_of_manuf_to|| '''';


Tom Kyte
September 03, 2003 - 12:05 pm UTC



well, i don't see any application context, i don't see any error.

i do see you NOT using binds which is really bad.

maybe if you give us a complete, concise example (concise being as important as complete) -- maybe we can comment.

I am trying to use app context to get a range of dates

A reader, September 03, 2003 - 12:31 pm UTC

dbms_session.set_context( 'BATCHAPI_CTX', 'batch_apis.date_of_manuf',to_char(v_date_of_manuf_to,'mm/dd/yyyy'));

str := str ||' and batch_apis.date_of_manuf => to_date(sys_context( ''BATCHAPI_CTX'', ''batch_apis.date_of_manuf'' ),''mm/dd/yyyy'') ';

str := str ||' and batch_apis.date_of_manuf <= to_date(sys_context( ''BATCHAPI_CTX'', ''batch_apis.date_of_manuf'' ),''mm/dd/yyyy'') ';

Tom Kyte
September 04, 2003 - 8:25 am UTC

well, your logic boils down to:


and COLUMN >= constant and COLUMN <= constant

which is the same as

and COLUMN = constant


is that what you meant to program -- a simple, concise -- yet complete example would be something against say dual or EMP and would be something we call can run in sqlplus ourselves.

I still have no idea WHAT the issue you have is -- but perhaps it is this error in logic here.

this is the eror message

A reader, September 03, 2003 - 1:18 pm UTC

ORA-00936: missing expression ORA-06512: at "ENOTEBOOK.BATCH_API_JAVA_PKG", line 384 ORA-06512: at line 1



f date_of_manuf_from and date_of_manuf_to then
dbms_session.set_context( 'BATCHAPI_CTX', 'batch_apis.date_of_manuf',to_char(v_date_of_manuf_from,'mm/dd/yyyy'));
str := str ||' and date_of_manuf_from => date_of_manuf_from to_date(sys_context( ''BATCHAPI_CTX'', ''date_of_manuf_from'' ),''mm/dd/yyyy'') ';
str := str ||' and date_of_manuf_to < date_of_manuf_to to_date(sys_context( ''BATCHAPI_CTX'', ''date_of_manuf_to'' ),''mm/dd/yyyy'') ';


end if;


Tom Kyte
September 04, 2003 - 8:30 am UTC

cut your example down.

find out what quote you miskakenly didn't put in there. It could be many lines up from this one.

also, the sql you have here, which is totally different from the last sql above bear in mind, is not syntactically valid at all -- look at it.

it would generate:

... and COLUMN => COLUMN to_date( CONSTANT )

what is that?


FOLLOW UP

MIKE, September 04, 2003 - 9:30 am UTC

Hi tom,

ALL I WANT TO DO WITH THIS DYNAMIC SQL IS TO USE APPLICATION CONTEXT TO TAKE ADVANTAGE OF BINDING.AND I AM HAVING A TOUGH TIME SELECTING BETWEEN 2 DATES. WOULD YOU PLEASE LET ME KNOW WHAT AM I DOING WRONG??

if date_of_manuf_from and date_of_manuf_to then

dbms_session.set_context( 'BATCHAPI_CTX', 'batch_apis.date_of_manuf',to_char(v_date_of_manuf_from,'mm/dd/yyyy'));
str := str ||' and date_of_manuf_from => date_of_manuf_from to_date(sys_context(''BATCHAPI_CTX'', ''date_of_manuf_from'' ),''mm/dd/yyyy'') ';

str := str ||' and date_of_manuf_to <= date_of_manuf_to to_date(sys_context( ''BATCHAPI_CTX'', ''date_of_manuf_to'' ),''mm/dd/yyyy'') ';

end if

Tom Kyte
September 04, 2003 - 10:11 am UTC

i've tried to say it over and over -- your sql is totally meaningless.  stop, take a look at it.  see what string you are building.  think about what string you WANT to be building.

your sql is not even sql.

you keep saying 'between two dates', but i only ever see a single date.

at least this time, you have two different dates you are looking at in the table.  but look -- just look -- at your SQL


... and date_of_manuf_from => date_of_manuf_from to_date( sys_context( 'batchapi_ctx', 'date_of_munf_from' ), 'mm/dd/yyyy' ) ....


what kind of SQL is that?????? 


here is a short, concise, yet complete (eg: you too can run this) example of what I believe you are trying to do.  building a test case is a very good thing when you hit a problem.  999 times out of 1000 i find my own bug in the development of a simple test case.

ops$tkyte@ORA920LAP> create table t ( x int, startd date, endd date );

Table created.

ops$tkyte@ORA920LAP> insert into t values ( 1, sysdate-5, sysdate+5 );

1 row created.

ops$tkyte@ORA920LAP> insert into t values ( 2, sysdate-50, sysdate-45 );

1 row created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create or replace package demo_pkg
  2  as
  3      type rc is ref cursor;
  4
  5      procedure p( p_cursor in out rc,
  6                   p_date   in date );
  7  end;
  8  /

Package created.

ops$tkyte@ORA920LAP> create or replace package body demo_pkg
  2  as
  3      procedure p( p_cursor in out rc,
  4                   p_date   in date )
  5      is
  6          l_str long;
  7      begin
  8          dbms_session.set_context( 'my_ctx', 'the_date', to_char(p_date,'yyyymmdd') );
  9
 10          l_str := 'select * from t where 1=1 ';
 11
 12          l_str := l_str ||
 13          ' and startd <= to_date(sys_context(''my_ctx'',''the_date''),''yyyymmdd'' )
 14            and endd >= to_date(sys_context(''my_ctx'',''the_date''),''yyyymmdd'' )';
 15
 16          open p_cursor for l_str;
 17      end;
 18  end;
 19  /

Package body created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> variable x refcursor
ops$tkyte@ORA920LAP> exec demo_pkg.p( :x, sysdate );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> print x

         X STARTD               ENDD
---------- -------------------- --------------------
         1 30-aug-2003 10:12:54 09-sep-2003 10:12:54


 

Thanks

mike, September 04, 2003 - 2:13 pm UTC

Hi tom,

I understand now your example. I noticed you are using
2 fields to do your search in between dates. however, the developer as I undersand it now, is using ONE field(batch_apis.date_of_manuf) to do his range search. Maybe we can't apply application context in this situation.

Thanks very much for your effort and patience.

Tom Kyte
September 05, 2003 - 2:01 pm UTC

tell me please -- how a single date can be used for a "range"


application context OR NOT -- if you have a single date -- explain to me "range"

if you can hard code it, I can bind it.

I am sending you the whole thing the part I am having a problem is with

A reader, September 05, 2003 - 2:23 pm UTC

the part that I am having problem is
if date_of_manuf_from and date_of_manuf_to then.....



function view_log (
p_batch in varchar2 default null,
p_name in varchar2 default null,
p_coa in varchar2 default null,
p_batch_size in varchar2 default null,
p_batch_size_units in varchar2 default null,
p_manufacturer in varchar2 default null,
p_date_of_manuf_from in varchar2 default null,
p_date_of_manuf_to in varchar2 default null,
p_manuf_batch_no in varchar2 default null,
p_description in varchar2 default null,
p_comments in varchar2 default null,
p_received_date_from in varchar2 default null,
p_received_date_to in varchar2 default null,
p_received_location in varchar2 default null,
p_updated in varchar2 default null,
p_created_by in varchar2 default null,
p_date_created_from in varchar2 default null,
p_date_created_to in varchar2 default null,
p_order in varchar2 default null
)
return cursortype_api
is
v_log cursortype_api;
str varchar2 (3000);
v_order varchar2 (50);
v_date_of_manuf_from date := to_date (p_date_of_manuf_from, 'mm/dd/yyyy');
v_date_of_manuf_to date := to_date (p_date_of_manuf_to, 'mm/dd/yyyy');
v_received_date_from date := to_date (p_received_date_from, 'mm/dd/yyyy');
v_received_date_to date := to_date (p_received_date_to, 'mm/dd/yyyy');
v_date_created_from date := to_date (p_date_created_from, 'mm/dd/yyyy');
v_date_created_to date := to_date (p_date_created_to, 'mm/dd/yyyy');

---------- CONDITIONS FOR WHERE CLAUSE -----------------------------------
updated constant boolean default p_updated <> ' ';
created_by constant boolean default p_created_by <> ' ';
batch constant boolean default p_batch <> ' ';
name constant boolean default p_name <> ' ';
coa constant boolean default p_coa <> ' ';
batch_size constant boolean default p_batch_size <> ' ';
batch_size_units constant boolean default p_batch_size_units <> ' ';
manufacturer constant boolean default p_manufacturer <> ' ';
manuf_batch_no constant boolean default p_manuf_batch_no <> ' ';
description constant boolean default p_description <> ' ';
received_location constant boolean default p_received_location <> ' ';
comments constant boolean default p_comments <> ' ';
date_of_manuf_from constant boolean default p_date_of_manuf_from <> ' ';
date_of_manuf_to constant boolean default p_date_of_manuf_to <> ' ';
received_date_from constant boolean default p_received_date_from <> ' ';
received_date_to constant boolean default p_received_date_to <> ' ';
date_created_from constant boolean default p_date_created_from <> ' ';
date_created_to constant boolean default p_date_created_to <> ' ';

BEGIN
----building select statement--------------
str :=
'Select
batch_apis.ID,
batch_apis.batch BATCH,
components_lkup.component_name NAME,
nvl(batch_apis.coa,''.'') COA,
nvl(batch_apis.batch_size,''.'') BATCH_SIZE,
nvl(batch_apis.batch_size_units,''.'') BATCH_SIZE_UNITS,
manufacturer_lkup.name MANUF,
nvl(to_char(batch_apis.date_of_manuf, ''mm/dd/yyyy''),''.'') DATE_OF_MANUF,
nvl(batch_apis.manuf_batch_no,''.'') MANUF_BATCH_NO,
nvl(batch_apis.description,''.'') DESCRIPTION,
nvl(to_char(batch_apis.received_date, ''mm/dd/yyyy''),''.'') RECEIVED_DATE,
nvl(locations_lkup.location,''.'') RECEIVED_LOCATION,
nvl(batch_apis.comments,''.'') COMMENTS,
batch_apis.updated,
columns_java_pkg.get_chemist_name_by_usid(batch_apis.created_by) CREATED_BY,
to_char(batch_apis.date_created, ''mm/dd/yyyy hh24:mi:ss'') CREATED_DATE';

---------define table(s) to select from ------------------------------------
str := str || ' from batch_apis, manufacturer_lkup, products,
locations_lkup, components_lkup';

----------define where conditions-------------------------------------------
str := str || ' where batch_apis.record_status = ''CURRENT''';
str := str || ' and product_fk_id = products.id(+)';
str := str || ' and manufacturer_fk_id = manufacturer_lkup.id(+)';
str := str || ' and received_location_fk_id = locations_lkup.id(+)';
str := str || ' and components_lkup.id = products.component_fk_id';

if updated then
dbms_session.set_context( 'BATCHAPI_CTX', 'batch_apis.updated','%'||upper (p_updated)||'%');
str := str || ' and upper(batch_apis.updated) like sys_context( ''BATCHAPI_CTX'', ''batch_apis.updated'' )';
end if;

if created_by then
dbms_session.set_context( 'BATCHAPI_CTX', 'batch_apis.created_by','%'||upper (p_created_by)||'%');
str := str || ' and upper(batch_apis.created_by) like sys_context( ''BATCHAPI_CTX'', ''batch_apis.created_by'' )';
end if;

-- if date_created_from and date_created_to then
-- dbms_session.set_context( 'BATCHAPI_CTX', 'batch_apis.date_created',to_char(v_date_created_from ,'mm/dd/yyyy'));
-- str := str ||' and batch_apis.date_created >= to_date(sys_context( ''BATCHAPI_CTX'', ''batch_apis.date_created'' ),''mm/dd/yyyy'') ';
-- dbms_session.set_context( 'BATCHAPI_CTX', 'batch_apis.date_created',to_char(v_date_created_to,'mm/dd/yyyy'));
-- str := str ||' and batch_apis.date_created <= to_date(sys_context( ''BATCHAPI_CTX'', ''batch_apis.date_created'' ),''mm/dd/yyyy'') ';
-- end if;

if batch then
dbms_session.set_context( 'BATCHAPI_CTX', 'batch','%'||p_batch||'%');
str := str || ' and batch like sys_context( ''BATCHAPI_CTX'', ''batch'' )';
end if;

if name then
dbms_session.set_context( 'BATCHAPI_CTX', 'component_name','%'||upper(p_name)||'%');
str := str || ' and upper(component_name)like sys_context( ''BATCHAPI_CTX'', ''component_name'' )';
end if;

if coa then
dbms_session.set_context( 'BATCHAPI_CTX', 'batch_apis.coa','%'||upper(p_coa)||'%');
str := str || ' and upper(batch_apis.coa)like sys_context( ''BATCHAPI_CTX'', ''batch_apis.coa'' )';
end if;

if batch_size then
dbms_session.set_context( 'BATCHAPI_CTX', 'batch_apis.batch_size','%'||upper(p_batch_size)||'%');
str := str || ' and upper(batch_apis.batch_size)like sys_context( ''BATCHAPI_CTX'', ''batch_apis.batch_size'' )';
end if;

if batch_size_units then
dbms_session.set_context( 'BATCHAPI_CTX', 'batch_apis.batch_size_units','%'||upper(p_batch_size_units)||'%');
str := str || ' and upper(batch_apis.batch_size_units)like sys_context( ''BATCHAPI_CTX'', ''batch_apis.batch_size_units'' )';
end if;

if manufacturer then
dbms_session.set_context( 'BATCHAPI_CTX', 'manufacturer_lkup.name','%'||upper(p_manufacturer)||'%');
str := str || ' and upper(manufacturer_lkup.name)like sys_context( ''BATCHAPI_CTX'', ''manufacturer_lkup.name'' )';
end if;

if manuf_batch_no then
dbms_session.set_context( 'BATCHAPI_CTX', 'batch_apis.manuf_batch_no','%'||upper(p_manuf_batch_no)||'%');
str := str || ' and upper(batch_apis.manuf_batch_no)like sys_context( ''BATCHAPI_CTX'', ''batch_apis.manuf_batch_no'' )';
end if;

if description then
dbms_session.set_context( 'BATCHAPI_CTX', 'batch_apis.description','%'||upper(p_description)||'%');
str := str || ' and upper(batch_apis.description)like sys_context( ''BATCHAPI_CTX'', ''batch_apis.description'' )';
end if;

if received_location then
dbms_session.set_context( 'BATCHAPI_CTX', 'locations_lkup.location','%'||upper(p_received_location)||'%');
str := str || ' and upper(locations_lkup.location)like sys_context( ''BATCHAPI_CTX'', ''locations_lkup.location'' )';
end if;

if comments then
dbms_session.set_context( 'BATCHAPI_CTX', 'batch_apis.comments','%'||upper(p_comments)||'%');
str := str || ' and upper(batch_apis.comments)like sys_context( ''BATCHAPI_CTX'', ''batch_apis.comments'' )';
end if;

if date_of_manuf_from and date_of_manuf_to then

dbms_session.set_context( 'BATCHAPI_CTX', 'batch_apis.date_of_manuf',to_char(v_date_of_manuf_from,'mm/dd/yyyy'));
dbms_session.set_context( 'BATCHAPI_CTX', 'batch_apis.date_of_manuf',to_char(v_date_of_manuf_to,'mm/dd/yyyy'));
str := str || ' and date_of_manuf BETWEEN to_date(sys_context(''BATCHAPI_CTX'',''date_of_manuf_from''),''mm/dd/yyyy'')
and to_date(sys_context(''BATCHAPI_CTX'',''date_of_manuf_to''),''mm/dd/yyyy'' )';



end if;

-- if received_date_from and received_date_to then
-- dbms_session.set_context( 'BATCHAPI_CTX', 'batch_apis.received_date',to_char(v_date_of_manuf,'mm/dd/yyyy'));
-- str := str ||' and batch_apis.received_date >= to_date(sys_context( ''BATCHAPI_CTX'', ''batch_apis.received_date'' ),''mm/dd/yyyy'') ';
-- dbms_session.set_context( 'BATCHAPI_CTX', 'batch_apis.received_date',to_char(v_received_date_to,'mm/dd/yyyy'));
-- str := str ||' and batch_apis.received_date <= to_date(sys_context( ''BATCHAPI_CTX'', ''batch_apis.received_date'' ),''mm/dd/yyyy'') ';
-- end if;



Tom Kyte
September 05, 2003 - 7:07 pm UTC

that is neither

a) small and concise

b) fully runnable on my system.


nor, does it really tell me "what the heck the problem is"

at this point, I can really only throw my hands up and say "I give up".


we won't get anywhere here, apparently. the concept here is pretty simple "build a sql string that runs". sys_context makes this no harder, nay -- it makes it easier -- then hard coding literals.

I've given examples, I don't know what else to do. sorry.

application context and performance

Dan, October 20, 2004 - 1:57 pm UTC

Tom,

I have a package in which I use sys_context to substitute in variables
When I run an explain plan on the query with a literal value I get the following execution path. When I run with sys context I get get 2 full table scans on the largest 2 tables. (see explains below) I have a function based index on business.participant_id (the column I'm using sys_codtext and sbms_session.set_context on). Why would this affect 2 tables that I'm not using sys_context on (they suddenly go to full table scans and increase execution tim to 20 seconds vc .019 seconds)


THE QUERY

SELECT distinct
T1.BUSINESS_NAME,
T1.PARTICIPANT_ID,
T1.ABA_NUMBER,
T2.PERSON_ID,
T2.PERSON_FIRST_NAME,
T2.PERSON_LAST_NAME,
T2.PERSON_PHONE,
contact_qry.getPersonRole(T2.PERSON_ID,T1.BUSINESS_ID) PERSON_ROLE_ID,
T5.ADDRESS_CITY,
T5.STATE_ID,
CONTACT_QRY.GETUSRGRP(T2.PERSON_ID) USER_GROUP,
t1.business_id
FROM BUSINESS T1,
PERSON T2,
PERSON_BUSINESS_USAGE T3,
PERSON_ADDRESS_USAGE T4,
ADDRESS T5 ,
PERSON_USER T6 ,
system_code_value t7
WHERE t1.business_id = t3.business_id
AND t2.person_id = t3.person_id
AND t2.person_id = t4.person_id
AND t4.address_id = t5.address_id
and t6.person_id(+) = t2.person_id
and t4.address_type_id = t7.system_code_value_id
and t7.system_code_table_name = 'Address_Type'
and t7.system_code_value_code = 'CO'
and UPPER(t1.participant_id) like '000%'
ORDER BY t2.person_last_name

Execution Plan wITH Sys_context
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=393 Card=341 Bytes=1417537)
1 0 VIEW (Cost=393 Card=341 Bytes=1417537)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=393 Card=341 Bytes=1413104)
4 3 SORT (UNIQUE STOPKEY) (Cost=373 Card=341 Bytes=39897)

5 4 HASH JOIN (Cost=352 Card=341 Bytes=39897)
6 5 HASH JOIN (Cost=316 Card=1390 Bytes=113980)
7 6 HASH JOIN (OUTER) (Cost=168 Card=1390 Bytes=93130)

8 7 HASH JOIN (Cost=164 Card=1390 Bytes=87570)
9 8 HASH JOIN (Cost=150 Card=7839 Bytes=431145)
10 9 NESTED LOOPS (Cost=7 Card=7839 Bytes=250848)
11 10 TABLE ACCESS (BY INDEX ROWID) OF 'SYSTEM_CODE_VALUE' (Cost=2 Card=1 Bytes=21)

12 11 INDEX (UNIQUE SCAN) OF 'XAK1SYSTEM_CODE_VALUE' (UNIQUE) (Cost=1 Card=2571)

13 10 INDEX (FAST FULL SCAN) OF 'XPKPERSON_ADDRESS_USAGE' (UNIQUE) (Cost=5 Card=1 Bytes=11)

14 9 TABLE ACCESS (FULL) OF 'PERSON' (Cost=136 Card=575203 Bytes=13229669)

15 8 INDEX (FAST FULL SCAN) OF 'XPKPERSON_BUSINESS_USAGE' (UNIQUE) (Cost=11 Card=96042 Bytes=768336)

16 7 INDEX (FAST FULL SCAN) OF 'PERSON_USER_PK' (UNIQUE) (Cost=3 Card=31 Bytes=124)

17 6 TABLE ACCESS (FULL) OF 'ADDRESS' (Cost=146 Card=444720 Bytes=6670800)

18 5 TABLE ACCESS (BY INDEX ROWID) OF 'BUSINESS' (Cost=35 Card=3044 Bytes=106540)

19 18 INDEX (RANGE SCAN) OF 'XAKBUSINESS_FUNC2' (NON-UNIQUE) (Cost=2 Card=20)


Execution Plan With Literal
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=60 Card=1 Bytes=4157
)

1 0 VIEW (Cost=60 Card=1 Bytes=4157)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=60 Card=1 Bytes=4144)
4 3 SORT (UNIQUE STOPKEY) (Cost=42 Card=1 Bytes=117)
5 4 NESTED LOOPS (Cost=23 Card=1 Bytes=117)
6 5 NESTED LOOPS (OUTER) (Cost=22 Card=1 Bytes=102)
7 6 NESTED LOOPS (Cost=21 Card=1 Bytes=98)
8 7 NESTED LOOPS (Cost=13 Card=8 Bytes=696)
9 8 NESTED LOOPS (Cost=5 Card=8 Bytes=512)
10 9 NESTED LOOPS (Cost=4 Card=1 Bytes=56)
11 10 TABLE ACCESS (BY INDEX ROWID) OF 'SYSTEM_CODE_VALUE' (Cost=2 Card=1 Bytes=21)

12 11 INDEX (UNIQUE SCAN) OF 'XAK1SYSTEM_CODE_VALUE' (UNIQUE) (Cost=1 Card=2571)

13 10 TABLE ACCESS (BY INDEX ROWID) OF 'BUSINESS' (Cost=2 Card=1 Bytes=35)

14 13 INDEX (RANGE SCAN) OF 'XAKBUSINESS_FUNC2' (NON-UNIQUE) (Cost=1 Card=1)

15 9 INDEX (RANGE SCAN) OF 'XPKPERSON_BUSINESS_USAGE' (UNIQUE) (Cost=1 Card=96042 Bytes=768336)

16 8 TABLE ACCESS (BY INDEX ROWID) OF 'PERSON'(Cost=1 Card=575203 Bytes=13229669)

17 16 INDEX (UNIQUE SCAN) OF 'XPKPERSON' (UNIQUE)

18 7 INDEX (RANGE SCAN) OF 'XPKPERSON_ADDRESS_USAGE' (UNIQUE) (Cost=1 Card=1 Bytes=11)

19 6 INDEX (RANGE SCAN) OF 'PERSON_USER_PK' (UNIQUE) (Cost=1 Card=1 Bytes=4)

20 5 TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESS' (Cost=1 Card=444720 Bytes=6670800)

21 20 INDEX (UNIQUE SCAN) OF 'XPKADDRESS' (UNIQUE)


Tom Kyte
October 20, 2004 - 5:02 pm UTC

tell you what -- for that literal, do this

select *
from (
select a.*, max(cnt) over() maxcnt, min(cnt) mincnt
from (
select that_column, count(*) cnt
from table
group by that_column )a
)
where cnt = maxcnt or cnt = mincnt;


Now, use the literal with the min and the max in there -- tell me what you see then.

sys_context works just like a bind there. you are most likely querying a skewed column.

so, do you see a change in plans when you use the two literals I suggest you to try?

Application context and performance

Dan, October 21, 2004 - 12:34 pm UTC

Tom, 

   I'm not understanding somthing When I replace this_column and table with my values I get the following


  1  select *
  2   from (
  3  select a.*, max(cnt) over() maxcnt, min(cnt) mincnt
  4    from (
  5  select PARTICIPANT_ID, count(*) cnt
  6    from business
  7   group by participant_id )a
  8  )
  9* where cnt = maxcnt or cnt = mincnt
SQL> /
select a.*, max(cnt) over() maxcnt, min(cnt) mincnt
         *
ERROR at line 3:
ORA-00937: not a single-group group function


SQL>  

Tom Kyte
October 21, 2004 - 3:08 pm UTC

ops$tkyte@ORA9IR2> @test
ops$tkyte@ORA9IR2> select *
  2   from (
  3  select a.*, max(cnt) over() maxcnt, min(cnt) <b>over()</b> mincnt
  4    from (
  5  select job, count(*) cnt
  6    from emp
  7   group by job )a
  8  )
  9  where cnt = maxcnt or cnt = mincnt
 10  /
 
JOB              CNT     MAXCNT     MINCNT
--------- ---------- ---------- ----------
CLERK              4          4          1
PRESIDENT          1          4          1
SALESMAN           4          4          1


<b>I was missing an "over()"</b>
 

Dynamic where and sys_context

phil, December 21, 2004 - 10:36 pm UTC

Hi Tom,

I have as part of my predicate an and which in turn can contain 1 or more or's

eg
and (
(currency='AUD' and amount=>100) or
(currency='AUD' and amount<-100) or
....
(currency='USD' and amount>2100)
)

the currency and amounts that are requested for the query come via an xml document. I extract the values from the relevant elements no probs, however am I binding correctly? And is this the best and most efficient way. I use your variable in list method regulary , is there a way I can do a variable or list as above in a similar fashion? Can I simplifiy this so that this part of the predicate will always be the same ?

my example:

declare
l_req xmlType := xmlType('<Doc:Document xmlns:Doc="urn:rt:xsd:rt.cashrepv1$gettransaction" xmlns:xsi="</code> http://www.w3.org/2001/XMLSchema-instance" <code>xsi:schemaLocation="urn:rt:xsd:rt.cashrepv1$gettransaction rt.CASHREPV1$GETTRANSACTION.xsd ">'
||' <Doc:gettransaction>'
||' <Doc:NstrTxSchCr>'
||' <Doc:NstrAcctCshNtrySch>'
||' <Doc:NtryAmt>'
||' <Doc:Amt>'
||' <Doc:FrAmt>'
||' <Doc:BdryAmt>1.0</Doc:BdryAmt>'
||' <Doc:Incl>true</Doc:Incl>'
||' </Doc:FrAmt>'
||' </Doc:Amt>'
||' <Doc:Ccy>AUD</Doc:Ccy>'
||' <Doc:CdtDbtInd>CRDT</Doc:CdtDbtInd>'
||' </Doc:NtryAmt>'
||' <Doc:NtryAmt>'
||' <Doc:Amt>'
||' <Doc:ToAmt>'
||' <Doc:BdryAmt>200</Doc:BdryAmt>'
||' <Doc:Incl>true</Doc:Incl>'
||' </Doc:ToAmt>'
||' </Doc:Amt>'
||' <Doc:Ccy>AUD</Doc:Ccy>'
||' <Doc:CdtDbtInd>DBIT</Doc:CdtDbtInd>'
||' </Doc:NtryAmt>'
||' </Doc:NstrAcctCshNtrySch>'
||' </Doc:NstrTxSchCr>'
||' </Doc:gettransaction>'
||'</Doc:Document>');

l_docxmlns varchar2(100) := 'xmlns:Doc="urn:rt:xsd:rt.cashrepv1$gettransaction"';
l_AmtCur varchar2(20);
l_sqladd2 varchar2(2000);
l_n number :=1;
l_or varchar2(5);
begin

select count(*) into l_AmtCur from table( xmlsequence( extract(l_req, '//Doc:NstrTxSchCr/Doc:NstrAcctCshNtrySch/Doc:NtryAmt/Doc:Amt/Doc:FrAmt/Doc:BdryAmt', l_docxmlns) ));
dbms_output.put_line(l_amtcur);

for c in (select extract(value(t), '//Doc:Ccy/text()',l_docxmlns).getStringVal() Ccy,
extract(value(t), '//Doc:Amt/Doc:FrAmt/Doc:BdryAmt/text()',l_docxmlns).getStringVal() FrAmt,
extract(value(t), '//Doc:Amt/Doc:ToAmt/Doc:BdryAmt/text()',l_docxmlns).getStringVal() ToAmt,
extract(value(t), '//Doc:Amt/Doc:FrToAmt/Doc:FrAmt/Doc:BdryAmt/text()',l_docxmlns).getStringVal() FrToAmt_FrAmt,
extract(value(t), '//Doc:Amt/Doc:FrToAmt/Doc:ToAmt/Doc:BdryAmt/text()',l_docxmlns).getStringVal() FrToAmt_ToAmt
from table( xmlsequence( extract(l_req, '//Doc:NstrTxSchCr/Doc:NstrAcctCshNtrySch/Doc:NtryAmt',l_docxmlns) ))t )
loop

-- dbms_session.set_context('bv_context','Ccy_'||l_n ,c.Ccy);
l_sqladd2 := l_sqladd2 || l_or || ' ( i.isocode sys_context(''bv_context'',''Ccy_'||l_n ||''') ';
if c.FrAmt is not null then
-- dbms_session.set_context('bv_context','FCcy_'||l_n ,c.FrAmt);
l_sqladd2 := l_sqladd2 || 'and l.amount >= sys_context(''bv_context'',''FCcy_'||l_n ||''') )';
elsif c.ToAmt is not null then
-- dbms_session.set_context('bv_context','TCcy_'||l_n ,c.ToAmt);
l_sqladd2 := l_sqladd2 || 'and l.amount <= sys_context(''bv_context'',''TCcy_'||l_n ||''') )';
end if;

l_n := l_n +1;
l_or := ' OR ' ;
end loop;
dbms_output.put_line(l_sqladd2);
end;
/

produces output of :

( i.isocode sys_context('bv_context','Ccy_1') and l.amount >= sys_context('bv_context','FCcy_1') ) OR ( i.isocode sys_context('bv_context','Ccy_2') and l.amount <= sys_context('bv_context','TCcy_2') )

which I can then concentenate onto the query.

many thanks
phil






Tom Kyte
December 22, 2004 - 8:45 am UTC

looks reasonable, since you have < > (ranges) to deal with, or'ing like that is going to be the only way.

(you need "=" in there -- i.isocode = sys_context...)




build Insert statement

Faisal, June 08, 2005 - 9:46 pm UTC

Hi Tom,

I am loading data from 20 external tables into one common table. Each external table has different structure but I want to code a single load process. How can I achive as follows?

insert /*+append */ into stage (list of columns here from meta data) select (list of columns here from meta data) from external_table (from meta data table).

Note: I have a meta data table where I have one to one mapping between stage tables and 20 external tables.

Thanks,

Tom Kyte
June 08, 2005 - 10:22 pm UTC

Each external
table has different structure but I want to code a single load process.


explain how that could be?

Seeing values stored in contexts

A reader, June 09, 2005 - 12:31 pm UTC

Various sessions use dbms_session.set_context() to set values in application contexts.

Is there some dba or v$ view or query that lets me peek at all these values?

Thanks

Tom Kyte
June 09, 2005 - 6:17 pm UTC

no, in general, this stuff is "private memory", in the process -- not necessarily in the SGA and therefore not generally available.

Now, global contexts are stored in the sga, but I'm assuming you are using normal contexts (as most should be)

More Info

Faisal, June 09, 2005 - 12:47 pm UTC

Hi Tom,

One process, which insert into one target table but have muliple different external tables. Each external table structure is a subset of target table. For example

Ext Table A

clm_no
claimant_no
subfile_code
cover_code
transaction_code

Ext Table B

clm_no
claimant_no
subfile_code
trans_type_code
transaction_amount

Target Staging Table (Stg)

clm_no
claimant_no
subfile_code
cover_code
transaction_code
trans_type_code
transaction_amount

Now when I am loading from external table A, I want like this

insert into stg (clm_no,claimant_no,subfile_code,cover_code
transaction_code ) select (clm_no,claimant_no,subfile_code,cover_code, transaction_code) from external_table_a;

for external table B

insert into stg (clm_no,claimant_no,subfile_code,trans_type_code,transaction_amount) select (clm_no,claimant_no,subfile_code,trans_type_code,transaction_amount) from external_table_b;


I need to build above dynamically.

Regards.




Tom Kyte
June 09, 2005 - 6:21 pm UTC

frankly, with 20 tables, I would not be thinking dyanmic sql, it would be at least as hard or harder to do dynamically.

I'd stick with static sql.

set_context results in dredful performance

James, February 14, 2006 - 11:50 pm UTC

Dear Tom,

Can you shed light onto the following?

I have 2 queries, exactly the same, except that that 2nd query uses dbms_session.set_context.

The query using set_context runs consideralby longer and actually uses much less consistant gets.

The plans in TKPROF look the same, but the funny thing is the static sql plan has "0" for rows in the execution plan and doesn't display the "Row Source Operation" at all.

RUNSTATS also shows how bad the context query runs.

Here is the info on the 1st query:

**************************************************************************************

SELECT /* static */ count(*)
from dealers,used_leads
where used_leads.dealer_id = dealers.dealer_id
and nvl(used_leads.new_flag,0)=0
and dealers.dealer_id=:b3
and create_date>=:b2
and create_date<=:b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 100 0.06 0.04 0 0 0 0
Fetch 100 1.61 2.10 1458 174554 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200 1.67 2.15 1458 174554 0 100

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 36180 (VEHICLES) (recursive depth: 1)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
0 FILTER
0 NESTED LOOPS
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'DEAL_PK' (UNIQUE)

0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'USED_LEADS'
0 BITMAP CONVERSION (TO ROWIDS)
0 BITMAP AND
0 BITMAP CONVERSION (FROM ROWIDS)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'USLE_DEID_IND' (NON-UNIQUE)
0 BITMAP CONVERSION (FROM ROWIDS)
0 SORT (ORDER BY)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'USLE_CRDA_IND' (NON-UNIQUE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1458 0.01 0.52

***************************************************************************

No row info, no "Row Source Operation" section.

Here is the set_context query (run via execute immediate):

***************************************************************************

select /* context */ count(*) from dealers,used_leads
where used_leads.dealer_id = dealers.dealer_id
and nvl(used_leads.new_flag,0)=0
and dealers.dealer_id=to_number(sys_context( 'VEHICLE_UTILS_CTX', 'DEALER_ID' ))
and create_date>=to_date(sys_context( 'VEHICLE_UTILS_CTX', 'START_DATE' ),'DD-MON-YYYY')
and create_date<=to_date(sys_context( 'VEHICLE_UTILS_CTX', 'END_DATE' ),'DD-MON-YYYY')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 100 0.00 0.01 0 0 0 0
Execute 100 0.11 0.07 0 0 0 0
Fetch 100 22.73 26.89 0 23131 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 300 22.84 26.98 0 23131 0 100

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 36180 (VEHICLES) (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=206 r=0 w=0 time=242396 us)
0 FILTER (cr=206 r=0 w=0 time=242385 us)
0 NESTED LOOPS (cr=206 r=0 w=0 time=242381 us)
1 INDEX UNIQUE SCAN DEAL_PK (cr=2 r=0 w=0 time=49 us)(object id 104079)
0 TABLE ACCESS BY INDEX ROWID USED_LEADS (cr=204 r=0 w=0 time=242253 us)
0 BITMAP CONVERSION TO ROWIDS (cr=204 r=0 w=0 time=242244 us)
0 BITMAP AND (cr=204 r=0 w=0 time=242238 us)
1 BITMAP CONVERSION FROM ROWIDS (cr=4 r=0 w=0 time=760 us)
378 INDEX RANGE SCAN USLE_DEID_IND (cr=4 r=0 w=0 time=303 us)(object id 106544)
1 BITMAP CONVERSION FROM ROWIDS (cr=200 r=0 w=0 time=241454 us)
40740 SORT ORDER BY (cr=200 r=0 w=0 time=229538 us)
40740 INDEX RANGE SCAN USLE_CRDA_IND (cr=200 r=0 w=0 time=37397 us)(object id 106543)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 SORT (AGGREGATE)
0 FILTER
0 NESTED LOOPS
1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'DEAL_PK' (UNIQUE)

0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'USED_LEADS'
0 BITMAP CONVERSION (TO ROWIDS)
0 BITMAP AND
1 BITMAP CONVERSION (FROM ROWIDS)
378 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'USLE_DEID_IND' (NON-UNIQUE)
1 BITMAP CONVERSION (FROM ROWIDS)
40740 SORT (ORDER BY)
40740 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'USLE_CRDA_IND' (NON-UNIQUE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch free 1 0.00 0.00

**********************************************************************************

And the runstats:


Run1 ran in 158 hsecs
Run2 ran in 2545 hsecs
run 1 ran in 6.21% of the time

Name Run1 Run2 Diff
LATCH.ktm global data 0 1 1
LATCH.kwqit: protect wakeup ti 0 1 1
LATCH.ncodef allocation latch 0 1 1
LATCH.session switching 0 1 1
STAT...free buffer requested 5 4 -1
LATCH.transaction branch alloc 0 1 1
LATCH.temp lob duration state 0 1 1
LATCH.spilled msgs queues list 0 1 1
STAT...parse time elapsed 0 1 1
STAT...active txn count during 4 6 2
STAT...calls to kcmgcs 4 6 2
STAT...db block gets 531 533 2
STAT...cleanout - number of kt 4 6 2
STAT...db block changes 1,037 1,040 3
LATCH.loader state object free 0 4 4
STAT...consistent changes 519 523 4
LATCH.session timer 0 9 9
LATCH.sort extent pool 0 10 10
LATCH.begin backup scn array 0 22 22
STAT...redo size 65,964 65,904 -60
LATCH.active checkpoint queue 6 67 61
LATCH.event group latch 1 72 71
LATCH.process allocation 1 72 71
STAT...index scans kdiixs1 100 194 94
STAT...sorts (memory) 4 98 94
STAT...shared hash latch upgra 100 194 94
STAT...opened cursors cumulati 0 100 100
STAT...parse count (total) 0 100 100
STAT...session cursor cache hi 0 100 100
LATCH.channel handle pool latc 3 147 144
LATCH.process group creation 3 147 144
LATCH.dummy allocation 5 169 164
LATCH.transaction allocation 6 188 182
STAT...workarea executions - o 8 196 188
STAT...consistent gets - exami 404 594 190
LATCH.JOX SGA heap latch 69 279 210
LATCH.channel operations paren 6 236 230
LATCH.library cache load lock 75 352 277
LATCH.cache buffer handles 721 1,016 295
LATCH.list of block allocation 22 341 319
LATCH.user lock 24 452 428
LATCH.FOB s.o list latch 38 471 433
LATCH.lgwr LWN SCN 31 618 587
LATCH.Consistent RBA 31 620 589
LATCH.mostly latch-free SCN 31 620 589
LATCH.simulator lru latch 128 912 784
LATCH.post/wait queue 49 884 835
LATCH.sequence cache 42 1,165 1,123
LATCH.cache buffers lru chain 139 1,539 1,400
LATCH.redo writing 101 1,944 1,843
STAT...recursive cpu usage 152 2,089 1,937
LATCH.multiblock read objects 6 2,044 2,038
STAT...Elapsed Time 170 2,558 2,388
LATCH.SQL memory manager worka 217 3,079 2,862
LATCH.enqueues 251 4,541 4,290
LATCH.dml lock allocation 246 4,622 4,376
LATCH.child cursor hash table 277 5,195 4,918
LATCH.messages 336 6,392 6,056
LATCH.checkpoint queue latch 638 7,034 6,396
LATCH.undo global data 347 7,178 6,831
LATCH.enqueue hash chains 513 10,097 9,584
LATCH.session idle bit 884 13,801 12,917
LATCH.session allocation 1,666 19,366 17,700
LATCH.redo allocation 2,338 30,883 28,545
LATCH.row cache enqueue latch 6,930 106,284 99,354
LATCH.row cache objects 7,060 109,535 102,475
LATCH.library cache pin alloca 7,746 125,845 118,099
LATCH.simulator hash latch 17,609 149,564 131,955
STAT...session logical reads 175,106 23,670 -151,436
STAT...consistent gets 174,575 23,137 -151,438
LATCH.shared pool 9,493 161,204 151,711
STAT...no work - consistent re 174,071 22,349 -151,722
STAT...buffer is not pinned co 173,699 3,422 -170,277
STAT...table fetch by rowid 185,809 3,487 -182,322
STAT...buffer is pinned count 197,919 3,552 -194,367
LATCH.library cache pin 14,922 263,933 249,011
LATCH.library cache 28,042 469,046 441,004
LATCH.cache buffers chains 533,974 4,131,365 3,597,391
STAT...sorts (rows) 5,985 3,835,545 3,829,560

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
635,027 5,643,371 5,008,344 11.25%

**********************************************************************

Totally amazing! How can they be so massivly different? I know that static sql will always run best, but this is a huge difference.

Can it be explained?

And how can the the line:

STAT...consistent gets 174,575 23,137 -151,438

be explained? When run 2 runs so badly?

BTW I am using Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production.

Regards,
James


Tom Kyte
February 15, 2006 - 8:54 am UTC

the trace file was incomplete. Can you

a) log into sqlplus
b) enable tracing
c) run both queries
d) >>>> EXIT <<<<< sqlplus


the first cursor is not getting closed, we cannot really tell *what* it did, it is incomplete. We are not seeing everything

OMG look at it now!

James, February 15, 2006 - 1:00 am UTC

Well an hour or so later:

Run1 ran in 3477 hsecs
Run2 ran in 3610 hsecs
run 1 ran in 96.32% of the time

Name Run1 Run2 Diff
LATCH.ncodef allocation latch 0 1 1
LATCH.session switching 0 1 1
LATCH.transaction branch alloc 0 1 1
STAT...free buffer requested 4 5 1
LATCH.temp lob duration state 9 10 1
LATCH.commit callback allocati 2 0 -2
STAT...consistent gets 23,134 23,136 2
STAT...recursive cpu usage 2,125 2,127 2
STAT...consistent gets - exami 591 593 2
STAT...cleanout - number of kt 3 5 2
STAT...active txn count during 3 5 2
STAT...calls to kcmgcs 3 5 2
LATCH.ktm global data 0 3 3
LATCH.sort extent pool 37 40 3
LATCH.FIB s.o chain latch 0 4 4
STAT...consistent changes 517 521 4
STAT...db block changes 1,034 1,039 5
STAT...db block gets 527 533 6
STAT...session logical reads 23,661 23,669 8
LATCH.event group latch 32 61 29
LATCH.process allocation 32 62 30
LATCH.FOB s.o list latch 473 441 -32
LATCH.mostly latch-free SCN 1,879 1,839 -40
LATCH.Consistent RBA 1,872 1,831 -41
LATCH.lgwr LWN SCN 1,874 1,831 -43
LATCH.active checkpoint queue 65 125 60
STAT...redo size 65,904 65,964 60
LATCH.channel handle pool latc 52 119 67
LATCH.process group creation 52 119 67
LATCH.sequence cache 1,045 977 -68
LATCH.transaction allocation 288 365 77
LATCH.dummy allocation 238 316 78
LATCH.dml lock allocation 6,410 6,328 -82
LATCH.redo writing 5,719 5,628 -91
STAT...opened cursors cumulati 0 100 100
STAT...session cursor cache hi 0 100 100
STAT...parse count (total) 0 100 100
LATCH.channel operations paren 106 210 104
LATCH.user lock 606 711 105
LATCH.post/wait queue 1,688 1,561 -127
STAT...Elapsed Time 3,499 3,634 135
LATCH.list of block allocation 476 312 -164
LATCH.enqueue hash chains 13,344 13,516 172
LATCH.enqueues 5,971 6,148 177
LATCH.begin backup scn array 39 227 188
LATCH.SQL memory manager worka 4,867 5,092 225
LATCH.JOX SGA heap latch 773 520 -253
LATCH.cache buffer handles 4,730 4,474 -256
LATCH.messages 10,991 11,378 387
LATCH.library cache load lock 636 1,154 518
LATCH.cache buffers lru chain 1,594 2,335 741
LATCH.loader state object free 1,910 2,678 768
LATCH.child cursor hash table 7,251 9,065 1,814
LATCH.simulator lru latch 806 2,727 1,921
LATCH.row cache objects 216,450 214,490 -1,960
LATCH.library cache 766,740 764,238 -2,502
LATCH.session idle bit 22,335 25,043 2,708
LATCH.library cache pin alloca 221,827 218,930 -2,897
LATCH.library cache pin 405,193 409,503 4,310
LATCH.checkpoint queue latch 7,089 11,998 4,909
LATCH.redo allocation 25,513 18,500 -7,013
LATCH.shared pool 262,232 269,788 7,556
LATCH.undo global data 18,064 9,945 -8,119
LATCH.simulator hash latch 215,853 224,375 8,522
LATCH.multiblock read objects 1,010 14,988 13,978
LATCH.row cache enqueue latch 208,854 193,112 -15,742
LATCH.session allocation 17,000 70,878 53,878
STAT...session uga memory 0 65,408 65,408
STAT...session pga memory 0 65,536 65,536
LATCH.cache buffers chains 5,597,420 7,080,005 1,482,585

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
8,061,466 9,608,022 1,546,556 83.90%

PL/SQL procedure successfully completed.

The 2 approaches now both look terrible and "consistant gets" have dissapeared from this report all together!

Unfourtunatly these stats do come from a production system which is obviously under greater load now then was a few hours ago... looks like I should do as you suggest and run this on our test server as benchmarking performance of these queries is being effected by what else is running.

Cheers





TKPROF from Production and Test

James, February 15, 2006 - 6:00 pm UTC

Hi Tom,

I've done as you suggested (although I thought I did exit the session previously...) I also ran each block once, turned on tracing and ran again and exited the sqlplus session.

The first file is from production. Currently it is a quite period on the production system and as you can see we get the strange context behaviour.

The "test" tkprof is much more well behaved. To be expected for sure, but still, releasing this context query into production scared me.

The scenario here is that I have coded as static sql the most popular versions of the query depending on the input paramters. If a given parameter combination is not covered by a static query, the function builds a context query.

Good thinking don't you agree? The context query will only be called infrequently but even so a 20X difference in CPU utilization is ridiculous - especially when my test system indicates the context query is only 2X less efficient.

Maybe it indicates we have a serious problem on production?

Regards

*********
****************PRODCUTION TKPROF*******************
*********

TKPROF: Release 9.2.0.4.0 - Production on Thu Feb 16 09:20:54 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: csdb2_ora_518.trc
Sort options: default
alter session set sql_trace=TRUE
alter session set timed_statistics=TRUE
alter session set events '10046 trace name context forever, level 12'

*******************************

declare
dealer_leads number :=0;
begin
for x in 1 .. 100 loop
dealer_leads := dealer_leads + test_vehicle_utils.countDealerLeads(x,null,null,null,to_date('01-MAR-2005','DD-MON-YYYY'),to_date('01-APR-2005','DD-MON-YYYY'),null,null);
end loop;
dealer_leads := 0;
for x in 1 .. 100 loop
dealer_leads := dealer_leads + test_vehicle_utils.countDealerLeads(x,null,null,null,to_date('01-MAR-2005','DD-MON-YYYY'),to_date('01-APR-2005','DD-MON-YYYY'),null,-2);
end loop;
end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.41 0.38 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.42 0.39 0 0 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 36180 (VEHICLES)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.01 0.01
********************************************************************************

SELECT count(*)
from dealers,used_leads
where used_leads.dealer_id = dealers.dealer_id
and nvl(used_leads.new_flag,0)=0
and dealers.dealer_id=:b3
and create_date>=:b2
and create_date<=:b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 100 0.04 0.05 0 0 0 0
Fetch 100 1.33 1.30 0 174651 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200 1.37 1.36 0 174651 0 100

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 36180 (VEHICLES) (recursive depth: 1)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
0 FILTER
0 NESTED LOOPS
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'DEAL_PK' (UNIQUE)

0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'USED_LEADS'
0 BITMAP CONVERSION (TO ROWIDS)
0 BITMAP AND
0 BITMAP CONVERSION (FROM ROWIDS)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'USLE_DEID_IND' (NON-UNIQUE)
0 BITMAP CONVERSION (FROM ROWIDS)
0 SORT (ORDER BY)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'USLE_CRDA_IND' (NON-UNIQUE)

********************************************************************************

select count(*) from dealers,used_leads
where used_leads.dealer_id = dealers.dealer_id
and nvl(used_leads.new_flag,0)=0 and dealers.dealer_id=to_number(sys_context( 'VEHICLE_UTILS_CTX', 'DEALER_ID' )) and create_date>=to_date(sys_context( 'VEHICLE_UTILS_CTX', 'START_DATE' ),'DD-MON-YYYY') and create_date<=to_date(sys_context( 'VEHICLE_UTILS_CTX', 'END_DATE' ),'DD-MON-YYYY')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 100 0.00 0.00 0 0 0 0
Execute 100 0.04 0.03 0 0 0 0
Fetch 100 20.38 20.33 0 23131 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 300 20.42 20.38 0 23131 0 100

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 36180 (VEHICLES) (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=206 r=0 w=0 time=228714 us)
0 FILTER (cr=206 r=0 w=0 time=228700 us)
0 NESTED LOOPS (cr=206 r=0 w=0 time=228697 us)
1 INDEX UNIQUE SCAN DEAL_PK (cr=2 r=0 w=0 time=42 us)(object id 104079)
0 TABLE ACCESS BY INDEX ROWID USED_LEADS (cr=204 r=0 w=0 time=228570 us)
0 BITMAP CONVERSION TO ROWIDS (cr=204 r=0 w=0 time=228564 us)
0 BITMAP AND (cr=204 r=0 w=0 time=228558 us)
1 BITMAP CONVERSION FROM ROWIDS (cr=4 r=0 w=0 time=436 us)
378 INDEX RANGE SCAN USLE_DEID_IND (cr=4 r=0 w=0 time=264 us)(object id 106544)
1 BITMAP CONVERSION FROM ROWIDS (cr=200 r=0 w=0 time=228099 us)
40740 SORT ORDER BY (cr=200 r=0 w=0 time=216207 us)
40740 INDEX RANGE SCAN USLE_CRDA_IND (cr=200 r=0 w=0 time=35053 us)(object id 106543)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 SORT (AGGREGATE)
0 FILTER
0 NESTED LOOPS
1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'DEAL_PK' (UNIQUE)

0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'USED_LEADS'
0 BITMAP CONVERSION (TO ROWIDS)
0 BITMAP AND
1 BITMAP CONVERSION (FROM ROWIDS)
378 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'USLE_DEID_IND' (NON-UNIQUE)
1 BITMAP CONVERSION (FROM ROWIDS)
40740 SORT (ORDER BY)
40740 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'USLE_CRDA_IND' (NON-UNIQUE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch free 1 0.00 0.00

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.01 0.01 0 0 0 0
Execute 6 0.41 0.38 0 0 0 3
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.42 0.39 0 0 0 3

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 5.47 6.17


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 100 0.00 0.00 0 0 0 0
Execute 200 0.08 0.09 0 0 0 0
Fetch 200 21.71 21.64 0 197782 0 200
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 500 21.79 21.74 0 197782 0 200

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch free 1 0.00 0.00

107 user SQL statements in session.
0 internal SQL statements in session.
107 SQL statements in session.
2 statements EXPLAINed in this session.
********************************************************************************
Trace file: csdb2_ora_518.trc
Trace file compatibility: 9.00.01
Sort options: default

1 session in tracefile.
107 user SQL statements in trace file.
0 internal SQL statements in trace file.
107 SQL statements in trace file.
8 unique SQL statements in trace file.
2 SQL statements EXPLAINed using schema:
VEHICLES.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
3496 lines in trace file.


*********
***************TEST TKPROF**********************
*********

TKPROF: Release 9.2.0.7.0 - Production on Thu Feb 16 09:24:19 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

alter session set sql_trace=TRUE
alter session set timed_statistics=TRUE
alter session set events '10046 trace name context forever, level 12'

********************************************************************************

declare
dealer_leads number :=0;
begin
for x in 1 .. 100 loop
dealer_leads := dealer_leads + test_vehicle_utils.countDealerLeads(x,null,null,null,to_date('01-MAR-2005','DD-MON-YYYY'),to_date('01-APR-2005','DD-MON-YYYY'),null,null);
end loop;
dealer_leads := 0;
for x in 1 .. 100 loop
dealer_leads := dealer_leads + test_vehicle_utils.countDealerLeads(x,null,null,null,to_date('01-MAR-2005','DD-MON-YYYY'),to_date('01-APR-2005','DD-MON-YYYY'),null,-2);
end loop;
end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.10 0.15 0 3 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.11 0.16 0 3 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 1482 (VEHICLES)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************

SELECT COUNT(*)
FROM
DEALERS,USED_LEADS WHERE USED_LEADS.DEALER_ID = DEALERS.DEALER_ID AND
NVL(USED_LEADS.NEW_FLAG,0)=0 AND DEALERS.DEALER_ID=:B3 AND CREATE_DATE>=:B2
AND CREATE_DATE<=:B1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 100 0.03 0.03 0 0 0 0
Fetch 100 0.37 0.34 0 40370 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200 0.40 0.37 0 40370 0 100

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 1482 (VEHICLES) (recursive depth: 1)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
0 FILTER
0 NESTED LOOPS
0 INDEX (UNIQUE SCAN) OF 'DEAL_PK' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'USED_LEADS'
0 INDEX (RANGE SCAN) OF 'USLE_DEID_IND' (NON-UNIQUE)

********************************************************************************

select count(*) from dealers,used_leads
where used_leads.dealer_id = dealers.dealer_id
and nvl(used_leads.new_flag,0)=0 and dealers.dealer_id=to_number(sys_context( 'VEHICLE_UTILS_CTX', 'DEALER_ID' )) and create_date>=to_date(sys_context( 'VEHICLE_UTILS_CTX', 'START_DATE' ),'DD-MON-YYYY') and create_date<=to_date(sys_context( 'VEHICLE_UTILS_CTX', 'END_DATE' ),'DD-MON-YYYY')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 100 0.00 0.01 0 0 0 0
Execute 100 0.01 0.01 0 0 0 0
Fetch 100 1.02 0.95 0 40370 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 300 1.03 0.97 0 40370 0 100

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 1482 (VEHICLES) (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
0 FILTER
0 NESTED LOOPS
1 INDEX UNIQUE SCAN DEAL_PK (object id 201431)
0 TABLE ACCESS BY INDEX ROWID USED_LEADS
0 INDEX RANGE SCAN USLE_DEID_IND (object id 201826)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 SORT (AGGREGATE)
0 FILTER
0 NESTED LOOPS
1 INDEX (UNIQUE SCAN) OF 'DEAL_PK' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'USED_LEADS'
0 INDEX (RANGE SCAN) OF 'USLE_DEID_IND' (NON-UNIQUE)

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.01 0.01 0 0 0 0
Execute 6 0.10 0.16 0 3 0 3
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.11 0.17 0 3 0 3

Misses in library cache during parse: 2

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 2.46 3.16


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 101 0.00 0.01 0 0 0 0
Execute 201 0.04 0.05 0 0 0 0
Fetch 201 1.39 1.29 0 80743 0 201
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 503 1.43 1.35 0 80743 0 201

Misses in library cache during parse: 1

107 user SQL statements in session.
1 internal SQL statements in session.
108 SQL statements in session.
2 statements EXPLAINed in this session.
********************************************************************************
Trace file: bsdb1_ora_14302.trc
Trace file compatibility: 9.02.00
Sort options: default

1 session in tracefile.
107 user SQL statements in trace file.
1 internal SQL statements in trace file.
108 SQL statements in trace file.
9 unique SQL statements in trace file.
2 SQL statements EXPLAINed using schema:
VEHICLES.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
2899 lines in trace file.

******************************************************





Tom Kyte
February 15, 2006 - 9:56 pm UTC

cut and paste the sqplus session from production.

if you don't have "row source operation", you didn't exit the session.

now with row source operation and a surpise

James, February 16, 2006 - 1:59 am UTC

Ahhhh...

Well I was running...

sqlplus>

declare

dealer_leads number :=0;

begin

--runStats_pkg.rs_start;

for x in 1 .. 100 loop

--dealer_leads := dealer_leads + test_vehicle_utils.countDealerLeads(x,null,null,null,to_date('01-MAR-2005','DD-MON-YYYY'),to_date('01-APR-2005','DD-MON-YYYY'),null,null);
dealer_leads := dealer_leads + test_vehicle_utils.countDealerLeads(x,null,null,null,to_date('01-MAR-2005','DD-MON-YYYY'),to_date('01-APR-2005','DD-MON-YYYY'),null,-2);

end loop;

end;
/

As a "priming" query... then I would run it again with tracing turned on (from the same session)

alter session set sql_trace=TRUE;
alter session set timed_statistics=TRUE;
alter session set events '10046 trace name context forever, level 12';

declare

dealer_leads number :=0;

begin

--runStats_pkg.rs_start;

for x in 1 .. 100 loop

dealer_leads := dealer_leads + test_vehicle_utils.countDealerLeads(x,null,null,null,to_date('01-MAR-2005','DD-MON-YYYY'),to_date('01-APR-2005','DD-MON-YYYY'),null,null);

end loop;

end;
/

exit

This was causing TKPROF to get confused somehow!

So now running without the "priming query" we can actually see what went on, and whilst the execution plans are the same, the row source opperations are wildly different:

***STATIC SQL***
SELECT count(*)
from dealers,used_leads
where used_leads.dealer_id = dealers.dealer_id
and nvl(used_leads.new_flag,0)=0
and dealers.dealer_id=:b3
and create_date>=:b2
and create_date<=:b1


Rows Row Source Operation
------- ---------------------------------------------------
100 SORT AGGREGATE (cr=174719 r=0 w=0 time=1756383 us)
3487 FILTER (cr=174719 r=0 w=0 time=1751801 us)
3487 NESTED LOOPS (cr=174719 r=0 w=0 time=1748190 us)
100 INDEX UNIQUE SCAN DEAL_PK (cr=200 r=0 w=0 time=3192 us)(object id 104079)
3487 TABLE ACCESS BY INDEX ROWID USED_LEADS (cr=174519 r=0 w=0 time=1741134 us)
185958 INDEX RANGE SCAN USLE_DEID_IND (cr=766 r=0 w=0 time=175857 us)(object id 106544)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
100 SORT (AGGREGATE)
3487 FILTER
3487 NESTED LOOPS
100 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'DEAL_PK' (UNIQUE)

3487 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'USED_LEADS'
185958 BITMAP CONVERSION (TO ROWIDS)
0 BITMAP AND
0 BITMAP CONVERSION (FROM ROWIDS)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'USLE_DEID_IND' (NON-UNIQUE)
0 BITMAP CONVERSION (FROM ROWIDS)
0 SORT (ORDER BY)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'USLE_CRDA_IND' (NON-UNIQUE)

***SET_CONTEXT SQL***
select count(*) from dealers,used_leads
where used_leads.dealer_id = dealers.dealer_id
and nvl(used_leads.new_flag,0)=0
and dealers.dealer_id=to_number(sys_context( 'VEHICLE_UTILS_CTX', 'DEALER_ID' ))
and create_date>=to_date(sys_context( 'VEHICLE_UTILS_CTX', 'START_DATE' ),'DD-MON-YYYY')
and create_date<=to_date(sys_context( 'VEHICLE_UTILS_CTX', 'END_DATE' ),'DD-MON-YYYY')



Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=206 r=0 w=0 time=217378 us)
0 FILTER (cr=206 r=0 w=0 time=217365 us)
0 NESTED LOOPS (cr=206 r=0 w=0 time=217362 us)
1 INDEX UNIQUE SCAN DEAL_PK (cr=2 r=0 w=0 time=40 us)(object id 104079)
0 TABLE ACCESS BY INDEX ROWID USED_LEADS (cr=204 r=0 w=0 time=217186 us)
0 BITMAP CONVERSION TO ROWIDS (cr=204 r=0 w=0 time=217179 us)
0 BITMAP AND (cr=204 r=0 w=0 time=217173 us)
1 BITMAP CONVERSION FROM ROWIDS (cr=4 r=0 w=0 time=447 us)
378 INDEX RANGE SCAN USLE_DEID_IND (cr=4 r=0 w=0 time=262 us)(object id 106544)
1 BITMAP CONVERSION FROM ROWIDS (cr=200 r=0 w=0 time=216705 us)
40740 SORT ORDER BY (cr=200 r=0 w=0 time=204675 us)
40740 INDEX RANGE SCAN USLE_CRDA_IND (cr=200 r=0 w=0 time=35356 us)(object id 106543)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 SORT (AGGREGATE)
0 FILTER
0 NESTED LOOPS
1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'DEAL_PK' (UNIQUE)

0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'USED_LEADS'
0 BITMAP CONVERSION (TO ROWIDS)
0 BITMAP AND
1 BITMAP CONVERSION (FROM ROWIDS)
378 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'USLE_DEID_IND' (NON-UNIQUE)
1 BITMAP CONVERSION (FROM ROWIDS)
40740 SORT (ORDER BY)
40740 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'USLE_CRDA_IND' (NON-UNIQUE)


Can you please help me understand what went on here? Why is it happening?


Regards

Tom Kyte
February 16, 2006 - 11:41 am UTC

bind variable peeking.

explain plan cannot do it
the row source operation is "reality"

sys_context() doesn't "bind peek", using where x > :y does bind peek.

what columns of what tables are indexed here?


could be version issue

James, February 16, 2006 - 9:16 pm UTC

Well there are indexes on:

dealers.dealer_id (DEAL_PK)
used_leads.dealer_id (USLE_DEID_IND)
used_leads.create_date (USLE_CRDA_IND)

I've hinted the correct acces in production by using

/*+ no_index( USED_LEADS USLE_CRDA_IND ) */

When I have a "dealer_id" to access the table with.

The interesting is to compare our TEST enviroment's operations:

select count(*) from dealers,used_leads
where used_leads.dealer_id = dealers.dealer_id
and nvl(used_leads.new_flag,0)=0
and dealers.dealer_id=to_number(sys_context 'VEHICLE_UTILS_CTX', 'DEALER_ID' ))
and create_date between to_date(sys_context 'VEHICLE_UTILS_CTX', 'START_DATE' ),'DD-MON-YYYY') and to_date(sys_context 'VEHICLE_UTILS_CTX', 'END_DATE' ),'DD-MON-YYYY')

Oracle9i Release 9.2.0.7.0 - 64bit Production
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
0 FILTER
0 NESTED LOOPS
1 INDEX UNIQUE SCAN DEAL_PK (object id 201431)
0 TABLE ACCESS BY INDEX ROWID USED_LEADS
378 INDEX RANGE SCAN USLE_DEID_IND (object id 201826)

With production...

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=206 r=0 w=0 time=217378 us)
0 FILTER (cr=206 r=0 w=0 time=217365 us)
0 NESTED LOOPS (cr=206 r=0 w=0 time=217362 us)
1 INDEX UNIQUE SCAN DEAL_PK (cr=2 r=0 w=0 time=40 us)(object id
104079)
0 TABLE ACCESS BY INDEX ROWID USED_LEADS (cr=204 r=0 w=0 time=217186
us)
0 BITMAP CONVERSION TO ROWIDS (cr=204 r=0 w=0 time=217179 us)
0 BITMAP AND (cr=204 r=0 w=0 time=217173 us)
1 BITMAP CONVERSION FROM ROWIDS (cr=4 r=0 w=0 time=447 us)
378 INDEX RANGE SCAN USLE_DEID_IND (cr=4 r=0 w=0 time=262 us)(object
id 106544)
1 BITMAP CONVERSION FROM ROWIDS (cr=200 r=0 w=0 time=216705 us)
40740 SORT ORDER BY (cr=200 r=0 w=0 time=204675 us)
40740 INDEX RANGE SCAN USLE_CRDA_IND (cr=200 r=0 w=0 time=35356
us)(object id 106543)

So it looks like 9.2.0.7.0 either peeks at binds better or is just smarter than 9.2.0.4.0.

The data is the same, the tables and indexes have all been analyised, just different row operations between the 2 versions of Oracle.

Regards





More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here