Learning is not a spectator sport

March 28, 2015

In-memory – can you REALLY drop those indexes ?

Filed under: Uncategorized — connormcdonald @ 1:26 pm

Its not a hard sell to convince anyone that a whopping huge great chunk of compressed column-based memory is going to assist with those analytic-style queries.  The In-memory option seems a natural fit, especially for those people where the bottleneck in their infrastructure is the capacity to consume data from storage.

What perhaps a more interesting area of investigation is the claim that In-memory will also be of benefit in OLTP systems, where (to paraphrase many presenters at OpenWorld 2014), we wont be needing all those indexes on transactional tables.

So I thought I’d have an initial play around in that space, and see what happens.

We’ll start with a simple test bed – a transactional table "T", being a child of parent table "PAR",  with a few indexes representing those "additional" OLTP indexes.

SQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 28 11:58:03 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


SQL> drop table PAR cascade constraints purge;

Table dropped.

SQL> create table PAR ( p int primary key);

Table created.

SQL> insert into PAR
  2  select rownum-1 from dual
  3  connect by level <= 100;

100 rows created.

SQL>

SQL> commit;

Commit complete.


SQL>

SQL> exec dbms_stats.gather_table_stats('','PAR');

PL/SQL procedure successfully completed.


SQL>

SQL> drop table T purge;

Table dropped.


SQL>

SQL> create table T (
  2    pk int,
  3    dist50  int,
  4    dist1000 int,
  5    dist100000 int,
  6    stuff char(30),
  7    numeric_stuff number(10,2)
  8  );

Table created.


SQL>

SQL> drop sequence seq;

Sequence dropped.


SQL> create sequence seq cache 1000;

Sequence created.


SQL>

SQL> insert /*+ APPEND */ into T
  2  select
  3    seq.nextval,
  4    trunc(dbms_random.value(1,50)),
  5    trunc(dbms_random.value(1,1000)),
  6    trunc(dbms_random.value(1,100000)),
  7    'x',
  8    dbms_random.value(1,1000)
  9  from
 10  ( select 1 from dual connect by level <= 1000 ),
 11  ( select 1 from dual connect by level <= 1000 )
 12  /

1000000 rows created.


SQL>

SQL> commit;

Commit complete.


SQL>

SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.


SQL>

SQL> alter table T add primary key ( pk );

Table altered.


SQL> create index IX1 on T ( dist50 );

Index created.


SQL> create index IX2 on T ( dist1000 );

Index created.


SQL> create index IX3 on T ( dist100000 );

Index created.


SQL>

SQL> alter table T add constraint FK foreign key ( dist50 )
  2  references PAR ( p );

Table altered.

So at this point, we’ve got our table pre-populated with 1million rows, with some columns of varying levels of cardinality, each indexed to support adhoc query.

Now we’ll create our "application" that sits on top of this table.  We have a routine for logging a transaction (this is the "OLTP" part of our application)

SQL> create or replace
  2  procedure TRANS_LOGGER is
  3  begin
  4  insert into T values (
  5    seq.nextval,
  6    trunc(dbms_random.value(1,50)),
  7    trunc(dbms_random.value(1,1000)),
  8    trunc(dbms_random.value(1,100000)),
  9    'x',
 10    dbms_random.value(1,1000)
 11  );
 12  commit work write wait immediate;
 13  end;
 14  /

Procedure created.

We’re using the extended commit syntax, because when we run this routine in a PL/SQL loop, we dont want to ‘cheat’ by using the PL/SQL optimization that avoids waiting for commits to complete. Now we’ll add the other half of our application, the support for adhoc queries on those non-primary key columns.

SQL> create or replace
  2  procedure adhoc_query is
  3    x int;
  4    res number;
  5  begin
  6    x := trunc(dbms_random.value(1,50));
  7    select sum(numeric_stuff) into res
  8    from t where dist50 = x;
  9
 10    x := trunc(dbms_random.value(1,1000));
 11    select sum(numeric_stuff) into res
 12    from t where dist1000 = x;
 13
 14    x := trunc(dbms_random.value(1,100000));
 15    select  sum(numeric_stuff) into res
 16    from t where dist100000 = x;
 17
 18  end;
 19  /

Procedure created.

So there we have it. Our "application" is ready to go :-) Now we need to make it "popular", that is, have lots of people use it. To do this, I’ll create a routine which simulates a moderately busy application server thread. We’ll be firing off lots of these later to crank up the load.

SQL> create or replace 
  2  procedure app_server_thread is 
  3    think_time number := 0.05; 
  4    iterations int := 600 * ( 1 / think_time ); 
  5  begin 
  6    for i in 1 .. iterations loop 
  7      dbms_application_info.set_client_info(i||' of '||iterations); 
  8      trans_logger; 
  9      dbms_lock.sleep(think_time); 
  10    end loop; 
  11  end ; 
  12  / 

