Oracle Tips and Tricks — David Fitzjarrell

November 1, 2019

Out Of Order

Filed under: General — dfitzjarrell @ 15:01

"If you want sense, you'll have to make it yourself."
- Norton Juster, The Phantom Tollbooth

An interesting issue reared its head back in version 9.2 that involves inserting data into multiple tables with a single statement when a foreign key is declared. Regardless of the table order in the insert statement the following error can be thrown:


ORA-02291: integrity constraint (SCOTT.FK1) violated - parent key not found 

The error is generated because the order of insert is not determinate, meaning that regardless of the order of the tables in the code it’s not guaranteed that the inserts will occur in the ‘correct’ order. To add fuel to the confusion fire the error isn’t always thrown as it can depend on the database block size and the number of rows inserted. Let’s look at an example that proves this.

A simple single-column table, named MAIN, is created and it contains a primary key:


SQL> --
SQL> -- Create the main table
SQL> --
SQL> create table main
  2  (my_id number,
  3  constraint main_pk  primary key (my_id));

Table created.

SQL> 

Another table, named DEPEND, is created with a foreign key constraint referencing MAIN:


SQL> --
SQL> -- Dependent table
SQL> --
SQL> create table depend
  2  (my_id number,
  3   vchar_a varchar2(4000),
  4   vchar_b varchar2(3920));

Table created.

SQL> 
SQL> --
SQL> -- Add the foreign key constraint
SQL> --
SQL> alter table depend add constraint fk1 foreign key (my_id) references main (my_id);

Table altered.

SQL> 

Using the multi-table insert syntax an attempt is made to insert data into both tables, with care being taken to list the MAIN table first in the statement. Unfortunately, due to Bug 2891576, this doesn’t happen:


SQL> --
SQL> -- Insert data into both tables with a
SQL> -- single statement
SQL> --
SQL> -- This could fail due to unresolved
SQL> -- bug 2891576
SQL> --
SQL> -- Since the order of the inserts is not
SQL> -- determinate (the first table in the
SQL> -- insert may not be the first table
SQL> -- processed by the insert) the child
SQL> -- table can be inserted into first, resulting
SQL> -- in an ORA-02291 being thrown
SQL> --
SQL> insert all
  2    into main ( my_id ) values ( my_id )
  3    into depend  ( my_id ) values ( my_id )
  4  select 666 my_id from dual;
insert all
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK1) violated - parent key not found 


SQL> 

Oracle is attempting to insert data into DEPEND first and since it has the foreign key constraint the insert fails due to a missing parent. One way around this is to create a basically useless trigger; it’s ‘basically’ useless as it does absolutely nothing TO the data. What it does, apparently, is shift Oracle into row-level processing mode which seems to force ‘proper’ table ordering for the insert:


SQL> --
SQL> -- Create a useless trigger that does absolutely
SQL> -- nothing but fire
SQL> --
SQL> -- This can force Oracle to switch to row-level processing
SQL> -- and preserve the order of inserts
SQL> --
SQL> create or replace trigger pre_tbl_ins_trg
  2  before insert on main
  3  for each row
  4  begin
  5  	     --
  6  	     -- Do absolutely nothing
  7  	     --
  8    null;
  9  end;
 10  /

Trigger created.

SQL> 
SQL> --
SQL> -- This time the insert should succeed
SQL> --
SQL> insert all
  2    into main ( my_id ) values ( my_id )
  3    into depend  ( my_id ) values ( my_id )
  4  select  100 my_id from dual;

2 rows created.

SQL> 

As if by magic the inserts now are successful. A different example, that may be more ‘real world’, follows. The script is adapted from work by Jonathan Lewis and shows how ‘random’ the error can appear. The example is run on a database with an 8K block size; it’s possible that a database with a larger block size (16K or 32k) could increase the number of rows inserted before throwing the error. Three tables are created for this example, not counting the source table that is initially created. [TAB_1 is used to populate the remaining tables using a multi-table insert … select statement.] As the test tables are not pupulated segment creation is declared as immediate; this ensures that the empty tables are physically created in the database. The insert is run three times, with varying numbers of rows to insert. We begin:


SQL> --
SQL> -- Modified script originally written by
SQL> -- Jonathan Lewis
SQL> --
SQL> --
SQL> -- The failure can be random, as it depends upon
SQL> -- the order of the table inserts as Oracle generates
SQL> -- them
SQL> --
SQL> 
SQL> --
SQL> -- Create table of source data
SQL> --
SQL> -- As it's a CTAS the segment will
SQL> -- be immediately created regardless
SQL> -- of the parameter setting
SQL> --
SQL> create table tab_1
  2  as
  3  with data_source as (
  4    select rownum my_id
  5    from dual
  6    connect by level <= 1e4
  7  )
  8  select rownum			    my_id,
  9   lpad(rownum,10,'0')	      vchar,
 10   lpad(rownum,100,'0')	      vchar_med,
 11   lpad(rownum,200,'0')	      vchar_lrg
 12  from data_source	    v1;

Table created.

SQL> 
SQL> --
SQL> -- Create the parent table of the group
SQL> --
SQL> -- Since it is not being populated when created
SQL> -- the segment creation is declared as immediate
SQL> --
SQL> -- Should the user have no quota on the default
SQL> -- tablespace this create statement will fail
SQL> -- immediately
SQL> --
SQL> create table prim_tab(
  2    my_id		  number,
  3    vchar		  varchar2(10),
  4    vchar_med	  varchar2(100),
  5    vchar_lrg	  varchar2(200),
  6    constraint ptab_pk primary key(my_id)
  7  )
  8  segment creation immediate;

Table created.

SQL> 
SQL> --
SQL> -- Create the dependent tables
SQL> --
SQL> -- Again the segment creation is declared
SQL> -- as immediate
SQL> --
SQL> create table dep_tab(
  2    my_id		  number,
  3    vchar		  varchar2(10),
  4    vchar_med	  varchar2(100),
  5    vchar_lrg	  varchar2(200),
  6    constraint chld_pk primary key(my_id),
  7    constraint chld_fk_par foreign key (my_id) references prim_tab(my_id)
  8  )
  9  segment creation immediate;

Table created.

SQL> 
SQL> create table dep_tab2(
  2    my_id		  number,
  3    vchar		  varchar2(10),
  4    vchar_med	  varchar2(100),
  5    vchar_lrg	  varchar2(200),
  6    constraint ch2_pk primary key(my_id),
  7    constraint ch2_fk_par foreign key (my_id) references prim_tab(my_id)
  8  )
  9  segment creation immediate;

Table created.

SQL> 
SQL> --
SQL> -- Here we go
SQL> --
SQL> -- This multi-table insert will succeed or fail
SQL> -- depending upon the number of rows that will
SQL> -- be inserted
SQL> --
SQL> insert all
  2    into prim_tab(my_id, vchar)  values(my_id, vchar)
  3    into dep_tab (my_id, vchar_med) values(my_id, vchar_med)
  4    into dep_tab2(my_id, vchar_med) values(my_id, vchar_med)
  5  select my_id, vchar, vchar_med, vchar_lrg
  6  from tab_1
  7  where rownum <= &num_rows;
Enter value for num_rows: 55
old   7: where rownum <= &num_rows
new   7: where rownum <= 55

165 rows created.

SQL> 

For an 8K block a bulk insert of 55 rows behaves as expected for all three tables. Let’s try again, this time with a 77-row bulk insert:


SQL> 
SQL> --
SQL> -- Here we go
SQL> --
SQL> -- This multi-table insert will succeed or fail
SQL> -- depending upon the number of rows that will
SQL> -- be inserted
SQL> --
SQL> insert all
  2    into prim_tab(my_id, vchar)  values(my_id, vchar)
  3    into dep_tab (my_id, vchar_med) values(my_id, vchar_med)
  4    into dep_tab2(my_id, vchar_med) values(my_id, vchar_med)
  5  select my_id, vchar, vchar_med, vchar_lrg
  6  from tab_1
  7  where rownum <= &num_rows;
Enter value for num_rows: 77
old   7: where rownum <= &num_rows
new   7: where rownum <= 77
insert all
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.CHLD_FK_PAR) violated - parent key not 
found 

For the block size in use for this database (8K) the bulk limit is 75 rows; any larger batch will throw the error shown above.

Oracle has two bugs listed for this same issue, 2891576 and 16133798. Bug 16133798 is listed as “Closed: not a bug”, yet bug 2891576 is not listed as resolved nor is there a version listed where this problem is fixed.

The issue still exists in version 19.2 so it’s likely that it could appear unexpectedly in any release from 9.2 onwards. As illustrated two possible work-arounds are available – create a ‘do-nothing’ trigger to force Oracle into row-level processing or restrict insert batch sizes to 75 rows or fewer (again, this could be a higher number in databases with block sizes larger than 8K). In either case knowing the issue exists may help developers write code that avoids using a multi-table insert.

