Oracle Tips and Tricks — David Fitzjarrell

June 20, 2019

“Just One More…”

Filed under: General — dfitzjarrell @ 13:54

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

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

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

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

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


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

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

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

Table created.

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


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


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

1 row created.

SQL> commit;

Commit complete.

SQL> 

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

For the test table a few numbers:


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

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

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

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

SQL> 
SQL>

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

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

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

I expect that we are in agreement.

Advertisements

June 4, 2019

“Hear ye, hear ye!”

Filed under: General — dfitzjarrell @ 18:21

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

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

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


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

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

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


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

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


$ lsnrctl reload listener

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

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

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

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


lsnrctl reload

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

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


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

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

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


[oracle@e3c57ee3dc64 ~]$ lsnrctl status

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

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

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

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

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

You are listening?

Blog at WordPress.com.