A Defining Moment

Within sqlplus a good number of parameters exist that can be set at the session level through the ‘set’ command. These parameters include arraysize, autocommit, autotrace, echo and linesize which affect how results are displayed and whether or not to require the commit instruction to preserve changes, nothing unusual here. There is one parameter that is most often ignored or forgotten: define. By default the value is set to ‘&’, but it can be set to almost any character one could desire. What define does is tell sqlplus how variables are, well, defined, that is, how sqlplus will identify what a variable is. That may be a bit confusing so let’s look at define a bit closer.

As already mentioned define is set to the ‘&’ character; this is the first character in variable names that sqlplus will use to so that sqlplus will take the assigned value rather than the literal text of the variable name. As a basic example the column command can use the new_value function to assign a variable to a returned column in a query:

column sysdate new_value sys_dt noprint

The above command will assign the variable name to the SYSDATE column when it is returned in a query result; additionally the column will NOT be printed as output from the query itself. Since define has not been altered it remains set to ‘&’, and the value of SYSDATE can be accessed using the following construct:

&sys_dt

Providing a working example as an llustration produces:

SQL> column sysdate new_value sys_dt noprint
SQL>
SQL> select sysdate from dual;

SQL> select ‘&sys_dt’ from dual
2 /
old 1: select ‘&sys_dt’ from dual
new 1: select ’28-MAR-22′ from dual

’28-MAR-2

28-MAR-22

SQL>

[The old and new output is because the verify parameter is set to ‘ON’; setting this to ‘OFF’ disables this output so spool files aren’t cluttered with these notifications:

SQL> set verify off
SQL> select ‘#sys_dt’ from dual;

’28-MAR-2

28-MAR-22

SQL>

When executing scripts or commands interactively having verify on can be helpful; when cron or other schedulers are executing SQL code it might be better to set verify off.]

Using this method a constant value for the variable can be set and passed throughout a SQL script, regardless of the script length, which can be very useful for generating report names and/or headers. However the default ‘&’ character can get ‘in the way’ when the & is used for other purposes, such as a special character in passwords. Regardless of the punctuation the & will be interpreted as a variable name for all text immediately after the & appears, producing some unexpected results:

SQL> create user obnopper identified by “A924857&882er0!”;
Enter value for 882er0:

Simply hitting the Enter/Return key can result in the command failing due to password verification failures or a password far shorter than expected:

SQL> create user obnopper identified by “A924857&882er0!”;
Enter value for 882er0:
old 1: create user obnopper identified by “A924857&882er0!”
new 1: create user obnopper identified by “A924857!”

User created.

SQL>

When automated scripts are used this behavior can, and will, result in login failures since the originally set password string is not the password finally set for the given user. This can be addressed in one of two ways:

• Set define off entirely
• Change the character define is set to use

Option #1 is great until you need to define a variable and use its value:

SQL> select ‘&sys_dt’ from dual;
old 1: select ‘&sys_dt’ from dual
new 1: select ’28-MAR-22′ from dual

’28-MAR-2

28-MAR-22

SQL> set define off
SQL> select ‘&sys_dt’ from dual;

‘&SYS_D

&sys_dt

SQL>

Now file names and report headers will be populated with the cryptic ‘&sys_dt’ string and multiple executions of a script across several days will all generate the same text, causing output files to be overwritten, which is not the desired result. Those not familiar with this behavior will find it odd that a date value is not present in a file name, especially when the ‘&’ character is used as the abbreviation for ‘and’:

set define off
ttitle ‘Profit & Loss Statement: ‘&sys_dt

Such report headers will, indeed, show ‘Profit & Loss Statement: ‘ but will include the &sys_dt string, rather than the date value it was assigned. One possible way around this is to use the CHR() function with the proper ASCII code for the &:

ttitle ‘Profit ‘||chr(38)||’ Loss Statement: ‘&sys_dt

Now, with define set at the default the header can still report ‘Profit & Loss Statement: ‘ and include the assigned value to &sys_dt. It could be a tedious task to modify every report header in this manner.

Option #2 is probably a better option, provided a character is selected that isn’t used for some other purpose elsewhere. There are some obvious choices to avoid:

| ( ) @ ! $

are all put to other uses in an Oracle database; ! and $ are used as shortcuts to escape to the operating system (! for UNIX/Linux, $ for Windows), @ is, obviously, the shortcut for the ‘run’ command for scripts, ( and ) are used within scripts to denote subqueries, in-lists, complex where clauses and as parameter delimiters for functions and procedures, among other uses. And the ‘|’ is used, in pairs, as a shortcut for concat(), so none of those characters would be good choices. There’s ‘?’, which sounds good until you realize that’s the shortcut for $ORACLE_HOME within sqlplus. Drat, drat and double drat.

There are still characters available, such as ‘#’, which might be the best option. Yes, ‘#’ is used as a comment character in shell scripts but its use in sqlplus should not be an issue. To set this fine character as the one to define variables:

SQL> set define ‘#’
SQL> show define
define “#” (hex 23)
SQL>

Now the sys_dt variable can be accessed using #sys_dt:

SQL> select ‘#sys_dt’ from dual;
old 1: select ‘#sys_dt’ from dual
new 1: select ’28-MAR-22′ from dual

’28-MAR-2

28-MAR-22

SQL>

and file names and report headers can again show the date, rather than the literal string. This setting persists for the duration of the sqlplus session or until another call to define changes the character; for scripts run through a scheduler simply exiting the session will undo the setting so that at the next login the default, ‘&’, is set. What convenience.

DEFINE may not be an oft-used parameter so the standard ‘&’ should work in most cases, but it is good to know how to change such settings should the need arise. Careful selection of the replacement character is the one important decision when changing this parameter value so that literal text and variable values can both appear in the same output, which allows report names and titles to include accurate and valid data, rather than obtuse variable names.

Definitions aren’t just found in a dictionary.

“Active” Duty

Standby database configurations have changed over the years. Where once stood the stoic, mounted standby in managed recovery mode we now have active standby configurations, open for read-only access while still performing active recovery. And this recovery has changed, as well; once relying solely on archived copies of the redo logs the “new guard” uses standby redo logs, and the LGWR process, to feed the standby the necessary transactions the recovery process needs to keep the standby in lock-step with the primary. Let’s look into those changes a bit deeper.

Originally a standby database configuration was one of two types: physical and logical. A physical standby copied every transaction and data type the database had to offer, using the standard redo apply mechanism to apply generated redo. This required the database to be in mount mode, available only for recovery, and a standby archivelog destination configured and managed for space. The ARCH process transferred each archived redo log to the standby; these copies were the only source of information this configuration had. The standby could be opened read-only, at the expense of recovery; the original design prevented a read-only database from performing recovery since only a mounted database could be recovered. A logical standby utilized a different mechanism, known as Streams, to provide redo transactions to such a standby. This mechanism used the normal transactional pathway, inserts/updates/deletes, that can be executed from a live user session. As such a logical standby could be open for limited user activity while “recovery” is active. Interestingly DBAs still cling to these definitions of a standby database, and questions on how a standby does what it does stop with this limited, almost antiquated, view.