Which certainly makes sense.

September 14, 2019

Unexpected Behavior

Filed under: General — dfitzjarrell @ 10:06

"Expect everything, I always say, and the unexpected never happens."
― Norton Juster, The Phantom Tollbooth

DBMS_LOGMNR is a great tool for finding information about what’s going on in an Oracle database. If it’s written to the redo logs DBMS_LOGMNR can find it. But sometimes it can find too much information, especially when assumptions are made regarding table ownership. Application code is often tested with ‘pristine’ database configurations, where ‘one table, one owner’ is the rule. Unfortunately that isn’t always the case. Let’s look at an example of how this can produce unwanted and unexpected results.

A database is configured where eight different users all have the same table, albeit with different data. Let’s use a new application that uses DBMS_LOGMNR to retrieve data changes for a given table. If the application was written to expect the ‘one owner, one table’ philosophy it could return a larger volume of data than the developers expected. In our database the APPLE table occurs eight times, once for each different owner. Let’s ‘fire up’ LogMiner and run an application query that could result from the single-owner theory and examine the results:


SQL> 
SQL> --
SQL> -- Let's start LogMiner
SQL> --
SQL> exec dbms_logmnr.start_logmnr(options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- We'll look for inserts and updates
SQL> -- to the apple table
SQL> --
SQL> -- We'll assume there's only one apple
SQL> -- table in the database owned by
SQL> -- BRAEBURN
SQL> --
SQL> -- The first query is, well, wrong
SQL> --
SQL> SELECT SEG_OWNER, OPERATION, SQL_REDO, SCN, CSCN
  2  from v$logmnr_contents
  3  where table_name = 'APPLE'
  4  AND operation in ('INSERT', 'UPDATE', 'DELETE')
  5  /
RED_DELICIOUS        INSERT         insert into "RED_DELICIOUS"."APPLE"("ID","DESCR") values ('1','Apple 1');             4590598788237 4590598788240
...
RED_DELICIOUS        INSERT         insert into "RED_DELICIOUS"."APPLE"("ID","DESCR") values ('1000','Apple 1000');       4590598788239 4590598788240
GRANNY_SMITH         INSERT         insert into "GRANNY_SMITH"."APPLE"("ID","DESCR") values ('1','Apple 1');              4590598788257 4590598788260
...
GRANNY_SMITH         INSERT         insert into "GRANNY_SMITH"."APPLE"("ID","DESCR") values ('1000','Apple 1000');        4590598788259 4590598788260
PINK_LADY            INSERT         insert into "PINK_LADY"."APPLE"("ID","DESCR") values ('1','Apple 1');                 4590598788278 4590598788281
...
PINK_LADY            INSERT         insert into "PINK_LADY"."APPLE"("ID","DESCR") values ('1000','Apple 1000');           4590598788280 4590598788281
BRAEBURN             INSERT         insert into "BRAEBURN"."APPLE"("ID","DESCR") values ('1','Apple 1');                  4590598788300 4590598788303
...
BRAEBURN             INSERT         insert into "BRAEBURN"."APPLE"("ID","DESCR") values ('1000','Apple 1000');            4590598788302 4590598788303
GOLDEN_DELICIOUS     INSERT         insert into "GOLDEN_DELICIOUS"."APPLE"("ID","DESCR") values ('1','Apple 1');          4590598788320 4590598788323
...
GOLDEN_DELICIOUS     INSERT         insert into "GOLDEN_DELICIOUS"."APPLE"("ID","DESCR") values ('1000','Apple 1000');    4590598788322 4590598788323
GALA                 INSERT         insert into "GALA"."APPLE"("ID","DESCR") values ('1','Apple 1');                      4590598788343 4590598788347
...
GALA                 INSERT         insert into "GALA"."APPLE"("ID","DESCR") values ('1000','Apple 1000');                4590598788346 4590598788347
ROME                 INSERT         insert into "ROME"."APPLE"("ID","DESCR") values ('1','Apple 1');                      4590598788365 4590598788368
...
ROME                 INSERT         insert into "ROME"."APPLE"("ID","DESCR") values ('1000','Apple 1000');                4590598788367 4590598788368
HONEYCRISP           INSERT         insert into "HONEYCRISP"."APPLE"("ID","DESCR") values ('1','Apple 1');                4590598788385 4590598788388
...
HONEYCRISP           INSERT         insert into "HONEYCRISP"."APPLE"("ID","DESCR") values ('1000','Apple 1000');          4590598788387 4590598788388

8000 rows selected.

SQL> 

The application wasn’t expecting 8000 rows of data since only 1000 were inserted into the APPLE table owned by BRAEBURN. The issue with the query is that the segment owner was not included in the WHERE clause; had there only been one APPLE table then the results would have been correct, that is until another user creates an APPLE table. When that happens LogMiner won’t know to search only for the BRAEBURN copy and, as can be seen by the results above, will return all inserts, updates and deletes for every table named APPLE in the database. If the application is using those results to process data for the BRAEBURN schema there will be a very large number of errors for ‘bad data’.

Let’s try this again, this time restricting the query to only the BRAEBURN schema:


SQL> 
SQL> --
SQL> -- There's MUCH more data here than
SQL> -- we expected
SQL> --
SQL> -- Let's try again and tell LogMiner
SQL> -- whose table to look for
SQL> --
SQL> 
SQL> SELECT SEG_OWNER, OPERATION, SQL_REDO, SCN, CSCN
  2  from v$logmnr_contents
  3  where table_name = 'APPLE'
  4  and seg_owner = 'BRAEBURN'
  5  AND operation in ('INSERT', 'UPDATE', 'DELETE')
  6  /
BRAEBURN             INSERT         insert into "BRAEBURN"."APPLE"("ID","DESCR") values ('1','Apple 1');                  4590598788300 4590598788303
...
BRAEBURN             INSERT         insert into "BRAEBURN"."APPLE"("ID","DESCR") values ('1000','Apple 1000');            4590598788302 4590598788303

1000 rows selected.

SQL> 

Now the query returns the expected results, and any processing done with that data will (hopefully) not encounter errors.

Assumptions can create unforseen problems in applications, especially when those applications touch a database. As mentioned previously not every database is built on the ‘one owner, one table’ mantra and it’s a very good idea to test code where that condition does not exist. Doing so can prevent any number of confusing mishaps.

If you expect something, make sure you get what you expected.

August 29, 2019

It’s A Date?

Filed under: General — dfitzjarrell @ 16:09

"You often learn more by being wrong for the right reasons
than right for the wrong reasons." 
― Norton Juster, The Phantom Tollbooth

Dates. The term can conjur fear in the strongest hearts, mainly due to problems that arise when date formats are taken for granted. Not every Oracle database is configured with the ‘default’ Oracle-supplied date format, DD-MON-RR. Since this setting can be changed at both the database level and the session level it can be rather confusing when scripts aren’t working properly. Let’s see what can go horribly wrong when date formats are taken for granted or ignored.

Implicit conversion is the most insidious in my opinion as it relies on the declared format for the database or the session, and that is defined by the NLS_DATE_FORMAT parameter. Date strings come in all shapes and sizes but for an Oracle database the format it’s expecting may not be the format the string is in. Given this NLS_DATE_FORMAT setting:.


DD-MON-RRRR

any attempt to implicitly convert ‘1999-01-31’ to an Oracle date will fail:


SQL> select * From dba_Objects where created = '1999-01-31';
select * From dba_Objects where created = '1999-01-31'
                                          *
ERROR at line 1:
ORA-01861: literal does not match format string


SQL>

If the string is put into a more … ‘normal’ … format a different error appears:


SQL$gt; select * From dba_Objects where created = '01-01-1999'
  2  /
select * From dba_Objects where created = '01-01-1999'
                                          *
ERROR at line 1:
ORA-01843: not a valid month


SQL>

To avoid such errors best practice is to always use TO_DATE() to convert strings into date values and always supply a matching format string. It is not a good practice to rely on session or database settings even if you KNOW the formats are the same. Someone else that might execute that script may not have set the same date format used in your session, and mystically the script fails to execute with one of the above errors. As another example let’s set the NLS_DATE_FORMAT in a session to a known string and perform some conversions. The session date format will be changed twice; for some conversions either format provides error-free results but when the month and day are reversed some conversions will complete but the month portion of the date could be a surprise::


SQL> --
SQL> -- Set the session date format
SQL> -- the same as the string we are trying
SQL> -- to convert
SQL> --
SQL> -- Notice the month is first in the format
SQL> -- string
SQL> --
SQL> alter session set nls_date_format = 'MM-DD-RRRR';

Session altered.

SQL>
SQL> --
SQL> -- Try implicit conversion again
SQL> --
SQL> -- Return the month name as well
SQL> --
SQL> -- This works but it's 'iffy'
SQL> --
SQL> -- Relying on the default format is
SQL> -- not a wise idea
SQL> --
SQL> select '01-01-1999' from dual
  2  where sysdate >= '01-01-1999';

'01-01-199
----------
01-01-1999

SQL>
SQL> select to_char(to_date('01-01-1999'),'Month') from dual;

TO_CHAR(T
---------
January

SQL>
SQL> --
SQL> -- Another attempt using TO_DATE()
SQL> -- without a format string
SQL> --
SQL> -- Again, this works but it's 'iffy'
SQL> --
SQL> select to_date('01-01-1999') from dual;

TO_DATE('0
----------
01-01-1999

SQL>
SQL> select to_char(to_date('01-01-1999'),'Month') from dual;

TO_CHAR(T
---------
January

SQL>
SQL> --
SQL> -- Use a properly constructed TO_DATE()
SQL> -- statement
SQL> --
SQL> -- This works without exception
SQL> --
SQL> select to_date('01-31-1999', 'MM-DD-RRRR') from dual;

TO_DATE('0
----------
01-31-1999

SQL>
SQL> --
SQL> -- Change the session format yet again
SQL> --
SQL> -- This time the month is not first
SQL> -- so conversions where the 'day' is 12
SQL> -- or less will still work with this format
SQL> --
SQL> alter session set nls_date_format = 'DD-MM-RRRR';

Session altered.

SQL>
SQL> --
SQL> -- Change the date string to convert
SQL> --
SQL> -- Even with the month and day reversed the
SQL> -- following conversions, implicit and unformatted,
SQL> -- work because there is a month number 1 in
SQL> -- the year
SQL> --
SQL> -- This won't throw any errors until the day
SQL> -- is greater than 12
SQL> --
SQL> -- In this case the month is what we expect
SQL> --
SQL> select '01-01-1999' from dual
  2  where sysdate >= '01-01-1999';

'01-01-199
----------
01-01-1999

SQL>
SQL> select to_date('01-01-1999') from dual;

TO_DATE('0
----------
01-01-1999

SQL>
SQL> select to_char(to_date('01-01-1999'),'Month') from dual;

TO_CHAR(T
---------
January

SQL>
SQL> --
SQL> -- Let's make the 'day' value 11
SQL> -- and see what date results
SQL> --
SQL> -- We won't get the month we
SQL> -- expect
SQL> --
SQL> select '01-11-1999' from dual
  2  where sysdate >= '01-11-1999';

'01-11-199
----------
01-11-1999

SQL>
SQL> select to_char(to_date('01-11-1999'),'Month') from dual;

TO_CHAR(T
---------
November

SQL>
SQL> --
SQL> -- Now the implicit and unformatted
SQL> -- conversions fail
SQL> --
SQL> -- The default format switched the month
SQL> -- and day in the string
SQL> --
SQL> -- The Gregorian calendar doesn't have
SQL> -- 31 months in a year
SQL> --
SQL> select '01-31-1999' from dual
  2  where sysdate >= '01-31-1999';
where sysdate >= '01-31-1999'
                 *
ERROR at line 2:
ORA-01843: not a valid month


SQL>
SQL> select to_date('01-31-1999') from dual;
select to_date('01-31-1999') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month


SQL>
SQL> --
SQL> -- The formatted conversion still works
SQL> --
SQL> select to_date('01-31-1999', 'MM-DD-RRRR') from dual;

TO_DATE('0
----------
31-01-1999

SQL>

Not knowing the pre-set NLS_DATE_FORMAT can wreak havoc with implicit date conversions, and do the same when TO_DATE() is used without a format specifier. Depending upon the format string it may take a while for Oracle to throw an error and the reason may seem to be a mystery. Not many applications return the month name from a date value so when the format starts ‘DD-MM’ rather than ‘MM-DD’ errors won’t appear for date strings similar to ’01-01-2020′ until day 13 of the month, leaving developers to scratch their heads in disbelief and confusion.

Good coding practice is to never rely on default date formats; always code conversion functions with a date format specifier so that generated dates are never in doubt. And never rely on implicit date conversions as each database configuration is different. The goal is to write portable code that will run on any installation of Oracle the code could encounter (barring functional changes due to version differences). Being lazy with dates can result in broken code and applications that abort seemingly without reason. Don’t let this happen to you.

Be right for the RIGHT reasons.

 

June 20, 2019

“Just One More…”

Filed under: General — dfitzjarrell @ 13:54

"So each one of you agrees to disagree with whatever the other
one agrees with, but if you both disagree with the same thing,
aren't you really in agreement?" 
― Norton Juster, The Phantom Tollbooth

Partitions can make DBA life easier; they allow faster access to data through partition pruning and they allow for easier archiving and removal of old data. There’s not much that can go wrong with partitions [ominous silence]… until you want to create an interval-partitioned table with hash subpartitions and you want use that strategy by creating daily partitions with 1,024 subpartitions. Let’s look at why that plan won’t work, but first let’s look at some logical database limits Oracle imposes on partitions and subpartitions.

The following table lists the logical limits that affect such a strategy:

Partitions Maximum number of partitions allowed per table or index 1024K – 1
Subpartitions Maximum number of subpartitions in a composite partitioned table 1024K – 1

Notice the maximum number of partitions is 1048575. Since we are creating a composite partitioned table the maximum number of subpartitions is also 1048575, which means when you compute the total subpartitions for the entire table it cannot be greater than 1048575. For this example each partition will have 1024 subpartitions, which limits the number of partitions we can create to 1023, since we cannot have a partial partition. Yes, 1023 partitions is a very large number of partitions, and with 365 days in a year that allows for 2.8 years of data in the table, again quite a lot of daily data to be stored. Processes can run happily along until the table reaches that magic number and things start to fail. The example, below, has been modified from one provided by Jonathan Lewis; notice the various errors it throws when logical partition/subpartition limits are reached:


SQL> 
SQL> set verify off echo on
SQL> column table_name format a20
SQL> column partition_name format a20
SQL> column subpartition_name format a20
SQL> column high_value format a20
SQL> column part_end_dt new_value startdt
SQL> select to_char(trunc(sysdate),'dd-mon-yyyy') part_end_dt from dual;

PART_END_DT
--------------------
02-mar-2016

SQL> 
SQL> create table hash_by_interval (
  2          int_dt         date            not null,
  3          num_val        number(10,0)    not null,
  4          strng_dat      varchar2(100)
  5  )
  6  tablespace assm_int_part_test
  7  partition by range (int_dt) interval (numtodsinterval(1,'DAY'))
  8  subpartition by hash (num_val) subpartitions 1024
  9          (
 10                  partition ip0 values less than (to_date('&startdt','dd-mon-yyyy'))
 11          )
 12  ;

Table created.

SQL> 
SQL> insert into hash_by_interval values (trunc(sysdate) + 1023, 1, rpad('x',100));
insert into hash_by_interval values (trunc(sysdate) + 1023, 1, rpad('x',100))
            *
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions


SQL> insert into hash_by_interval values (trunc(sysdate) + 1022, 1, rpad('x',100));
insert into hash_by_interval values (trunc(sysdate) + 1022, 1, rpad('x',100))
*
ERROR at line 1:
ORA-14299: total number of partitions/subpartitions exceeds the maximum limit


SQL> insert into hash_by_interval values (trunc(sysdate) + 1021, 1, rpad('x',100));

1 row created.

SQL> commit;

Commit complete.

SQL> 

The first attempted insert in the example fails immediately, the second takes a while but it also fails. On the face of it the errors seem to be saying the same thing, but they’re not. Going back to the logical limits the maximum number of partitions is 1048575; the first insert error reports that the partitioning key would map to a partition outside the maximum partition limit, which would create a partition numbered 1048576, clearly a violation of the logical limits. The second insert error reports that it should go into partition number 1025, which is not a violation of the number of subpartitions for a single partition but it does violate the total subpartitions limit by creating a total of 1048576 subpartitions. They aren’t the same condition so they generate different errors. Both conditions result in the same problem: no data for the current day in the partitioned table, which may be confusing.

For the test table a few numbers:


SQL> 
SQL> select table_name, partition_name, subpartition_count
  2  from user_tab_partitions;

TABLE_NAME           PARTITION_NAME       SUBPARTITION_COUNT
-------------------- -------------------- ------------------
HASH_BY_INTERVAL     SYS_P45714                         1024
HASH_BY_INTERVAL     IP0                                1024

SQL> 
SQL> select table_name, partitioning_type, subpartitioning_type, partition_count, def_subpartition_count, subpartitioning_key_count
  2  from user_part_tables;

TABLE_NAME           PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT SUBPARTITIONING_KEY_COUNT
-------------------- --------- --------- --------------- ---------------------- -------------------------
HASH_BY_INTERVAL     RANGE     HASH              1048575                   1024                         1

SQL> 
SQL>

For interval partitioned tables Oracle sets the partition count to the maximum limit, 1048575. For hash and range partitioned tables the partition count is the actual number of partitions in the table. An interesting fact emerges with interval-partitioned tables: the first partition is always a RANGE partition, after which the interval partitioning takes over. This is why we see two partitions in the first set of query results shown above.

This example shows how important it is to have a partition archiving process in place for such tables; archiving older partitions by converting them into stand-alone tables provides the necessary ‘room’ to keep this strategy going by ensuring that the table will never reach, much less try to exceed, the logical limits on partition/subpartition counts enforced by Oracle.

Partitioning, when given careful thought and robust testing, can be extremely beneficial in both query performance and data management. Thorough testing is the key, especially at the extreme limits for the data. Devising tests that take Oracle to its logical limits may not be easy, but in cases like this they’re necessary to plan for, and, hopefully eliminate, issues that can stop production in its tracks.

I expect that we are in agreement.

June 4, 2019

“Hear ye, hear ye!”

Filed under: General — dfitzjarrell @ 18:21

"Have you ever heard the wonderful silence just before the dawn? Or the quiet
and calm just as a storm ends? Or perhaps you know the silence when you haven't
the answer to a question you've been asked, or the hush of a country road at
night, or the expectant pause of a room full of people when someone is just
about to speak, or, most beautiful of all, the moment after the door closes
and you're alone in the whole house? Each one is different, you know, and all
very beautiful if you listen carefully."
-- Norton Juster, The Phantom Tollbooth

In versions of Oracle prior to 12.2 the Listener has been more forgiving with static registrations; with 12.2 and later releases that seems to have changed as static registrations, if not configured completely, can cause the listener to dump a core file in the directory where the listener process was started. What can make this even more confusing is that dynamic registrations are not affected. And to add to all of this interesting behavior having databases down when starting the listener doesn’t ‘fix’ the problem because the listener isn’t connecting to a running database, it’s trying to resolve library paths for static declarations. Let’s look at how static registrations can be written to avoid this (and allow one to keep his or her hair and sanity in the process).

Statis registrations for TCP connections are the ‘problem’ with the 12.2 listener (from personal experience) when they stop short of configuring the library path for the given database and home. A typical static TCP registration can look like this:


SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = smerma)
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = alpoz)
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = plorg)
    )
)

Even up to release 12.1.0.2 the listener would appear to ‘pick up’ the necessary environment settings and find the associated libraries in the given ORACLE_HOME; in 12.2.0.1 this seems to have changed which causes the core file to be generated for Oracle databases not using the same home as the running listener. This can be a problem if your site has a dedicated home for the listener to run from since no databases will be running from that home. And, not knowing that static registrations are at fault the core file geenration will be a mystery.

The solution to this is fairly simple and has been available in listener configurations for many releases; of course having a dedicated listener home is not a standard configuration and, as such, this issue may never rear its ugly head. Modifying the configuration shown above to provide the ‘missing’ information the listener needs is merely a matter of adding the ENVS configuration parameter for each static database registration, as shown below:


SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = smerma)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/lib')
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = alpoz)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/lib')
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = plorg)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/lib')
    )
)