Procedure created. 

So the "app_server_thread" procedure, will iterate a number of times, calling TRANS_LOGGER with a little bit of "think time", and will roughly run for 10 minutes (the 600 seconds in the iterations expression above).

We’ll also have a another procedure doing a similar thing, but for adhoc query. It will have a little longer think time, representing that our app is mainly transactional focussed with intermittent query.

SQL> create or replace
  2  procedure app_server_thread_query is
  3    think_time number := 0.25;
  4    iterations int := 600 * ( 1 / think_time );
  5  begin
  6   for i in 1 .. iterations loop
  7     dbms_application_info.set_client_info(i||' of '||iterations);
  8     adhoc_query;
  9     dbms_lock.sleep(think_time);
 10   end loop;
 11  end ;
 12  /

Procedure created.

We’re pretty much ready to go now.  I’m running this on a Linux VM which has 12 "virtual" cores, and 128G of RAM hooked up to a Netapp filer for storage.  Using some shell script, we can fire off concurrent session of our "app server" routines.  I’m taking an AWR before and after so we can poke around in the results.

#!/bin/ksh

awr()
{
print "
connect / as sysdba
exec dbms_workload_repository.create_snapshot
exit" | sqlplus /nolog 1>awr.out 2>&1
}

TxnThread()
{
print "
connect xxx/yyy
set timing on
exec app_server_thread
exit" | sqlplus /nolog 1>txnthread.out.$1 2>&1
}

QueryThread()
{
print "
connect xxx/yyy
set timing on
exec app_server_thread_query
exit" | sqlplus /nolog 1>querythread.out.$1 2>&1
}


echo AWR
awr

echo TxnThreads
for i in 0 1 2 3 4 5 6 7 8 9
do
  TxnThread $i &
done

echo QueryThreads
for i in 1 2 3 4 5
do
  QueryThread $i &
done


wait

echo AWR
awr

echo Done

and wait 10 minutes for for it to finish. 

The simplest analysis is how long did it take – we know that a good chunk of the time is sleep time, but we’re just going to compare this later with an in-memory test.

# grep Elapsed *out*
querythread.out.1:Elapsed: 00:12:09.42
querythread.out.2:Elapsed: 00:12:08.45
querythread.out.3:Elapsed: 00:12:08.20
querythread.out.4:Elapsed: 00:12:09.04
querythread.out.5:Elapsed: 00:12:08.74
txnthread.out.0:Elapsed: 00:10:20.91
txnthread.out.1:Elapsed: 00:10:20.92
txnthread.out.2:Elapsed: 00:10:21.01
txnthread.out.3:Elapsed: 00:10:21.11
txnthread.out.4:Elapsed: 00:10:20.90
txnthread.out.5:Elapsed: 00:10:21.00
txnthread.out.6:Elapsed: 00:10:21.06
txnthread.out.7:Elapsed: 00:10:21.10
txnthread.out.8:Elapsed: 00:10:20.86
txnthread.out.9:Elapsed: 00:10:20.95

So about 130 seconds (over the 10mins) for the query threads, and 21 seconds (over the 10mins) for the transactional threads. Now I’ve re-run the exact same setup script above, and added a few more steps:

SQL> alter table T inmemory priority critical;

Table altered.

SQL> alter index ix1 unusable;

Index altered.

SQL> alter index ix2 unusable;

Index altered.

SQL> alter index ix3 unusable;

Index altered.

SQL> select segment_name, bytes from v$im_segments;

SEGMENT_NAME                                  BYTES
---------------------------------------- ----------
T                                          67108864

So now our table is loaded into the in-memory store, and the non-primary key indexes have been tossed out. Now we re-run our benchmark, and see what pops out

# grep Elapsed *out*
querythread.out.1:Elapsed: 00:10:53.10
querythread.out.2:Elapsed: 00:10:53.10
querythread.out.3:Elapsed: 00:10:53.10
querythread.out.4:Elapsed: 00:10:53.07
querythread.out.5:Elapsed: 00:10:53.09
txnthread.out.0:Elapsed: 00:10:20.55
txnthread.out.1:Elapsed: 00:10:21.02
txnthread.out.2:Elapsed: 00:10:20.82
txnthread.out.3:Elapsed: 00:10:20.77
txnthread.out.4:Elapsed: 00:10:20.92
txnthread.out.5:Elapsed: 00:10:20.82
txnthread.out.6:Elapsed: 00:10:20.86
txnthread.out.7:Elapsed: 00:10:20.91
txnthread.out.8:Elapsed: 00:10:20.75
txnthread.out.9:Elapsed: 00:10:20.75

