Oracle Tips and Tricks — David Fitzjarrell

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.

Advertisements

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.

March 10, 2019

Final Elimination

Filed under: General — dfitzjarrell @ 08:07

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

Occasionally a join using primary key columns in a query can be eliminated because of the data involved. If Oracle determines that all of the ‘required’ data is available in a single table the optimizer will rewrite the query to eliminate the ‘unnecessary’ join. Oracle has been doing this for years, with the limitation that the primary keys involved were single-column keys. In versions 12.2 and later multi-column primary keys can be used, however there are cases where the join elimination isn’t complete and that can depend on table order in the FROM clause or on the join syntax in use. Let’s look at an example that illustrates these points.

This example uses three tables — my_table, my_other_table, and my_other_other_table — in various parent->child relationships. [Building the tables won’t be covered here, as we are interested only in the join elimination results.] Using the traditional Oracle syntax for the joins we find that the table order can affect the extent of the join elimination. Remember that the goal is to reduce the query to its simplest form having a minimum of join conditions (ideally, none). Using the traditional Oracle syntax, joining the tables in the following order:


  my_other_other_table,
  my_other_table,
  my_tablec

we eliminate only the join involving my_other_table:


BING @ orclpdb1 > 
BING @ orclpdb1 > select
  2  	 count(c.my_varch)
  3  from
  4  	 my_other_other_table g,
  5  	 my_other_table      p,
  6  	 my_table	c
  7  where
  8  	 c.my_num between 200 and 215
  9  and p.my_other_id	 = c.my_other_id
 10  and p.my_other_other_id = c.my_other_other_id
 11  and g.my_other_other_id   = p.my_other_other_id
 12  ;

COUNT(C.MY_VARCH)
-----------------
               80