It is a simple change that may be unfamiliar to some DBAs who have never configured a listener outside of NETCA, and if static registrations are not required at a site the DBA may not have had the opportunity to even see a listener.ora [the listener will run just fine without a listener.ora file, but it will use the default port of 1521]. Of course simply changing the listener.ora doesn’t fix anything in a currently running listener as it will need to be reloaded to make the changes effective:


$ lsnrctl reload listener

LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 07-JAN-2018 14:39:24

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.mydomain.com)(PORT=1522)))
The command completed successfully
$

If your listener is named LISTENER (the default naming provided by NETCA) then a simple:


lsnrctl reload

is all that’s necessary. It is good practice, however, to supply the listener name every time one performs maintenance on a listener so the correct listener is affected. Not doing so may result in no actual reload taking place as the current lsnrctl executable won’t ‘know’ which LISTENER to reload on systems where multiple Oracle installations exist. In situations where LISTENER is not the name of the running listener (creativity may run amok and it may be named LISTENER_BOB, LISTENER_TURNIP or even LISTENER_POODLE) there won’t be any listener to act upon.

Every static registration can have its own ENVS setting; in installations involving multiple Oracle versions all using a single listener it’s perfectly legal to have each ENVS entry specify a different lib location:


SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle)
      (SID_NAME = smerma)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/lib')
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle/12)
      (SID_NAME = alpoz)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/12/lib')
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/oracle/10)
      (SID_NAME = plorg)
      (ENVS='LD_LIBRARY_PATH=/u01/oracle/10/lib')
    )
)

