Oracle Tips and Tricks — David Fitzjarrell

November 20, 2017

“It’s The Latest Version!”

Filed under: General — dfitzjarrell @ 12:01

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

It’s inevitable that shops will eventually need to upgrade to Oracle 12.2.0.1 since 11.2.0.x is soon to be out of support and Oracle 18 (which would have been 12.2.0.2 but Oracle has decided to name new releases with the last two digits of the year of release) is looming on the horizon. There are good reasons for such an upgrade; the availabilty of expanded JSON functionality, APEX improvements and online table management such as moves and partition splits to name three. This means that careful planning is crucial to a successful upgrade, including object management tasks that may not seem important that can send an upgrade ‘sideways’. Let’s look at those possibly overlooked items.

Depending upon which release the upgrade starts from the path may become longer than expected, traveling through more than one Oracle release before finally ending up at 12.2.0.1. Upgrades from versions lower than 11.2.0.3 will necessitate getting to release 11.2.0.3 or 11.2.0.4 before proceeding further; it shall be presumed that the database is at either of those two releases for this discussion.

The obvious place to begin is with the pfile or spfile; deprecated parameters should be removed or commented to prevent them from being set. The following list shows all but one of the parameters deprecated by Oracle for releases up to and including 12.2.0.1:


active_instance_count
background_dump_dest
buffer_pool_keep
buffer_pool_recycle
commit_write
cursor_space_for_time
fast_start_io_target
global_context_pool_size
instance_groups
lock_name_space
log_archive_start
max_enabled_roles
parallel_automatic_tuning
parallel_io_cap_enabled
parallel_server
parallel_server_instances
plsql_debug
plsql_v2_compatibility
rdbms_server_dn
remote_os_authent
resource_manager_cpu_allocation
sec_case_sensitive_logon
serial_reuse
sql_trace
standby_archive_dest
user_dump_dest

The missing parameter is sec_case_sensitive_logon and is unsupported in what Oracle calls ‘Exclusive Mode’, a reference to how the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter is set (if it’s set at all) in the sqlnet.ora file. By default it’s set to 12, so that only version 12 clients can connect to the database. It can be set to values as low as 8 (to ensure backward compatibility for older applications) and it also affects accounts with a password version less than 11G. For compatibility reasons case-insensitive passwords are required for some older applications or applications that access both a 12.x database and an older release. The pre-upgrade tool provided by Oracle will report such issues so they can be addressed prior to the upgrade. The command to run the tool is:


$ORACLE_HOME/jdk/bin/java -jar {Oracle 12.2 home}/rdbms/admin/preupgrade.jar FILE TEXT DIR {directory to write the output to}

The $ORACLE_HOME variable, and the corresponding environment, must be set to the current, pre-12.2 Oracle home for the database to be upgraded. The tool is run from the 12.2 home. That utility will generate the following scripts and directories:


dbms_registry_basic.sql         postupgrade_fixups.sql          preupgrade.log                  upgrade
dbms_registry_extended.sql      preupgrade_driver.sql           preupgrade_messages.properties
oracle                          preupgrade_fixups.sql           preupgrade_package.sql

The preupgrade.log has all of the information necessary to prepare the database for upgrade. The .sql scripts generated tell you what they are for and the log will report which scripts to run to prepare the database. [For those who are curious setting SQLNET.ALLOWED_LOGON_VERSION_SERVER to 8 ‘turns off’ Exclusive Mode so accounts with a password version of 10G will still be accessible; not setting SQLNET.ALLOWED_LOGON_VERSION_SERVER when accounts have a 10G version password (such as those accounts created by a legacy application) will render them no longer accessible.] There may be post-upgrade actions to take (such as upgrading the database timezone) which will be reported once the postupgrade_fixups.sql script has run.

Invalid objects can interfere with an upgrade in such a way as to cause ORA-00600 errors to be generated. To be honest it is a special case that can cause this: views, materialized views, packages, functions and procedures that use database links that no longer exist can send an upgrade into a downward spiral. Obviously since these are already invalid and cannot be successfully compiled it’s highly likely they are not being used; another case, however, is when a test, dev or UA database is created from a production database and such database links are dropped for security reasons. It will still be necessary to find these objects (utlrp.sql will report them in the DBA_ERRORS table) so the DDL can be preserved and the objects dropped prior to starting the upgrade. After the upgrade is successfully completed these objects can be recreated, although they will still be invalid.

The dba_recyclebin MUST be purged prior to starting an upgrade; as the upgrade process accesses the data dictionary for the database undergoing the upgrade an recyclebin objects can stop an upgrade or cause the database to throw unexpected errors after the upgrade has apparently completed successfully.

Unused columns can also an issue as the data dictionary keeps track of them and sets their position in the table to 0; the upgrade process doesn’t check for a position value of 0, it simply checks the dictionary for columns that may need to be upgraded because of features that have been selected. Unlike invisible columns that CAN be used after they are set to INVISIBLE, dropped columns have no useful names and cannot be recovered. If developers have set set columns to UNUSED then they are really, truly gone and need to be dropped. A query of DBA_UNUSED_COL_TABS will provide a list of the owners, tables and number of columns marked UNUSED. It’s a simple task to use that view to create a script to rid those tables of their unused baggage:



select 'alter table '||owner||'.'||table_name||' drop unused columns;'
from dba_unused_col_tabs

spool drop_unused.sql
/
spool off

spool drop_unused_cols.log
set echo on
@drop_unused
set echo off
spool off

so the upgrade won’t fail.


[Compressed tables with unused columns won’t cause an upgrade to fail so there is no need to uncompress, drop the unused columns, then compress the table.]

Backup the database BEFORE starting an upgrade; should the upgrade fail the database can be restored into its original home so that the business isn’t without it during the ‘investigate the failure’ phase. Do not ignore this step; it has saved many a DBA and enterprise from losing a database to a failed upgrade.

It is no longer possible to run the catupgrd.sql script directly from the SQL> prompt; it’s a Java script that is called as shown below:


$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl $ORACLE_HOME/rdbms/admin/catupgrd.sql

which runs in a mixture of serial and parallel modes; each step, and the mode used, are reported at the terminal screen during the upgrade. Sample output is shown below:


------------------------------------------------------
Phases [0-115]         Start Time:[2017_09_21 13:59:45]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [{sid}] Files:1    Time: 146s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [{sid}] Files:5    Time: 54s
Restart  Phase #:2    [{sid}] Files:1    Time: 0s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [{sid}] Files:19   Time: 18s
Restart  Phase #:4    [{sid}] Files:1    Time: 0s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [{sid}] Files:6    Time: 22s
*****************   Catproc Start   ****************
Serial   Phase #:6    [{sid}] Files:1    Time: 22s
*****************   Catproc Types   ****************
Serial   Phase #:7    [{sid}] Files:2    Time: 21s
Restart  Phase #:8    [{sid}] Files:1    Time: 1s
****************   Catproc Tables   ****************
Parallel Phase #:9    [{sid}] Files:69   Time: 35s
Restart  Phase #:10   [{sid}] Files:1    Time: 1s
...

When the upgrade completes the total time is reported and the names and locations of the log file and summary report are displayed. These files need to be checked before moving on to any post-upgrade activities as any errors encountered or generated during the upgrade will be reported in these files; no such output is displayed on the terminal during the upgrade process. If the steps recommended by the preupgrade tool have been followed it’s very unlikely that the upgrade will fail; obviously there is the odd chance that something in the database configuration specific to an application can cause problems but the ‘odds’ ase in the DBA’s favor if the preupgrade instructions are followed.

Upgrading to version 12.2.0.1 is different from previous upgrades but it doesn’t have to end in tragedy. Using the preupgrade tool (as Oracle recommends), following the instructions provided by that tool and addressing the issues listed here will go a long way toward making a 12.2.0.1 upgrade a success.

Which is, obviously, what you were expecting.

Advertisements

November 3, 2017

Go Big …

Filed under: General — dfitzjarrell @ 12:15

"You see, to tall men I'm a midget, and to short men I'm a giant;
to the skinny ones I'm a fat man, and to the fat ones I'm a thin man."
-- Norton Juster, The Phantom Tollbooth

For years DBAs (and users) have been searching for the elusive ‘go_faster’ initialization parameter in Oracle, only to be disappointed at every turn as no such setting exists, at least not with that name. Oracle has, though, moved in that direction lately with the 12.x releases by providing a way for speedier access to ‘big tables’. So what IS a ‘big table’? That’s up to how your database has the “_small_table_threshold” parameter configured. Let’s look at how Oracle has decided to make ‘big table’ access faster.

Oracle offers, in releases 12 and later, a new ‘twist’ that may improve performance of ‘big’ tables called, appropriately enough, Automatic Big Table Caching. This allows Oralce to automatically cache such tables into an area of the SGA reserved specifically for this purpose. The big table cache won’t ‘work’ for direct path reads; in the example that follows those have been ‘turned off’ for the session to ensure that the table will be loaded into the configured cache. Let’s look at how it’s configured and what it can do for you when it’s active.

Unlike the In-Memory database option Automatic Big Table Caching is a no additional cost feature; no additional licensing is required to use it. It does require a change to memory settings which may increase the shared pool/sga/memory settings and could require that additional memory be installed (if the database is heavily used and memory can’t be reallocated to the big table cache from the existing SGA/memory settings). That being said, configuring Automatic Big Table Caching is fairly simple as only one parameter, db_big_table_cache_percent_target, needs to be set. Depending upon the percentage configured this could create a situation where the current sga or memory settings need to be increased; that will depend upon the size of the big table or tables Oracle will be caching. [If the table is too large for the cache then it could be ‘cached’ to disk and the usage query will reflect that.] Let’s look at a possible configuration and see how it performs.

