Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

using bind variable instead of literal

kaericnSep 11 2018 — edited Sep 18 2018

The below sample sql we want to convert from using literal to bind variable for the IN clause.

( Note the sql is just a sample to convey the idea)

Do we have to use dynamic SQL ???

The story:

We use the temp table as an intermediate steps for further data processing.

We see inconsistency response time in the insert statement when using tmp table sometimes but once  its bad it 10000 times slower .

When the response time is good, it comes back immediately.

When it is bad, it takes more than 15 minutes.

The insert data volume is about the same for both good and bad response time.

We dont see anything  suspicious in v$session_wait when its very very slow. 

From the explain plan, The plan looks good also.

The DBA claims that from the ash report shows the root clause is the literal of the IN clause.  The suggested solution is to use bind variable instead.

We have asked the ASH perf report to back it up and document the resolution of the root cause.

In the mean time, we will try to use the suggested solution.

create table tst_all_objects as select * from all_objects

create unique index tst_obj_idx1 on tst_all_objects(object_id)

create bitmap index tst_obj_idx2 on tst_all_objects(object_Type)

CREATE GLOBAL TEMPORARY TABLE tmp_all_objects(

OWNER              VARCHAR2(128)

,OBJECT_NAME        VARCHAR2(128)

,SUBOBJECT_NAME             VARCHAR2(128)

,OBJECT_ID          NUMBER       

,DATA_OBJECT_ID             NUMBER       

,OBJECT_TYPE                VARCHAR2(23) 

,CREATED            DATE         

,LAST_DDL_TIME      DATE         

,TIMESTAMP                  VARCHAR2(19) 

,STATUS                     VARCHAR2(7)  

,TEMPORARY                  VARCHAR2(1)  

,GENERATED                  VARCHAR2(1)  

,SECONDARY                  VARCHAR2(1)  

,NAMESPACE          NUMBER       

,EDITION_NAME               VARCHAR2(128)

,SHARING                    VARCHAR2(13) 

,EDITIONABLE                VARCHAR2(1)  

,ORACLE_MAINTAINED          VARCHAR2(1)

) ON COMMIT PRESERVE ROWS;

insert into tmp_all_objects

(

OWNER

,OBJECT_NAME

,SUBOBJECT_NAME

,OBJECT_ID       

,DATA_OBJECT_ID       

,OBJECT_TYPE 

,CREATED         

,LAST_DDL_TIME         

,TIMESTAMP 

,STATUS  

,TEMPORARY  

,GENERATED  

,SECONDARY  

,NAMESPACE       

,EDITION_NAME

,SHARING 

,EDITIONABLE  

,ORACLE_MAINTAINED

)

select

OWNER

,OBJECT_NAME

,SUBOBJECT_NAME

,OBJECT_ID       

,DATA_OBJECT_ID       

,OBJECT_TYPE 

,CREATED         

,LAST_DDL_TIME         

,TIMESTAMP 

,STATUS  

,TEMPORARY  

,GENERATED  

,SECONDARY  

,NAMESPACE       

,EDITION_NAME

,SHARING 

,EDITIONABLE  

,ORACLE_MAINTAINED

from tst_all_objects  where object_id  >  3000

and  object_type in ('RULE','PROGRAM','CHAIN','JOB')

This post has been answered by AndrewSayer on Sep 12 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 16 2018
Added on Sep 11 2018
29 comments
1,330 views