[This may not be obvious to DBAs not familiar with static configurations created by writing or editing a listener.ora file. If standby databases are not in use or if database backups are executed locally with RMAN then static registrations may not be necessary.]

For any DBA not familiar with static database registrations they exist so that the database can be succesfully connected to even when the database is not running. Static registrations are indicated by UNKNOWN when the listener status is generated:


[oracle@e3c57ee3dc64 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 04-JUN-2019 18:05:55

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=e3c57ee3dc64)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                04-JUN-2019 14:08:31
Uptime                    0 days 9 hr. 57 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/e3c57ee3dc64/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=e3c57ee3dc64)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=e3c57ee3dc64)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl" has 2 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@e3c57ee3dc64 ~]$

The UNKNOWN status is not an error; it simply indicates that the connection is manually configured and the listener cannot possibly know if the database is up or down, nor does it care. Having such a configuration makes it possible to run remote ‘cold’ backups and to run Data Guard databases by allowing remote startup and switchover of the database and standby.

Sometimes the most confusing issues can have the simplest of solutions; fortunately for listener static configurations this is the case as it only requires the addition of the ENVS parameter to provide the information the listener process was missing. This is, of course, covered in the Oracle on-line documentation (for several releases) which makes a good reason to have such documentation bookmarked in the browser.

You are listening?

May 11, 2019

Union Station

Filed under: General — dfitzjarrell @ 10:00

"Since you got here by not thinking, it seems reasonable to expect that,
in order to get out, you must start thinking."
-- Norton Juster, The Phantom Tollbooth

Combining two data sets can go in several directions and, in the absence of common columns, conventional wisdom would point to using UNION to generate such sets and for many applications this is a suitable solution. If, however, these unions are the ‘table’ for another query, one intending to produce distinct results, simply diving in and using UNION may not be the best direction to take.

The following example has been specifically written to highlight an issue when using UNION; it is similar to actual code I was asked to tune (although the actual code was not intentionally written to generate SORT UNIQUE operations as this was) and illustrates the issue that was slowing down execution. Let’s begin by creating three simple two-column tables:


BLEEPLE @ smang > create table tab1(
  2  snord   number,
  3  queebo  varchar2(40));

Table created.

BLEEPLE @ smang > 
BLEEPLE @ smang > create table tab2(
  2  plang   number,
  3  horbst  varchar2(40));

Table created.

BLEEPLE @ smang > 
BLEEPLE @ smang > create table tab3(
  2  umo     number,
  3  erst    varchar2(40));

Table created.

BLEEPLE @ smang > 

To speed up the resulting queries function-based indexes will be created and the tables will be populated. Finally statistics will be gathered on each table and index::


BLEEPLE @ smang > create index tab1_fbi on tab1(mod(snord,970));

Index created.

BLEEPLE @ smang > create index tab2_fbi on tab2(mod(plang,970));

Index created.

BLEEPLE @ smang > create index tab3_fbi on tab3(mod(umo,970));

Index created.

BLEEPLE @ smang > 
BLEEPLE @ smang > begin
  2  	     for i in 1..1000000 loop
  3  		     insert into tab1
  4  		     values(i, 'Eebeeneebee'||i);
  5  		     if mod(i,970) = 0 then
  6  			     insert into tab2
  7  			     values(i, 'Ooboonooboo'||i);
  8  			     insert into tab3
  9  			     values(i, 'Uubuunuubuu'||i);
 10  		     else
 11  			     insert into tab2
 12  			     values(i, 'Eebeeneebee'||i);
 13  			     insert into tab3
 14  			     values(i, 'Eebeeneebee'||i);
 15  		     end if;
 16  	     end loop;
 17  
 18  	     commit;
 19  end;
 20  /

PL/SQL procedure successfully completed.

BLEEPLE @ smang > 
BLEEPLE @ smang > exec dbms_stats.gather_table_stats(user, 'TAB1', cascade=>true);

PL/SQL procedure successfully completed.

BLEEPLE @ smang > exec dbms_stats.gather_table_stats(user, 'TAB2', cascade=>true);

PL/SQL procedure successfully completed.

BLEEPLE @ smang > exec dbms_stats.gather_table_stats(user, 'TAB3', cascade=>true);

PL/SQL procedure successfully completed.

BLEEPLE @ smang > 

So far, so good. Now, let’s generate a result set using UNION and see how Oracle processes that request:


BLEEPLE @ smang > set autotrace on
BLEEPLE @ smang > 
BLEEPLE @ smang > with u1 as(
  2  select snord, queebo From tab1
  3   where mod(snord,970) = 0
  4  union
  5  select plang, horbst From tab2
  6   where mod(plang,970) = 0
  7  ),
  8  u2 as (
  9  select plang, horbst From tab2
 10   where mod(plang,970) = 0
 11  union
 12  select umo, erst  from tab3
 13  where mod(umo,970) = 0
 14  )
 15  select distinct t.* from
 16  (select * from u1 union select * from u2) t;

     SNORD QUEEBO                                                                                                                           
---------- ----------------------------------------                                                                                         
       970 Eebeeneebee970                                                                                                                   
       970 Ooboonooboo970                                                                                                                   
       970 Uubuunuubuu970                                                                                                                   
      1940 Eebeeneebee1940                                                                                                                  
      1940 Ooboonooboo1940                                                                                                                  
      1940 Uubuunuubuu1940                                                                                                                  
      2910 Eebeeneebee2910                                                                                                                  
      2910 Ooboonooboo2910                                                                                                                  
      2910 Uubuunuubuu2910                                                                                                                  
      3880 Eebeeneebee3880                                                                                                                  
      3880 Ooboonooboo3880                                                                                                                  
...
    996190 Uubuunuubuu996190                                                                                                                
    997160 Eebeeneebee997160                                                                                                                
    997160 Ooboonooboo997160                                                                                                                
    997160 Uubuunuubuu997160                                                                                                                
    998130 Eebeeneebee998130                                                                                                                
    998130 Ooboonooboo998130                                                                                                                
    998130 Uubuunuubuu998130                                                                                                                
    999100 Eebeeneebee999100                                                                                                                
    999100 Ooboonooboo999100                                                                                                                
    999100 Uubuunuubuu999100                                                                                                                

3090 rows selected.


Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 2741225135                                                                                                                 
                                                                                                                                            
------------------------------------------------------------------------------------------------------                                      
| Id  | Operation                                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                                      
------------------------------------------------------------------------------------------------------                                      
|   0 | SELECT STATEMENT                          |          |  4109 |   140K|  4155   (1)| 00:00:01 |                                      
|   1 |  VIEW                                     |          |  4109 |   140K|  4155   (1)| 00:00:01 |                                      
|   2 |   SORT UNIQUE                             |          |  4109 |   140K|  4155   (1)| 00:00:01 |                                      
|   3 |    UNION-ALL                              |          |       |       |            |          |                                      
|   4 |     VIEW                                  |          |  2048 | 71680 |  2076   (1)| 00:00:01 |                                      
|   5 |      SORT UNIQUE                          |          |  2048 | 55296 |  2076   (1)| 00:00:01 |                                      
|   6 |       UNION-ALL                           |          |       |       |            |          |                                      
|   7 |        TABLE ACCESS BY INDEX ROWID BATCHED| TAB1     |  1018 | 27486 |  1037   (0)| 00:00:01 |                                      
|*  8 |         INDEX RANGE SCAN                  | TAB1_FBI |  1031 |       |     6   (0)| 00:00:01 |                                      
|   9 |        TABLE ACCESS BY INDEX ROWID BATCHED| TAB2     |  1030 | 27810 |  1037   (0)| 00:00:01 |                                      
|* 10 |         INDEX RANGE SCAN                  | TAB2_FBI |  1031 |       |     6   (0)| 00:00:01 |                                      
|  11 |     VIEW                                  |          |  2061 | 72135 |  2076   (1)| 00:00:01 |                                      
|  12 |      SORT UNIQUE                          |          |  2061 | 55647 |  2076   (1)| 00:00:01 |                                      
|  13 |       UNION-ALL                           |          |       |       |            |          |                                      
|  14 |        TABLE ACCESS BY INDEX ROWID BATCHED| TAB2     |  1030 | 27810 |  1037   (0)| 00:00:01 |                                      
|* 15 |         INDEX RANGE SCAN                  | TAB2_FBI |  1031 |       |     6   (0)| 00:00:01 |                                      
|  16 |        TABLE ACCESS BY INDEX ROWID BATCHED| TAB3     |  1031 | 27837 |  1037   (0)| 00:00:01 |                                      
|* 17 |         INDEX RANGE SCAN                  | TAB3_FBI |  1031 |       |     6   (0)| 00:00:01 |                                      
------------------------------------------------------------------------------------------------------                                      
                                                                                                                                            
Predicate Information (identified by operation id):                                                                                         
---------------------------------------------------                                                                                         
                                                                                                                                            
   8 - access(MOD("SNORD",970)=0)                                                                                                           
  10 - access(MOD("PLANG",970)=0)                                                                                                           
  15 - access(MOD("PLANG",970)=0)                                                                                                           
  17 - access(MOD("UMO",970)=0)                                                                                                             


Statistics
----------------------------------------------------------                                                                                  
          1  recursive calls                                                                                                                
          0  db block gets                                                                                                                  
       7230  consistent gets                                                                                                                
       3055  physical reads                                                                                                                 
     247332  redo size                                                                                                                      
     115568  bytes sent via SQL*Net to client                                                                                               
       2807  bytes received via SQL*Net from client                                                                                         
        207  SQL*Net roundtrips to/from client                                                                                              
          3  sorts (memory)                                                                                                                 
          0  sorts (disk)                                                                                                                   
       3090  rows processed                                                                                                                 

BLEEPLE @ smang > 

Notice lines 2, 5 and 12 of the execution plan — all three are SORT UNIQUE, one for each of the interim result sets and the last for the combined set of data. Only one SORT UNIQUE was expected when this code was written. Implicit sorts from the UNION operators added the other two to the plan. Let’s modify this code a bit and use UNION ALL in place of the UNION statements originally used:


BLEEPLE @ smang > 
BLEEPLE @ smang > with u1 as(
  2  select snord, queebo From tab1
  3   where mod(snord,970) = 0
  4  union all
  5  select plang, horbst From tab2
  6   where mod(plang,970) = 0
  7  ),
  8  u2 as (
  9  select plang, horbst From tab2
 10   where mod(plang,970) = 0
 11  union all
 12  select umo, erst  from tab3
 13  where mod(umo,970) = 0
 14  )
 15  select distinct t.* from
 16  (select * from u1 union all select * from u2) t;

     SNORD QUEEBO                                                                                                                           
---------- ----------------------------------------                                                                                         
      1940 Eebeeneebee1940                                                                                                                  
     11640 Eebeeneebee11640                                                                                                                 
     20370 Eebeeneebee20370                                                                                                                 
     23280 Eebeeneebee23280                                                                                                                 
     25220 Eebeeneebee25220                                                                                                                 
...
    978730 Uubuunuubuu978730                                                                                                                
    956420 Uubuunuubuu956420                                                                                                                
    982610 Uubuunuubuu982610                                                                                                                
    389940 Uubuunuubuu389940                                                                                                                
    303610 Uubuunuubuu303610                                                                                                                
    312340 Uubuunuubuu312340                                                                                                                
    301670 Uubuunuubuu301670                                                                                                                
    352110 Uubuunuubuu352110                                                                                                                
    349200 Uubuunuubuu349200                                                                                                                
    392850 Uubuunuubuu392850                                                                                                                

3090 rows selected.


Execution Plan
----------------------------------------------------------                                                                                  
Plan hash value: 294584558                                                                                                                  
                                                                                                                                            
-----------------------------------------------------------------------------------------------------                                       
| Id  | Operation                                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                                       
-----------------------------------------------------------------------------------------------------                                       
|   0 | SELECT STATEMENT                         |          |  4109 |   140K|  4150   (1)| 00:00:01 |                                       
|   1 |  HASH UNIQUE                             |          |  4109 |   140K|  4150   (1)| 00:00:01 |                                       
|   2 |   VIEW                                   |          |  4109 |   140K|  4149   (1)| 00:00:01 |                                       
|   3 |    UNION-ALL                             |          |       |       |            |          |                                       
|   4 |     VIEW                                 |          |  2048 | 71680 |  2074   (0)| 00:00:01 |                                       
|   5 |      UNION-ALL                           |          |       |       |            |          |                                       
|   6 |       TABLE ACCESS BY INDEX ROWID BATCHED| TAB1     |  1018 | 27486 |  1037   (0)| 00:00:01 |                                       
|*  7 |        INDEX RANGE SCAN                  | TAB1_FBI |  1031 |       |     6   (0)| 00:00:01 |                                       
|   8 |       TABLE ACCESS BY INDEX ROWID BATCHED| TAB2     |  1030 | 27810 |  1037   (0)| 00:00:01 |                                       
|*  9 |        INDEX RANGE SCAN                  | TAB2_FBI |  1031 |       |     6   (0)| 00:00:01 |                                       
|  10 |     VIEW                                 |          |  2061 | 72135 |  2074   (0)| 00:00:01 |                                       
|  11 |      UNION-ALL                           |          |       |       |            |          |                                       
|  12 |       TABLE ACCESS BY INDEX ROWID BATCHED| TAB2     |  1030 | 27810 |  1037   (0)| 00:00:01 |                                       
|* 13 |        INDEX RANGE SCAN                  | TAB2_FBI |  1031 |       |     6   (0)| 00:00:01 |                                       
|  14 |       TABLE ACCESS BY INDEX ROWID BATCHED| TAB3     |  1031 | 27837 |  1037   (0)| 00:00:01 |                                       
|* 15 |        INDEX RANGE SCAN                  | TAB3_FBI |  1031 |       |     6   (0)| 00:00:01 |                                       
-----------------------------------------------------------------------------------------------------                                       
                                                                                                                                            