In this example the cache percent is set to 80 (a rather large number, true, but this is for a small, personal database and the table will be quite large in comparison to the available SGA. Let’s create and populate a table to run an example with:


BING @ quanghoo > 
BING @ quanghoo > create table bigly_bigly_table(
  2  smorgun	     number,
  3  fleester	     varchar2(800),
  4  yupqast	     date);

Table created.

BING @ quanghoo > 
BING @ quanghoo > begin
  2  	     for i in 1..1000000 loop
  3  		     insert into bigly_bigly_table
  4  		     values(i, 'Altoona'||lpad(i, 773,'0'), sysdate+mod(i,991));
  5  	     end loop;
  6  
  7  	     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

BING @ quanghoo > 
BING @ quanghoo > exec dbms_stats.gather_table_stats(user, 'BIGLY_BIGLY_TABLE', cascade=>true)

PL/SQL procedure successfully completed.

BING @ quanghoo > 

Let’s check on what the database considers a ‘small’ table:


BING @ quanghoo > 
BING @ quanghoo > connect sys/########### as sysdba
Connected.
SYS @ quanghoo > 
SYS @ quanghoo > SELECT a.ksppinm  "Parameter",  c.ksppstvl "Value"
  2  	   FROM   x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
  3  	   WHERE  a.indx = b.indx AND a.indx = c.indx
  4  	    AND   p.name(+) = a.ksppinm
  5  	    AND   UPPER(a.ksppinm) IN ( UPPER('_db_block_buffers'),
  6  					UPPER('_small_table_threshold'));

Parameter                                             Value
----------------------------------------------------- ---------------------------------
_db_block_buffers                                     208502         
_small_table_threshold                                4170    
                                                                                                                                                      
SYS @ quanghoo > 

The value is in blocks, so any table larger than 4170 blocks in this particular database will be considered a big table. Let’s now check on the number of blocks our example table occupies:


SYS @ quanghoo > connect bing/##########
Connected.
BING @ quanghoo > set echo on linesize 150 pagesize 100
BING @ quanghoo > column table_name format a35
BING @ quanghoo > alter session set parallel_degree_policy=auto;

Session altered.

BING @ quanghoo > alter session set "_serial_direct_read"=never;

Session altered.

BING @ quanghoo > select blocks from dba_tables where table_name = 'BIGLY_BIGLY_TABLE';

    BLOCKS                                                                                                                                            
----------                                                                                                                                            
    112097                                                                                                                                            

BING @ quanghoo > 

We verify the percent target that was set earlier:


BING @ quanghoo > show parameter DB_BIG_TABLE_CACHE_PERCENT_TARGET

NAME                                 TYPE        VALUE                                                                                                
------------------------------------ ----------- ------------------------------                                                                       
db_big_table_cache_percent_target    string      80                                                                                                   
BING @ quanghoo > 

Now we check to see what the cache shows as being used; since this example has been run more than once the object count is greater than 1:


BING @ quanghoo > select BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP from v$bt_scan_cache;

BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP                                                                                         
--------------- ------------ ---------------- ---------------                                                                                         
             80            2           170037            1000                                                                                         

BING @ quanghoo > 

Let’s run a count against the table (there are no indexes on the table) and exercise that cache a bit:


BING @ quanghoo > select count(*) from BIGLY_BIGLY_TABLE;

  COUNT(*)                                                                                                                                            
----------                                                                                                                                            
   1000000                                                                                                                                            

BING @ quanghoo > 
BING @ quanghoo > select BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP from v$bt_scan_cache;

BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP                                                                                         
--------------- ------------ ---------------- ---------------                                                                                         
             80            2           170037            1000                                                                                         

BING @ quanghoo > 

That the object count did not increase indicates that the object is cached and that Oracle is using it to provide the results we requested. Repeated queries against the table (using full table scans and NOT using direct reads) will provide the same results. Checking the query statistics we see nothing but consistent gets, which is what is to be expected when using the big table cache for cached objects:


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     111360  consistent gets
          1  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Running the same query again produces the same results (since the statistics didn’t change those numbers will not be repeated):


BING @ quanghoo > 
BING @ quanghoo > select count(*) from BIGLY_BIGLY_TABLE;

  COUNT(*)                                                                                                                                            
----------                                                                                                                                            
   1000000                                                                                                                                            

BING @ quanghoo > 
BING @ quanghoo > select BT_CACHE_TARGET, OBJECT_COUNT, MEMORY_BUF_ALLOC, MIN_CACHED_TEMP from v$bt_scan_cache;

BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP                                                                                         
--------------- ------------ ---------------- ---------------                                                                                         
             80            2           170037            1000                                                                                         

BING @ quanghoo > 

Should another ‘big’ table be accessed (by conventional reads) it would also go into the cache, provided there is enough space in the cache. Again, this is automatically done by Oracle once the big table cache has been configured.

Automatic Big Table Caching can be a performance improvement when large tables are the norm rather than the exception. That it’s also a feature that doesn’t require additional licensing makes it all the more desirable to configure and use.

Of course, it’s all in how you see things.

October 20, 2017

It’s From The Catalog

Filed under: General — dfitzjarrell @ 17:24

"Have you ever heard a blindfolded octopus unwrap a cellophane-covered bathtub?"
-- Norton Juster, The Phantom Tollbooth

Database upgrades aren’t terribly difficult in general, unless the upgrade is for an RMAN catalog database that has two catalogs with different versions. It’s completely ‘legal’ to have an 11.2.0.x RMAN catalog database with an additional 12.1.0.x catalog; this is where the going can get rough during an upgrade since the owner of the 12c catalog will likely have an after logon context trigger which will become invalid during the ‘startup upgrade’ step of the database upgrade. Once that trigger goes invalid the only accounts that can login to the database are SYS, SYSTEM and the catalog owner of the 11.2.0.x catalog.

All of the documentation on upgrading RMAN catalog databases and the associated catalogs address the one database/one catalog scenario, with that one catalog having the same version as the database. So what IF you have two catalogs of differing versions in an 11.2.0.x database? The upgrade can be completed successfully but it takes some additional steps. Let’s look at the differences between 11.2.0.x and 12.1.0.x (or 12.2.0.x) RMAN and then at those steps to see where this can differ from a ‘normal’ upgrade.

The trigger mentioned above is the first hurdle that must be addressed, and that’s an easy hurdle to jump. The name of that problematic trigger is VPC_CONTEXT_TRG and it sets a specific context for catalog owners. Prior to changing ORACLE_HOME locations to the 12.x version simply log in as the catalog owner of the 12.x catalog and disable the trigger:


SQL> alter trigger VPC_CONTEXT_TRG disable;

Trigger altered.

SQL>

Now the upgrade to 12.x can proceed and the invalid trigger won’t affect the second catalog owner.

Oracle, in the 12.x versions of RMAN, change the VPC configuration used in 11.2.0.x to a VPD configuration; because of this some additional steps need to be taken even with single-catalog repository databases. The first change in the procedure is a script, located in $ORACLE_HOME/rdbms/admin named dbmsrmansys.sql. This script adds privileges to the RECOVERY_CATALOG_OWNER role necessary to access and manage the VPD configuration and must be run before connecting to the RMAN catalog database otherwise the dreaded ORA-01031 “insufficient privileges” error will rear its ugly head. This will cover all recovery catalog owners in the repository database, regardless of the catalog version.

After that script is executed then each catalog owner needs to be granted the necessary privileges to access the VPD configuration so another script is run, this time for each catalog owner in the database:


sqlplus / as sysdba @?/rdbms/admin/dbmsrmanvpc.sql -vpd rman
sqlplus / as sysdba @?/rdbms/admin/dbmsrmanvpc.sql -vpd rman12c

The script exits after execution which is why the code listed above shows two calls to sqlplus. The output of that script is shown below:


SYS @ blerble > @?/rdbms/admin/dbmsrmanvpc.sql -vpd rman

Checking the operating user... Passed

Granting VPD privileges to the owner of the base catalog schema RMAN

========================================
VPD SETUP STATUS:
VPD privileges granted successfully!
Connect to RMAN base catalog and perform UPGRADE CATALOG.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Once this is done for all catalog owners in the database it’s time to start upgrading catalogs. Normally executing UPGRADE CATALOG; will prompt for a second submission of that command to verify that is exactly what you want to do. To get around this the NOPROMPT option is available so UPGRADE CATALOG need be entered only once:


Recovery Manager: Release 12.2.0.1.0 - Production on Fri Oct 20 15:15:31 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RCATT (DBID=4052274215)
recovery catalog database Password:
connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RMAN database is too old

RMAN> upgrade catalog noprompt;

recovery catalog upgraded to version 12.02.00.01
DBMS_RCVMAN package upgraded to version 12.02.00.01
DBMS_RCVCAT package upgraded to version 12.02.00.01.

RMAN> exit


Recovery Manager complete.

This now needs to be done to the second (12.x) catalog, even if it’s at 12.2.0.1 so that the objects can be rebuilt.

A final script needs to be executed before the upgrade is actually completed:


SYS @ blerble > @?/rdbms/admin/dbmsrmanvpc.sql -all

Checking the operating user... Passed

----------------------------------------
Removing old VPC views in the base catalog of RMAN12...
----------------------------------------
Removing old VPC views in the base catalog of RMAN...
========================================
UPGRADE STATUS:
The VPC user schemas of these catalogs: RMAN12, RMAN
have been successfully upgraded to the new VPD model!

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

The final step is to log in as the 12.x catalog owner (in this case RMAN12) and enable the trigger that was disabled prior to the upgrade:


SQL> alter trigger VPC_CONTEXT_TRG enable;

Trigger altered.

SQL>

The database upgrade, along with the RMAN catalog upgrades, are complete. Verify this by logging in as a ‘regular’ user; the connection should be successful:


SQL> connect yerble
Enter password:
Connected.
SQL>

It may not be the easiest upgrade to perform but having the steps beforehand can certainly help.

And it’s much less annoying than that octopus.

October 16, 2017

Roll With It

Filed under: General — dfitzjarrell @ 11:07

"I know one thing for certain; it is much harder to tell whether you are lost than whether you were lost,
for, on many occasions, where you are going is exactly where you are. On the other hand, if you often find
that where you've been is not at all where you should have gone, and, since it's much more difficult to
find your way back from someplace you've never left, I suggest you go there immediately and then decide."
-- Norton Juster, The Phantom Tollbooth

Waiting is, well, inconvenient at times because it interferes with getting work done yet there are times when there is nothing to do, really, except wait. Of course this usually happens during an important update to a critical application table, when the transaction fails on error or, horror of horrors, the network ‘gives way’ and the session is disconnected, forcing Oracle to rollback all of that work you just did. Unfortunately that rollback seems to take forever to complete. The reason for that is that large, long-running transactions can generate large volumes of UNDO. And UNDO is how Oracle restores the data to its starting point should the transaction fail or be cancelled (which, by golly, just happened). This, of course, generates long rollback times that can seem interminable when you are waiting for the rollback to complete before other work can begin. But, monitoring the rollback progress isn’t difficult since V$TRANSACTION, in conjunction with V$SESSION, can report on the UNDO blocks that have yet to be applied. Let’s see how that can be done.

V$TRANSACTION records various pieces of information about active and cancelled transactions, with one of those pieces being the blocks of UNDO that are used (either allocated or applied, depending on whether the transaction is running or being rolled back). That information can be used to great advantage when monitoring the progress of a rollback. The queries used are shown below:


set echo on pagesize 100 linesize 132

select
case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
                                          else 'Not Rolling Back'
       end   as "Roll Status"
from v$transaction
where addr in (select taddr from v$session where username = upper('&&1'));

select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
from v$transaction t, v$session s
where t.addr = s.taddr
group by s.sid, s.serial#, s.client_info, t.addr;

The first query returns the rollback status of the transaction; the second returns the used UNDO blocks being generated by the transaction or read to undo the work that has already been done. The client_info column may or may not be populated (it isn’t in these examples) but it is provided to help differentiate different sessions an application user may have running so that the transaction or transactions of interest can be monitored. Looking at an example should help; a long-running transaction is in progress in session #1; looking at the UNDO blocks being allocated (from a second session) we see the USED_UBLK column increasing in value and that the transaction is not rolling back:


BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Not Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      30106                                                                  000007FF8EF7DC88              430

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Not Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      30106                                                                  000007FF8EF7DC88              739

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

no rows selected

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

no rows selected

BING @ quanghoo >
...
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Not Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      18316                                                                  000007FF8EF38688             6475

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Not Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      18316                                                                  000007FF8EF38688             6755

BING @ quanghoo >

At the time the transaction is cancelled the used UNDO block total was:


--
-- Transaction still active and generating UNDO
--
BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      18316                                                                  000007FF8EF38688             7914

BING @ quanghoo >

From the second session we can now monitor the progress of the rollback, using the same queries as before. This time the first query reports a rollback is in progress:


--
-- Transaction cancelled
--
-- Successive executions of these queries return a decreasing used undo block count
-- thus tracking the rollback progress
--
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      18316                                                                  000007FF8EF38688             5110

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133          0                                                                  000007FF8EF38688             3491

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133          0                                                                  000007FF8EF38688             1363

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133          0                                                                  000007FF8EF38688               70

BING @ quanghoo >

--
-- Transaction rollback complete
--
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

no rows selected

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

no rows selected

BING @ quanghoo >

The used UNDO block count eventually goes away (since the transaction is neither active nor rolling back) which signals the end of the rollback phase. Now other work can proceed against the table or tables involved in the previous transaction. And, having this data available usually makes the wait seem shorter as progress is registered for the rollback phase.

Waiting seems to be the hardest part, especially when no apparent progress can be monitored. Now, for rollbacks, a method is available to produce usable information regarding the progress of the rollback, which can make everyone’s life (at least for those who are waiting on a rollback to finish) a bit easier.

Which doesn’t make you lost, just … impatient.

July 24, 2017

That’s Not A Latch

Filed under: General — dfitzjarrell @ 08:22

"They all looked very much like the residents of any small valley to which you've never been."
-- Norton Juster, The Phantom Tollbooth

While investigating latches another interesting process popped up that’s smaller and lighter, that performs a similar function, called a mutex. So what is a mutex? By definition it’s a mutual exclusion object that locks memory structures to prevent concurrent sessions from changing the same object at the same time, so in at least one way a mutex is like a latch. [Operating systems also have a process called a mutex that behaves in a similar way.] In other ways it isn’t; let’s look into the mutex side of Oracle and see how they differ from latches.

A mutex is smaller than a latch but what does that mean? It’s a reference to the code path each must take to be executed; in terms of size code for a latch is around 110 bytes (which contains anywhere between 150 and 200 instructions) while the code for a mutex is a mere 28 bytes (having around 30 to 35 instructions), making a mutex faster to execute. Being smaller and lighter (gee, I’ve seen that phrase before …) a mutex won’t contain the level of information a latch contains; it won’t have data on who is waiting or how about the length of the wait, it won’t have information on requests and misses but there is sleep data available (how many, how long) and a couple of other details that can help monitor them.

Let’s look at what benefits a mutex can provide:

  • Less potential for false contention.
  • Replace both latches and pins.
  • Mutex structure is located in each child cursor.

Let’s look at each one of those benefits. First is the lower potential for false contention; a latch can protect multiple hash buckets so if two users were searching two different hash buckets the possibility exists that those two buckets could be protected by the same latch and, as a result, the two sessions would contend for access to that latch. With mutexes each hash bucket could easily have it’s own mutex for protection thus eliminating the contention to lock and access the bucket.

Second, mutexes can replace both latches and pins. A mutex can act as a serialization mechanism (just like a latch, restricting access to a single session) and also as a pin (to prevent an object from ‘aging out’ of the relevant cache). A mutex has both an exclusive and a shared mode, and in shared mode multiple sessions can access (using the term ‘reference’) the same mutex and the number of sessions currently referencing a given mutex is known as the ‘reference count’. That information is stored in the mutex. And, like a latch, a mutex can also be held in exclusive mode and the reference count of 1 could indicate an exclusive hold on that mutex. Additionally an object cannot be aged out of a cache until the mutex reference count is 0.

Third, the mutex structure is located in each child cursor handle with the mutex itself acting as the pin structure for the cursor. Changing the pin status is now simply a matter of changing the mutex reference count; this eliminates the need to get the library cache latch making the mutex even more efficient than the latch/pin combination.

So what information is available about mutexes and where can it be found? Oracle provides the V$MUTEX_SLEEP and V$MUTEX_SLEEP_HISTORY views that can be queried to get the following information:


SQL> desc v$mutex_sleep
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MUTEX_TYPE                                         VARCHAR2(32)
 LOCATION                                           VARCHAR2(40)
 SLEEPS                                             NUMBER
 WAIT_TIME                                          NUMBER
 CON_ID                                             NUMBER

SQL> desc v$mutex_sleep_history
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MUTEX_IDENTIFIER                                   NUMBER
 SLEEP_TIMESTAMP                                    TIMESTAMP(6)
 MUTEX_TYPE                                         VARCHAR2(32)
 GETS                                               NUMBER
 SLEEPS                                             NUMBER
 REQUESTING_SESSION                                 NUMBER
 BLOCKING_SESSION                                   NUMBER
 LOCATION                                           VARCHAR2(40)
 MUTEX_VALUE                                        RAW(8)
 P1                                                 NUMBER
 P1RAW                                              RAW(8)
 P2                                                 NUMBER
 P3                                                 NUMBER
 P4                                                 NUMBER
 P5                                                 VARCHAR2(64)
 CON_ID                                             NUMBER

SQL>

Querying V$MUTEX_SLEEP we see:


MUTEX_TYPE                       LOCATION                                     SLEEPS  WAIT_TIME     CON_ID
-------------------------------- ---------------------------------------- ---------- ---------- ----------
Library Cache                    kglllal3 111                                      5      21885       0
Library Cache                    kglllal1 109                                      1         78       0
Library Cache                    kglhdgn2 106                                     39     136235       0
Library Cache                    kglpnal1  90                                      5      46642       0
Library Cache                    kglhdgn1  62                                      5      35087       0
Library Cache                    kglscn1   18                                      3       9972       0
Library Cache                    kglpin1   4                                       1      25173       0
Library Cache                    kglget2   2                                       2      90986       0
Cursor Parent                    kkscsPruneChild [KKSPRTLOC35]                     4       8852       0
Cursor Parent                    kksfbc [KKSPRTLOC2]                               2      45124       0
Cursor Pin                       kkslce [KKSCHLPIN2]                             296     432125       0
Cursor Pin                       kksfbc [KKSCHLFSP2]                               2       1795       0

12 rows selected.

SQL>

MUTEX_TYPE indicates the action or object the mutex protects, and in the above example there are mutexes protecting library cache objects and acting as cursor pins. LOCATION is the code location where waiters slept for the indicated mutex. SLEEPS is the number of times a requestor had to sleep before getting the mutex and WAIT_TIME is, oddly enough, the wait time in microseconds. CON_ID is the container ID, which is only relevant in 12.1 and later releases; in 11.2 and earlier the CON_ID column does not exist.

V$MUTEX_SLEEP_HISTORY provides more information:


MUTEX_IDENTIFIER SLEEP_TIMESTAMP                                                             MUTEX_TYPE                             GETS     SLEEPS RE
QUESTING_SESSION BLOCKING_SESSION LOCATION                               MUTEX_VALUE              P1 P1RAW                    P2         P3         P4
 P5                                                                 CON_ID
---------------- --------------------------------------------------------------------------- -------------------------------- ---------- ---------- --
---------------- ---------------- ---------------------------------------- ---------------- ---------- ---------------- ---------- ---------- --------
-- ---------------------------------------------------------------- ----------
        71628033 10-JUL-17 07.00.07.021000 AM                                                Cursor Parent                    10          4
     6              133 kkscsPruneChild [KKSPRTLOC35]            0000008500000000         22 00                0          0          0
                                                         0
      3495269867 10-JUL-17 07.00.08.673000 AM                                                Cursor Pin                                1        101
           368               16 kkslce [KKSCHLPIN2]                      0000001000000000          2 00                        0          0          0
                                                                                 0
      2252038564 10-JUL-17 07.00.07.390000 AM                                                Library Cache                            90          1
           135              368 kglhdgn2 106                             0000017000000000          0 000007FF8810FDC0          0          0          0
                                                                                 0
      2252038564 10-JUL-17 07.00.07.390000 AM                                                Library Cache                            90          1
           133              368 kglhdgn2 106                             0000017000000000          0 000007FF8810FDC0          0          0          0
                                                                                 0
      4087692675 10-JUL-17 07.00.07.101000 AM                                                Library Cache                             7          4
             6              251 kglllal3 111                             000000FB00000000          0 000007FF86347838          0          0          0
                                                                                 0
      4039937844 10-JUL-17 07.00.08.581000 AM                                                Library Cache                            22          2
           251              135 kglhdgn2 106                             0000008700000000          0 000007FF86F0C1C0          0          0          0
                                                                                 0
      4039937844 10-JUL-17 07.00.08.581000 AM                                                Library Cache                            22          4
           249              135 kglhdgn2 106                             0000008700000000          0 000007FF86F0C1C0          0          0          0
                                                                                 0
      2912853027 10-JUL-17 07.00.07.277000 AM                                                Cursor Pin                                1         60
           135              368 kkslce [KKSCHLPIN2]                      0000017000000000          2 00                        0          0          0
                                                                                 0
      2912853027 10-JUL-17 07.00.07.277000 AM                                                Cursor Pin                                1         59
           133              368 kkslce [KKSCHLPIN2]                      0000017000000000          2 00                        0          0          0
                                                                                 0
      2034504591 10-JUL-17 07.00.01.555000 AM                                                Library Cache                             3          1
           123                6 kglhdgn2 106                             0000000600000000          0 000007FF86F64210          0          0          0
                                                                                 0
      3323840122 10-JUL-17 07.00.01.548000 AM                                                Library Cache                             3          1
           123                6 kglhdgn2 106                             0000000600000000          0 000007FF86F8B300          0          0          0
                                                                                 0
        71628033 10-JUL-17 07.00.06.980000 AM                                                Cursor Parent                     9          2
   133              364 kksfbc [KKSPRTLOC2]                      0000016C00000000         22 00                0          0          0
                                                         0
      1264706735 10-JUL-17 07.00.01.554000 AM                                                Library Cache                             3          1
             6              123 kglhdgn2 106                             0000007B00000000          0 000007FF86F68DD0          0          0          0
                                                                                 0
       746657336 10-JUL-17 07.00.06.893000 AM                                                Library Cache                            38          1
           364                0 kglpnal1  90                             00                        0 000007FF864AF268          0          0          0
                                                                                 0
...

Some of the same information in V$MUTEX_SLEEP also is present in v$MUTEX_SLEEP_HISTORY but WAIT_TIME is essentially replaced with SLEEP_TIMESTAMP and the number of gets is reported along with additional information on the requesting session and any blocking session for the mutex (Oracle collects that information at the time the mutex is requested; it’s not information stored internally in the mutex structure). ‘Internal use’ information will be found in the P1, P1RAW, P2, P3, P4 and P5 columns; P1, P2 and P3 can contain the hash value of the library cache objects under contention as well as other information. The PARAMETER1-3 columns in V$EVENT_NAME and the P1TEXT, P2TEXT and P3TEXT columns in V$SESSION_WAIT can shed light on what values those columns can hold and their meaning. Since such information is release-specific the following query can help identify what additional information may be available in the V$MUTEX_SLEEP_HISTORY view:


select name, parameter1, parameter2, parameter3
from v$event_name
where name in (select event from v$session_wait where (p1,p2,p3) in (select p1, p2, p3 from v$mutex_sleep_history));

The above query is provided to help identify possible additional data in the V$MUTEX_SLEEP_HISTORY view; as always you should test it on your own systems to see what results are provided.

Since it may not be obvious latches and mutexes are independent mechanisms; a process can hold both a latch and a mutex at the same time.

As of release 11.2.0.x the following latches have been replaced by mutexes:

  • library cache pin allocation
  • library cache lock allocation
  • library cache hash chains
  • library cache lock
  • library cache
  • library cache pin

The library cache waits listed below no longer exist in releases 11.2.0.x and later:

  • latch: library cache
  • latch: library cache lock
  • latch: library cache pin

As good as mutexes are there are still conditions, like excessive hard parsing and poor application cursor management (where cursors are closed after every execution and no cursor caching is in effect), that can cause hash collisions or continuous traversing of library cache hash chains and, thus, contention. On the whole, however, mutexes have improved concurrency and considerably reduced latch and pin contention.

Next time you don’t go somewhere you’ve never been, look carefully. You just might see something new.

July 12, 2017

Latch, What Latch?!?

Filed under: General — dfitzjarrell @ 13:04

"I know one thing for certain; it is much harder to tell whether you are lost than whether you were lost, 
for, on many occasions, where you are going is exactly where you are. On the other hand, if you often find
that where you've been is not at all where you should have gone, and, since it's much more difficult to
find your way back from someplace you've never left, I suggest you go there immediately and then decide."
-- Norton Juster, The Phantom Tollbooth

Latches can be, well, confusing. Sessions can wait for them, and wait for them, and when those waits become excessive (kind of like at the doctor’s office) performance degrades. To many they appear to be the same as locks, but they aren’t, even though lock waits, when the times get excessive, can also degrade performance. So, what are the differences between latches and locks? Let’s examine both and see where they differ, what can cause latch waits and how latch waits can be investigated.

Latches are similar to locks but they operate on memory to protect code and internal data structures by preventing concurrent access. For example the LRU latches are used when managing the buffer cache, an operation that is restricted to being run by a single process; other processes must wait for the current process to complete its actions on the buffer cache before the next one in line can proceed. The latch holds this structure for the current process to access; when the current process is done the latch is released and the next process in the queue can acquire it. Since only one process at a time can hold a latch ‘popular’ actions can create a long line of processes waiting to obtain the associated latch. Normally latch waits are in milliseconds or fractions of milliseconds so waiting for a latch usually isn’t noticeable.

Let’s look at the differences between a lock and a latch:

    Locks

  • Protect the logical contents of the database object (table, index) from other transactions.
  • Are held for the transaction duration.
  • Provide rollback capability for the associated transaction.
  • Latches

  • Protect the critical sections of the associated internal data structures from other threads.
  • Are held only until the operation completes and then are released.
  • Prevent concurrent access to a memory structure.

Since memory operations aren’t transactions the ability to ‘rollback’ a change isn’t necessary; clearing a buffer so another process can use it is a fast, irreversible operation that isn’t designed to be undone. Contrast that to a lock, which controls access to change data in a table; such actions are transactional in nature and are designed with the ability to undo the changes should the need arise. Locks can also protect a data image while it’s being read which gives rise to the different types of locks that are available — shared, row shared, row shared exclusive, exclusive — that govern who and how the data can be modified. Without exception every lock in an Oracle database allows other sessions to read data in the locked table; the locks are there to control concurrent updates and protect against the possibility of ‘lost’ transactions.

Latches can indicate the existence of a locking problem, as the ‘cursor pin s wait on x’ latch illustrates. This latch, when its wait times become long, usually indicates an issue with a ‘hot block’, a data or index block that is so popular everyone and their Aunt Gertrude are waiting for it. Long waits for this latch usually accompany concurrent sessions inserting data into a table using a sequence; the sequence values will be clustered together and will usually access the same data (for direct-path inserts) or index block and can cause sessions to wait until the current process completes. And since the latch allows one, and only one, process access to it at a time the rest of the interested transactions (and their processes) form a long queue, sort of like a really busy day at the amusement park. In such cases addressing the locking problem also addresses the latching problem.

Various tools are available to investigate latching issues; Tanel Poder has written two scripts, latchprof.sql and latchprofx.sql, that provide a wealth of information about latch wait history. They can be invaluable in investigating latch waits and contention. An example of the output that can be generated is shown below:


-- LatchProfX 2.00 by Tanel Poder ( http://www.tanelpoder.com )

  SID NAME                                HMODE        FUNC                                             OBJECT SQLID               Held       Gets  Held %     Held ms Avg hold ms
----- ----------------------------------- ------------ ---------------------------------------- -------------- ------------- ---------- ---------- ------- ----------- -----------
  144 transaction allocation              exclusive    ktcxbr                                                0 9brz3jsxmf4ts        788         57   78.80     267.920       4.700
  144 DML lock allocation                 exclusive    ktadmc                                                0 9brz3jsxmf4ts        609         59   60.90     207.060       3.509
 1168 shared pool                         exclusive    kghalo                                                0 0zmr8ccx3xpck        177          5   17.70      60.180      12.036
 1562 cache buffers lru chain             exclusive    kcbzgws                                               0 8n2dsqpu94h4a        119          7   11.90      40.460       5.780
 1562 cache buffers chains                exclusive    kcbgtcr: slow path excl                        2403D825 8n2dsqpu94h4a         90          1    9.00      30.600      30.600
 1562 cache buffers lru chain             exclusive    kcbzgws                                               0 gt8xcw52s32ha         40         16    4.00      13.600        .850
 2794 redo copy                           exclusive    kcrfw_redo_gen: nowait                                0 4vs91dcv7u1p6         38          2    3.80      12.920       6.460
 2328 KTILM latch                         shared       ktilmht get                                           0 bnqcqzztx3a41         37          4    3.70      12.580       3.145
 2794 sim partition latch                 exclusive    kcbm_simulate                                         0 4vs91dcv7u1p6         37          1    3.70      12.580      12.580
 1562 KTILM latch                         shared       ktilmht get                                           0 8n2dsqpu94h4a         30          6    3.00      10.200       1.700
 2328 cache buffers chains                exclusive    kcbgcur: fast path excl                          87316B 9zg9qd9bm4spu         26          1    2.60       8.840       8.840
 2412 row cache objects                   exclusive    kqrso                                                 0                       24         15    2.40       8.160        .544
 1413 cache buffers chains                exclusive    kcbgcur: fast path excl                          873294 9zg9qd9bm4spu         22          1    2.20       7.480       7.480
 2412 row cache objects                   exclusive    kqrpre: find obj                                      0                       21         11    2.10       7.140        .649
 1168 row cache objects                   exclusive    kqrpre: find obj                                      0 0zmr8ccx3xpck         20         10    2.00       6.800        .680
 2328 row cache objects                   exclusive    kqrso                                                 0                       20         13    2.00       6.800        .523
 1168 cache buffers chains                exclusive    kcbgcur: fast path excl                          873294 9zg9qd9bm4spu         19          1    1.90       6.460       6.460
 2328 row cache objects                   exclusive    kqrpre: find obj                                      0 5wwkp7spyq2fn         19         11    1.90       6.460        .587
 2412 cache buffers chains                exclusive    kcbgcur: fast path excl                          87260B 9zg9qd9bm4spu         18          1    1.80       6.120       6.120
 1562 KTILM latch                         shared       ktilmht get                                           0 gt8xcw52s32ha         18          5    1.80       6.120       1.224
 1413 cache buffers lru chain             exclusive    kcbzgb                                                0 9zg9qd9bm4spu         18          1    1.80       6.120       6.120
 2794 row cache objects                   exclusive    kqrso                                                 0                       17         13    1.70       5.780        .445
 1168 cache buffers lru chain             exclusive    kcbzgb                                                0 9zg9qd9bm4spu         17          1    1.70       5.780       5.780
 2328 row cache objects                   exclusive    kqrpre: find obj                                      0                       17         10    1.70       5.780        .578
 2328 shared pool                         exclusive    kghalo                                                0                       15          7    1.50       5.100        .729
 2794 row cache objects                   exclusive    kqrpre: find obj                                      0                       14         12    1.40       4.760        .397
 2412 shared pool                         exclusive    kghalo                                                0                       14          4    1.40       4.760       1.190
 2794 row cache objects                   exclusive    kqrpre: find obj                                      0 4k3y3hrxfzh6v         14          8    1.40       4.760        .595
 2794 shared pool                         exclusive    kghalo                                                0 4k3y3hrxfzh6v         13          6    1.30       4.420        .737
 1562 cache buffers lru chain             exclusive    kcbzgws                                               0 149htpw5s5n0c         13          5    1.30       4.420        .884
 2412 row cache objects                   exclusive    kqreqd                                                0                       13          9    1.30       4.420        .491
 2794 row cache objects                   exclusive    kqrso                                                 0 4k3y3hrxfzh6v         11          9    1.10       3.740        .416
 1562 KTILM latch                         shared       ktilmht get                                           0 by143mnp65wgp         10          2    1.00       3.400       1.700
 1562 cache buffers chains                exclusive    kcbzgb: scan from tail. nowait                        0 gt8xcw52s32ha         10          8    1.00       3.400        .425
 2794 cache buffers chains                exclusive    kcbgtcr: slow path excl                          400276 4k3y3hrxfzh6v          9          1     .90       3.060       3.060
 2794 cache buffers lru chain             exclusive    kcbzgws                                               0 4k3y3hrxfzh6v          9          1     .90       3.060       3.060
 2412 row cache objects                   exclusive    kqrpre: find obj                                      0 5wwkp7spyq2fn          9          7     .90       3.060        .437
 2412 simulator lru latch                 exclusive    kcbs_simulate: simulate set                          34 5wwkp7spyq2fn          9          1     .90       3.060       3.060
 2328 row cache objects                   exclusive    kqreqd                                                0                        9          6     .90       3.060        .510
 1168 cache buffers chains                exclusive    kcbgtcr: slow path excl                          402106 0zmr8ccx3xpck          9          1     .90       3.060       3.060
 1562 object queue header operation       exclusive    kcbo_link_q                                           0 gt8xcw52s32ha          9          6     .90       3.060        .510
 2328 KTILM latch                         shared       ktilmht get                                           0 4twyxstjxggc1          9          2     .90       3.060       1.530
 1168 cache buffers lru chain             exclusive    kcbzgws                                               0 0zmr8ccx3xpck          9          1     .90       3.060       3.060
  144 simulator lru latch                 exclusive    kcbs_simulate: simulate set                          3C 9brz3jsxmf4ts          9          1     .90       3.060       3.060
 1168 KTILM latch                         shared       ktilmht get                                           0 24kkd5u3fdf0c          9          1     .90       3.060       3.060
 2328 simulator lru latch                 exclusive    kcbs_simulate: simulate set                          3A 24kkd5u3fdf0c          9          1     .90       3.060       3.060
 2412 simulator lru latch                 exclusive    kcbs_simulate: simulate set                          39 5wwkp7spyq2fn          8          1     .80       2.720       2.720
 2412 cache buffers chains                maybe-shared kcbgtcr: fast path exam                          4003C1 5wwkp7spyq2fn          8          1     .80       2.720       2.720
 2412 cache buffers lru chain             exclusive    kcbzgb                                                0 9zg9qd9bm4spu          8          1     .80       2.720       2.720
 2328 cache buffers lru chain             exclusive    kcbzgb                                                0 9zg9qd9bm4spu          8          1     .80       2.720       2.720

In this example the latchprofx.sql script is being called; it reports the SID of the holding session, the name of the latch, the mode in which the latch is held (notice that almost all of the latches reported are exclusive), the associated kernel function, the object involved (for latches associated with transactional activity), the number of times the latch has been held, the number of times the latch has been gotten successfully, the percentage of gets to holds, the total hold time in milliseconds and the average hold time, again in milliseconds. The report is orderd by the the HELD values to make it easier to see which latches are being held and for how long. Other values can be output depending upon the parameters passed to the script.

As latches control access to memory structures anything that affects how memory is allocated and used can affect them. It’s possible to run afoul of latch contention when memory ‘leaks’ occur (usually caused by code that doesn’t properly manage the memory structures it uses). Common memory ‘leaks’ involve memory areas that are no longer used but are no longer accessible because the code that created the memory structure didn’t free it properly. As available memory slowly decreases from these leaks it can become more difficult to obtain a latch, resulting in queues that are much longer than they would normally be. Another problem that can create the same situation is remote user sessions that ‘die’ at the client but can’t clean up properly, leaving orphan processes holding latches that can’t be released. How such situations are managed is dependent upon the specific conditions surrounding them and resolution in extreme cases could require drastic action. Since these are usually rare occurrences these won’t be discussed further.

AWR and ASH reports are a good place to start when investigating wait activity and knowing whether the waits are lock or latch related; of course those reports require the Diagnostic and Tuning pack license and may not be available if your site isn’t licensed. Statspack reports are also good sources of information to start an investigation and list latch activity in a dedicated report section; a part of that section is reproduced below:


Latch Activity  DB/Inst: FNORG/fnorg  Snaps: 1-2
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
  willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0

                                           Pct    Avg   Wait                 Pct
                              Get          Get   Slps   Time       NoWait NoWait
Latch                       Requests      Miss  /Miss    (s)     Requests   Miss
------------------------ -------------- ------ ------ ------ ------------ ------
AQ Background: interrupt              1    0.0             0            0
AQ deq hash table latch               1    0.0             0            0
ASM db client latch                   5    0.0             0            0
ASM map operation hash t              1    0.0             0            0
Change Notification Hash              3    0.0             0            0
Consistent RBA                       40    0.0             0            0
DML lock allocation                 706    0.0             0            0
Event Group Locks                     1    0.0             0            0
File State Object Pool P              1    0.0             0            0
I/O Staticstics latch                 1    0.0             0            0
ILM Stats Stripe Latch                1    0.0             0            0
ILM Stats main anchor la            155    0.0             0            0
ILM access tracking exte              1    0.0             0            0
ILM activity tracking la              1    0.0             0            0
IM area sb latch                      1    0.0             0            0
IM area scb latch                     1    0.0             0            0
IM emb latch                          1    0.0             0            0
IM seg hdr latch                      1    0.0             0            0
IPC stats buffer allocat              1    0.0             0            0
In memory undo latch                  1    0.0             0            0
JS Sh mem access                      1    0.0             0            0
JS queue access latch                 1    0.0             0            0
JS queue state obj latch             84    0.0             0            0
JS slv state obj latch                1    0.0             0            0
KCNIBR - invalid block r              1    0.0             0            0
KFC FX Hash Latch                     1    0.0             0            0
KFC Hash Latch                        1    0.0             0            0
KFCL LE Freelist                      1    0.0             0            0
KGNFS-NFS:SHM structure               1    0.0             0            0
KGNFS-NFS:SVR LIST                    1    0.0             0            0
KJC message pool free li              1    0.0             0            0
KJC message pool pending              1    0.0             0            0
KJCT flow control latch               1    0.0             0            0
KMG MMAN ready and start              2    0.0             0            0
KSFS OFS ctx level paren              1    0.0             0            0
KSFS OFS req layer paren              1    0.0             0            0
KSFS OFS sess layer pare              1    0.0             0            0
KSFS id table parent lat              1    0.0             0            0
KSFS ksfs_node latch                  1    0.0             0            0
KSFSD name cache parent               1    0.0             0            0
KSXR hang analysis phase              1    0.0             0            0
KSXR hang analysis phase              1    0.0             0            0
KTF sga latch                         0                    0            1    0.0
KTILM latch                           1    0.0             0            0
KWQP Prop Status                      2    0.0             0            0
Locator state objects po              1    0.0             0            0
Lsod array latch                      1    0.0             0            0
Memory Management Latch               1    0.0             0            2    0.0
Memory Queue                          1    0.0             0            0
Memory Queue Message Sub              1    0.0             0            0
...

Latch contention occurs with the ‘willing to wait’ latch calls; nowait calls won’t build a queue and will be retried at a later time.

Knowing the difference between a latch and a lock, and that latches can be affected by locks (but in most cases they are not) can make troubleshooting application and database performance a bit easier. Since latches are memory control structures they can be affected by the amount of available memory as well as by the overall activity of the database. Keeping this in mind during such investigations can prevent the DBA from being led into trying to address a lock problem when it’s really a latch affecting performance. Also, having the right tools available can greatly assist in determining where the problem area or areas lie.

Yes, latches can be confusing. Hopefully this has provided guidance and insight into what latches are, how they can affect performance and what tools to use to find additional information.

Now, go back where you weren’t.

June 19, 2017

Extended Engagement

Filed under: General — dfitzjarrell @ 12:03

"You see, to tall men I'm a midget, and to short men I'm a giant; 
 to the skinny ones I'm a fat man, and to the fat ones I'm a thin man."
-- Norton Juster, The Phantom Tollbooth

Oracle offers, in release 12.1.0.2, the option of increasing the maximum string length from the standard 4000 to a whopping 32767; depending on the nls_length_semantics setting this could be in bytes or in characters. When nls_length_semantics is set to BYTE then the traditional behavior can be expected, that is when VARCHAR2 fields are declared longer than 4000 bytes there is an issue when creating indexes, especially for unique or primary key constraints. The following error can be thrown:


ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

The three factors that affect this behavior are the overall length of the index key, the nls_length_semantics setting and the block size of the database or tablespace in use. The issue arises when the total length of the VARCHAR2 key columns exceeds a pre-determined length, dependent upon the nls_length_semantics of the database or session, the block size and the database characterset in use. The following example illustrates these limits under both length semantics conditions using the AL32UTF8 characterset, a characterset where one character can consume up to four bytes. The example creates the same table with differing definitions and attempts to create a unique constraint on the table. For byte-length semantics the following results were obtained with an 8k block size in use. Multiple tests were run varying the VARCHAR2 lengths of all columns in the constraint/index, which may seem repitious but it more clearly illustrates the issue:


SQL> 
SQL> --
SQL> -- Create tables with varying length columns
SQL> -- to determine the length limit when
SQL> -- VARCHAR2(32767) is configured
SQL> --
SQL> -- Byte-length semantics
SQL> --
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(6347)  not null,
  4  stringm	     varchar2(20) ,
  5  stringz	     varchar2(20)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(6348)  not null,
  4  stringm	     varchar2(20) ,
  5  stringz	     varchar2(20)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(6327)  not null,
  4  stringm	     varchar2(30) ,
  5  stringz	     varchar2(30)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(6328)  not null,
  4  stringm	     varchar2(30) ,
  5  stringz	     varchar2(30)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(6307)  not null,
  4  stringm	     varchar2(40) ,
  5  stringz	     varchar2(40)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(6308)  not null,
  4  stringm	     varchar2(40) ,
  5  stringz	     varchar2(40)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(6287)  not null,
  4  stringm	     varchar2(50) ,
  5  stringz	     varchar2(50)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(6288)  not null,
  4  stringm	     varchar2(50) ,
  5  stringz	     varchar2(50)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 

Notice in all cases the maximum key length calculates to 6387 bytes which means that the sum of the declared lengths of all VARCHAR2 columns to be included in a given index cannot exceed that maximum. This changes dramatically when CHAR length semantics are in use, again with the AL32UTF8 character set:


SQL> 
SQL> --
SQL> -- Now set length semantics to CHAR
SQL> --
SQL> -- Using AL32UTF8 character set the VARCHAR2
SQL> -- lengths reduce to 1/4 of those for BYTE
SQL> -- semantics
SQL> --
SQL> alter session set nls_length_semantics=CHAR;

Session altered.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1576)  not null,
  4  stringm	     varchar2(10) ,
  5  stringz	     varchar2(10)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1577)  not null,
  4  stringm	     varchar2(10) ,
  5  stringz	     varchar2(10)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1556)  not null,
  4  stringm	     varchar2(20) ,
  5  stringz	     varchar2(20)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1557)  not null,
  4  stringm	     varchar2(20) ,
  5  stringz	     varchar2(20)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1536)  not null,
  4  stringm	     varchar2(30) ,
  5  stringz	     varchar2(30)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1537)  not null,
  4  stringm	     varchar2(30) ,
  5  stringz	     varchar2(30)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1516)  not null,
  4  stringm	     varchar2(40) ,
  5  stringz	     varchar2(40)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1517)  not null,
  4  stringm	     varchar2(40) ,
  5  stringz	     varchar2(40)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1496)  not null,
  4  stringm	     varchar2(50) ,
  5  stringz	     varchar2(50)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1497)  not null,
  4  stringm	     varchar2(50) ,
  5  stringz	     varchar2(50)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1476)  not null,
  4  stringm	     varchar2(60) ,
  5  stringz	     varchar2(60)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1477)  not null,
  4  stringm	     varchar2(60) ,
  5  stringz	     varchar2(60)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1456)  not null,
  4  stringm	     varchar2(70) ,
  5  stringz	     varchar2(70)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1457)  not null,
  4  stringm	     varchar2(70) ,
  5  stringz	     varchar2(70)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1436)  not null,
  4  stringm	     varchar2(80) ,
  5  stringz	     varchar2(80)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);

