Learning is not a spectator sport

February 24, 2015

RETURNING BULK COLLECT and database links

Filed under: Uncategorized — connormcdonald @ 6:42 pm

Looks like the nice PL/SQL facility for returning a set of updated rows is restricted when it comes to database links

(This tested on 12.1.0.1)

SQL> declare
  2    type int_list is table of number(12) index by pls_integer;
  3    l_results int_list;
  4
  5  begin
  6    update MY_TABLE b
  7    set b.my_col = ( select max(last_ddl_time) from user_objects@dblink where object_id = b.key_col)
  8    where b.my_col is null
  9    returning b.other_col bulk collect into l_results;
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-22816: unsupported feature with RETURNING clause
ORA-06512: at line 6

When we remove the database link, things revert to what we would expect

 
 
SQL> declare
  2    type int_list is table of number(12) index by pls_integer;
  3    l_acct int_list;
  4
  5  begin
  6    update MY_TABLE b
  7    set b.my_col = ( select max(last_ddl_time) from user_objects where object_id = b.key_col)
  8    where b.my_col is null
 10    returning b.other_col bulk collect into l_results;
 11  end;
 12  /
 
PL/SQL procedure successfully completed.
 

A workaround is to perform an equivalent SELECT to fetch the required data from the remote source (for example, into a temporary table), and then update locally.

February 12, 2015

GROUP BY – wrong results in 12.1.0.2

Filed under: Uncategorized — connormcdonald @ 7:18 pm

I encountered this during some testing – the optimizer can be a little overzealous when it encounters nested group-by’s

SQL> create table T ( ts number not null, c char(10));
 
Table created.
 
SQL> insert into T
  2  select 100+dbms_random.value(1,50),'x'
  3  from ( select 1 from dual  connect by level < 1000 ),
  4       ( select 1 from dual  connect by level < 1000 )
  5  /
 
998001 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL>
SQL> exec dbms_stats.gather_table_stats('','T') ;
 
PL/SQL procedure successfully completed.
 

So I’ve got ~1million rows, with column TS containing decimal numbers ranging between 100 to 150. Now first I’m going to reduce those numbers to integers, via an inline view, and then truncate those numbers to the nearest 100 (the outer statement). Because all the numbers are between 100 and 150, rounding down to the nearest 100, should return just a single row. Lets see what happens…

SQL> select trunc(ts/100),
  2         sum(cnt) per_min,
  3         avg(cnt) avg_cnt_per_min,
  4         max(cnt) max_cnt
  5  from
  6     ( select cast(ts as int) ts,
  7               count(*) cnt
  8        from   t
  9        group by cast(ts as int)
10      )
11  group by trunc(ts/100)
12  order by 1
13  /
 
[snip]
 
50 rows selected.

The optimizer saw the two group-by’s, and mistakenly decided one could be eliminated. We can work around the problem either by using MATERIALIZE

 
SQL>
SQL> with
  2   inline_view as
  3    ( select /*+ materialize */ cast(ts as int) ts,
  4               count(*) cnt
  5        from   t
  6        group by cast(ts as int)
  7      )
  8  select trunc(ts/100),
  9         sum(cnt) per_min,
10         avg(cnt) avg_cnt_per_min,
11         max(cnt) max_cnt
12  from  inline_view
13  group by trunc(ts/100)
14  order by 1
15  /
 
TRUNC(TS/100)    PER_MIN AVG_CNT_PER_MIN    MAX_CNT
------------- ---------- --------------- ----------
            1     998001        19960.02      20613
 
SQL>

or by setting the initialization parameter "_optimizer_aggr_groupby_elim" to false.

Hope this helps anyone hitting this.

Addenda:

Thanks to Sayan Malakshinov for pointing to some more information on this here: https://jonathanlewis.wordpress.com/2014/09/04/group-by-bug/

Addenda #2:

I’ve just applied the patch to bug 19567916 which claims to fix the issue.  The patch was applied on top of 12.1.0.2 bp4, and does not appear to have solved it (at least in my case).

January 13, 2015

Inappropriate behaviour

Filed under: Uncategorized — connormcdonald @ 9:04 pm