Modern Oracle databases (from 11 onward) implement a different mechanism for recovery — the standby redo log. These logs are created on the primary, one standby log for each configured redo log group, plus one as a failsafe should it be needed. As an illustration if the database has 4 redo log groups the recommended standby log configuration would be to create five standby redo logs. Once created Oracle is poised to populate those in any standby database created from the primary, using the LGWR process. Not all standby redo logs may be used, depending upon the transaction volume and the speed at which redo is applied to the standby. If recovery is interrupted and a lag develops it’s common to see all of the standby logs involved as recovery progresses during the period where the gap is being closed. Additionally the standby redo mechanism allows the physical standby to be actively recovered while open read-only; this is known as Active Data Guard and does require a license.

[Even though standby redo logs are now the norm in standby database configuration this doesn’t mean the older, archivelog-based recovery is no longer available. Starting the recovery process in the following manner:

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

puts recovery back into the ‘old-style’ configuration. This is possible since a standby delay can be configured for one or more standby databases and such a delay cannot be executed using the standby redo log mechanism.]

Standby logs can be monitored for activity as Oracle records this data as a matter of course. An uncomplicated script, like the one provided below, can report on all “necessary” information a monitoring session would need:

select group#,
thread#,
sequence#,
used,
round((used/bytes)100, 2) “%FULL”, archived, status, first_change#, first_time, last_change#, last_time, (last_time – first_time)86400 seconds_active,
round(used/((last_time – first_time)*86400),0) bytes_per_sec,
last_change#-first_change# changes
from v$standby_log;

Such a report looks like this on the terminal:

  GROUP#      THREAD#    SEQUENCE#         USED   %FULL ARC STATUS     FIRST_CHANGE# FIRST_TIME                    LAST_CHANGE# LAST_TIME                     SECONDS_ACTIVE BYTES_PER_SEC      CHANGES

---

      10            1            0            0    0.00 NO  UNASSIGNED
      11            1       206517     27586560    2.63 YES ACTIVE       90402722758 25-MAR-2022 10:17:02          90402737245  25-MAR-2022 10:17:09                       7       3940937        14487
      12            1            0            0    0.00 NO  UNASSIGNED
      13            1            0            0    0.00 NO  UNASSIGNED
      14            1            0            0    0.00 YES UNASSIGNED

Subsequent executions will show increases in used, %full, last_change#, last_time, seconds_active, bytes_per_sec and changes columns, and, as the %full value approaches 90% or higher a standby log switch will shift the active log, starting the byte accrual and changes count over for the “new” standby log:

  GROUP#      THREAD#    SEQUENCE#         USED   %FULL ARC STATUS     FIRST_CHANGE# FIRST_TIME                    LAST_CHANGE# LAST_TIME                     SECONDS_ACTIVE BYTES_PER_SEC      CHANGES

---

      10            1       206518    885264896   84.43 NO  ACTIVE       90403231787 25-MAR-2022 10:21:05          90403714470  25-MAR-2022 10:25:15                     250       3541060       482683
      11            1       206519      2527232    0.24 YES ACTIVE       90403714473 25-MAR-2022 10:25:14          90403714739  25-MAR-2022 10:25:15                       1       2527232          266
      12            1            0            0    0.00 NO  UNASSIGNED
      13            1            0            0    0.00 NO  UNASSIGNED
      14            1            0            0    0.00 YES UNASSIGNED

More than one standby log can be active at a given time, as illustrated here: log group 10 is completing its transfer as log group 11 is starting its data flow. The next execution will show log group 10 as unassigned while log group 11 retains its active status.

The reported changes count, as seen from the query, is simply the difference between the first recorded change number in the log and the most recent change number in that log. The benefit of keeping track of the changes is that slowdowns in processing can be noticed; both the CHANGES and BYTES_PER_SEC values will decrease if the recovery encounters issues signalling a possible network problem. The SECONDS_ACTIVE value should provide a fairly constant timing of the standby redo logs performance, and given that the standby logs don’t increase in size the active seconds for each log should be roughly the same.

Notice that the standby logs don’t fill to capacity; this is directly related to the normal redo log activity on the primary database. Rarely, if ever, is a redo log filled to capacity; available space and transaction size determine when the redo process switches logs. Another factor affecting how ‘full’ a redo log gets is supplemental logging; this increases the size of the redo entry, especially if all table columns are supplementally logged. Since a single transaction cannot span multiple redo logs a log switch occurs when free space in the current redo log is getting scarce. The average %FULL for this report is around 90%; notice that log group 10 reached about 85% full at the time of the log switch.

Because Oracle is reading from, and applying the transactions in, the standby redo logs the database does not need to be in mount mode for recovery to occur. Interestingly gap recovery can be successfully executed while the database is open read-only even though the standby is using archived redo logs rather than the standby redo logs. While the database is reading through the newly acquired archivelogs it is still taking transactions through the standby redo log mechanism and will process the transactions recorded there once the gap has been resolved. The apply process can be monitored with another simple query:

select *
from v$managed_standby
where thread# > 0;

That report provides the following output:

PROCESS            PID STATUS       CLIENT_P CLIENT_PI CLIENT_DBID  GROUP#    RESETLOG_ID       THREAD#    SEQUENCE#       BLOCK#       BLOCKS   DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS
--------- ------------ ------------ -------- --------- ------------ --------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ -------------
ARCH              8032 CLOSING      ARCH     8032      3605096953   10          1003098742            1       206520      1730560            4            0            0             0
ARCH              8634 CLOSING      ARCH     8634      3605096953   10          1003098742            1       206522      1724416          184            0            0             0
ARCH              8638 CLOSING      ARCH     8638      3605096953   11          1003098742            1       206521      1726464          761            0            0             0
MRP0             10498 APPLYING_LOG N/A      N/A       N/A          N/A         1003098742            1       206523       657012      2048000            0           17            17
RFS              12512 RECEIVING    LGWR     8881      3605096953   2           1003098742            1       206523       657011            2            0            0             0

---

Subsequent executions will show increases in the BLOCK# value for the row where the state is ‘APPLYING_LOG’, and the BLOCK# and BLOCKS values where the state is ‘RECEIVING’.