Table altered.

SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 
SQL> create table hashtest(
  2  id 	     number primary key,
  3  stringtst	     varchar2(1437)  not null,
  4  stringm	     varchar2(80) ,
  5  stringz	     varchar2(80)  not null);

Table created.

SQL> 
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded 


SQL> 
SQL> drop table hashtest purge;

Table dropped.

SQL> 

[Again multiple examples were configured to more clearly illustrate the problem.] The maximum length now becomes 1596, and remember that refers to characters; the overall byte length is three bytes shorter than when nls_length_semantics was set to BYTE this is due to the 1 character = 4 bytes conversion for the AL32UTF8 character set. Such behavior is repeated for any of the UTF-8 family of character sets as they all map one character to up to four bytes, and Oracle defaults to the 1:4 conversion for these character sets regardless of the actual storage a character may consume. These limitations present themselves when the database block size is 8192, or 8K; as stated previously the block size does influence when the limit is reached and larger block sizes will result in longer keys and a slightly different error message. The following results are generated using a 16k block size tablespace (the maximum block size for a Windows PC environment):


SQL>
SQL> --
SQL> -- Create tables with varying length columns
SQL> -- to determine the length limit when
SQL> -- VARCHAR2(32761) is configured
SQL> --
SQL> -- Byte-length semantics
SQL> --
SQL> create table hashtest(
  2  id              number primary key,
  3  stringtst       varchar2(12907)  not null,
  4  stringm         varchar2(20) ,
  5  stringz         varchar2(20)  not null) tablespace yerg_16k;

