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')