Oracle and RMAN know which logs have been applied and which logs need to be kept in the event database recovery is required; using RMAN to manage archivelog space is the recommend method. Of course it’s not beyond the realm of comprehension to have archived redo logs deleted by an O/S level process. Using the standby database server as a source for VM snapshots can cause issues as the database needs to be shutdown during snapshot creation; this interrupts the recovery mechanism and can cause gaps in the redo/archivelog data. Provided a proper backup strategy is implemented for the primary the missing archivelog data can be restored by RMAN to the primary database server; as the archivelogs are restored they are transmitted to the standby and registered and gap recovery automatically begins. This can put a larger load on the standby server while this catch-up is active but it shouldn’t dramatically impact user sessions.

Standby technology has changed over the years and the DBA would be wise to keep abreast of such changes, especially if such changes are not in use in the shop where he or she works. Even though the older recovery mechanism is still available most shops have moved on to the current standby model and, unless an apply delay is configured for one or more standby databases, standby redo logs will be the method of choice, and Active Data Guard will likely be in use to provide a pseudo report server for OLTP environments. Of course it won’t provide the robust performance of a true reporting server (a standby database, configured and indexed with report generation in mind, open read-only with recovery disabled) but it will provide a source for basic reporting without impacting the primary production database. Knowing this configuration exists, and how to monitor it, should be required knowledge for a DBA.

As Bob Dylan told us, years ago, “The times, they are a-changing.”

Don’t Constrain Yourself

There is nothing like the life of a Database Administrator — a workday filled with backups, user queries, user problems, password resets … the list sometimes appears to be endless. Behind all of those tasks is a foundation of knowledge gleaned from years of experience and education. Or there should be. Lately it appears that areas of knowledge that used to be considered basic now no longer exist, or exist in such a reduced form as to make them almost useless. Questions that once would elicit longer, thoughtful responses now produce simple, sometimes terse, “yes” or “no” answers. The current “crop” of DBAs appear to fixate on the mechanical issues: backups, recoveries, user creation, some query tuning and similar areas of responsibility. What is not immediately apparent is conceptual knowledge, topics that used to produce clear and concise answers now are met with facial expressions and conversations that are clearly one-sided in favor of the interviewer. Sadly, DBAs can, and should, do better.

Some areas where knowledge is found to be lacking:

• Constraint knowledge
• Referential Integrity concepts
• Basic optimiser concepts
• Standby database concepts

Other areas may also be included, this is simply a basic list. How do DBAs brush up on such topics? Blogs can help, along with database documentation, but it may take actually playing with a database and data to learn such basic concepts. Maybe if DBA bloggers included occasional posts on such topics, including worked examples and code, the DBA population in general might improve their awareness and knowledge, making them better DBAs. Along that train of thought here are a few starting points for the first two topics in that list.

For those unfamiliar with the concept constraints exist to ensure the integrity of data. Constraints come in many forms:

• Not Null constraints
  Such constraints ensure that no “empty” values occur in a data set. For example each employee must have a first name, last name and employee id number; making these columns NOT NULL ensures that all of the relevant information for the
  employee is entered and preserved in the database table.
• Primary key constraints
  These constraints combine a NOT NULL requirement along with a uniqueness requirement — the actual key must uniquely identify each row in the table. Going back to the employee example a possible primary key configuration would include
  all three columns (although uniqueness in the first name, last name combinations probably can’t be enforced absent the employee id). Normally just the employee id is unique, ensuring that no two employees have the same ID value. The
  purpose is to have a unique identifier for each and every employee in the company.
• Referential Integrity constraints
  Referential integrity ensures that data that depends on the main, or parent, record exists and is properly configured to “point back to” the parent record. Usually this configuration involves two constraints, a primary key and what is
  known as a foreign key. Using the employee example dependent data might be a department assignment, address information or payroll data. In each of those cases the employee id (presuming it is the primary key) would be used as the
  foreign key to the salary, contact_info or department table, ensuring that data stored in those tables is properly associated with the correct employee.
• Check constraints
  These constraints validate data as it is entered into the table, verifying that it meets the stated and desired criteria. A possibile check constraint could verify that the data is composed of numbers, throwing an error when non-numeric
  characters are submitted for insert. Other validations can occur, such as password validation functions to ensure complexity and length rules are enforced.

Sometimes examples can aid in understanding. This first example illustrates how a NOT NULL constraint ensures every row in a given table has a certain column populated. Since it’s already a common example in this post let’s continue with the employee example:

The employee table has the following structure:

SQL> desc employee
Name Null? Type

---

EMPLOYEE_ID NUMBER
FIRST_NAME VARCHAR2(45)
MIDDLE_NAME VARCHAR2(45)
LAST_NAME VARCHAR2(45)
HIRE_DATE DATE

SQL>

Unfortunately, as it is currently defined, no data is actually required in any row of the table. Since the employee_id is required, along with a first name and last name, the definition is modified to ensure those values occur for every record:

alter table employee modify (employee_id not null, first_name not null, last_name not null);

Executing the above statement produces:

SQL> desc employee
Name Null? Type

---

EMPLOYEE_ID NOT NULL NUMBER
FIRST_NAME NOT NULL VARCHAR2(45)
MIDDLE_NAME VARCHAR2(45)
LAST_NAME NOT NULL VARCHAR2(45)
HIRE_DATE DATE

SQL>

Now these columns must contain data. However, that change doesn’t ensure the uniqueness of the employee in the table. Providing a minimalist approach the only column that needs to be unique is the employee_id; making that the primary key of the table will allow each employee_id number to be distinct. The following statement modifies the table:

alter table employee add constraint employee_pk primary key(employee_id);

Looking at the “final” definition of the table notice that no apparent change in structure occurred:

SQL> alter table employee add constraint employee_pk primary key(employee_id);

Table altered.

SQL> desc employee
Name Null? Type

---

EMPLOYEE_ID NOT NULL NUMBER
FIRST_NAME NOT NULL VARCHAR2(45)
MIDDLE_NAME VARCHAR2(45)
LAST_NAME NOT NULL VARCHAR2(45)
HIRE_DATE DATE

SQL>

The difference appears upon inserting employee data:

SQL> insert all
2 into employee
3 values (11111111, ‘Milford’,null, ‘Plopsquat’, null)
4 into employee
5 values (11111111, ‘Ernestine’,’Hazel’,’Bumbershoot’, sysdate-900)
6 select * from dual;
insert all
*
ERROR at line 1:
ORA-00001: unique constraint (BING.EMPLOYEE_PK) violated

Two issues occur here, first is the duplicate employee_id, the second is tne NULL hire_date. Fixing the hire_date issue is simply to make hire_date a NOT NULL column:

alter table employee modify hire_date not null;

The second is to assign a correct employee id to Ernestine. Making those changes and supplying a valid hire date for Milford produces:

SQL> insert all
2 into employee
3 values (11111111, ‘Milford’,null, ‘Plopsquat’, sysdate – 773)
4 into employee
5 values (11111112, ‘Ernestine’,’Hazel’,’Bumbershoot’, sysdate-900)
6 select * from dual
7 /

2 rows created.