Table created.

SQL>
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz)
  3  using index (
  4  create unique index hashtest_uq_idx on hashtest(stringtst, stringm, stringz)
  5  tablespace yerg_16k);

Table altered.

SQL>
SQL> drop table hashtest purge;

Table dropped.

SQL>
SQL> create table hashtest(
  2  id              number primary key,
  3  stringtst       varchar2(12908)  not null,
  4  stringm         varchar2(20) ,
  5  stringz         varchar2(20)  not null) tablespace yerg_16k;

Table created.

SQL>
SQL> alter table hashtest add constraint
  2  hashtest_uq unique (stringtst, stringm, stringz)
  3  using index (
  4  create unique index hashtest_uq_idx on hashtest(stringtst, stringm, stringz)
  5  tablespace yerg_16k);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (12958) exceeded


SQL>
SQL> drop table hashtest purge;

Table dropped.

SQL>

One way to prevent this is to keep track of the total VARCHAR2 length; when using BYTE semantics this must not exceed 6387 for an 8K block size and it must not exceed 12947 for a 16K block size. Of course adding up all of the VARCHAR2 lengths could take some time when a large number of columns are included in an index (not a wise idea to begin with) and indexing long VARCHAR2 columns may not be very helpful (which depends on the data they contain). Another way around this dilemma is to modify the table in question to create virtual columns based upon the standard hash values of the longer VARCHAR2 columns, then using those virtual columns in the unique index/constraint. An example of this technique follows:


SQL>
SQL> --
SQL> -- Create a table with VARCHAR2 columns in the extended
SQL> -- length range
SQL> --
SQL> create table longstrings (id number, text varchar2(32000), moretext varchar2(32000), smalltxt varchar2(20));

Table created.

SQL>
SQL> --
SQL> -- Create virtual columns using the long text columns
SQL> -- as the source
SQL> --
SQL> -- Use the standard_hash function to ensure the key length
SQL> -- will not be excessive
SQL> --
SQL> alter table longstrings add (text_hash as (standard_hash(text)), moretext_hash as (standard_hash(moretext)));

Table altered.

SQL>
SQL> --
SQL> -- Use these virtual columns in the unique key/index structure
SQL> --
SQL> alter table longstrings add constraint longstrings_text_unq unique (text_hash, moretext_hash, smalltxt);

Table altered.

SQL>
SQL> --
SQL> -- Try to populate the table with non-unique data
SQL> --
SQL> -- The constraint works as expected and the inserts
SQL> -- eventually fail
SQL> --
SQL> begin
  2     for i in 1..10000 loop
  3             insert into longstrings(id, text, moretext, smalltxt)
  4             values (i, 'Borscht '||mod(i,9), 'BorschtBorscht '||mod(i,13), 'Hubba');
  5     end loop;
  6     commit;
  7  end;
  8  /
begin
*
ERROR at line 1:
ORA-00001: unique constraint (BING.LONGSTRINGS_TEXT_UNQ) violated
ORA-06512: at line 3


SQL>

Describing the table after the virtual columns are created reveals:


SQL> desc longstrings
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 TEXT                                               VARCHAR2(32000)
 MORETEXT                                           VARCHAR2(32000)
 SMALLTXT                                           VARCHAR2(20)
 TEXT_HASH                                          RAW(20)
 MORETEXT_HASH                                      RAW(20)

SQL>

Each of the long VARCHAR2 columns are reduced to a 20-byte hash of the original values, allowing Oracle to create constraints in situations where this would not normally be possible.

Looking at how the virtual columns are defined reveals that no additional triggers or processing will be necessary to populate them when inserts are processed:


SQL> alter table longstrings add (text_hash as (standard_hash(text)), moretext_hash as (standard_hash(moretext)));

The standard_hash function will be applied to all values of text and moretext at the time the insert is processed, populating the index columns ensuring the constraint will function properly. As a consequence of this inserts will need to specify all but the _hash columns in the insert list:


insert into longstrings(id, text, moretext, smalltxt) …

Additionally any forms, code or web pages that return data from tables modified in this manner will also need to list the columns to be returned, which would exclude any _hash named columns. This prevents forms from failing from the following error:


SQL> declare
  2     v_id    longstrings.id%type;
  3     v_text  longstrings.text%type;
  4     v_mretxt longstrings.moretext%type;
  5     v_smlltxt longstrings.smalltxt%type;
  6
  7     cursor getall is
  8     select * From longstrings
  9     where rownum <=12;
 10  begin
 11     open getall;
 12     loop
 13             fetch getall into v_id, v_text, v_mretxt, v_smlltxt;
 14             exit when getall%notfound;
 15
 16             dbms_output.put_line(v_id||':'||v_text||':'||v_mretxt||':'||v_smlltxt);
 17     end loop;
 18  end;
 19  /
                fetch getall into v_id, v_text, v_mretxt, v_smlltxt;
                *
ERROR at line 13:
ORA-06550: line 13, column 3:
PLS-00394: wrong number of values in the INTO list of a FETCH statement
ORA-06550: line 13, column 3:
PL/SQL: SQL Statement ignored


SQL>

Implied select lists like those generated by ‘select * from …’ queries can no longer be used in situations illustrated in the above example after such table modifications have been made since the returned list of values is greater than the number of variables declared to hold such values. Of course a PL/SQL for loop and ‘select * …’ cursors can be used since it’s necessary to append the column name to the cursor variable to return the selected value:


SQL> declare
  2     cursor getall is
  3     select * from longstrings
  4     where rownum <=10;
  5  begin
  6  for i in 1..10000 loop
  7          insert into longstrings(id, text, moretext, smalltxt)
  8          values (i, 'Borscht '||i, 'BorschtBorscht '||i, 'Hubba');
  9  end loop;
 10  commit;
 11     for ls in getall loop
 12             dbms_output.put_line(ls.id||'   '||ls.text||'   '||ls.moretext||'  '||ls.smalltxt);
 13     end loop;
 14  end;
 15  /
1   Borscht 1   BorschtBorscht 1  Hubba
2   Borscht 2   BorschtBorscht 2  Hubba
3   Borscht 3   BorschtBorscht 3  Hubba
4   Borscht 4   BorschtBorscht 4  Hubba
5   Borscht 5   BorschtBorscht 5  Hubba
6   Borscht 6   BorschtBorscht 6  Hubba
7   Borscht 7   BorschtBorscht 7  Hubba
8   Borscht 8   BorschtBorscht 8  Hubba
9   Borscht 9   BorschtBorscht 9  Hubba
10   Borscht 10   BorschtBorscht 10  Hubba

PL/SQL procedure successfully completed.

SQL>

Yet ANOTHER way around this limitation, if constraints are not being created, is to apply the standard_hash() function to the column or columns directly in the index definition:


SQL> --
SQL> -- Create a table with VARCHAR2 columns in the extended
SQL> -- length range
SQL> --
SQL> create table longstrings (id number, text varchar2(32000), moretext varchar2(32000), smalltxt varchar2(20));

Table created.

SQL> 
SQL> --
SQL> -- Create index using the extended string length columns
SQL> -- as the source
SQL> --
SQL> create index longstrings_idx on longstrings(standard_hash(text), standard_hash(moretext), smalltxt);

Index created.

SQL> 
SQL> --
SQL> -- Populate the table
SQL> --
SQL> begin
  2  	for i in 1..10000 loop
  3  		insert into longstrings(id, text, moretext, smalltxt)
  4  		values (i, 'Borscht '||mod(i,9), 'BorschtBorscht '||mod(i,13), 'Hubba');
  5  	end loop;
  6  	commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Select using the index
SQL> --
SQL> -- Works for equality queries only
SQL> --
SQL> set autotrace on linesize 132
SQL> column text format a45
SQL> column moretext format a45
SQL> 
SQL> select *
  2  from longstrings
  3  where text = 'Borscht 7';

        ID TEXT                                          MORETEXT                                      SMALLTXT                     