Predicate Information (identified by operation id):                                                                                         
---------------------------------------------------                                                                                         
                                                                                                                                            
   7 - access(MOD("SNORD",970)=0)                                                                                                           
   9 - access(MOD("PLANG",970)=0)                                                                                                           
  13 - access(MOD("PLANG",970)=0)                                                                                                           
  15 - access(MOD("UMO",970)=0)                                                                                                             


Statistics
----------------------------------------------------------                                                                                  
          1  recursive calls                                                                                                                
          0  db block gets                                                                                                                  
       4140  consistent gets                                                                                                                
        213  physical reads                                                                                                                 
          0  redo size                                                                                                                      
     113955  bytes sent via SQL*Net to client                                                                                               
       2807  bytes received via SQL*Net from client                                                                                         
        207  SQL*Net roundtrips to/from client                                                                                              
          0  sorts (memory)                                                                                                                 
          0  sorts (disk)                                                                                                                   
       3090  rows processed                                                                                                                 

BLEEPLE @ smang > 

The three SORT UNIQUE operations have now been replaced with a single HASH UNIQUE operation. If the result sets are examined it will be noticed that the SORT UNIQUE results are ordered by the leading column of the table and the HASH UNIQUE results appear to be haphazard. Believe it or not the hash results are ordered; they are ordered by the hash key used to ‘weed out’ the distinct elements. In previous releases of Oracle (9 and earlier) the three SORT UNIQUE statements would be replaced by a single SORT UNIQUE from the ‘select distinct’ query. Nonetheless using UNION ALL does produce a less resource-intensive plan and returns the same data, even if not in the same physical order.

It may not be possible to give such problems much thought in this day and age because someone wants the results … NOW. But, the more you know about how Oracle behaves can make such work easier in the long run.

And that is worth thinking about.

May 7, 2019

Alphabet Soup

Filed under: General — dfitzjarrell @ 18:38

"Ali zar u buci nema i dobrih zvukova?" 
-- Norton Juster, The Phantom Tollbooth

Disaster recovery is on the mind of every Oracle DBA, the goal being that there is never a need to use it. Setting optimism aside, many enerprises run regular DR tests to ensure that business can continue as usual should a catastrophic event affect the data center. Before the advent of Oracle Managed Files a ‘proper’ DR onfiguration included generating scripts to create controfiles, to replace the standby controlfile at the DR site so the standby can be opened and used as a ‘regular’ database. When Oracle Managed Files are in use the situation changes. Let’s look at what OMF does and why a script to create the controlfile can’t originate from the primary database.

Oracle Managed Files, for those not familiar with them, are dynamically named based on the tablespace and use a generated value to uniquely identify them in the database. Each database installation will generate unique identifiers, and such identifiers won’t match between primary and standby databases. Thus a ‘create controlfile’ statement from the primary database won’t use the local file names generated from the standby and no controlfile will be created. Let’s look at an example using a primary databaase (prim_db) and it’s associated standby (stby_db).

PRIM_DB uses Oracle Managed Files to make it easier on the DBA to add data files. This is configured using the db_create_file_dest parameter:


SQL$gt; show parameter create_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /spatula
SQL>

So, for PRIM_DB all datafiles will be located at /spatula/PRIM_DB/datafile and will have uniquely generated file names:


...
o1_mf_sysaux_fsf2fb5y_.dbf
o1_mf_system_fsf2dkw2_.dbf
...

The standby database is also configured to use OMF; looking at the file names for SYSTEM and SYSAUX for that database we see:


...
o1_mf_sysaux_g02yt5l0_.dbf
o1_mf_system_g02yqhl6_.dbf
...

As mentioned previously the unique identifiers will not match between databases; a controlfile creation script generated on the primary will therefore be unusable on the standby. In this case the script generation procedures need to shift to the standby server so that a usable create controlfile script can be generated. It should not be necessary to copy the init.ora file from the primary to the standby as minimal changes will be required to run the new primary from the standby location. Those changes are:


db_unique_name
local_listener
log_archive_dest_1

Having a second init.ora file, named init_prim.ora with these changes, will make it easy to start the new primary so the controlfile can be created.

Having a current create controlfile script ready and waiting is simple and straightforward using the ‘alter database backup controfile to trace’ command; as an example such a command is shown below:


set echo on
spool /spatula/oracle/sql/stby_db_ctlfile.out
alter database backup controlfile to trace as '/home/oracle/recov_inits/stby_db_ccf.txt' reuse resetlogs;
spool off
exit

The ‘create controlfile’ script created will generate one CREATE CONTROLFILE statement rather than two, restricting the output to the REEETLOGS option and reusing the existing controlfile name. [Note that a ‘normal’ create controlfile script will generate two statements, a RESETLOGS option and a NORESETLOGS option, and in the case of a DR exercise the RESETLOGS option is the one that is used, thus the restriction.] Since this statement is generated from the standby the file names will be correctly specified and the new, regular controlfile will create successfully.

Using OMF also changes how the TEMP files are created; since file and path names are no longer required when creating such files the command is simplified:


SQL> alter tablespace temp add tempfile size [value];

The same command can be executed multiple times to create as many tempfiles as necessary.

Using OMF with Data Guard configurations changes how controlfiles are created but it isn’t a difficult change to implement. It also eliminates issues across network links as the files are created locally. Create a location to store them and set up a cron task to execute the script on a weekly basis (to catch any new datafiles created on the primary) and should the need arise to activate the standby the task should be a fairly simple one.

And the DBA won’t be swimming through alphabet soup to get there.

April 17, 2019

Threadbare

Filed under: General — dfitzjarrell @ 07:51

"Expect everything, I always say, and the unexpected never happens."
-- Norton Juster, The Phantom Tollbooth

Oracle has, since version 11.2, required standby redo logs to be configured for a Data Guard installation, even though in 11.2 the default log apply process relied on archived redo logs, not the standby logs. That changed in 12.1 and the default became what Oracle labelled “Real Time Apply”; the choice to use archived redo logs instead was, and is still available. In 12.2 and later releases another change has been made, this time with respect to creating those standby logs. Let’s look at how that aspect has progressed and what it means in a modern Data Guard configuration.

In days of old (11.2 and 12.1) creating standby logs was as easy as:


SQL> alter database add standby logfile ('/u04/redolog/sbylog1.rdo'.'/u05/redo/sbylog2.rdo') size 500M;

Database altered.

SQL>

and the standby logs would be created and use, by default, thread #1. If a different thread number was required:


SQL> alter database add standby logfile thread 3 ('/u04/redolog/sbylog1.rdo'.'/u05/redo/sbylog2.rdo') size 500M;

Database altered.

SQL>

Since Data Guard starts with thread #1 the default behavior was usually sufficient to create usable standby redo logs. However, in 12.2 that behavior has changed and now the default thread is 0, a thread that Data Guard cannot use. Thus, in 12.2 and later releases the thread must be specified, even if it’s thread 1:


SQL> alter database add standby logfile thread 1 ('/u04/redolog/sbylog1.rdo','/u05/redo/sbylog2.rdo') size 500M;

Database altered.

SQL>

Relying on previous default behavior will result in Data Guard not using the standby logfiles, and using the broker to configure and manage the Data Guard configuration will result in dgmgrl reporting the following warning:


 Database Warning(s): ORA-16789: standby redo logs configured incorrectly

and subsequent monitoring of those standby logs will show they are not being used because they are associated with thread 0:


DBID		  THREAD#  SEQUENCE#	   USED ARCHIVED  STATUS			 FIRST_CHANGE# FIRST_TIME      LAST_CHANGE# LAST_TIME
-------------- ---------- ---------- ---------- --------- ------------------------------ ------------- --------------- ------------ ---------------
UNASSIGNED		0	   0	      0 YES	  UNASSIGNED
UNASSIGNED		0	   0	      0 YES	  UNASSIGNED
UNASSIGNED		0	   0	      0 YES	  UNASSIGNED
UNASSIGNED		0	   0	      0 YES	  UNASSIGNED

Of course managed recovery can be started using archived redo logs in 12.2:


SQL> alter database recover managed standby database using archived logfile disconnect from session;

Database altered.

SQL>