The employee data is now correct and all future employees entered will require an employee_id, first_name, last_name and hire_date. Additionally the employee_id will be unique because of the primary key.

Employees are present in the database, but contact information is lacking. The company wants an address, phone number and an email address for each employee. Since multiple phone numbers can exist for each employee a primary phone is required; the address is also required, however the email address is not. The contact_info table definition becomes:

SQL> desc contact_info
Name Null? Type

---

EMPLOYEE_ID NOT NULL NUMBER
ADDRESS_LINE_1 NOT NULL VARCHAR2(80)
ADDRESS_LINE_2 VARCHAR2(80)
CITY NOT NULL VARCHAR2(80)
STATE NOT NULL VARCHAR2(2)
ZIPCODE NOT NULL VARCHAR2(10)
PRIMARY_PHONE NOT NULL VARCHAR2(12)
SECONDARY_PHONE VARCHAR2(12)
EMAIL VARCHAR2(80)

SQL>

Providing referential integrity to the dependent data a foreign key is created against the employee table:

alter table contact_info add constraint contact_info_fk foreign key (employee_id) references employee;

This ensures that the employee_id values in the contact_info table also are values that occur in the employee table. [Oracle is aware that the primary key column of employee is the same as the foreign key column of contact_info, which is why it wasn’t required to list the primary key column in the constraint definition. Should the foreign key column not match that of the parent table the column name for the primary key would be specified.] Currently we have two valid values, 11111111 and 11111112. Adding contact info for the two existing employees:

SQL> insert into contact_info
2 values(11111111, ‘1314 15th Street’,null,’Pickleburg’,’VA’,’44432-9983′, ‘774-775-7654′,null,’mplopsq@floptwaddle.com’);

1 row created.

SQL>
SQL> insert into contact_info
2 values(11111112, ‘654 32bd Avenue’,’Apt 345′,’Squonk’,’WA’,’72315-0004′, ‘974-675-7654′,null,’ebumber@floptwaddle.com’);

1 row created.

SQL>

Attempting to add a third address, for an employee that was just hired but not yet entered into the employee table produces:

SQL> insert into contact_info
2 values(11111113, ‘654 32bd Avenue’,’Apt 745′,’Squonk’,’WA’,’72315-0004′, ‘974-675-7654′,null,’ebumber@floptwaddle.com’);
insert into contact_info
*
ERROR at line 1:
ORA-02291: integrity constraint (BING.CONTACT_INFO_FK) violated – parent key not found

SQL>

Adding this new employee to the employee table fixes that issue:

SQL> insert all
2 into employee
3 values (11111113, ‘Hester’,null, ‘Obnotz’, sysdate – 763)
4 into employee
5 values (11111114, ‘Elwood’,’Hooper’,’Snibble’, sysdate-762)
6 select * from dual
7 /

2 rows created.

SQL> insert into contact_info
2 values(11111113, ‘654 32bd Avenue’,’Apt 745′,’Squonk’,’WA’,’72315-0004′, ‘974-675-7654′,null,’ebumber@floptwaddle.com’);

1 row created.

SQL>

[It is left to the reader to provide contact information for Elwood Snibble; the provided examples should be a good guide for getting that task completed.]

Notice how the various constraints ensured that the data was correct on insert and could be validated for accuracy — the primary key provides unique employee_id values so each employee can be correctly identified, the NOT NULL constraints make certain that required fields are populated and the foreign key constraint verifies that the parent record for each child record is present in the employee table.

There are other topics that appear to be either glossed-over or poorly understood with respect to DBA knowledge; those will wait for another post, mainly because they take a bit more space, and time, to discuss. Hopefully this offering, on various constraints, has been helpful. It hasn’t covered all of the possible constraints but it should provide a good start for those new to the career or those whose memory might be a bit … lacking.

Being a successful DBA does encompass a broad range of knowledge and a fairly robust skill set, along with a solid grounding in the basics of the job. Even though questions on foreign keys and check constraints aren’t common workplace conversation they should not stop the DBA in his/her tracks. The ability to explain, simply and concisely, such topics is, I think, key to a long and fruitful career as a database administrator. Yes, the landscape is changing, and new information is always something the DBA must acquire, but that shouldn’t replace basic concepts like constraints and referential integrity, it should augment that knowledge.

As one network puts it, “The more you know…”

Put It Into The Pipeline

Time is of the essence, especially with database performance. DBAs regularly spend time tuning queries and database parameters in an effort to speed things up for the end users. One area that is usually missed or ignored involves the output mechanisms for PL/SQL functions and procedures. Normally the dbms_output package is invoked, which does a pretty good job of getting output to the screen. It can be slow, however, as the PL/SQL block must finish its work before dbms_output can display anything. Enter the pipelined function, a way to send output to the screen at the time it’s generated, relying solely on the arraysize setting for the sqlplus session. Let’s look at how a pipelined function can get results to the screen faster.

Pipelined functions differ from regular functions in two ways:

1) A table type must be created for the function to act upon
2) The function must be declared as pipelined

Once these prerequisites are in place a pipelined function can do its work. Scouring the web, examples of pipelined functions are fairly easy to find. To simplify matters some examples from Tim Hall (ORACLE_BASE) and one from Morgan’s Library are included to illustrate the concepts. Additional comments have been added to provide guidance and details for these examples. We start with Tim Hall’s offerings, first with a table type built from a multi-column object. The code is provided::

—

— These examples are from ORACLE_BASE

set echo on pagesize 30
— iDrop the types to ensure the definitions are as we expect
DROP TYPE t_tf_tab;
DROP TYPE t_tf_row;

—

— Create the types, first the rowtype

— This is what the row contains

— This is the only way to create a multi-column rowtype

CREATE TYPE t_tf_row AS OBJECT (
id NUMBER,
description VARCHAR2(50)
);
/

—

— Create table type using the row type above

CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
/

—

— Build a pipelined table function.

— Notice that both dbms_output and pipe row are used for display
— Interestingly the piped rows appear first, even though the

— dbms_output is executed before the row is piped

CREATE OR REPLACE FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_tf_tab PIPELINED AS
BEGIN
FOR i IN 1 .. p_rows LOOP
DBMS_OUTPUT.put_line(‘Row: ‘ || i);
PIPE ROW(t_tf_row(i, ‘Description for ‘ || i));
dbms_lock.sleep(1);
END LOOP;

RETURN;
EXCEPTION
WHEN NO_DATA_NEEDED THEN
RAISE;
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(‘OTHERS Handler’);
RAISE;
END;
/

Now we execute the pipelined function, using an arraysize of 1:

SQL> — Test it.
SQL> SET SERVEROUTPUT ON
SQL> SET ARRAYSIZE 1
SQL>
SQL> SELECT *
2 FROM TABLE(get_tab_ptf(25))
3 WHERE rownum <= 15;