You pick up little funny things in the day to day with Oracle.

Like this one when you try to drop a tablespace with a queue table in it:

drop tablespace MY_TSPACE including contents
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: Inappropriate utilities used to perform DDL on AQ table
SCOTT.QUEUE_TABLE

You would think that since you’ve asked to drop everything, that well…everything could be dropped, but no :-)

The solution here is to use DBMS_AQADM.DROP_QUEUE_TABLE (with force=true if necessary) to clean it out, and the re-issue your command.

Data Pump import makes me crabby

Filed under: Uncategorized — connormcdonald @ 8:04 pm

I’m sitting here watching the import of a moderately sized database via transportable tablespaces.  You know…the thing you use when a full export / import would be too slow, and this is meant to be … well…fast.

And fast it is.. until it reaches the following step:

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Now understandably, there’s plenty of stats to import, so its fair that it should take a little while.  But a quick look at the SQL that’s being run, reveals something truly hideous.  You’ll see a succession of giant PL/SQL blocks, chock full of literals and row-by-row (slow by slow) processing.  Things like this:

DECLARE   c varchar2(60)
   nv varchar2(1)
   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'
   s varchar2(60) := 'MY_SCHEMA'
   t varchar2(60) := 'MY_TABLE'
   p varchar2(60) := 'MY_PARTITION'
   sp varchar2(1)
   stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,r1,r2,r3,flags,cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24)'