---------- --------------------------------------------- --------------------------------------------- --------------------         
       313 Borscht 7                                     BorschtBorscht 1                              Hubba                        
        79 Borscht 7                                     BorschtBorscht 1                              Hubba                        
       196 Borscht 7                                     BorschtBorscht 1                              Hubba                        
       781 Borscht 7                                     BorschtBorscht 1                              Hubba                        
       898 Borscht 7                                     BorschtBorscht 1                              Hubba                        
       430 Borscht 7                                     BorschtBorscht 1                              Hubba                        
       547 Borscht 7                                     BorschtBorscht 1                              Hubba                        
       664 Borscht 7                                     BorschtBorscht 1                              Hubba                        
      1249 Borscht 7                                     BorschtBorscht 1                              Hubba                        
      1366 Borscht 7                                     BorschtBorscht 1                              Hubba                        
      1015 Borscht 7                                     BorschtBorscht 1                              Hubba                        
...
      6874 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      6991 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      7693 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      7810 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      7342 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      7459 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      7576 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      8278 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      7927 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      8044 Borscht 7                                     BorschtBorscht 10                             Hubba                        
      8161 Borscht 7                                     BorschtBorscht 10                             Hubba                        

1111 rows selected.


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 1813482573                                                                                                         
                                                                                                                                    
-------------------------------------------------------------------------------------------------------                             
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |                             
-------------------------------------------------------------------------------------------------------                             
|   0 | SELECT STATEMENT                    |                 |   829 |    25M|     1   (0)| 00:00:01 |                             
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| LONGSTRINGS     |   829 |    25M|     1   (0)| 00:00:01 |                             
|*  2 |   INDEX RANGE SCAN                  | LONGSTRINGS_IDX |    30 |       |     1   (0)| 00:00:01 |                             
-------------------------------------------------------------------------------------------------------                             
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT"='Borscht 7' AND                                                                  
              INTERNAL_FUNCTION("LONGSTRINGS"."MORETEXT"))                                                                          
   2 - access(STANDARD_HASH("TEXT")=HEXTORAW('D55F75A7098D2100B35FEF4A344FDC64A4FFF29E'))                                           
                                                                                                                                    

SQL>

Range queries unfortunately use a table scan:

 
SQL> --
SQL> -- Try using a range
SQL> --
SQL> -- Generates tablescan
SQL> --
SQL> select *
  2  from longstrings
  3  where text between 'Borscht 6' and 'Borscht 8';

        ID TEXT                                          MORETEXT                                      SMALLTXT                     
---------- --------------------------------------------- --------------------------------------------- --------------------         
       214 Borscht 7                                     BorschtBorscht 6                              Hubba                        
       215 Borscht 8                                     BorschtBorscht 7                              Hubba                        
       222 Borscht 6                                     BorschtBorscht 1                              Hubba                        
       223 Borscht 7                                     BorschtBorscht 2                              Hubba                        
       224 Borscht 8                                     BorschtBorscht 3                              Hubba                        
       231 Borscht 6                                     BorschtBorscht 10                             Hubba                        
       232 Borscht 7                                     BorschtBorscht 11                             Hubba                        
       233 Borscht 8                                     BorschtBorscht 12                             Hubba                        
       240 Borscht 6                                     BorschtBorscht 6                              Hubba                        
       241 Borscht 7                                     BorschtBorscht 7                              Hubba                        
       242 Borscht 8                                     BorschtBorscht 8                              Hubba                        
...
      9484 Borscht 7                                     BorschtBorscht 7                              Hubba                        
      9485 Borscht 8                                     BorschtBorscht 8                              Hubba                        
      9492 Borscht 6                                     BorschtBorscht 2                              Hubba                        
      9493 Borscht 7                                     BorschtBorscht 3                              Hubba                        
      9494 Borscht 8                                     BorschtBorscht 4                              Hubba                        
      9501 Borscht 6                                     BorschtBorscht 11                             Hubba                        
      9502 Borscht 7                                     BorschtBorscht 12                             Hubba                        
      9503 Borscht 8                                     BorschtBorscht 0                              Hubba                        
      9510 Borscht 6                                     BorschtBorscht 7                              Hubba                        
      9511 Borscht 7                                     BorschtBorscht 8                              Hubba                        
      9512 Borscht 8                                     BorschtBorscht 9                              Hubba                        

3333 rows selected.


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 411405923                                                                                                          
                                                                                                                                    
---------------------------------------------------------------------------------                                                   
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                   
---------------------------------------------------------------------------------                                                   
|   0 | SELECT STATEMENT  |             |  2471 |    75M|   163   (0)| 00:00:01 |                                                   
|*  1 |  TABLE ACCESS FULL| LONGSTRINGS |  2471 |    75M|   163   (0)| 00:00:01 |                                                   
---------------------------------------------------------------------------------                                                   
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT">='Borscht 6' AND                                                                 
              "TEXT"<='Borscht 8' AND INTERNAL_FUNCTION("LONGSTRINGS"."MORETEXT"))                                                  
                                                                                                                                    

SQL> 

A caveat of this method is the index created works very well for equality predicates but fails with inequality and IN constructs so using this technique may not provide the performance desired if the queries against the table aren’t using equality predicates.

The method chosen to address such issues depends on the length of the VARCHAR2 columns involved; VARCHAR2 columns declared that are longer than 4000 characters (the standard default from Oracle) will need to have virtual columns created using the standard_hash() function to ensure the key length won’t exceed 80% of the usable space based on the block size; for 8K blocks there will be 7998 bytes available , and for 16K blocks there will be 16198 bytes available (the remaining space is the overhead for block management). How the nls_length_semantics parameter is configured for your database will make a difference when deciding how to proceed with index and constraint creation should the extended string length option be chosen.

The bigger they are …

May 24, 2017

The BLOB

Filed under: General — dfitzjarrell @ 10:16

"Have you ever heard a blindfolded octopus unwrap a cellophane-covered bathtub?"
-- Norton Juster, The Phantom Tollbooth

An interesting question was posted on the Oracle General Database forums fairly recently, inquiring about creating a B-Tree index on a BLOB column to speed queries looking for NULLs in that BLOB. The conventional wisdom states that, because it’s a BLOB, NULLs shouldn’t be present. And because it’s a BLOB, containing binary data, creating such an index shouldn’t be possible, but through the magic of Oracle (and a possible bug) that ‘wisdom’ has been proven wrong. Let’s look at that situation and see how someone can make an ‘end run’ around Oracle and do the seemingly impossible.

For those who don’t use them, or who may not be familiar with the term, a BLOB is a Binary Large OBject, a datatype that stores, well, binary data. As such there should be no NULL values in binary data (it’s all 0s and 1s and the character(?) associated with NULL has a binary representation of 0 [looking at an ASCII table]) so the benefit of having such an index is questionable, at best. It IS possible to set a BLOB to NULL, however, as the following example proves:


SQL> update blobbo set yorm = null;

1 row updated.

SQL>

Even with that it can make someone wonder what actual benefit a B-Tree index can provide on binary data. Looking at the complete example that was run may provide some additional insight. It begins, simply enough, by creating a table to ‘experiment on’ and then trying to create a B-Tree index on all columns, including the BLOB:


SQL> 
SQL> 
SQL> set echo on linesize 150 pagesize 60
SQL> 
SQL> create table blobbo(
  2  yorm    blob,
  3  qwert   number default 4);

Table created.

SQL> 

SQL> create index blobbo_blob_ix on blobbo(yorm, qwert);
create index blobbo_blob_ix on blobbo(yorm, qwert)
                                      *
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB 


SQL> 

The initial attempt to create the index failed, even with the non-BLOB column specified. But, one can create a B-Tree index by specifying a constant value along with the column name, which is the workaround for non-BLOB columns to get NULLs into the index [since the entire key will not be NULL] so let’s try that avenue of attack:


SQL> 
SQL> create index blobbo_blob_ix on blobbo(yorm, 'X');

Index created.

SQL> 

And we have a B-Tree index on a BLOB. As shown above the BLOB can be set to NULL by assignment:


SQL> 
SQL> insert into blobbo(yorm) values(empty_blob());

1 row created.

SQL> 
SQL> update blobbo set yorm = null;

1 row updated.

SQL> 

but it’s not common, and not likely, that a BLOB column will contain any NULL values (again by the nature of the data stored within).

Since a NULL and the LOB locator established by using the empty_blob() function look the same to the naked eye it’s difficult to tell if the query output shows a NULL exists:


SQL> 
SQL> select * from blobbo;

YORM                                                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------
     QWERT                                                                                                                                            
----------                                                                                                                                            
                                                                                                                                                      
         4                                                                                                                                            
                                                                                                                                                      

SQL> 

The execution plan generated by the optimizer shows what is to be expected, a full table scan for the unqualified query:


SQL> 
SQL> select * from table(dbms_xplan.display_Cursor());

PLAN_TABLE_OUTPUT                                                                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4gcbmd14ap73j, child number 0                                                                                                                 
-------------------------------------                                                                                                                 
select * from blobbo                                                                                                                                  
                                                                                                                                                      
Plan hash value: 925165977                                                                                                                            
                                                                                                                                                      
----------------------------------------------------------------------------                                                                          
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                                          
----------------------------------------------------------------------------                                                                          
|   0 | SELECT STATEMENT  |        |       |       |     3 (100)|          |                                                                          
|   1 |  TABLE ACCESS FULL| BLOBBO |     1 |  2015 |     3   (0)| 00:00:01 |                                                                          
----------------------------------------------------------------------------                                                                          
                                                                                                                                                      
Note                                                                                                                                                  
-----                                                                                                                                                 
   - dynamic statistics used: dynamic sampling (level=2)                                                                                              
                                                                                                                                                      

18 rows selected.

SQL> 

Writing a qualified query, to find records where our BLOB is NULL, shows a different, but expected, plan:


SQL> 
SQL> select * from blobbo where yorm is null;

YORM                                                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------
     QWERT                                                                                                                                            
----------                                                                                                                                            
                                                                                                                                                      
         4                                                                                                                                            
                                                                                                                                                      

SQL> 
SQL> select * from table(dbms_xplan.display_Cursor());

PLAN_TABLE_OUTPUT                                                                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cz0x5cbkwqkbu, child number 0                                                                                                                 
-------------------------------------                                                                                                                 
select * from blobbo where yorm is null                                                                                                               
                                                                                                                                                      
Plan hash value: 1649995771                                                                                                                           
                                                                                                                                                      
------------------------------------------------------------------------------------------------------                                                
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |                                                
------------------------------------------------------------------------------------------------------                                                
|   0 | SELECT STATEMENT                    |                |       |       |     1 (100)|          |                                                
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BLOBBO         |     1 |  2015 |     1   (0)| 00:00:01 |                                                
|*  2 |   INDEX RANGE SCAN                  | BLOBBO_BLOB_IX |     1 |       |     1   (0)| 00:00:01 |                                                
------------------------------------------------------------------------------------------------------                                                
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   2 - access("YORM" IS NULL)                                                                                                                         
                                                                                                                                                      
Note                                                                                                                                                  
-----                                                                                                                                                 
   - dynamic statistics used: dynamic sampling (level=2)                                                                                              
                                                                                                                                                      

24 rows selected.

SQL> 

Setting the BLOB to an actual, non-NULL value and querying the table shows the expected non-index plan, because we have no function-based index on the BLOB (and, actually, would have no reason to attempt to create one):


SQL> 
SQL> update blobbo set yorm = hextoraw(rpad('EAEAEAFF',32764, 'CDEF')) where qwert = 4;

1 row updated.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select * from blobbo where yorm is not null;

YORM                                                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------
     QWERT                                                                                                                                            
----------                                                                                                                                            
EAEAEAFFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCD
EFCDEFCDEF                                                                                                                                            
         4                                                                                                                                            
                                                                                                                                                      

SQL> 
SQL> select * from table(dbms_xplan.display_Cursor());

PLAN_TABLE_OUTPUT                                                                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3d24twzgz2zry, child number 0                                                                                                                 
-------------------------------------                                                                                                                 
select * from blobbo where yorm is not null                                                                                                           
                                                                                                                                                      
Plan hash value: 925165977                                                                                                                            
                                                                                                                                                      
----------------------------------------------------------------------------                                                                          
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                                          
----------------------------------------------------------------------------                                                                          
|   0 | SELECT STATEMENT  |        |       |       |     3 (100)|          |                                                                          
|*  1 |  TABLE ACCESS FULL| BLOBBO |     1 |  2015 |     3   (0)| 00:00:01 |                                                                          
----------------------------------------------------------------------------                                                                          
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   1 - filter("YORM" IS NOT NULL)                                                                                                                     
                                                                                                                                                      
Note                                                                                                                                                  
-----                                                                                                                                                 
   - dynamic statistics used: dynamic sampling (level=2)                                                                                              
                                                                                                                                                      

23 rows selected.

SQL> 
SQL> select * from blobbo where dbms_lob.instr(yorm,hextoraw('EAEAEAFF')) > 0;

YORM                                                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------
     QWERT                                                                                                                                            