ID DESCRIPTION

---

 1 Description for 1
 2 Description for 2
 3 Description for 3
 4 Description for 4
 5 Description for 5
 6 Description for 6
 7 Description for 7
 8 Description for 8
 9 Description for 9
10 Description for 10
11 Description for 11
12 Description for 12
13 Description for 13
14 Description for 14
15 Description for 15

15 rows selected.

Row: 1
Row: 2
Row: 3
Row: 4
Row: 5
Row: 6
Row: 7
Row: 8
Row: 9
Row: 10
Row: 11
Row: 12
Row: 13
Row: 14
Row: 15
SQL>

Notice that even though the dbms_output call was executed BEFORE the pipe row call the pipelined output appears first, illustrating how a pipelined function returns data as it’s generated, rather than filling an output buffer and waiting for the PL/SQL code block to complete.

The next example from Tim uses a single-column table type with an embedded call to dbms_lock.sleep(), to delay the next row processing for 1 second. The arraysize is set to 1 and the data is displayed on the screen one row at a time:

—

— Create a single-column table type using a standard Oracle datatype

CREATE OR REPLACE TYPE msgType AS TABLE OF VARCHAR2(60);
/

—

— Create pipelined table function

CREATE OR REPLACE FUNCTION msgOutStream RETURN msgType
PIPELINED AUTHID CURRENT_USER AS
BEGIN
PIPE ROW(‘start run ‘ || sysTimestamp);
FOR i IN 1 .. 10 LOOP
PIPE ROW(‘output ‘ || TO_CHAR(i) || ‘ ‘ || sysTimestamp);
dbms_lock.sleep(1);
PIPE ROW(‘output ‘ || TO_CHAR(i) || ‘ completed’);
END LOOP;
RETURN;
END;
/

With an arraysize of 1 rows are displayed essentially one at a time (due to the sleep); the final output is shown below, with the time each row was presented to the screen:

SQL>
SQL> —
SQL> — Output depends on the arraysize
SQL> —
SQL> — Show each row as it’s piped
SQL> —
SQL> SET ARRAYSIZE 1
SQL>
SQL> SELECT * FROM TABLE(msgOutStream);

COLUMN_VALUE

start run 17-MAR-22 03.47.51.669080000 PM -06:00
output 1 17-MAR-22 03.47.51.669580000 PM -06:00
output 1 completed
output 2 17-MAR-22 03.47.52.688527000 PM -06:00
output 2 completed
output 3 17-MAR-22 03.47.53.713174000 PM -06:00
output 3 completed
output 4 17-MAR-22 03.47.54.734706000 PM -06:00
output 4 completed
output 5 17-MAR-22 03.47.55.760411000 PM -06:00
output 5 completed
output 6 17-MAR-22 03.47.56.783193000 PM -06:00
output 6 completed
output 7 17-MAR-22 03.47.57.806690000 PM -06:00
output 7 completed
output 8 17-MAR-22 03.47.58.834595000 PM -06:00
output 8 completed
output 9 17-MAR-22 03.47.59.820601000 PM -06:00
output 9 completed
output 10 17-MAR-22 03.48.00.844703000 PM -06:00
output 10 completed

21 rows selected.

SQL>

Note the time difference between consecutive rows, which is 1 second plus the fractional time the execution takes. To prove the rows actually do appear one at a time simply execute the provided code including the type creation — be certain to set the arraysize to 1 prior to execution.

Both of the above examples generate data ‘on-the-fly’, which is fine for proof of concept testing or for educational purposes. Fetching data from a cursor is more ‘real-world’, so the following example, as noted before from Morgan’s Library, provides a way to modify some existing functions utilizing cursors to be pipeline-friendly. It starts as the other examples do, with a table type created for the pipelined function to operate on:

—

— Using a cursor to fetch data to pipe

— Type and function from Morgan’s Library

— First create a single-column table type

CREATE OR REPLACE TYPE name_t AS TABLE OF VARCHAR2(30);
/

The function is now created:

—

— Create the pipelined function using the above type

CREATE OR REPLACE FUNCTION dependencies(pkg_name IN VARCHAR2)
RETURN name_t AUTHID CURRENT_USER PIPELINED AS
CURSOR dcur IS
SELECT name FROM dba_dependencies
WHERE referenced_name = pkg_name
AND name <> pkg_name
AND name <> ‘STANDARD’
UNION
SELECT referenced_name from dba_dependencies
WHERE name = pkg_name
AND referenced_name <> pkg_name
AND referenced_name <> ‘STANDARD’;
BEGIN
FOR drec IN dcur LOOP
PIPE ROW(drec.name);
END LOOP;
RETURN;
END dependencies;
/

Time to test the function; the pipelined function is nested in a TABLE() function, and the pipelined function output is cast to the newly created table type:

SQL>
SQL> SELECT * FROM TABLE(CAST(dependencies(‘OWA_OPT_LOCK’) AS name_t));

COLUMN_VALUE

DBMS_ASSERT
DBMS_SQL
DBMS_STANDARD
HTF
HTP

SQL>

The table type must be created with the proper columns returned by the cursor, so not every cursor is a qualified candidate for pipelining. Using a row object type to create such a table type has issues when using cursors as the pipelined data source, and packages cannot include pipelined functions as errors will be thrown during the attempt. Pipelining data from a function isn’t a universal solution, but it can be a tool to consider during the development phase. Such decisions must be considered carefully and tested prior to any attempt at implementation. Consider it one more option to return data from the database.

As in all database work no one tool or process is suitable for every situation; knowing the limitations of each tool or process to be considered is key to providing a successful solution with a minimum of risk. The goal is to make the work the end users are doing both successful and as efficient as possible. Choose methods wisely to avoid unnecessary work and to bring usability and reliability to the user community.

Just one more suggestion added to the pipeline.

Idle Hands

V$SESSION can provide a wealth of session-level information, unless idle sessions are the target of investigation. The V$SESSION view reports the session state as:

WAITING
WAITED SHORT TIME
WAITED UNKNOWN TIME

Adding status to the query results doesn’t help much as the following status values are reported:

ACTIVE
INACTIVE
KILLED
CACHED
SNIPED

which still doesn’t provide a definite answer as to whether a session is idle or not. A status of WAITED SHORT TIME combined with a state of INACTIVE really doesn’t provide a definite answer to the ‘is the session idle?’ question. Yes, one can presume those two results combine to identify a session as idle, but that could also be a fleeting state. Can a better, more reliable, answer on idle sessions be obtained? Using a different view, V$SESSION_WAIT, in conjunction with V$SESSION, has possibilities; let’s try that and see where that ‘solution’ gets us:

SQL> select s.sid, s.serial#, s.state, s.status, w.wait_class
2 from v$session s join v$session_wait w on w.sid = s.sid
3 order by s.status;

     SID      SERIAL# STATE               STATUS   WAIT_CLASS