BEGIN  
   DELETE FROM "SYS"."IMPDP_STATS"
   INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n9,n10,n11,n12,d1) VALUES ('T',6,2,t,p,sp,s,795877,2232,61,795877,0,NULL,NULL,NULL,TO_DATE('2015-01-11 01:13:44',df))  
   c := 'COL1'
   EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,7,6.40253651379786E-07,7,9627,0,2456710,2456716,8,1,1639,2456710,0,TO_DATE('2015-01-11 01:13:44',df),'78720215010101','7872021B010101',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,7,6.40253651379786E-07,7,9627,0,2456710,2456716,8,1,4073,2456711,0,TO_DATE('2015-01-11 01:13:44',df),'78720215010101','7872021B010101',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,7,6.40253651379786E-07,7,9627,0,2456710,2456716,8,1,5520,2456712,0,TO_DATE('2015-01-11 01:13:44',df),'78720215010101','7872021B010101',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,7,6.40253651379786E-07,7,9627,0,2456710,2456716,8,1,6305,2456713,0,TO_DATE('2015-01-11 01:13:44',df),'78720215010101','7872021B010101',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,7,6.40253651379786E-07,7,9627,0,2456710,2456716,8,1,7183,2456714,0,TO_DATE('2015-01-11 01:13:44',df),'78720215010101','7872021B010101',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,7,6.40253651379786E-07,7,9627,0,2456710,2456716,8,1,8431,2456715,0,TO_DATE('2015-01-11 01:13:44',df),'78720215010101','7872021B010101',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,7,6.40253651379786E-07,7,9627,0,2456710,2456716,8,1,9627,2456716,0,TO_DATE('2015-01-11 01:13:44',df),'78720215010101','7872021B010101',nv,2,nv
  c := 'COL2'
   EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,4,6.40253651379786E-07,4,9627,0,3.64799419131279E+35,4.32664361665097E+35,3,1,2610,3.64799419131279E+35,0,TO_DATE('2015-01-11 01:13:44',df),'4642','5354',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,4,6.40253651379786E-07,4,9627,0,3.64799419131279E+35,4.32664361665097E+35,3,1,2650,3.65042808046523E+35,0,TO_DATE('2015-01-11 01:13:44',df),'4642','5354',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,4,6.40253651379786E-07,4,9627,0,3.64799419131279E+35,4.32664361665097E+35,3,1,9626,4.25768342399856E+35,0,TO_DATE('2015-01-11 01:13:44',df),'4642','5354',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,4,6.40253651379786E-07,4,9627,0,3.64799419131279E+35,4.32664361665097E+35,3,1,9627,4.32664361665097E+35,0,TO_DATE('2015-01-11 01:13:44',df),'4642','5354',nv,2,nv
  c := 'COL3'
   EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,0,9325,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,1,51741,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,2,51742,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,3,51743,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,4,51745,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,6,51746,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,7,51747,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,8,51748,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,9,51749,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,11,51750,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,12,51751,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,14,51752,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,15,51753,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,16,51754,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,17,51755,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,18,51757,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,19,51758,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,21,51760,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,23,51761,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,24,51762,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,25,51765,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,26,51766,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,27,51767,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,33,51770,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,36,51771,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,38,51772,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,40,51773,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,41,51777,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,42,51778,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,43,51779,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,45,51780,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,46,51782,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,47,51783,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,48,51786,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,49,51787,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,50,51788,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,51,51789,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,52,51790,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,53,51791,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,54,51792,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,55,51793,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,56,51794,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,57,51795,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,58,51796,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,59,51797,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,60,51798,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,61,51799,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,62,51800,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,63,51801,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,64,51802,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,65,51803,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,67,51804,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,68,51806,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,69,51807,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,70,51810,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,72,51811,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,73,51815,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,74,51819,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,75,51820,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,76,51821,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,77,51824,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,78,51825,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,79,51827,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,80,51829,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,81,51830,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,82,51833,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,83,51834,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,84,51835,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,85,51837,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,86,51840,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,87,51841,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,88,51842,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,89,51843,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,90,51844,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,91,51846,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,92,51847,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,93,51849,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,94,51851,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,95,51852,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,96,51853,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,97,51855,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,99,51856,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,100,51858,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,101,51859,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,102,51861,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,103,51862,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,104,51863,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,105,51864,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,106,51866,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,107,51867,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,108,51869,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,109,51870,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,110,51871,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,111,51873,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,113,51874,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,114,51875,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,115,51876,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,116,51877,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,117,51878,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,119,51879,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,120,51880,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,121,51881,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,122,51882,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,123,51884,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,124,51885,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,125,51886,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,126,51887,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,127,51888,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,128,51889,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,129,51891,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,130,51892,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,131,51894,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,132,51896,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,133,51901,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,134,51902,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,135,51903,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,136,51904,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,137,51905,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,139,51910,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,140,51911,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,141,51912,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,142,51914,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,143,51915,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,144,51917,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,145,51918,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,146,51919,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,147,51920,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,148,51921,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,149,51922,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,150,51924,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,151,51925,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,152,51926,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,153,51997,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,155,51999,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,156,52001,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,157,52004,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,163,52005,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,164,52006,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,165,52007,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,166,52008,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,170,52012,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,171,52014,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,172,52015,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,173,52016,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,175,52017,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,177,52018,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,178,52031,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,179,52040,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,180,52138,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,181,52146,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,182,52533,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,183,53076,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,184,6770894,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,185,34964939,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,186,34964971,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,187,35013110,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,188,35013117,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,189,35013120,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,190,35013127,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,191,35016806,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,192,35017376,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,193,35017433,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,194,35017486,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,195,35017703,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,196,35018421,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,197,35018426,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,198,35018438,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,199,35018450,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,200,35018503,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
...
...

And it just goes on and on and on.

My advice would be:

Never let data pump import manage you statistics, ie, use EXCLUDE=TABLE_STATISTICS for any job with a large amount of objects.

Either recalculate them yourself, or export / import them via DBMS_STATS.  Anything to avoid the drivel above :-(

January 2, 2015

Slow external table access

Filed under: Uncategorized — connormcdonald @ 8:40 am

We had an interesting issue on 12.1.0.1, where users were reporting very slow performance on queries to external tables. When I tried to replicate the problem, everything seemed just fine, so I initially reported back the familiar "Well, it works on my PC" :-) [Just kidding]

Anyway, connecting by proxy to one of their accounts, did reveal the error, which suggested something to do with privileges.  A sql trace revealed that the performance was due to a query which appears to get the list of directories and their privileges:

SELECT NAME, PATH, READ, WRITE, EXECUTE FROM SYS.LOADER_DIR_OBJS

the definition of which was:

create or replace force view sys.loader_dir_objs
(name, path, read, write, execute)
bequeath definer
as
select o.name
      ,d.os_path
      ,'TRUE'
      ,'TRUE'
      ,'TRUE'