Well thats certainly showing some promise. Our transaction times dont seem to have been adversely affected, and our query performance is better. We can also look at the AWR reports and see how in-memory has influenced things:

Without in-memory

image

 

With in-memory

image

One of the positive outcomes is that redo generation shrinks; less indexes to update means less redo.  If you’ve got crappy storage and you’re suffering with redo write issues, then perhaps in-memory is an avenue worth exploring (although you’d want to be carefully considering license costs versus storage upgrade costs!)

Taking a look at the wait events is also interesting

Without in-memory

image

With in-memory

image

With in-memory enabled, buffer busy waits have dropped…but they have been "replaced" with a new event "IM buffer busy".  Always remember – Memory is memory – if multiple people want to manipulate it, then some sort of serialisation is going to occur.  Similarly, note the appearance of the "latch free" event once in-memory comes into play.  But (in this simple demo), the enabling of in-memory has not hindered the transactional characteristics of the app, and looks to have yielded some redo and query benefits.

This is by no means a definitive "yes, you are fine to drop those indexes" and similarly, not a definitive "you must keep those indexes" .  Your own application will have its own particular idiosyncracies.  But the results are promising – if you’ve got the license dollars for in-memory, you may as well look at exploiting that expenditure in every way possible.

March 20, 2015

WHEN OTHERS … sometimes just not enough

Filed under: Uncategorized — connormcdonald @ 7:04 am

We currently are working on a bug with Oracle Support with dbms_stats in 12.1.0.2.

That may be the subject of a latter post, but in a nutshell, here’s the issue

SQL> exec dbms_stats.gather_table_stats('MY_SCHEMA', 'MY_SCHEMA');
BEGIN dbms_stats.gather_table_stats('MY_SCHEMA', 'MY_SCHEMA'); END;
 
*
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete

Now obviously that’s not meant to be happening, and it pertains to incremental stats on a database that’s been upgraded from 12.1.0.1 to 12.1.0.2.   More on that another time.

But as we work on the bug, a seemingly obvious measure would be to catch that exception and move on…So lets try that, in fact, lets be brutal and ignore all errors, mainly for the purpose of the example, but also to raise the hackles of good friend Tom :-)

SQL> begin
  2    dbms_stats.gather_table_stats('MY_SCHEMA', 'MY_SCHEMA');
  3  exception
  4    when others then
  5       null;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete

Interesting…

By the way, if you’re encountering this issue, delete your INCREMENTAL stats preference as a temporary workaround.

March 14, 2015

v$object_usage catches me out every time :-)

Filed under: Uncategorized — connormcdonald @ 10:25 am

 

Every …. single …. time….

Why does index monitoring make me scratch my head and charge off to google so many times…Well, I’m over it, so time to put it on my blog (even though its already on many other places) so I do not get caught out anymore :-)

It always starts like this:

  • Turn on index monitoring on some indexes
  • Wait…
  • Come back later, and get ready to check on my results
  • Then this happens…
SQL> select * from v$object_usage;

no rows selected

And I panic…Did I run the commands on the wrong database ?  Did they not run properly ? Do I need to flush a pool ? etc etc etc

And after plenty of wasted minutes…I end up digging up the source for the v$object_usage view

create or replace force view sys.v$object_usage
(
  index_name
,table_name
,monitoring
,used
,start_monitoring
,end_monitoring
) as
  select io.name
        ,t.name
        ,decode(bitand(i.flags
                      ,65536)
               ,0, 'NO'
               ,'YES')
        ,decode(bitand(ou.flags
                      ,1)
               ,0, 'NO'
               ,'YES')
        ,ou.start_monitoring
        ,ou.end_monitoring
  from   sys.obj$ io
        ,sys.obj$ t
        ,sys.ind$ i
        ,sys.object_usage ou
  where  io.owner# = userenv('SCHEMAID')
  and    i.obj# = ou.obj#
  and    io.obj# = ou.obj#
  and    t.obj# = i.bo#;

And there it is…you can only see rows for the schema you are currently in….Grrrr.

So from now on, I’m gonna to always create a new view

create or replace view sys.an_object_usage_definition_that_works as
  select io.name index_name
        ,t.name table_name
        ,decode(bitand(i.flags
                      ,65536)
               ,0, 'NO'
               ,'YES') monitoring
        ,decode(bitand(ou.flags
                      ,1)
               ,0, 'NO'
               ,'YES') used
        ,ou.start_monitoring
        ,ou.end_monitoring
  from   sys.obj$ io
        ,sys.obj$ t
        ,sys.ind$ i
        ,sys.object_usage ou
  where  i.obj# = ou.obj#
  and    io.obj# = ou.obj#
  and    t.obj# = i.bo#;

but I have to wait until we can have 37 character long object names :-)

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..

Older Posts »

The WordPress Classic Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 85 other followers