---

       1            1 WAITING             ACTIVE   Idle
    4074        50877 WAITING             ACTIVE   Idle
       3            1 WAITING             ACTIVE   Idle
       8          833 WAITING             ACTIVE   Idle
     104        18889 WAITING             ACTIVE   Idle
     106        29271 WAITED SHORT TIME   ACTIVE   Network
     110        45121 WAITING             ACTIVE   Idle
     132        50453 WAITING             ACTIVE   Idle
     567            1 WAITING             ACTIVE   Idle
     570            3 WAITING             ACTIVE   Idle
     668           13 WAITING             ACTIVE   Idle

…

     SID      SERIAL# STATE               STATUS   WAIT_CLASS

---

       7           19 WAITING             INACTIVE Idle
       9           15 WAITING             INACTIVE Idle
      10        63437 WAITING             INACTIVE Idle
      11        37529 WAITING             INACTIVE Idle
      13           13 WAITING             INACTIVE Idle
      14        14055 WAITING             INACTIVE Idle
      15           73 WAITING             INACTIVE Idle
      16         1303 WAITING             INACTIVE Idle
      18        32745 WAITING             INACTIVE Idle
      20          255 WAITING             INACTIVE Idle
      21          741 WAITING             INACTIVE Idle

…

802 rows selected.

From this ‘report’ there are 52 sessions listed as ACTIVE, but with a wait_class of Idle. One session shows a non-idle wait_class, and 749 sessions are marked as INACTIVE and Idle. It appears that even though V$SESSION_WAIT does list sessions as being in an Idle wait_class ACTIVE sessions, not currently processing data at the time this query was executed, are also in that same wait_class. Interestingly sessions marked as INACTIVE may not be entirely ‘inactive’ as any session not currently executing SQL is considered as inactive, even if the user is typing in a query statement. Once SQL execution begins the session becomes ACTIVE and remains in that state until the execution completes. But there can be truly idle sessions in the database, users logged in for hours or days without executing a single statement. Given that the rudimentary query provided above won’t show those sessions accurately as idle it appears the modifications don’t really help. Maybe there’s another way …

V$SESSION conveniently records the elapsed time from the last call a session made, where a ‘call’ can be a SQL execution or any status switch, like from ACTIVE to INACTIVE. For ACTIVE sessions this value reports the time the call was active, doing work. For INACTIVE sessions it reports the elapsed time the session has been idle. The units for this value are seconds, and it’s fairly easy to convert seconds to minutes, hours or days. Given that information let’s construct a query to report active/idle time by session. Let’s start with a basic query to return the sid, serial#, status and elapsed_time for all user sessions (excluding SYS and SYSTEM):

SELECT sid, serial#, status, last_call_et
FROM v$session
WHERE username IS NOT NULL AND username NOT IN (‘SYS’,’SYSTEM’)
ORDER BY last_call_et;

When the status value changes, from ACTIVE to INACTIVE or the reverse, LAST_CALL_ET starts counting seconds from 0. Refining the above query to consider only INACTIVE sessions produces:

SELECT sid, serial#, status, last_call_et
FROM v$session
WHERE username IS NOT NULL AND username NOT IN (‘SYS’,’SYSTEM’)
AND status = ‘INACTIVE’
ORDER BY last_call_et;

Now we have a report of the elapsed seconds for idle sessions, which can include LAST_CALL_ET values from 0 upwards. Since that’s probably not what we want to report let’s add a limiting condition to report elapsed times longer than the provided limit, again in seconds:

SELECT sid, serial#, status, last_call_et
FROM v$session
WHERE username IS NOT NULL AND username NOT IN (‘SYS’,’SYSTEM’)
AND status = ‘INACTIVE’
AND last_call_et > &1
ORDER BY last_call_et;

The &1 construct may be new to some — this is how SQLPlus passes in command-line arguments to a query (&1 is the first parameter after the script name, &2 the second, and so on). Comments in the final script should provide that information so the DBA knows how to submit the script to SQLPlus to provide the desired output. Let’s query the database for sessions with idle elapsed times greater than 60 seconds; in this example the script is named rpt_idle_sess.sql:

SQL> @rpt_idle_sess 60

     SID      SERIAL# STATUS   LAST_CALL_ET

---

    1148           25 INACTIVE           99
       5            1 INACTIVE          137
    1819        18765 INACTIVE          143
    1237        19599 INACTIVE          144
     581           49 INACTIVE          159
     577         5983 INACTIVE          160
    2371        36861 INACTIVE          161
    1264        39861 INACTIVE          203
    1815        32657 INACTIVE          203
    2368         8653 INACTIVE          203
    2400        39233 INACTIVE          203

     SID      SERIAL# STATUS   LAST_CALL_ET

---

    2849         1505 INACTIVE          204
    1710        13061 INACTIVE          204
    2959        50263 INACTIVE          263
    1715           45 INACTIVE          280
    2372        36185 INACTIVE          322
      15           73 INACTIVE          358
    3400            3 INACTIVE          428
     569           15 INACTIVE          548
    1832         2091 INACTIVE          802
    1703            7 INACTIVE          809
    2851        32009 INACTIVE          819

     SID      SERIAL# STATUS   LAST_CALL_ET

---

    1137           23 INACTIVE          851
     579         7837 INACTIVE          971
    1230           11 INACTIVE         4438
      97           25 INACTIVE         4438
    4061           15 INACTIVE         5111
    2364           51 INACTIVE         5154
     664           25 INACTIVE         5214
    1798           19 INACTIVE         5274
     663           13 INACTIVE         5431
    1231           25 INACTIVE         5535
      98           45 INACTIVE         5756

     SID      SERIAL# STATUS   LAST_CALL_ET

---

    1799            9 INACTIVE         5863

…
3970 53 INACTIVE 219492
568 7 INACTIVE 219497

126 rows selected.

Since a limiting value can be passed to the script we can peruse the current report data and determine a better limiting value for the report. Given a good number of sessions are waiting in excess of 200,000 seconds (almost two and a half days) let’s see how many sessions meet that idle time criteria:

SQL> @rpt_idle_sess 200000

 SID      SERIAL# STATUS   LAST_CALL_ET

---

 671        39683 INACTIVE   213185
3983         2139 INACTIVE   213185
2274          737 INACTIVE   213282
2282          743 INACTIVE   213283
2931        1 INACTIVE   219918
3493           17 INACTIVE   219918
1797        7 INACTIVE   219920
2929           17 INACTIVE   219920
2362           19 INACTIVE   219920
3494        5 INACTIVE   219920
1227        3 INACTIVE   219921

 SID      SERIAL# STATUS   LAST_CALL_ET

---