from   sys.obj$ o
      ,sys.x$dir d
where  o.obj# = d.obj#
and    (o.owner# = uid
or      exists
          (select null
           from   v$enabledprivs
           where  priv_number in (-177
                                 ,-178)))
union all
select 
       o.name
      ,d.os_path
      ,decode(sum(decode(oa.privilege#, 17, 1, 0)), 0, 'FALSE', 'TRUE')
      ,decode(sum(decode(oa.privilege#, 18, 1, 0)), 0, 'FALSE', 'TRUE')
      ,decode(sum(decode(oa.privilege#, 12, 1, 0)), 0, 'FALSE', 'TRUE')
from   sys.obj$ o
      ,sys.x$dir d
      ,sys.objauth$ oa
where  o.obj# = d.obj#
and    oa.obj# = o.obj#
and    oa.privilege# in (12
                        ,17
                        ,18)
and    oa.grantee# in (select kzsrorol
                       from   x$kzsro)
and    not (o.owner# = uid
or          exists
              (select null
               from   v$enabledprivs
               where  priv_number in (-177
                                     ,-178)))
group by o.name
        ,d.os_path;

Re-gathering dictionary and fixed object stats yielded no benefit, so I tinkered with the view definition to come up with a faster version, which was this:

create or replace force view sys.loader_dir_objs
(name, path, read, write, execute)
bequeath definer
as
select o.name
      ,d.os_path
      ,'TRUE'
      ,'TRUE'
      ,'TRUE'
from   sys.obj$ o
      ,sys.x$dir d
where  o.obj# = d.obj#
and    (o.owner# = uid
or      exists
          (select null
           from   v$enabledprivs
           where  priv_number in (-177
                                 ,-178)))
union all
select /*+ leading(d o oa) */
       o.name
      ,d.os_path
      ,decode(sum(decode(oa.privilege#, 17, 1, 0)), 0, 'FALSE', 'TRUE')
      ,decode(sum(decode(oa.privilege#, 18, 1, 0)), 0, 'FALSE', 'TRUE')
      ,decode(sum(decode(oa.privilege#, 12, 1, 0)), 0, 'FALSE', 'TRUE')
from   sys.obj$ o
      ,sys.x$dir d
      ,sys.objauth$ oa
where  o.obj# = d.obj#
and    oa.obj# = o.obj#
and    oa.privilege# in (12
                        ,17
                        ,18)
and    oa.grantee# in (select kzsrorol
                       from   x$kzsro)
and    not (o.owner# = uid
or          exists
              (select null
               from   v$enabledprivs
               where  priv_number in (-177
                                     ,-178)))
group by o.name
        ,d.os_path;

[Note: If you’re having the same issue, before you race out as use the same hints, then be aware that the hints above work for us because the number of directories in our database is very small.  If you’ve got lots of directories defined, this might not be the best approach for you]

The challenge now was getting this performance benefit without actually hacking the dictionary view (which means calls to MOS..and well…that’s no way to be starting the new year :-))

So initially, I used the standard technique of applying a baseline, but encountered some dramas (more on that in a later post).  Then, following a suggestion from an OakTable colleague (http://www.oaktable.net/users/lothar) I ventured down the path of "sql patch":

I put my ‘corrected’ view in place, issued the problem query, and got the full plan using the DBMS_XPLAN with the +OUTLINE parameter.  I put the original version of the view back in place, and then attempted to patch in the altered plan as thus:

declare
  l_sql  clob;
  l_hint clob :=
'[the full text of the plan outline]';
begin
  --
  -- '23ka1fq59wg0b' is the sqlid for my problem query.  
  --
  select sql_text into l_sql from v$sql where sql_id = '23ka1fq59wg0b';

  sys.dbms_sqldiag_internal.i_create_patch(
    sql_text=>l_sql,
    hint_text=>l_hint,
    name=>'patch_LOADER_DIR_OBJS');
end;

This failed with a PL/SQL error, because the hint text for sql patch is limited to 500 characters.  So then it was a case of stripping out hints that were "redundant" [Note: This is generally a bad idea, because there’s a strong argument to be made that no hint is redundant].  But once within 500 chars, the patch was applied, and external table performance is now fine for all users.

December 13, 2014

ORA-4068 and CONSTANT keyword…good and bad

Filed under: Uncategorized — connormcdonald @ 8:31 pm

Anyone that has ever coded PLSQL will be familiar with the error ORA-4068, where you had some state persisted in a session due to a package variable, and then when you change the package, the state is cleared along with an ORA-4068.  Here’s a quick example:

 

Session 1 

SQL> create or replace
  2  package PKG is
  3    procedure P;
  4  end;
  5  /

Package created.

SQL> create or replace
  2  package body PKG is
  3      my_global int := 10;
  4    procedure P is
  5    begin
  6       null;
  7    end;
  8  end;
  9  /

Package body created.

Session 2 

SQL> exec pkg.p

PL/SQL procedure successfully completed.

Session 1

Now we change the package… In this case, I’ve just changed the value of the global

SQL> create or replace
  2  package body PKG is
  3      my_global int := 11;
  4    procedure P is
  5    begin
  6       null;
  7    end;
  8  end;
  9  /

Package body created.

Session 2 

And on next invocation, session 2 gets the error.  Not a great thing if you want to deploy changes to a live application (unless that application had the foresight to have appropriate handlers for ORA-4068).

SQL> exec pkg.p
BEGIN pkg.p; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "MCDONAC.PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "MCDONAC.PKG"
ORA-06508: PL/SQL: could not find program unit being called: "MCDONAC.PKG"
ORA-06512: at line 1

 

One of the nice things to come in 11.2, was that if the global variable was declared as CONSTANT, then the compiler/runtime engines was intelligent enough to know that there was no state, and hence the ORA-4068 could be avoided.  For example, the code below will not suffer from the ORA-4068 error.

SQL> create or replace
  2  package body PKG is
  3      my_global CONSTANT int := 12;
  4    procedure P is
  5    begin
  6       null;
  7    end;
  8  end;
  9  /

Package body created.

This was cool because many a PLSQL programmer comes from a C, C++ or C# background, where constants are often used to enumerate static values, and so they use the same approach in PLSQL.  However, it appears that the compiler is not smart enough to deal with any form of expression.  For example, whilst the code below has a global defined as a constant…

SQL> create or replace
  2  package body PKG is
  3      my_global CONSTANT int := 13+15;
  4    procedure P is
  5    begin
  6       null;
  7    end;
  8  end;
  9  /

Package body created.

…because it is assigned via an expression, the ORA-4068 still rears it head Sad smile 

SQL> exec pkg.p
BEGIN pkg.p; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "MCDONAC.PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "MCDONAC.PKG"
ORA-06508: PL/SQL: could not find program unit being called: "MCDONAC.PKG"
ORA-06512: at line 1

I discovered this when storing some constants for powers of 2 (for IP address manipulation).  So whilst

x := power(2,32)

conveys more meaning than

x := 4294967296

you need to be aware of the implications of doing so.

(plsql optimize level of 3 does not assist here).

Tested on 11.2.0.4 and 12.1.0.2

November 30, 2014

truncated ddl in 12c (and 11.2.0.4)

Filed under: Uncategorized — connormcdonald @ 8:35 pm

Just curious if anyone else is seeing this behaviour..

SQL> set long 500000
SQL> @pt "select * from v$sql where sql_id = '1km492z723vpu'"
 
SQL_TEXT                      : alter table scott.emp
SQL_FULLTEXT                  : alter table scott.emp
SQL_ID                        : 1km492z723vpu
...

Suffice to say, there’s a lot more to that SQL statement, but its lost in v$sql. Once complete, it does appear in full in v$sqlstats, but its a pain when you cant see the whole statement..

November 7, 2014

AUSOUG conference Perth

Filed under: Uncategorized — connormcdonald @ 11:28 am

The annual two day AUSOUG conference in Perth is well underway, and so far, its been a wonderfully successful event. In particular, we Australians are notorious for not really getting into the networking thing, so conferences often have a lot of ‘awkward silences’ when sessions are not on, and we are meant to be (god forbid) *talking* to each other :-)

But this year has been different. Lots of banter, lots of discussion. Also, the AUSOUG committee got it exactly right this year. The venue is just the right size, the rooms are the right size, the timings of the sessions and transit times were spot on. It is a real credit to them – it shows how much they are listening to the speakers and attendees feedback.

I had a couple of talks, which I’ll slideshare in the next couple of days, and we also had plenty of good talks as part of the OTN tour from Tim Hall, Bjorn Rost, Craig Shallamer. Penny Cookson, Jeff Kemp and Chris Muir rounded off the talks I saw.

So if you’re ever in Perth around conference time – put it on your calendar. Its well worth it. (www.ausoug.org.au)

November 6, 2014

A simple 12c query with a cool result …

Filed under: Uncategorized — connormcdonald @ 9:40 pm

Its not immediately obvious the significance of this query…but trust me…you’ll love it :-)

SQL> select table_name, column_name from dba_tab_cols
  2  where column_name like '%\_VC' escape '\'
  3  and owner = 'SYS'
  4  order by 1,2;

TABLE_NAME                     COLUMN_NAME
------------------------------ ----------------------
ALL_CONSTRAINTS                SEARCH_CONDITION_VC
ALL_VIEWS                      TEXT_VC
CDB_CONSTRAINTS                SEARCH_CONDITION_VC
CDB_VIEWS                      TEXT_VC
DBA_CONSTRAINTS                SEARCH_CONDITION_VC
DBA_VIEWS                      TEXT_VC
INT$DBA_CONSTRAINTS            SEARCH_CONDITION_VC
INT$DBA_VIEWS                  TEXT_VC
INT$INT$DBA_CONSTRAINTS        SEARCH_CONDITION_VC
USER_CONSTRAINTS               SEARCH_CONDITION_VC
USER_VIEWS                     TEXT_VC

October 5, 2014

Openworld

Filed under: Uncategorized — connormcdonald @ 9:20 pm

Well, the annual spectacle of enormous proportions has come to a conclusion again. And thats probably the first reason I’d recommend OpenWorld to anyone who works with Oracle who has never been to it. It’s a jaw dropping moment just to see the scale of the event, and how impressively its organised in terms of facilities, lunches, registration and the like.

But onto the technical elements, here’s my impressions of this years conference:

1) Big data, Big data, Big data, Big data

Somewhere in the conference I think there might have been some coverage of database, middleware and cloud :-) but it was dwarfed by Big Data (no pun intended). From an australian perspective, I found this quite interesting, because whilst there is a groundswell of interest in the philosophies and tools relating to big data, I’d hardly say its taken the nation by storm. Maybe things will change in the coming years.

Having said that, one thing I was pleased to see (as an Oracle dinosaur) was a realisation that SQL still persists as the dominant query language, and hence the clamour by vendors to provide a good SQL interface layer on top of their respective big data products. The promise of running SQL over some, any or all data sources, whether they be RDBMS or any other data structure sounds very cool.

2) In-memory

Lots and lots of presentations on the new in-memory database option, and its very hard to find anything bad to see about the feature. It just looks like a wonderful feature although you have to pay for all that wonderment :-) My only disappointment from the conference was that each session was done “standalone” which means the first 15 mins of each precious 45min slot, was the same 10 slides describing the fundamentals. I would have preferred a single intro session, with then other sessions going more into the nitty gritty.

3) Passion matters

A number of presentations on topics close to my heart did little to inspire me, whereas others on topics that (I thought) had no interest to me were riveting, and it all comes down to the enthusiasm of the presenter for the technology they were talking about.

4) The app

Sadly….the OpenWorld app was a PR disaster for Oracle based on the tweets I saw about it (some of which were my own). I’m mentioning this not to unload a fresh bucket of invective in the public arena, but to encourage people to provide feedback to the conference organisers. I was as guilty as anyone else in terms of getting more and more emotive with my tweets as I got more and more frustrated with the app as the week went on. But I also tabulated my issues and sent them off to the organisers, trying to be as objective as possible as well as providing suggested fixes. I recommend you do the same.

All up, another great week, with lots of cool new things to explore and blog about :-) Now onto downloading all the slides…

Older Posts »

The WordPress Classic Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 86 other followers