----------                                                                                                                                            
EAEAEAFFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCDEFCD
EFCDEFCDEF                                                                                                                                            
         4                                                                                                                                            
                                                                                                                                                      

SQL> 
SQL> select * from table(dbms_xplan.display_Cursor());

PLAN_TABLE_OUTPUT                                                                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bj30wyv2sf5jg, child number 0                                                                                                                 
-------------------------------------                                                                                                                 
select * from blobbo where dbms_lob.instr(yorm,hextoraw('EAEAEAFF')) > 0                                                                              
                                                                                                                                                      
Plan hash value: 925165977                                                                                                                            
                                                                                                                                                      
----------------------------------------------------------------------------                                                                          
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                                          
----------------------------------------------------------------------------                                                                          
|   0 | SELECT STATEMENT  |        |       |       |     3 (100)|          |                                                                          
|*  1 |  TABLE ACCESS FULL| BLOBBO |     1 |  2015 |     3   (0)| 00:00:01 |                                                                          
----------------------------------------------------------------------------                                                                          
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   1 - filter("DBMS_LOB"."INSTR"("YORM",HEXTORAW('EAEAEAFF'))>0)                                                                                      
                                                                                                                                                      
Note                                                                                                                                                  
-----                                                                                                                                                 
   - dynamic statistics used: dynamic sampling (level=2)                                                                                              
                                                                                                                                                      

23 rows selected.

SQL> 

As mentioned earlier the ability to create such an index on a BLOB is likely a bug that should be addressed, but probably won’t be because it’s not common to want a B-Tree index on a BLOB, much less common to create one. One hint that this might be a bug is the fact that an index key can only be as long as 80% of the configured block size, and since a BLOB easily exceeds the maximum length of a database block problems can arise from such an index. With the index in place on our BLOB trying to get the length of it throws an error:


SQL> select dbms_lob.getlength(yorm) from blobbo;
select dbms_lob.getlength(yorm) from blobbo
       *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1_1607784644$" too small 
ORA-06512: at "SYS.DBMS_LOB", line 837 


SQL> 

Dropping the ‘errant’ index solves the problem:


SQL> 
SQL> drop index blobbo_blob_ix;

Index dropped.

SQL> 
SQL> select dbms_lob.getlength(yorm) from blobbo;

DBMS_LOB.GETLENGTH(YORM)                                                                                                                              
------------------------                                                                                                                              
                   16382                                                                                                                              

SQL> 

Even though a B-Tree index on a BLOB column can be created it seems to be of limited, if not questionable, use since NULLs (as ‘seen’ in character data) can’t exist in the binary data stream. It also appears to create problems when performing standard operations on a BLOB, such as finding the length.

Sometimes what can be done, and what should be done, are two completely different things, so think carefully when asking for non-standard behavior from Oracle. Getting what you asked for can also give you much that you DIDN’T ask for or even expect.

Even a blindfolded octopus can see that.

April 28, 2017

Adjusting Things

Filed under: General — dfitzjarrell @ 10:53

"The only thing you can do easily is be wrong, and that's hardly worth the effort." 
-- Norton Juster, The Phantom Tollbooth

Query tuning is both an art and a science and, because of this, usually occurs on a case-by-case basis. There may be occasions, though, where a series of queries, similar in structure and differing in predicate values, need to be tuned. Rather than go through each and every query, setting up a ‘standard’ SQL Profile (where force_match=FALSE) and enabling it, it may be easier to alter the setting for optimizer_index_cost_adj (presuming indexes are in use) so the index scans/index access paths are more ‘favorable’ than a table scan. Yet on the other hand it might be worth the effort to create the profile with force_match=TRUE, to cover all queries with the same SQL text outside of the literal values supplied. Let’s look at examples of why these might be good plans of attack (and a case where it wouldn’t be).

The optimizer, in its infinite wisdom and using current statistics, computes the cost of access for every table and associated index touched by the problem query. Sometimes the index cost is ever so slightly greater than that for a table scan and, as a result, the index path gets scrapped in favor of the table scan. In such cases nudging the optimizer in the ‘proper’ direction is as simple as changing the value on the optimizer_index_cost_adj parameter, which defaults to 100. The value you need should be chosen carefully, hopefully so that the queries you want affected will be affected and most others won’t. In our example let’s look at a very small portion of the level 2 10053 trace; the names were changed to protect the ‘innocent’:


 ****** Costing Index PLORGENFLOTZ_PK
  SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_SCAN
  SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_FILTER
  Access Path: index (RangeScan)
    Index: PLORGENFLOTZ_PK
    resc_io: 3.000000  resc_cpu: 342602
    ix_sel: 0.954069  ix_sel_with_filters: 0.954069
    Cost: 3.014879  Resp: 3.014879  Degree: 1
...
  Best:: AccessPath: TableScan
         Cost: 2.006465  Degree: 1  Resp: 2.006465  Card: 228.000000  Bytes: 0.000000

Notice the cost of the index access is just slightly higher than the cost of a full table scan so the optimizer passes up that option and chooses the table scan. This is where optimizer_index_cost_adj can change things. If, for example, we set optimizer_index_cost_adj to 50 the cost of the index access will go down:


 ****** Costing Index PLORGENFLOTZ_PK
  SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_SCAN
  SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_FILTER
  Access Path: index (IndexOnly)
    Index: PLORGENFLOTZ_PK
    resc_io: 1.000000  resc_cpu: 63786
    ix_sel: 0.954069  ix_sel_with_filters: 0.954069
    Cost: 1.001385  Resp: 1.001385  Degree: 0
    SORT ressource         Sort statistics
      Sort width:        5989 Area size:     1048576 Max Area size:  1046896640
      Degree:               1
      Blocks to Sort: 1 Row size:     21 Total Rows:            243
      Initial runs:   1 Merge passes:  0 IO Cost / pass:          0
      Total IO sort cost: 0.000000      Total CPU sort cost: 23112595
      Total Temp space used: 0
...
  Best:: AccessPath: IndexRange
  Index: PLORGENFLOTZ_PK
         Cost: 1.507553  Degree: 1  Resp: 1.507553  Card: 34.346487  Bytes: 0.000000

The calculated cost of using this index has been cut in half (which should be expected when setting optimizer_index_cost_adj to 50) so now the optimizer elects to take the index range scan as the best possible path. Notice that the optimizer_index_cost_adj isn’t applied until the actual cost has been calculated; the total cost is adjusted by the percentage provided in the optimizer_index_cost_adj setting as the final step. Looking at the final execution plan we see the following steps:


...
| 44  |                   TABLE ACCESS BY INDEX ROWID BATCHED         | PLORGENFLOTZ_TBL              |    34 |  1326 |     2 |  00:00:01 |      |      |     |        |       |
| 45  |                    INDEX RANGE SCAN                           | PLORGENFLOTZ_PK               |   243 |       |     1 |  00:00:01 |      |      |     |        |       |
...

which replaced this step in the plan where optimizer_index_cost_adj was unmodified:


...
| 111 |                        TABLE ACCESS FULL                           | PLORGENFLOTZ_TBL            |    32 |  1248 |     2 |  00:00:01 |      |      |           |       |
...

Other path steps were changed in addition to those listed here and the overall execution plan was shortened, as evidenced by the step numbers from the included plan excerpts.

Careful planning and testing needs to be done before settling on a value for optimizer_index_cost_adj as it will affect all index access calculations and could change acceptable plans using table scans to less-than-desirable plans forcing index access. The value of 50 used here was chosen after several runs using smaller and smaller settings until the desired plans were obtained. Being aggressive isn’t necessarily best when setting optimizer_index_cost_adj as extremely small settings, such as 20 or lower, may make some queries run very fast and make some others very slow (because index access isn’t always the best path to choose). Never make such changes on a production system without first investigating the effects in your test environment. The user community does not like unpleasant surprises.

Yet another way to tune a set of queries that differ only in literal values is by using a SQL Profile with force_match set to TRUE. This works by replacing the literal values with system-generated bind variables before the signature is generated; any similar query with different literal values will be associated with the same profile as the original query and thus will use the same execution plan. This presumes that ONLY literal values are in the source query statement; any additional bind variables present will generate a new signature that won’t match the signature associated with the profile and the known ‘good’ plan won’t be selected.

Let’s look at an example of that in action:


SQL>
SQL>--
SQL>-- Create table
SQL>--
SQL>create table plan_test(
  2  id      number,
  3  class   number,
  4  data    varchar2(45),
  5  cr_dt   date);

Table created.

SQL>
SQL>--
SQL>-- Load table
SQL>--
SQL>begin
  2  	     for i in 1..500000 loop
  3  		     insert into plan_test
  4  		     values(i, mod(i,337)+1, 'Value '||i, sysdate+mod(i,337));
  5  	     end loop;
  6
  7  	     for i in 500001..1000000 loop
  8  		     if mod(i,2)=0 then
  9  			     insert into plan_test
 10  			     values(3999, 3999, 'Value '||i, sysdate+mod(i,37));
 11  		     else
 12  			     insert into plan_test
 13  			     values(7734, 1234, 'Value '||i, sysdate+mod(i,37));
 14  		     end if;
 15  	     end loop;
 16
 17  	     commit;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL>--
SQL>-- Add an index
SQL>--
SQL>create index plan_test_idx on plan_test(class);

Index created.

SQL>
SQL>--
SQL>-- Compute stats and histograms
SQL>--
SQL>exec dbms_stats.gather_table_stats(user, 'PLAN_TEST', method_opt=>'for all columns size skewonly', cascade=>true);

PL/SQL procedure successfully completed.

SQL>
SQL>--
SQL>-- Run a query to get an index-access plan
SQL>--
SQL>select *
  2  from plan_test
  3  where class = 1;

        ID      CLASS DATA                                          CR_DT
---------- ---------- --------------------------------------------- ---------
       337          1 Value 337                                     28-APR-17
       674          1 Value 674                                     28-APR-17
      1011          1 Value 1011                                    28-APR-17
...
    483932          1 Value 483932                                  28-APR-17
    487302          1 Value 487302                                  28-APR-17
    477529          1 Value 477529                                  28-APR-17
    480899          1 Value 480899                                  28-APR-17
    484269          1 Value 484269                                  28-APR-17
    487639          1 Value 487639                                  28-APR-17

1483 rows selected.

SQL>
SQL>--
SQL>-- Display the plan
SQL>--
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  19vnyya8kzzsw, child number 0
-------------------------------------
select * from plan_test where class = 1

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |  1136 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |  2236 | 64844 |  1136   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |  2236 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

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

   2 - access("CLASS"=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]


32 rows selected.

SQL>
SQL>--
SQL>-- Run a query to get an full scan plan
SQL>--
SQL>select *
  2  from plan_test
  3  where class = 3999;

        ID      CLASS DATA                                          CR_DT
---------- ---------- --------------------------------------------- ---------
      3999       3999 Value 500682                                  02-JUN-17
      3999       3999 Value 500684                                  28-APR-17
      3999       3999 Value 500686                                  30-APR-17
...
      3999       3999 Value 997392                                  18-MAY-17
      3999       3999 Value 997394                                  20-MAY-17
      3999       3999 Value 997396                                  22-MAY-17

250000 rows selected.

SQL>
SQL>--
SQL>-- Display the plan
SQL>--
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g41z4n4rnvbqc, child number 0
-------------------------------------
select * from plan_test where class = 3999

Plan hash value: 534695957

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |  1304 (100)|          |
|*  1 |  TABLE ACCESS FULL| PLAN_TEST |   244K|  6916K|  1304   (1)| 00:00:01 |
-------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1

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

   1 - filter("CLASS"=3999)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22],
       "PLAN_TEST"."DATA"[VARCHAR2,45], "PLAN_TEST"."CR_DT"[DATE,7]


29 rows selected.

SQL>

SQL>--
SQL>-- Create script to create profile
SQL>--
SQL>-- Profile uses force_match=TRUE
SQL>--
SQL>@coe_xfr_sql_profile 19vnyya8kzzsw 2494389488
SQL>--
SQL>-- Create the profile
SQL>--
SQL>@coe_xfr_sql_profile_19vnyya8kzzsw_2494389488
SQL>--
SQL>-- Test the profile
SQL>--
SQL>select *
  2  from plan_test
  3  where class = 1;

        ID      CLASS DATA                                          CR_DT
---------- ---------- --------------------------------------------- ---------
       337          1 Value 337                                     28-APR-17
       674          1 Value 674                                     28-APR-17
      1011          1 Value 1011                                    28-APR-17
...
    483932          1 Value 483932                                  28-APR-17
    487302          1 Value 487302                                  28-APR-17
    477529          1 Value 477529                                  28-APR-17
    480899          1 Value 480899                                  28-APR-17
    484269          1 Value 484269                                  28-APR-17
    487639          1 Value 487639                                  28-APR-17

1483 rows selected.