1228        3 INACTIVE   219921
1229        3 INACTIVE   219921
1792        7 INACTIVE   219921
1794        3 INACTIVE   219921
1796        1 INACTIVE   219921
2357        3 INACTIVE   219921
2358        7 INACTIVE   219921
2359        3 INACTIVE   219921
2360        3 INACTIVE   219921
2361        3 INACTIVE   219921
2924        7 INACTIVE   219921

 SID      SERIAL# STATUS   LAST_CALL_ET

---

2926        3 INACTIVE   219921
2927        3 INACTIVE   219921
2928        3 INACTIVE   219921
3483          487 INACTIVE   219921
3487           17 INACTIVE   219921
3488        5 INACTIVE   219921
3489        1 INACTIVE   219921
3491        3 INACTIVE   219921
4057        5 INACTIVE   219921
4058        3 INACTIVE   219921
4059        3 INACTIVE   219921

 SID      SERIAL# STATUS   LAST_CALL_ET

---

1226        3 INACTIVE   219921
1224        5 INACTIVE   219921
 662        7 INACTIVE   219921
 661        3 INACTIVE   219921
 660        3 INACTIVE   219921
 659        3 INACTIVE   219921
 658        9 INACTIVE   219921
  95        3 INACTIVE   219921
4060        5 INACTIVE   219921
  92        7 INACTIVE   219921
  94        3 INACTIVE   219921

 SID      SERIAL# STATUS   LAST_CALL_ET

---

  87          407 INACTIVE   219922
  88           41 INACTIVE   219922
 653          283 INACTIVE   219922
 655        3 INACTIVE   219922
 656        3 INACTIVE   219922
 657        3 INACTIVE   219922
1220          243 INACTIVE   219922
1221        3 INACTIVE   219922
1222        3 INACTIVE   219922
1223        5 INACTIVE   219922
1769           11 INACTIVE   219922

 SID      SERIAL# STATUS   LAST_CALL_ET

---

1787          345 INACTIVE   219922
1789        3 INACTIVE   219922
2353          465 INACTIVE   219922
2354        3 INACTIVE   219922
2355        3 INACTIVE   219922
2356           13 INACTIVE   219922
3465        3 INACTIVE   219922
3484           15 INACTIVE   219922
3485        3 INACTIVE   219922
3486        9 INACTIVE   219922
4034        9 INACTIVE   219922

 SID      SERIAL# STATUS   LAST_CALL_ET

---

4053        3 INACTIVE   219922
4054        3 INACTIVE   219922
4055           13 INACTIVE   219922
3410           53 INACTIVE   220016
2847           57 INACTIVE   220016
  13           13 INACTIVE   220025
3407           65 INACTIVE   220025
 578           57 INACTIVE   220025
2844           57 INACTIVE   220025
3976           15 INACTIVE   220025
1146        7 INACTIVE   220025

 SID      SERIAL# STATUS   LAST_CALL_ET

---

1711           41 INACTIVE   220026
2273           23 INACTIVE   220034
2840           21 INACTIVE   220034
3404           27 INACTIVE   220034
3970           53 INACTIVE   220034
   9           15 INACTIVE   220034
1140        3 INACTIVE   220035
 573        9 INACTIVE   220035
 568        7 INACTIVE   220039
1136        5 INACTIVE   220039

87 rows selected.

Of the 126 sessions reported by the first run 87 have sat idle for over 200,000 seconds. Since no idle_time limits have been set for the profiles associated with these user accounts the idle time will continue to accrue until the sessions become active, the user session terminates or the database is shutdown and started.

Can we make the output more “user friendly”? Yes; let’s make a pass at that to convert seconds to minutes, hours or days depending upon the LAST_CALL_ET value for a given session:

—

— &1 is the limiting value, in seconds, for this report

— Sessions with elapsed times longer than &1 will be reported/

set verify off

SELECT sid, serial#, status,
case when last_call_et < 120 then last_call_et|| ‘ seconds’ when last_call_et >= 120 and last_call_et <= 43200 then round(last_call_et/60)||’ minutes’ when last_call_et > 43200 and last_call_et < 86400 then round(last_call_et/60/60)||’ hours’ when last_call_et = 86400 then round(last_call_et/60/60/24)||’ day’ when last_call_et > 86400 then round(last_call_et/60/60/24)||’ days’
end idle_time
FROM v$session
WHERE username IS NOT NULL AND username NOT IN (‘SYS’,’SYSTEM’)
AND status = ‘INACTIVE’
AND last_call_et > &1
ORDER BY last_call_et;

[The line “set verify off” tells SQL*Plus to not print before and after lines for variable substitutions. Not having that set will display “before” and “after” lines containing the line or lines containing & variables and the resulting substitutions.]

Executing that script produces:

 SID      SERIAL# STATUS   IDLE_TIME

---

 634        7 INACTIVE 61 seconds
3464           17 INACTIVE 63 seconds
2335        5 INACTIVE 68 seconds
1200        3 INACTIVE 68 seconds
 694        61971 INACTIVE 68 seconds
4022           17 INACTIVE 73 seconds
 628        3 INACTIVE 73 seconds
2896        3 INACTIVE 73 seconds
2899        3 INACTIVE 73 seconds
3460        3 INACTIVE 73 seconds
1756        3 INACTIVE 73 seconds

…

 SID      SERIAL# STATUS   IDLE_TIME

---

4061           15 INACTIVE 15 minutes
  97           25 INACTIVE 15 minutes
2372        36185 INACTIVE 15 minutes
2959        50263 INACTIVE 19 minutes
1832         2091 INACTIVE 20 minutes
1819        18765 INACTIVE 25 minutes
  15           73 INACTIVE 33 minutes
3400        3 INACTIVE 34 minutes
 569           15 INACTIVE 36 minutes
1703        7 INACTIVE 40 minutes
2851        32009 INACTIVE 41 minutes

 SID      SERIAL# STATUS   IDLE_TIME

---

1137           23 INACTIVE 41 minutes
 579         7837 INACTIVE 43 minutes
1230           11 INACTIVE 101 minutes
2364           51 INACTIVE 113 minutes
 663           13 INACTIVE 117 minutes
1231           25 INACTIVE 119 minutes
  98           45 INACTIVE 123 minutes
1799        9 INACTIVE 125 minutes
1269        36025 INACTIVE 2 days
3457        47095 INACTIVE 2 days
  11        37529 INACTIVE 2 days

…
573 9 INACTIVE 3 days
1140 3 INACTIVE 3 days
568 7 INACTIVE 3 days
1136 5 INACTIVE 3 days

660 rows selected.

This report provides considerable information on idle sessions in an Oracle database — providing both the SID and SERIAL# gives the DBA all necessary information to terminate sessions where the idle time is in excess of, say, 1 day to reduce the mempruy footprint and free up resources that other, active sessions may require. Taking that script as a starting point it’s possible to create a script to generate statements to kill sessions with extremely long idle elapsed times:

set pagesize 0

SELECT ‘alter system kill session ”’||sid||’,’||serial#||”’;’
FROM v$session
WHERE username IS NOT NULL AND username NOT IN (‘SYS’,’SYSTEM’)
AND status = ‘INACTIVE’
AND last_call_et > 200000
ORDER BY last_call_et

spool /tmp/kill_long_idle.sql
/
spool off

@/tmp/kill_long_idle

It’s not recommended that such a script be executed without first notifying users and administrators that such accounts will be terminated; this is provided as an example of how such a script could be written to clear out “dead wood” — sessions that have sat idle for days without activity. It’s best to run the report on a regular basis, investigate the sessions exhibiting long idle times and decide on a course of action, if any action is required. Connection pooling can keep idle sessions connected to speed access to the database and such sessions should probably be left alone. The goal is to report idle sessions and their elapsed idle times, and the final report shown above appears to do that quite well.

We see that reporting truly idle database sessions takes a bit more than a quick status query of V$SESSION, but the resulting statement does produce a usable, accurate report. Regular executons of that query can provide the DBA with all of the data necessary to assess which sessions have overstayed their welcome, so to speak, so administrative action, if required, can be taken.

You know what they say about idle hands…

“My, What Big Logs You Have…”

Supplemental logging, as mentioned in a prior post, increases the size of the redo record based upon the columns configured for logging. ALL is a valid option, which, as one might expect, logs all table columns in the redo stream for every table configured in this manner. As configured ‘out of the box’ Oracle logs only the changed columns for a given table/row, making the redo stream more compact. Obviously this column count changes depending upon the change executed and the table or tables involved. Let’s look at a conventional redo record and one for supplemental logging and note the differences.

Oracle uses change vectors to more efficiently record redo information and to make the redo stream more compact. Looking at an update recorded for a table not configured for supplemental logging we see that of the 26 columns in the table there were 17 that were updated. The redo record reports which columns (according to the ordinal position, starting with 0) were affected:

CHANGE #2 CON_ID:3 TYP:2 CLS:1 AFN:17 DBA:0x00406c7c OBJ:18 SCN:0x0000000000c2395b SEQ:1 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x000a.014.00001013 uba: 0x02400a58.025a.11
Block cleanout record, scn: 0x0000000000c23961 ver: 0x01 opt: 0x02 bigscn: Y compact: Y spare: 00000000, entries follow…
itli: 1 flg: (opt=2 whr=1) scn: 0x0000000000c2395b
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00406c7c hdba: 0x004000f0
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 1 ckix: 0
ncol: 26 nnew: 17 size: 0
col 0: [ 4] c3 09 2d 22
col 1: NULL
col 6: [ 2] c1 08
col 7: [ 7] 78 7a 03 01 17 31 12
col 8: [ 7] 78 7a 03 01 17 31 12
col 9: [ 7] 78 7a 03 01 17 31 12
col 10: [ 2] c1 02
col 13: [ 5] c4 02 05 56 4d
col 14: NULL
col 15: [ 2] c1 07
col 16: [ 4] c3 07 38 24
col 17: [ 1] 80
col 21: [16] 15 b3 17 92 21 ba 9e 38 ce c9 75 17 e3 6b 5c 38
col 22: [ 1] 80
col 23: [ 1] 80
col 24: [ 1] 80
col 25: [ 4] c3 02 40 53

Notice that there are gaps in the column numbers; this is expected behavior when the standard redo vector strategy is in use.

Supplemental logging, for all columns, paints a different picture for an update, as all columns are reported in the redo record regardless of whether or not values are modified. The following redo record is for a table configured for supplemental logging of all columns:

CHANGE #2 TYP:2 CLS:1 AFN:1 DBA:0x00413880 OBJ:18 SCN:0x0014.ac6fe208 SEQ:1 OP:11.2 ENC:0 RBL:0
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0004.013.004a0ce7 uba: 0x00c1605a.8d1a.25
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00413880 hdba: 0x004000f0
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 46(0x2e) size/delt: 72
fb: –H-FL– lb: 0x2 cc: 18
null:
01234567890123456789012345678901234567890123456789012345678901234567890123456789
-N—N—–NN-N—
col 0: [ 5] c4 02 10 55 35
col 1: NULL
col 2: [ 2] c1 02
col 3: [ 8] 44 55 4d 50 5f 4c 4f 47
col 4: [ 2] c1 02
col 5: NULL
col 6: [ 2] c1 06
col 7: [ 7] 78 7a 03 01 12 2b 2e
col 8: [ 7] 78 7a 03 01 12 2b 2e
col 9: [ 7] 78 7a 03 01 12 2b 2e
col 10: [ 2] c1 02
col 11: NULL
col 12: NULL
col 13: [ 1] 80
col 14: NULL
col 15: [ 2] c1 07
col 16: [ 4] c3 07 38 24
col 17: [ 2] c1 02

This table contains 18 columns, all of which are recorded in the redo record since supplemental logging for all table columns has been set as active. Supplemental logging, therefore, increases the redo volume for those tables configured to use it. Since EVERY table in the database has NOT been altered to provide supplemental logging it’s reasonable to expect that the redo volume, although increased, won’t be so great as to double the original redo volume. Log switches will increase, that’s true, but not to the extent that the archive log destination will fill twice as fast as it did before supplemental logging was implemented. [For supplemental logging configurations involving only primary key, foreign key and unique columns the redo record increases by the number of columns declared as primary key, foreign key or as unique. Thus not all columns will be recorded for those configurations, but the redo stream will contain more data than usual.]

As long as supplemental logging is enabled the redo data for configured tables will continue to include more than the standard redo vector would provide. For production databases this usually means that once supplemental logging is enabled the redo stream will always contain the extra data such logging provides. For lower environments (DEV, UAT, INT) supplemental logging will remain in effect (provided it is NOT in use in the PROD environment) until the environment is refreshed, most likely from PROD. Once the refresh has completed and data is obfuscated it will be necessary, in lower environments, to run the supplemental logging configuration script or scripts again, to restore the additional logging these environments may require. [CDC replication testing is the main reason such logging would be in place for lower environments. Replication tasks and utilities will fail should the supplemental logging not be enabled, or not be enabled completely or properly.]

Oracle utilities and configurations such as GoldenGate and DataGuard rely on either forced logging (which causes the database to ‘ignore’ NOLOGGING requests) or on supplemental logging (GoldenGate requires both forced logging and supplemental logging) to function properly. Processes such as DMS (Data Migration Service, used to replicate and/or migrate data) to or from AWS require supplemental logging; forced logging can be enabled for DMS but it’s not required as normally only the tables of interest need the additional redo data. That can make the DBA’s life a bit easier.

Which leaves the DBA more time to answer such burning questions as “So, do I NEED a WHERE clause with this eight table join?”