BING @ orclpdb1 > 
BING @ orclpdb1 > select * from table(dbms_xplan.display_cursor(null,null,'outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fmu4cu5mqprtt, child number 0
-------------------------------------
select     count(c.my_varch) from     my_other_other_table g,
my_other_table      p,     my_table       c where     c.my_num between
200 and 215 and p.my_other_id   = c.my_other_id and p.my_other_other_id
= c.my_other_other_id and g.my_other_other_id   = p.my_other_other_id

Plan hash value: 2096404014

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |       |       |    55 (100)|          |
|   1 |  SORT AGGREGATE     |                   |     1 |    23 |            |          |
|   2 |   NESTED LOOPS      |                   |    85 |  1955 |    55   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| MY_TABLE          |    85 |  1615 |    55   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| MY_OTHER_OTHER_PK |     1 |     4 |     0   (0)|          |
-----------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$A43D1678")
      ELIMINATE_JOIN(@"SEL$1" "P"@"SEL$1")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$A43D1678" "C"@"SEL$1")
      INDEX(@"SEL$A43D1678" "G"@"SEL$1" ("MY_OTHER_OTHER_TABLE"."MY_OTHER_OTHER_ID"))
      LEADING(@"SEL$A43D1678" "C"@"SEL$1" "G"@"SEL$1")
      USE_NL(@"SEL$A43D1678" "G"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   3 - filter(("C"."MY_NUM"=200))
   4 - access("G"."MY_OTHER_OTHER_ID"="C"."MY_OTHER_OTHER_ID")


44 rows selected.

The highlighted text in the outline proves only one join was eliminated, even though the data would allow the optimizer to eliminate both joins. The table order makes the difference; with this order the join between my_table and my_other_table can be eliminated but the join between that row source and my_other_other_table cannot be. Changing the order of the FROM clause to:


  my_table,
  my_other_table,
  my_other_other_table

provides the optimizer with the ‘correct’ order to eliminate both joins:


BING @ orclpdb1 > 
BING @ orclpdb1 > select
  2  	 count(c.my_varch)
  3  from
  4  	 my_table	c,
  5  	 my_other_table      p,
  6  	 my_other_other_table g
  7  where
  8  	 c.my_num between 200 and 215
  9  and p.my_other_id	 = c.my_other_id
 10  and p.my_other_other_id = c.my_other_other_id
 11  and g.my_other_other_id   = p.my_other_other_id
 12  ;

COUNT(C.MY_VARCH)
-----------------
               80

BING @ orclpdb1 > 
BING @ orclpdb1 > select * from table(dbms_xplan.display_cursor(null,null,'outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dur2a6u4k0dw7, child number 0
-------------------------------------
select     count(c.my_varch) from     my_table       c,
my_other_table      p,     my_other_other_table g where     c.my_num
between 200 and 215 and p.my_other_id   = c.my_other_id and
p.my_other_other_id = c.my_other_other_id and g.my_other_other_id   =
p.my_other_other_id

Plan hash value: 3996063390

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |    55 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |    15 |            |          |
|*  2 |   TABLE ACCESS FULL| MY_TABLE |    85 |  1275 |    55   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$EE3A0715")
      ELIMINATE_JOIN(@"SEL$1" "P"@"SEL$1")
      ELIMINATE_JOIN(@"SEL$1" "G"@"SEL$1")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$EE3A0715" "C"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   2 - filter(("C"."MY_NUM"=200))


40 rows selected.

With this order the optimizer can eliminate both joins it joine my_other_other_table to my_other_table, reducing that join to a single-table query against my_other_table, and that allows for the second join to be eliminated (as in the first query) leaving only my_table as the row source.

Since Oracle allows for using either the ‘native’ Oracle syntax or the ANSI syntax the example can be rewritten to conform to the ANSI syntax. Using the table order from the first Oracle-syntax query the optimizer has no problem eliminating both joins:


BING @ orclpdb1 > 
BING @ orclpdb1 > select
  2  	 count(c.my_varch)
  3  from
  4  	 my_other_other_table g
  5  join
  6  	 my_other_table      p
  7  on  p.my_other_other_id = g.my_other_other_id
  8  join
  9  	 my_table	c
 10  on  c.my_other_id = p.my_other_id
 11  and c.my_other_other_id = p.my_other_other_id
 12  where
 13  	 c.my_num between 200 and 215
 14  ;

COUNT(C.MY_VARCH)
-----------------
               80

BING @ orclpdb1 > 
BING @ orclpdb1 > select * from table(dbms_xplan.display_cursor(null,null,'outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1avs3dy4zyfyf, child number 0
-------------------------------------
select     count(c.my_varch) from     my_other_other_table g join
my_other_table      p on  p.my_other_other_id = g.my_other_other_id
join     my_table       c on  c.my_other_id = p.my_other_id and
c.my_other_other_id = p.my_other_other_id where     c.my_num between
200 and 215

Plan hash value: 3996063390

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |    55 (100)|          |
|   1 |  SORT AGGREGATE    |          |     1 |    15 |            |          |
|*  2 |   TABLE ACCESS FULL| MY_TABLE |    85 |  1275 |    55   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$8E5E10D6")
      ELIMINATE_JOIN(@"SEL$9DC5130D" "P"@"SEL$1")
      OUTLINE(@"SEL$9DC5130D")
      MERGE(@"SEL$E7C364C4" >"SEL$3")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$E7C364C4")
      MERGE(@"SEL$1C2B93D4" >"SEL$2")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1C2B93D4")
      ELIMINATE_JOIN(@"SEL$1" "G"@"SEL$1")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$8E5E10D6" "C"@"SEL$2")
      END_OUTLINE_DATA
  */

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

   2 - filter(("C"."MY_NUM"=200))


47 rows selected.

In yet another interesting turn of events if the join order is changed to that of the “successful” query writtem using Oracle syntax only the join involving my_other_table is eliminated:


BING @ orclpdb1 > 
BING @ orclpdb1 > select
  2  	 count(c.my_varch)
  3  from
  4  	 my_table	c
  5  join
  6  	 my_other_table      p
  7  on      p.my_other_id   = c.my_other_id
  8  and p.my_other_other_id = c.my_other_other_id
  9  join
 10  	 my_other_other_table g
 11  on  g.my_other_other_id = p.my_other_other_id
 12  where
 13  	 c.my_num between 200 and 215
 14  ;

COUNT(C.MY_VARCH)
-----------------
               80

BING @ orclpdb1 > 
BING @ orclpdb1 > select * from table(dbms_xplan.display_cursor(null,null,'outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3jqqzwwsy9t6r, child number 0
-------------------------------------
select     count(c.my_varch) from     my_table       c join
my_other_table      p on      p.my_other_id   = c.my_other_id and
p.my_other_other_id = c.my_other_other_id join     my_other_other_table
g on  g.my_other_other_id = p.my_other_other_id where     c.my_num
between 200 and 215

Plan hash value: 2096404014

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                   |       |       |    55 (100)|          |
|   1 |  SORT AGGREGATE     |                   |     1 |    23 |            |          |
|   2 |   NESTED LOOPS      |                   |    85 |  1955 |    55   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| MY_TABLE          |    85 |  1615 |    55   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| MY_OTHER_OTHER_PK |     1 |     4 |     0   (0)|          |
-----------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$935DEA34")
      MERGE(@"SEL$AAEC2A2A" >"SEL$3")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$AAEC2A2A")
      MERGE(@"SEL$A43D1678" >"SEL$2")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$A43D1678")
      ELIMINATE_JOIN(@"SEL$1" "P"@"SEL$1")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$935DEA34" "C"@"SEL$1")
      INDEX(@"SEL$935DEA34" "G"@"SEL$2" ("MY_OTHER_OTHER_TABLE"."MY_OTHER_OTHER_ID"))
      LEADING(@"SEL$935DEA34" "C"@"SEL$1" "G"@"SEL$2")
      USE_NL(@"SEL$935DEA34" "G"@"SEL$2")
      END_OUTLINE_DATA
  */

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

   3 - filter(("C"."MY_NUM"=200))
   4 - access("G"."MY_OTHER_OTHER_ID"="C"."MY_OTHER_OTHER_ID")


51 rows selected.

It is worth noting that when Oracle processes an ANSI syntax join it first converts it to an equivalent Oracle-syntax join then proceeeds with the optimization/join elimination. With the first ANSI example the re-write essentially produces a query of the form


	select [] from (select [] from my_other_other_table join my_other_table) join my_table

which allows for elimination of the my_other_other_table join, leaving the my_other_table, my_table join which, given the data set, can also be eliminated leaving a simple query againt my_table. The re-write of tne first ANSI join produces basically the same query as the second Oracle-syntax query in the example. With the other ANSI query the resulting re-write is of the form:


	select [] from (select [] from my_table join my_other_table) join my_other_other_table

That essentially matches the first Oracle-syntax query in the example which provides the optimizer with only enough information to eliminate the first join.

It’s a good idea to check execution plans for queries utilizing multiple joins on primary-key columns; it’s possible that simply changing the order of the tables could change the execution plan, as illustrated here. It’s also worth noting that ANSI-syntax queries can behave (as far as the optimizer is concerned) in opposite ways because of the way Oracle re-writes them.

Which should be expected.

February 6, 2019

“It’s Nice To Share…”

Filed under: General — dfitzjarrell @ 11:04

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

In a valiant attempt to let the adminstrator affect performance Oracle has provided various settings in the spfile to enable or disable features that could, possibly, make things go faster. Long on the list of bloggers has been cursor_sharing, the parameter used to tell Oracle how to treat query text. By default it’s set to EXACT, which means Oracle takes the query as-is and executes it. This can, however, clutter the shared SQL area with what appear to be multiple copies of the same query. differing only in the literal values used. One setting for cursor_sharing, FORCE, causes Oracle to treat every query as though it’s using bind variables, or so DBAs were lead to believe. As George Gershwin so eloquently put it — “It ain’t necessarily so.” Let’s look at conditions that can throw a curve ball in this bind-variable circus.

If a query contains nothing but SQL text and literal values then cursor_sharing = FORCE does what DBAs have expected for years — the literals are replaced with bind variables and, using bind variable peeking, the query is parsed and executed. This allows for almost unlimited reuse of a cursor, since the cursor is generated with bind variables and Oracle then uses the shared cursor to execute the query, using the system-generated bind variables to hold the literal values the original query contained. The problem arises when a query, tnat once used only literal values, is slightly rewritten to include a user-defined bind variable. This ‘derails’ the system-generated bind variable train and that good plan is no longer the default plan since the optimizer, having a new bind variable to contend with, reparses and generates a new, not necessarily better, plan.

Thanks to Jonathan Lewis we have an example to illustrate this. Let’s use the data set he’s so generously provided:


rem
rem     Script:         cursor_sharing_limit.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jun 2018
rem     Purpose:
rem
rem     Last tested
rem             18.1.0.0
rem             12.2.0.1
rem             12.1.0.2
rem

create table t1
as
select
        rownum            n1,
        rownum            n2,
        lpad(rownum,10)   small_vc,
        rpad('x',100,'x') padding
from dual
connect by
        level <= 1e4
;

The next step is to execute queries that, supposedly, should undergo bind variable substitution at the hands of cursor_sharing=FORCE, but don’t. Jonathan has also generously provided that code:


alter system flush shared_pool;
alter session set cursor_sharing=force;

declare
        m_ct number;
        m_n1 number := 20;
begin
        execute immediate
                'select /*+ trace this */ count(*) from t1 where n2 = 15 and n1 = :b1'
                into m_ct using m_n1;
        dbms_output.put_line(m_ct);

        execute immediate
                'select /*+ trace this too */ count(*) from t1 where n1 = 15 and n2 = 15'
                into m_ct;
        dbms_output.put_line(m_ct);
end;
/

alter session set cursor_sharing=exact;

The ‘hints’ (which are really nothing more than comments) make it fairly easy to find and return the query text Oracle generates when those queries are executed. Putting this all together into a full example produces:


SQL> set linesize 300 trimspool on pagesize 50
SQL>
SQL> rem
SQL> rem        Script:      cursor_sharing_limit.sql
SQL> rem        Author:      Jonathan Lewis
SQL> rem        Dated:       Jun 2018
SQL> rem        Purpose:
SQL> rem
SQL> rem        Last tested
SQL> rem             18.1.0.0
SQL> rem             12.2.0.1
SQL> rem             12.1.0.2
SQL> rem
SQL>
SQL> create table t1
  2  as
  3  select
  4          rownum            n1,
  5          rownum            n2,
  6          lpad(rownum,10)   small_vc,
  7          rpad('x',100,'x') padding
  8  from dual
  9  connect by
 10          level  comment to avoid WordPress format issue
 11  ;

Table created.

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL> alter session set cursor_sharing=force;

Session altered.

SQL>
SQL> declare
  2          m_ct number;
  3          m_n1 number := 20;
  4  begin
  5          execute immediate
  6                  'select /*+ trace this */ count(*) from t1 where n2 = 15 and n1 = :b1'
  7                  into m_ct using m_n1;
  8          dbms_output.put_line(m_ct);
  9
 10          execute immediate
 11                  'select /*+ trace this too */ count(*) from t1 where n1 = 15 and n2 = 15'
 12                  into m_ct;
 13          dbms_output.put_line(m_ct);
 14  end;
 15  /
0
1

PL/SQL procedure successfully completed.

SQL>
SQL> alter session set cursor_sharing=exact;

Session altered.

SQL>
SQL> select     sql_id, parse_calls, executions, rows_processed, sql_text
  2  from       v$sql
  3  where      sql_text like 'select%trace this%'
  4  and        sql_text not like '%v$sql%'
  5  ;

SQL_ID        PARSE_CALLS EXECUTIONS ROWS_PROCESSED
------------- ----------- ---------- --------------
SQL_TEXT
-----------------------------------------------------------------------------------------
cbu4s78h5pfj5           1          1              1
select /*+ trace this too */ count(*) from t1 where n1 = :"SYS_B_0" and n2 = :"SYS_B_1"

cru67sufptx8x           1          1              1
select /*+ trace this */ count(*) from t1 where n2 = 15 and n1 = :b1


SQL>

Notice that the first query in the script, with a combination of a literal value and a user-defined bind variable, underwent no substitution with respect to the literal value. If straight SQL is used, through SQL*plus, this is what happens:


SQL> variable b1 number
SQL>
SQL> begin
  2          :b1 := 15;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ SQL*Plus session */ count(*) from t1 where n2 = 15 and n1 = :b1;

  COUNT(*)
----------
         1

SQL>
SQL> select     sql_id, parse_calls, executions, rows_processed, sql_text
  2  from       v$sql
  3  where      sql_text like 'select%Plus session%'
  4  and        sql_text not like '%v$sql%'
  5  ;

SQL_ID        PARSE_CALLS EXECUTIONS ROWS_PROCESSED
------------- ----------- ---------- --------------
SQL_TEXT
--------------------------------------------------------------------------------------------------
gq2qy2a9yuta7           1          1              1
select /*+ SQL*Plus session */ count(*) from t1 where n2 = :"SYS_B_0" and n1 = :b1


SQL>

Executing this through SQL*Plus directly, without the context switch of ‘execute immediate’ from a PL/SQL block, changes the behavior of the optimizer so that Oracle provides the expected results. It’s interesting, to say the least.

The expected doesn’t always happen with SQL statements, and that may be due to how those statements reach the optimizer. Knowing when, and when not, to expect behavior may make explaining things to users and developers a bit less stressful. As Norton Juster put it in “The Phantom Tollbooth”: “Expect everything, I always say, and the unexpected never happens.” Which is good advice for a DBA.

Even when you take a different road.

December 28, 2018

Cloning Around

Filed under: General — dfitzjarrell @ 17:41

"We never choose which words to use, for as long as they mean what
they mean to mean, we don’t care if they make sense or nonsense." 
― Norton Juster, The Phantom Tollbooth

Sometimes it’s necessary to clone an existing ORACLE_HOME; one case for this is when the business requires a new ORACLE_HOME when CPU patchsets are applied. With some operating systems cloning a home is simiply a matter of creating a suitable archive (tar, pax, cpio) and extracting the contents to the destination directory. Some operating systems, however, aren’t as well-mannered. Linux is one such operating system and cloning an ORACLE_HOME requires the use the Universal Installer to set things ‘right’ once the copy has completed. Unfortunately this cloning process tends to have issues of its own; one such issue is the setting of the ddl_lock_timeout parameter. Let’s look at the process, the error and how to get around it.

Presuming the business does require a new home to patch it to the latest CPU so the original home can be preserved let’s proceed with the cloning process. This imaginary database will be named GLORBLE (reminding us that this is not an real database) and the original database home will be /u01/zapp/oracle/product/12.2.0/glorble. The April 2018 CPU will be applied so the cloned home will be /u01/app/oracle/product/12.2.0/glorble_apr2018, to make it obvious that it contains software patched with the April 2018 CPU.

The destination directory is created and the original database home files are copied to it from an archive — pax, tar, cpio, gzip can all be used to create such an archive. Presuming this will be a repeated task the clone command can be saved as a script (possibly named clone_home.sh):


$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/clone/bin/clone.pl ORACLE_BASE="/u01/app/oracle/"
ORACLE_HOME="/u01/app/oracle/product/12.2.0/glorble_apr2018" 
OSDBA_GROUP=dba OSOPER_GROUP=oper -defaultHomeName

For readability the command has been split into three lines; it must be a single line in the script or it won’t execute.

The environment must be set to ‘point’ to the home being cloned, then the script can be executed. Prodigous output is generated that won’t be provided here; the ‘problems’ begin after the home is cloned and the root.sh script has been run. Once cloned utilities like sqlplus will perform generally as expected until it becomes desirable or necessary to set a dynamic init parameter, like ddl_lock_timeout, when an unexpected ORA-00068 error is thrown, even when the parameter value is within the accepted range:


SQL> alter session set ddl_lock_timeout = 5;
ERROR:
ORA-00068: invalid value "" for parameter ddl_lock_timeout, must be
between 0 and 1000000

The clue appears to be in the value reported by Oracle; something in the linking step of the cloning process apparently failed. What that may be is not clear and Oracle Support has no document providing any explanation other than an upgrade process was not completed. Fortunately this home will be patched in the next step, which requires the most recent release of OPatch (patch number p6880880) which simply needs to be unzipped into the cloned home. Using the current OPatch the cloned home is processed:


$ cd 
$ $ORACLE_HOME/OPatch/opatch apply
...

As if by magic the ORA-00068 errors disappear. In reality the patch process applies software updates amd re-links the software in a manner different than the Perl script used to clone the home, which executes the setup command in $ORACLE_HOME/oui/bin. It isn’t clear WHAT is different between the two processes but no errors are thrown after patching is completed.

It’s truly an oddity; Oracle supplied tools and utiliities are expected to work and produce usable results. In this case something was missed in the overall cloning process and for those cloning an existing ORACLE_HOME for another database (which isn’t necessary but isn’t wrong) because they are running Oracle on an Oracle VM built from a template and have no software distribution to use this could be a major problem.

Take care when using this cloning process as things may not be as they seem on the surface. Many bug fixes have gone into the Perl script Oracle provides yet it appears there is at least one that hasn’t been addressed (possibly because it hasn’t been reported). This has been reported to Oracle support so hopefully an answer is forthcoming. In the interim if a home is cloned it should be patched to apply the latest security patches and to avoid the ORA-00068 surprises.

And that just makes sense.

December 11, 2018

“The Best-laid Plans…”

Filed under: General — dfitzjarrell @ 08:14

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

Oracle version 12 introduced the hybrid histogram as a performance improvement — such histograms are based on some ‘rules’ set forth in version 12.1. Those ‘rules’ are:

        1) a value should not be found in more than one bucket
        2) the bucket size is allowed to be extended in order to contain all instances of the same distinct value
        3) adjusted bucket size cannot be less than the original size (not applicable at either end of the data set)
        4) the original number of buckets should not be reduced

This type of histogram was a major improvement over previous histograms (height-balanced and frequency) in terms of how a query plan is generated. There was, however, a bug with this type of histogram involving cardinality estimates (Bug 25994960) and a patch was supplied as a corrective measure. Since the bug was not a ‘show stopper’ many 12.2 databases remain unpatched; a backport patch for 12.1 also exists, again not widely applied. With Oracle 18 (specifically 18.3) this issue has been addressed which leads to the following situation between unpatched 12.2 and 12.1 databases and the 18.3 version. The following script based on work by Jonathan Lewis illustrates the problem.

The dbms_stats.gather_table_stats procedure is used to generate the hybrid histogram by specifying that 13 ‘buckets’ will be created. It’s not the number of buckets that is the issue, it’s the endpoints of those buckets that change between the various versions. The script used is posted below:


drop table hist_test purge;
 
execute dbms_random.seed(0)
 
create table hist_test(
        my_id           number(8,0),
        id_mod_20       number(6,0),
        id_mod_30       number(6,0),
        id_mod_50       number(6,0),
        my_rand_id      number(6,0)
)
;
 
insert into hist_test
with datasource as (
        select
                rownum my_id
        from dual
        connect by
                level  'HIST_TEST',
                method_opt       => 'for all columns size 1 for columns my_rand_id size 13'
        );
end;
/

The script above generates 22 distinct values to base the histogram on. The query below reports on those values and on the histogram information Oracle has generated:


select
        my_rand_id, count(*)
from
        hist_test
group by
        my_rand_id
order by
        my_rand_id
;
 
select
        endpoint_value                                                            value,
        endpoint_number,
        endpoint_number - lag(endpoint_number,1,0) over(order by endpoint_number) bucket_size,
        endpoint_repeat_count
from
        user_tab_histograms
where
        table_name  = 'HIST_TEST'
and     column_name = 'MY_RAND_ID'
order by
        endpoint_value
;

Since the data sets are the same between versions the data set will be reported only once:


MY_RAND_ID   COUNT(*)
---------- ----------
         1          1
         8          3
         9          1
        10          5
        11          4
        12          8
        13         14
        14          9
        15         11
        16         22
        17         34
        18         31
        19         36
        20         57
        21         44
        22         45
        23         72
        24         70
        25         87
        26        109
        27         96
        28         41
 
22 rows selected.
 

As mentioned at the beginning of the article it’s the endpoints that change. Let’s look at the histogram in an unpatched 12.2 database:


     VALUE ENDPOINT_NUMBER BUCKET_SIZE ENDPOINT_REPEAT_COUNT
---------- --------------- ----------- ---------------------
         1               1           1                     1
        15              56          55                    11
        17             112          56                    34
        18             143          31                    31
        19             179          36                    36
        20             236          57                    57
        21             280          44                    44
        22             325          45                    45
        23             397          72                    72
        24             467          70                    70
        25             554          87                    87
        26             663         109                   109
        28             800         137                    41
 
13 rows selected.
 

Oracle generates the histogram based on the estimated cardinalities and notice that, in 12.2, Oracle takes every value from 17 through 26 for the histogram. Moving to 18.3, with the same script, the histogram data is slightly different:


     VALUE ENDPOINT_NUMBER BUCKET_SIZE ENDPOINT_REPEAT_COUNT
---------- --------------- ----------- ---------------------
         1               1           1                     1
        15              56          55                    11
        17             112          56                    34
        19             179          67                    36
        20             236          57                    57
        21             280          44                    44
        22             325          45                    45
        23             397          72                    72
        24             467          70                    70
        25             554          87                    87
        26             663         109                   109
        27             759          96                    96
        28             800          41                    41
 
13 rows selected.

Because of the patch the value 18 is now missing from the histogram, replaced with the value 27, a result of correcting the cardinality estimates generated to create the histogram. It will probably be a rare occurrence for such a change to affect an execution plan, but stranger things have been known to happen. Over the years the optimizer has had its share of mishaps with cardinality estimates, and this one appears to be minor in nature. It can be confusing, though, to upgrade to 18.3 and find that a hybrid histogram has changed unexpectedly. It may be a rare occurrence to actually check histogram data without a performance issue at hand so this could easily be overlooked in a database upgrade. It is nice, though, to be aware such changes could occur so that if someone complains that a query that ran ‘fine’ in 12.2 is running a bit … ‘off’ … in 18.3 it can be explained.

Even if it wasn’t expected.

October 25, 2018

Tuning Time

Filed under: General,Performance — dfitzjarrell @ 15:42

"“You see,” he continued, beginning to feel better, “once there was no time at all, and people found it very inconvenient.
They never knew whether they were eating lunch or dinner, and they were always missing trains. So time was invented to
help them keep track of the day and get places when they should. When they began to count all the time that was available,
what with 60 seconds in a minute and 60 minutes in an hour and 24 hours in a day and 365 days in a year, it seemed as if
there was much more than could ever be used. ‘If there’s so much of it, it couldn’t be very valuable,’ was the general
opinion, and it soon fell into disrepute. People wasted it and even gave it away. Then we were given the job of seeing
that no one wasted time again,” he said, sitting up proudly.” 
-- Norton Juster, The Phantom Tollbooth

DBAs usually get questions on query performance when one runs slower than the user would like and. as a result, questions on how best to go about tuning a query soon follow. End-users usually don’t like waiting for results and queries that take longer than a few seconds tend to be considered as suspect. What many end-users don’t know is that query tuning is a combination of art, science and (gasp) intuition. Elegant tuning solutions are art, based on science, and that science uses two of Oracle’s events, 10046 and 10053, to provoide more detailed information. Which one is more beneficial depends on the query and the ‘bad’ execution plan. A 10046 trace can show what Oracle is waiting on and where time is being spent in the execution phase of running a query. Sometimes, however, the waits and elapsed time breakdowns aren’t enough, which is where the 10053 trace can be extremely helpful. Note that this post takes one particular situation and gives abbreviated steps in solving it; it’s not meant to be a treatise on the subject nor a detailed case study. With that in mind let’s look at a plan where that event can provide much-needed information.

Queries using nested subqueries can take a number of paths to return data; one possibility involves what Oracle calls ‘subquery unnesting’, a process where Oracle re-writes the nested query into an unnested join with the intent that this will improve data access and thus query performance. This can be done for several nested subqueries, resulting in one or more NESTED LOOP joins in the execution plan. This is where the road can take a less than desirable turn; joins consume time, making the query execution longer than it probably should be. If the query in question is a ‘one-off’, run once, maybe twice in a day, this may not be a real problem as a few seconds here and there won’t throw off the regular day’s work. If, on the other hand, this is a regularly run statement, the longer run times can stack up on each other and cause delays in processing, and, as the old addage states: “Time is money.” Since the query plan won’t give details on how Oracle eventually generated it one course of action is setting the 10053 event in the current session and executing the problem statement.

The first step in tuning is to obtain the current execution plan and see if it makes ‘sense’. A query with joins will likely produce NESTED LOOP JOIN or HASH JOIN steps. In this case the plan produced a NESTED LOOPS SEMI step that, on the face of it, made no sense as the query contained no join conditions as originally written. This could be a clue that the optimizer did something different and posibly unexpected with the plan. That is exactly what prompted this investigation which used a 10053 event trace to ‘track down’ what the optimizer chose to do. This, in turn, caused a slight rewrite of the query to include the necessary hints to ‘nudge’ the optimizer toward a more efficient plan.

Setting event 10053 is not a difficult task; let’s look at a script that does this and then executes several SELECT statements before turning the 10053 ‘switch’ off (unfortunately the actual queries can’t be published as they are proprietary so placeholders will be used):


alter session set tracefile_identifier='MAILQUEUE';
alter session set events = '10053 trace name context forever, level 2';

[First query statement here]

[Second query statement here]

[Third query statement here]

[Fourth query statement here]

alter system set events = '10053 trace name context off';

The important parts of the above example are the lines prior to any query statements, which turn on the 10053 event and set a trace file identifier so the generated trace file can be easily identified, and the statement after the queries have run, which stops the 10053 event. Unlike the 10046 event, which is more easily read after processing through the tkprof utility, the 10053 trace file needs no additional work. It can be a bit daunting at first since it’s like no other trace file most DBAs have seen. Let’s go through the example, using the actual trace file generated by the unobfuscated script, and see what it reveals.

The beginning of the trace file provides some basic information on the environment and provides query ‘signatures’ that the optimizer uses to identify query and subquery blocks:


Trace file ....
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME = ...
System name:	SunOS
Node name:	...
Release:	5.10
Version:	Generic_150400-61
Machine:	sun4v
Instance name: ...
Redo thread mounted by this instance: 1
Oracle process number: 1134
Unix process pid: 22631, image: oracle@... (TNS V1-V3)

Query Block Registry:
SEL$2 0x77a10ca0 (PARSER)
  SEL$F5BB74E1 0x77a198b0 (VIEW MERGE SEL$1; SEL$2) [FINAL]
SEL$1 0x77a198b0 (PARSER)
  SEL$F5BB74E1 0x77a198b0 (VIEW MERGE SEL$1; SEL$2) [FINAL]

:
    call(in-use=12456, alloc=347568), compile(in-use=95008, alloc=158520), execution(in-use=15312, alloc=16200)

End of Optimizer State Dump
Dumping Hints
=============
====================== END SQL Statement Dump ======================
*** SESSION ID:(758.2581) 2018-08-21 09:40:23.286
 
Registered qb: SEL$1 0x77a198b0 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=4 objn=68513 hint_alias="DQ"@"SEL$1"

Registered qb: SEL$2 0x7751be68 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$2 nbfros=1 flg=0
    fro(0): flg=4 objn=69663 hint_alias="WPV"@"SEL$2"

Registered qb: SEL$3 0x7751b188 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$3 nbfros=1 flg=0
    fro(0): flg=4 objn=10290765 hint_alias="MRE"@"SEL$3"

SPM: statement not found in SMB
SPM: capture of plan baseline is OFF

This begins the portion of the trace file of interest, including the query [not posted due to confidentiality concerns] and the legend of abbreviations:


**************************
Automatic degree of parallelism (AUTODOP)
**************************
Automatic degree of parallelism is disabled: Parameter.
kkopqSetForceParallelProperties: Hint:no
Query: compute:yes forced:no forceDop:0
kkopqSetDopReason: Reason why we chose this DOP is: table property.
table property forces parallelism

Global Manual DOP: 1 - Rounded?: no
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION

******************************************
----- Current SQL Statement for this session (sql_id=*************) -----
[ Removed for confidentiality reasons ]
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
CBY - connect by
SLP - select list pruning
DP - distinct placement
VT - vector transformation
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUSPEED - CPU Speed 
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
  1: no partitioning required
  2: value partitioned
  4: right is random (round-robin)
  128: left is random (round-robin)
  8: broadcast right and partition left
  16: broadcast left and partition right
  32: partition left using partitioning of right
  64: partition right using partitioning of left
  256: run the join in serial
  0: invalid distribution method
sel - selectivity
ptn - partition
AP - adaptive plans

Other areas of the trace file provide environment information that is not necessary for this discussion. Because the execution plan includes a NESTED LOOPS SEMI step the reason for this should be ascertained since the original query had no join conditions. Looking through the trace file doesn’t reveal anything that looks unusual to the modestly trained eye until the section provided below is examined. Searching through the trace file for indications of join activity allowed the DBA to eventually ‘zero in’ on the offending optimizer step. Subquery unnesting isn’t uaually an issue, but in this case it involved two similar nested subqueries that Oracle thought would perform better as joins. Unfortunately this optimizer decision ended up as the more costly alternative:


*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery removal for query block SEL$3 (#3)
RSW: Not valid for subquery removal SEL$3 (#3)
Subquery unchanged.
Subquery Unnesting on query block SEL$2 (#2)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$2 (#2).
SU:   Checking validity of unnesting subquery SEL$3 (#3)
SU:   Passed validity checks.
SU:   Transforming ANY subquery to a join.
Registered qb: SEL$BE5C8E5F 0x755990f8 (SUBQUERY UNNEST SEL$2; SEL$3)

The query unnesting results in performance degradation because Oracle now must process a nested loop join; hinting the subqueries altered the nested loops path into two filter operations, speeding up the execution time considerably. Having this information allows the DBA to use the proper hint (no_unnest) on each of the nested subqueries to eliminate this behavior.

Sometimes query tuning can require a considerable amount of time, especially when searching a trace file for a cause with little more than an execution plan and the fact that the plan doesn’t match how the query was constructed. Even DBAs experienced with the oddities of the optimizer may miss the cause when it’s buried in a section that doesn’t appear to apply at first glance.

No post on any aspect of query tuning can provide an absolute, foolproof method for improving performance. There are simply too many variables and too many optimizer possibilities to consider. Hopefully this post has provided a starting point for query and statement tuning; remember that each query is different and that performance tuning is statement-specific. Consider each query on its own and major improvements may not be guaranteed, but sometimes even the smallest change can produce measurable results.

Only time will tell.

August 10, 2018

Well, That Didn’t Work …

Filed under: General — dfitzjarrell @ 11:46

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

Interesting behavior with the unified audit trail has been reported by Jeff Hunter and verified on at least one platform, RedHat Enterprise Linux 6. While other ports of Oracle behave as expected (Windows and Oracle Enterprise Linux 7, to name two that I’ve tested) the problem platform fails to return data from V$SESSION because the AUDSID values apparently don’t correspond to SESSIONID values returned by SYS_CONTEXT() or in the UNIFIED_AUDIT_TRAIL table. Let’s take this ‘for a spin’.

The following query returns data for the current user session from Oracle databases running on Windows, Solaris 10 or OEL 7:


select sid, serial#, os_username, machine, program
from v$session, unified_audit_trail
where audsid = sessionid
and sessionid = (select sys_context('userenv','sessionid') from dual);

As an example let’s run this on a Windows version of Oracle:


SQL> select sid, serial#, os_username, machine, program
  2  from v$session, unified_audit_trail
  3  where audsid = sessionid
  4  and sessionid = (select sys_context('userenv','sessionid') from dual)
  5  /

       SID    SERIAL# OS_USERNAME        MACHINE           PROGRAM
---------- ---------- ------------------ ----------------- -------------------------
       229       8579 SPLEEZO\fnordwamp  SPLEEZ0\NOTYOURPC sqlplus.exe

SQL>

From OEL 7 similar results are produced, again for the currently connected session:


SQL> select sid, serial#, os_username, machine, program
  2  from v$session, unified_audit_trail
  3  where audsid = sessionid
  4  and sessionid = (select sys_context('userenv','sessionid') from dual)
  5  /

       SID    SERIAL# OS_USERNAM   MACHINE           PROGRAM
---------- ---------- ------------ ----------------- ----------------------------------
        12      33089 oracle       mydbserver        sqlplus@mydbserver (TNS V1-V3)

SQL>

This is the output (or lack thereof) reported by Jeff Hunter for RHEL 6:


SQL> select sid, serial#, os_username, machine, program
  2  from v$session, unified_audit_trail
  3  where audsid = sessionid
  4  and sessionid = (select sys_context('userenv','sessionid') from dual)
  5  /

no rows returned

SQL>

From the above tests and results it appears that Oracle 12.x doesn’t behave the same way on all platforms. The interesting part of this is that the values do appear to match when selected independently yet they don’t ‘match’ when the AUDSID and SESSIONID columns are joined for user sessions. I can’t believe that the RHEL 6 behavior is the ‘norm’ as audit records should be able to be linked to sessions so that activity can be tracked and recorded.

Oracle support provides no documents regarding this behavior so no work-around is provided, and this may be an issue to raise an SR for. All platforms should behave in the same way with unified auditing, otherwise it’s of no benefit to the DBA.

Sometimes that fork in the road is actually a spoon …

Next Page »

Create a free website or blog at WordPress.com.