SQL>
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  19vnyya8kzzsw, child number 0
-------------------------------------
select * from plan_test where class = 1

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |  1136 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |  2236 | 64844 |  1136   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |  2236 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

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

   2 - access("CLASS"=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]

Note
-----
   - SQL profile coe_19vnyya8kzzsw_2494389488 used for this statement


36 rows selected.

SQL>
SQL>select *
  2  from plan_test
  3  where class = 107;

        ID      CLASS DATA                                          CR_DT
---------- ---------- --------------------------------------------- ---------
       443        107 Value 443                                     12-AUG-17
       780        107 Value 780                                     12-AUG-17
      1117        107 Value 1117                                    12-AUG-17
...
    487071        107 Value 487071                                  12-AUG-17
    477298        107 Value 477298                                  12-AUG-17
    480668        107 Value 480668                                  12-AUG-17
    484038        107 Value 484038                                  12-AUG-17
    487408        107 Value 487408                                  12-AUG-17
    477635        107 Value 477635                                  12-AUG-17
    481005        107 Value 481005                                  12-AUG-17

1484 rows selected.

SQL>
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  93zcxckzy9g3f, child number 0
-------------------------------------
select * from plan_test where class = 107

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |  1136 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |  2236 | 64844 |  1136   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |  2236 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

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

   2 - access("CLASS"=107)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]

Note
-----
   - SQL profile coe_19vnyya8kzzsw_2494389488 used for this statement


36 rows selected.

SQL>
SQL>select *
  2  from plan_test
  3  where class = 391;

no rows selected

SQL>
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7n1ab3tyk1f33, child number 0
-------------------------------------
select * from plan_test where class = 391

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |  1136 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |  2236 | 64844 |  1136   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |  2236 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

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

   2 - access("CLASS"=391)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]

Note
-----
   - SQL profile coe_19vnyya8kzzsw_2494389488 used for this statement


36 rows selected.

SQL>
SQL>select *
  2  from plan_test
  3  where class = 1044;

no rows selected

SQL>
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  52r213wp9sr9a, child number 0
-------------------------------------
select * from plan_test where class = 1044

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |  1136 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |  2236 | 64844 |  1136   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |  2236 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

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

   2 - access("CLASS"=1044)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]

Note
-----
   - SQL profile coe_19vnyya8kzzsw_2494389488 used for this statement


36 rows selected.

SQL>

All of the queries shown above return about the same number of rows, presuming they return rows, and all used the created profile, which is good. What isn’t so good is the next query, returning 250000 rows, also uses the profile:


SQL>
SQL>--
SQL>-- This one probably shouldn't use the profile but it does
SQL>--
SQL>-- Result of force_match=TRUE
SQL>--
SQL>select *
  2  from plan_test
  3  where class = 3999;

        ID      CLASS DATA                                          CR_DT
---------- ---------- --------------------------------------------- ---------
      3999       3999 Value 500682                                  02-JUN-17
      3999       3999 Value 500684                                  28-APR-17
      3999       3999 Value 997388                                  14-MAY-17
...
      3999       3999 Value 997392                                  18-MAY-17
      3999       3999 Value 997394                                  20-MAY-17
      3999       3999 Value 997396                                  22-MAY-17

250000 rows selected.

SQL>
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g41z4n4rnvbqc, child number 0
-------------------------------------
select * from plan_test where class = 3999

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |   123K(100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |   244K|  6916K|   123K  (1)| 00:00:05 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |   244K|       |   510   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

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

   2 - access("CLASS"=3999)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]

Note
-----
   - SQL profile coe_19vnyya8kzzsw_2494389488 used for this statement


36 rows selected.

SQL>

Compare the cost of the index plan (123K) and the full table scan plan (1304) and you can see using the SQL Profile when returning a quarter of the table data is not the preferred path to take. Fixing the majority of the queries can ‘fix’ queries that don’t need fixing, and that’s the major issue with tuning with a broad brush.

It’s usually best to tune queries on an individual basis, but sometimes applications generate a set of queries that need attention. Judicious setting of optimizer_index_cost_adj could be the answer to such a tuning task, as could creating a SQL Profile with force_match=TRUE. Remember that making such changes at the database level can affect more than you had bargained for so test, test, test to verify minimal impact outside of the set of queries you are targeting. With respect to setting force_match=TRUE for a SQL Profile you may inadvertently set an inefficient plan using an index when a table scan would be preferable, as shown in the provided example.

Sometimes the “easy button” can be too easy to press.

April 7, 2017

You Bet Your ASCII

Filed under: General — dfitzjarrell @ 10:58

"Why, did you know that if a beaver two feet long with a tail a foot and a half long can build a dam twelve 
feet high and six feet wide in two days, all you would need to build Boulder Dam is a beaver sixty-eight
feet long with a fifty-one-foot tail?"
"Where would you find a beaver that big?" grumbled the Humbug as his pencil point snapped.
"I'm sure I don't know," he replied, "but if you did, you'd certainly know what to do with him."
-- Norton Juster, The Phantom Tollbooth 

International character sets, such as AL32UTF8, can solve a host of problems when non-ASCII characters need to be stored in the database. This, unfortunately, can create problems when having to convert those characters to ASCII-compatible text using Oracle’s built-in function ASCIISTR(). Let’s look at an example and see what might occur.

Two databases exist, one 11.2.0.4, the other 12.1.0.2, and both use the AL32UTF8 character set. Let’s create a table in both databases and load the CLOB column with non-ASCII characters (characters that will print on the screen but will be processed by the ASCIISTR() function):


SQL> create table yumplerzle(
  2  smarg   number,
  3  weebogaz	     clob);

Table created.

SQL> 
SQL> begin
  2  	     for i in 1..1000 loop
  3  		     insert into yumplerzle
  4  		     values(i, rpad(i, 8000, chr(247)));
  5  	     end loop;
  6  
  7  	     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> 

Query the table absent the ASCIISTR() function to see what character we’ve chosen:


...
SUBSTR(WEEBOGAZ,1,4000)
--------------------------------------------------------------------------------
991ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
992ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
993ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
994ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
995ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
996ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
997ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
998ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
999ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
1000ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷

1000 rows selected.

Interesting data, to be sure. Now let’s try to use the ASCIISTR() function on the output:


SQL> select asciistr(substr(weebogaz,1,4000)) from yumplerzle
  2  /
select asciistr(substr(weebogaz,1,4000)) from yumplerzle
                *
ERROR at line 1:
ORA-64203: Destination buffer too small to hold CLOB data after character set 
conversion. 


SQL> 

Unfortunately the character ‘conversion’ replaces the non-ASCII characters with their HEX codes and that can expand the line length considerably. Since this is 11.2.0.4 the length limit for VARCHAR2 columns is 4000 (characters or bytes depending on how your database or table column is configured). Given that restriction it’s impossible to use ASCIISTR() on any longer line than 1000 characters/bytes as shown below:


SQL> select asciistr(substr(weebogaz,1,32767)) from yumplerzle
  2  /
select asciistr(substr(weebogaz,1,32767)) from yumplerzle
                *
ERROR at line 1:
ORA-64203: Destination buffer too small to hold CLOB data after character set 
conversion. 


SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,16000)) from yumplerzle
  2  /
select asciistr(substr(weebogaz,1,16000)) from yumplerzle
                *
ERROR at line 1:
ORA-64203: Destination buffer too small to hold CLOB data after character set 
conversion. 


SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,4000)) from yumplerzle
  2  /
select asciistr(substr(weebogaz,1,4000)) from yumplerzle
                *
ERROR at line 1:
ORA-64203: Destination buffer too small to hold CLOB data after character set 
conversion. 


SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,3000)) from yumplerzle
  2  /
select asciistr(substr(weebogaz,1,3000)) from yumplerzle
                *
ERROR at line 1:
ORA-64203: Destination buffer too small to hold CLOB data after character set 
conversion. 


SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,2000)) from yumplerzle
  2  /
select asciistr(substr(weebogaz,1,2000)) from yumplerzle
                *
ERROR at line 1:
ORA-64203: Destination buffer too small to hold CLOB data after character set 
conversion. 


SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,1000)) from yumplerzle
  2  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,1000))                                               
--------------------------------------------------------------------------------
1\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF

ASCIISTR(SUBSTR(WEEBOGAZ,1,1000))                                               
--------------------------------------------------------------------------------
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
...

It’s apparent how the line length has expanded based on the output from ASCIISTR(). If you’re using any release older than 12.1 you’re stuck with this restriction. Thankfully Oracle 12.1 and later versions offer the possibility of extended string length for text fields, configured using the max_string_size parameter. Setting this to EXTENDED and running the utl32k.sql script in $ORACLE_HOME/rdbms/admin (on UNIX and Linux systems, %ORACLE_HOME%\rdbms\admin on Windows) can fix this error. This requires a shutdown of the database and starting in UPGRADE mode. The exact steps are shown below:


SQL>
SQL> alter system set max_string_size = EXTENDED scope=spfile;
SQL> shutdown immediate
...
SQL> startup upgrade
...
SQL> @?/rdbms/admin/utl32k.sql
...
SQL> shutdown immediate
...
SQL> startup
...
SQL>

The script makes necessary changes to the data dictionary that allow Oracle to utilize this expanded string length and, in turn, indirectly modify functions like ASCIISTR() so their string buffer lengths are increased. Moving over to the database running under 12.1.0.2 that has had this modification completed the error experienced in 11.2.0.4 is gone:


SQL> select asciistr(substr(weebogaz,1,32767)) from yumplerzle
  2  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,32767))                                               
--------------------------------------------------------------------------------
1\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF

ASCIISTR(SUBSTR(WEEBOGAZ,1,32767))                                               
--------------------------------------------------------------------------------
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
...
SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,16000)) from yumplerzle
  2  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,16000))                                               
--------------------------------------------------------------------------------
1\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF

ASCIISTR(SUBSTR(WEEBOGAZ,1,16000))                                               
--------------------------------------------------------------------------------
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
...
SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,4000)) from yumplerzle
  2  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,4000))                                               
--------------------------------------------------------------------------------
1\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF

ASCIISTR(SUBSTR(WEEBOGAZ,1,4000))                                               
--------------------------------------------------------------------------------
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
...
SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,3000)) from yumplerzle
  2  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,3000))                                               
--------------------------------------------------------------------------------
1\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF

ASCIISTR(SUBSTR(WEEBOGAZ,1,3000))                                               
--------------------------------------------------------------------------------
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
...
SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,2000)) from yumplerzle
  2  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,2000))                                               
--------------------------------------------------------------------------------
1\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF

ASCIISTR(SUBSTR(WEEBOGAZ,1,2000))                                               
--------------------------------------------------------------------------------
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
...
SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,1000)) from yumplerzle
  2  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,1000))                                               
--------------------------------------------------------------------------------
1\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF

ASCIISTR(SUBSTR(WEEBOGAZ,1,1000))                                               
--------------------------------------------------------------------------------
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
...

No “buffer too small” errors were thrown with the extended string length configured in 12.1.0.2, even passing a substring length of 32767. Using even longer sub-strings, and even eliminating the substr() call entirely, also seems to pose no problems:


SQL> 
SQL> select asciistr(substr(weebogaz,1,64000)) from yumplerzle
  2  where rownum < 2
  3  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,64000))                                              
--------------------------------------------------------------------------------
305\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
...
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD                                     
                                                                                

SQL> 
SQL> select asciistr(substr(weebogaz,1,128000)) from yumplerzle
  2  where rownum < 2
  3  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,128000))                                             
--------------------------------------------------------------------------------
305\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
...
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD                                     
                                                                                

SQL> 
SQL> select asciistr(substr(weebogaz,1,256000)) from yumplerzle
  2  where rownum < 2
  3  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,256000))                                             
--------------------------------------------------------------------------------
305\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
...
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD                                     
                                                                                

SQL> 
SQL> select asciistr(weebogaz) from yumplerzle
  2  where rownum < 2
  3  /

ASCIISTR(WEEBOGAZ)                                                              
--------------------------------------------------------------------------------
305\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
...
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD                                     
                                                                                

SQL>  

When using character sets like WE8MSWIN1252 and US7ASCII these issues aren’t present as the data is converted to something ASCII can handle during the insert; only extended character sets seem to produce this error on conversion, something to remember since, if your database is NOT using a UTF8/UTF16 character set such problems won’t occur. There will be no need to increase the max_string_size when ASCII-centric character sets are used unless, of course, you want the extended length to store longer pieces of text.

Notice that the character set of the database was NOT changed during this process, only the maximum declarable length of a VARCHAR2/NVARCHAR2 column was affected. Also remember that this should be done in a test environment first, to ensure that such a change doesn’t adversely affect existing applications and code. I have not experienced any issues of that sort but mine isn’t the only database in the world and there could be exceptions in other environments. Only after you are reasonably certain this change doesn’t break anything can you move this into a production environment.

Fill ‘er up.

Next Page »

Create a free website or blog at WordPress.com.