Unless there is a need for a delay in log application (to provide a ‘cushion’ against errant updates to the standby) it’s best to use the standby logfiles. For smaller databases this issue is fairly easy to correct by dropping the original standby logs and creating new ones, specifying the thread. The procedure to do this is as follows:


--
-- Query v$standby_log to find the unused logs
--

SQL> select group#, thread#, status, bytes/1024/1024 from v$standby_log ;

    GROUP#    THREAD# STATUS	 BYTES/1024/1024
---------- ---------- ---------- ---------------
	 4	    0 UNASSIGNED	     200
	 5	    0 UNASSIGNED	     200
	 6	    0 UNASSIGNED	     200
	 7	    0 UNASSIGNED	     200


--
-- Stop standby recovery
--

SQL> alter database recover managed standby database cancel;

Database altered.

--
-- Create new standby logs specifying the correct thread
--
SQL> alter database add standby logfile thread 1 '/u04/redolog/sbylog1.rdo' size 500M;

Database altered.

SQL> alter database add standby logfile thread 1 '/u04/redolog/sbylog2.rdo' size 500M;

Database altered.

SQL> alter database add standby logfile thread 1 '/u04/redolog/sbylog3.rdo' size 500M;

Database altered.

SQL> alter database add standby logfile thread 1 '/u04/redolog/sbylog4.rdo' size 500M;

Database altered.

--
-- Drop the old standby logfiles
-- In this installation those are groups
-- 4, 5, 6, and 7
--
SQL> alter database drop standby logfile group 4;

Database altered.

SQL> alter database drop standby logfile group 5;

Database altered.

SQL> alter database drop standby logfile group 6;

Database altered.

SQL> alter database drop standby logfile group 7;

Database altered.

--
-- Start managed recovery 
--

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Since none of the standby redo logs were in use it was a simple task to drop them without a log switch. Once managed recovery has been started the database should catch up and one of the standby logs should show as being assigned. It’s a simple task to check the progress of the standby logs:


SQL> select dbid, thread#, sequence#, used, archived, status, first_change#, first_time, last_change#, last_time
  2  from v$standby_log;

DBID		  THREAD#  SEQUENCE#	   USED ARC STATUS     FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME
-------------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ ---------
3503443853		1	 446	1934848 YES ACTIVE	  4.5893E+12 15-APR-19	 4.5893E+12 15-APR-19
UNASSIGNED		1	   0	      0 NO  UNASSIGNED
UNASSIGNED		1	   0	      0 YES UNASSIGNED
UNASSIGNED		1	   0	      0 YES UNASSIGNED

And dgmgrl should report no warnings:


DGMGRL> show configuration

Configuration - my_splord_config

  Protection Mode: MaxPerformance
  Members:
  splord          - Primary database
    splord_x86_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 28 seconds ago)

DGMGRL>

Default behavior can change between database releases so it’s a good idea to check the documentation before making any assumptions based on experience with prior database releases; surprises can lurk around any corner.

Especially when they are unexpected.

April 9, 2019

Making A List

Filed under: General — dfitzjarrell @ 15:18

"Expect everything, I always say, and the unexpected never happens."
-- Norton Juster, The Phantom Tollbooth

In-lists can be a convenient way to search for multiple values in a single select statement. They can be hard-coded or can operate on the results of a subquery, and in the first case the reported limitation is 1000 elements, and that is correct for single-valued elements. If the elements are multi-valued that limit changes, which may be a surprise to many. Let’s take that for a spin by creating a test table and populating it:


BLEEBO @ splang > 
BLEEBO @ splang > --
BLEEBO @ splang > -- Create a table for testing
BLEEBO @ splang > --
BLEEBO @ splang > 
BLEEBO @ splang > create table inlist_test(
  2  inval1  number,
  3  inval2  number,
  4  val2return      varchar2(40));

Table created.

BLEEBO @ splang > 
BLEEBO @ splang > --
BLEEBO @ splang > -- Populate the table
BLEEBO @ splang > --
BLEEBO @ splang > 
BLEEBO @ splang > begin
  2  	     for i in 1..3000 loop
  3  		     insert into inlist_test(inval1, inval2, val2return)
  4  		     values(i, i, 'Value '||i);
  5  	     end loop;
  6  
  7  	     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

A query with a hard-coded in-list of 1001 elements fails to execute, throwing the expected error:


BLEEBO @ splang > 
BLEEBO @ splang > --
BLEEBO @ splang > -- IN-LIST with 1001 single elements
BLEEBO @ splang > --
BLEEBO @ splang > 
BLEEBO @ splang > select val2return
  2  from inlist_test
  3  where inval1 in (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
where inval1 in (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 *
ERROR at line 3:
ORA-01795: maximum number of expressions in a list is 1000

Oracle found the extra element and threw the expected error. So far the listed limitation is found to be correcti for single-valued, hard-coded elements. What if, for instance, we use multi-valued elements — does that behavior change? It certainly does. Let’s prove that by using element pairs and make the in-list 2500 members long:


BLEEBO @ splang > 
BLEEBO @ splang > --
BLEEBO @ splang > -- Now try an IN-LIST of pairs
BLEEBO @ splang > --
BLEEBO @ splang > 
BLEEBO @ splang > select val2return
  2  from inlist_test
  3  where (inval1,inval2) in ((1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),
  4  (1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),
  5  (1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),
  6  (1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),
  7  (1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),
  8  (1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),
  9  (1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),
 10  (1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),
 11  (1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(9,9),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1),(1,1));

VAL2RETURN
----------------------------------------
Value 1
Value 2
Value 3
Value 4
Value 5
Value 6
Value 7
Value 8
Value 9

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3899671621

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     9 |   432 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| INLIST_TEST |     9 |   432 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("INVAL1"=1 AND "INVAL2"=1 OR "INVAL1"=2 AND "INVAL2"=2 OR
              "INVAL1"=3 AND "INVAL2"=3 OR "INVAL1"=4 AND "INVAL2"=4 OR "INVAL1"=5 AND
              "INVAL2"=5 OR "INVAL1"=6 AND "INVAL2"=6 OR "INVAL1"=7 AND "INVAL2"=7 OR
              "INVAL1"=8 AND "INVAL2"=8 OR "INVAL1"=9 AND "INVAL2"=9)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         11  recursive calls
          0  db block gets
         43  consistent gets
          0  physical reads
          0  redo size
        699  bytes sent via SQL*Net to client
       6338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

The expected limitation no longer applies and this list of 2500 elements throws no errors and returns results.

Some might be tempted to cry ‘BUG!!!’ when seeing this given the documented physical limit on in-list length, and a 10053 event trace (which provides the optimizer decision ‘tree’ used to create the execution plan) returns information for all but the first query in the example, which errored at the parse phase, as a portion of a 10046 event trace (execution, wait and bind information) proves:


PARSE ERROR #140283620983280:len=2054 dep=0 uid=102 oct=3 lid=102 tim=26169356395 err=1795

Apparently when in-list elements are composed of pairs, triples, etc. the code that rejects lists longer than 1000 elements is somehow bypassed. Hard-coded or generated by a subquery any in-list composed of multi-valued elements passes through unscathed:


BLEEBO @ splang > 
BLEEBO @ splang > --
BLEEBO @ splang > -- Now let's try a subquery
BLEEBO @ splang > --
BLEEBO @ splang > 
BLEEBO @ splang > select val2return
  2  from inlist_test
  3  where (inval1, inval2) in (select inval1, inval2 from inlist_test);

VAL2RETURN
----------------------------------------
Value 330
Value 331
Value 332
Value 333
Value 334
Value 335
Value 336
Value 337
Value 338
Value 339
Value 340
...
Value 2466
Value 2467
Value 2468
Value 2469
Value 2470
Value 2471
Value 2472
Value 2473

3000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2957450017

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    74 |    10   (0)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|             |     1 |    74 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | INLIST_TEST |  3000 | 78000 |     5   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  | INLIST_TEST |  3000 |   140K|     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("INVAL1"="INVAL1" AND "INVAL2"="INVAL2")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        265  consistent gets
          0  physical reads
          0  redo size
      74145  bytes sent via SQL*Net to client
       2741  bytes received via SQL*Net from client
        201  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       3000  rows processed

To make this even more interesting a subquery of single-valued elements also successfully executes with more than 1000 members:


BLEEBO @ splang > 
BLEEBO @ splang > --
BLEEBO @ splang > -- There will be 3000 single values from the subquery
BLEEBO @ splang > --
BLEEBO @ splang > 
BLEEBO @ splang > select val2return
  2  from inlist_test
  3  where inval2 in (select inval2 from inlist_test);

VAL2RETURN
----------------------------------------
Value 330
Value 331
Value 332
Value 333
Value 334
Value 335
Value 336
Value 337
Value 338
Value 339
Value 340
...
Value 2470
Value 2471
Value 2472
Value 2473

3000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2957450017

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  3000 |   140K|    10   (0)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|             |  3000 |   140K|    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | INLIST_TEST |  3000 | 39000 |     5   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  | INLIST_TEST |  3000 |   102K|     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("INVAL2"="INVAL2")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        265  consistent gets
          0  physical reads
          0  redo size
      74145  bytes sent via SQL*Net to client
       2741  bytes received via SQL*Net from client
        201  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3000  rows processed

BLEEBO @ splang > 

But, what if the driving table for the in-list has only one column? Simply manufacture element pairs by including a constant in the element:


BLEEBO @ splang > 
BLEEBO @ splang > --
BLEEBO @ splang > -- If there is only one column for an in-list a
BLEEBO @ splang > -- second column can be 'manufactured' to get
BLEEBO @ splang > -- past the 1000 element limitation
BLEEBO @ splang > --
BLEEBO @ splang > 
BLEEBO @ splang > select val2return
  2  from inlist_test
  3  where (inval2,9) in
  4  ((inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
  5  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
  6  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
  7  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
  8  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
  9  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 10  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 11  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 12  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 13  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 14  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 15  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 16  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 17  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 18  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 19  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 20  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 21  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 22  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 23  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 24  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 25  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 26  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),
 27  (inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9),(inval2,9));

VAL2RETURN
----------------------------------------
Value 1
Value 2
Value 3
Value 4
Value 5
Value 6
Value 7
Value 8
Value 9
Value 10
Value 11
...
Value 2996
Value 2997
Value 2998
Value 2999
Value 3000

3000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3899671621

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |  3000 |   102K|     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| INLIST_TEST |  3000 |   102K|     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("INVAL2" IS NOT NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
        233  consistent gets
          0  physical reads
          0  redo size
      74145  bytes sent via SQL*Net to client
      28041  bytes received via SQL*Net from client
        201  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3000  rows processed

BLEEBO @ splang > 

So Oracle’s ‘hard-and-fast’ limit of 1000 in-list elements … isn’t, depending on how the list is constructed. For hard-coded lists using element pairs gets around the limitation, and subqueries of any construction also make an ‘end run’ around that barrier. Presumably from this demonstration new ‘rules’ regarding in-lists could be created:


1. In-lists can be longer than 1000 members when multi-valued elements are used.
2. Single-valued subqueries can also allow in-lists of more than 1000 members.
3. Multi-valued elements can include constants.

Bug? Possibly, but if this truly is a bug then it’s probably a good idea to not rely on the current behavior as it could be fixed in a future release. As of now, though, it is possible to code in-lists that exceed the 1000-member mark by using the techniques provided here. Don’t be surprised if, one day, such queries fail since this is not documented behavior.

And that shouldn’t be unexpected.

April 5, 2019

Recovery Room

Filed under: recovery — dfitzjarrell @ 14:45

"There are no wrong roads to anywhere."
-- Norton Juster, The Phantom Tollbooth

The common wisdom is that any operation declared NOLOGGING won’t contribute to the redo stream, and that includes operations on tables created NOLOGGING. [It cannot be stressed enough that NOLOGGING operations in production databases should NOT be executed if for no other reason than recoverability.] Yet there are operations that will defy the NOLOGGING directive, generating the usual amount of redo. Insert /*+ append */ operations obey NOLOGGING, as do truncates. Conventional inserts and deletes, however, don’t as illustrated with this example. It starts with creating a table NOLOGGING and performing an insert /*+ append */ into it; the statements used are shown below:


--
-- Create a table using the NOLOGGING
-- option
--
-- It will soon be evident that not
-- all operations/inserts/deletes
-- obey that instruction
--
create table nologging_tbl(
id      number,
tname   varchar2(35),
ddl_dt  date)
nologging;

--
-- Insert data
--
-- Using 'append' allows Oracle to
-- observe the nologging aspect of
-- the table
--
insert /*+ append */ into nologging_tbl
select rownum, table_name, sysdate
from all_tables;

Using Log Miner to return the redo generated shows that no rows are returned:


SQL > --
SQL > -- Start checking which inserts and
SQL > -- deletes obey NOLOGGING
SQL > --
SQL > -- The APPEND hint inserts are nologging
SQL > --
SQL > select sql_redo
  2  from v$logmnr_contents
  3  where seg_name= upper('nologging_tbl')
  4  and sql_redo like 'insert%'
  5  /

no rows selected

SQL > 

Truncating the table also shows no redo is generated:


SQL > 
SQL > 
SQL > --
SQL > -- So is truncate, but it should be
SQL > -- since it's DDL, not DML
SQL > --
SQL > truncate table nologging_tbl;

Table truncated.

SQL > 
SQL > select sql_redo
  2  from v$logmnr_contents
  3  where seg_name= upper('nologging_tbl')
  4  and sql_redo like 'insert%'
  5  /

no rows selected

SQL > 

Now let’s execute a conventional, plain-vanilla insert (with no append hint) and see if we stil get no redo:


SQL > 
SQL > --
SQL > -- This insert will generate redo
SQL > --
SQL > -- Even though we told Oracle not
SQL > -- to for this table
SQL > --
SQL > insert into nologging_tbl
  2  select rownum, table_name, sysdate
  3  from all_tables;

2358 rows created.

SQL > 
SQL > commit;

Commit complete.

SQL > 
SQL > select sql_redo
  2  from v$logmnr_contents
  3  where seg_name = upper('nologging_tbl')
  4  and sql_redo like 'insert%'
  5  /

SQL_REDO                                                                        
--------------------------------------------------------------------------------
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('1','TYPE_MIS
C$',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                         
                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('2','ATTRCOL$
',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                           
                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('3','ASSEMBLY
$',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                          
                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('4','LIBRARY$
',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                           

insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('5','VIEWTRCO
L$',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                         
                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('6','ICOLDEP$
',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                           
                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('7','OPQTYPE$
',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                           
                                                                                
...                                                                                
insert into "SCOTT"."NOLOGGING_TBL"("ID","TNAME","DDL_DT") values ('2358','YAMPO
OZA',TO_DATE('25-MAR-19', 'DD-MON-RR'));                                        


2358 rows selected.

SQL > 

Deletes fare no better:


SQL > 
SQL > --
SQL > -- This also generates redo
SQL > --
SQL > delete from nologging_tbl;

2358 rows deleted.

SQL > 
SQL > select sql_redo
  2  from v$logmnr_contents
  3  where seg_name = upper('nologging_tbl')
  4  and sql_redo like 'delete%'
  5  /

SQL_REDO                                                                        
--------------------------------------------------------------------------------
delete from "SCOTT"."NOLOGGING_TBL" where "ID" = '704' and "TNAME" = 'WRI$_ADV_A
CTIONS' and "DDL_DT" = TO_DATE('25-MAR-19', 'DD-MON-RR') and ROWID = 'AAAX2BAAGA
AAACzAAU';                                                                      
                                                                                
delete from "SCOTT"."NOLOGGING_TBL" where "ID" = '705' and "TNAME" = 'WRI$_ADV_R
ATIONALE' and "DDL_DT" = TO_DATE('25-MAR-19', 'DD-MON-RR') and ROWID = 'AAAX2BAA
GAAAACzAAV';                                                                    
                                                                                
delete from "SCOTT"."NOLOGGING_TBL" where "ID" = '706' and "TNAME" = 'WRI$_ADV_R
EC_ACTIONS' and "DDL_DT" = TO_DATE('25-MAR-19', 'DD-MON-RR') and ROWID = 'AAAX2B
AAGAAAACzAAW';                                                                  
                                                                                
...

delete from "SCOTT"."NOLOGGING_TBL" where "ID" = '1275' and "TNAME" = 'WM$HINT_T
ABLE$' and "DDL_DT" = TO_DATE('25-MAR-19', 'DD-MON-RR') and ROWID = 'AAAX2BAAGAA
AAC/ADJ';                                                                       
                                                                                

2338 rows selected.

SQL > 

Remember that the table was created NOLOGGING and still redo was generated for selected operations. Of course the create table statement is logged so the table is recoverable by RMAN. As illustrated conventional inserts are logged, and there are at least three ways an /*+ append */ hint can be circumvented which will cause redo to be generated. The three that immediately come to mind are:


1 Row-level triggers
2 Foreign key constraints that are enabled
3 Non-unique indexes that are used to enforce unique constraints

There could be other ways the append hint will be essentially disregarded; the three listed above either convert array processing (one of the benefits of the /*+ append */ hint) into row-level processing (1,3) or impact the process by performing row-level checking (2). Of course making inserts and deletes NOLOGGING operations isn’t the best idea as that impacts recoverability. And ANY operation executed NOLOGGING in a production database is a disaster just waiting to happen. And the DBA’s job isn’t to create disaster, it’s to prevent disaster from happening.

Sometimes anywhere isn’t where you wanted to be.

Next Page »

Create a free website or blog at WordPress.com.