The Benefits of Oracle Certification

Last year I posted three videos produced by my colleague Harold Green, from a talk I gave outlining the development process for Oracle DBA Certification exams including OCA, OCP, OCE and OCM. They described the process from design to completion of creating the exams and discussed the philosophy of the exam design. If you have not seen those videos before then you can find them here.

Harold had now produced another video, the first in a two part series on the benefits of Oracle Certification. In the first part I discuss the benefits to employees and explain what motivates DBAs and other to get certified. In the second part which will be released soon the benefits to employers are discussed.

Here is Part 1 covering Benefits to Employees

Here is Part 2 covering Benefits to Employers

What Is Your View of Your DBA Views?

A User Group delegate asked me recently if I ever post topics for new Oracle DBAs who may not yet be administering any advanced options such as RAC, ASM, Exadata etc.. Then I had a question during an Oracle Live Virtual Class (LVC) which I presented late last year also from a new DBA who wished to know if there is a quick way to examine all the possible values for certain status columns in Data Dictionary Views or in Dynamic Performance (V$ and GV$) views.

Many of these views contain various columns some of which are of datatype date, some are number, occasionally there are long or clob columns and for the V$ views there are raw columns representing pointers in from one memory structure to another. But there are many important columns that are varchar or maybe char and whose values are important in determining the current situation with regard to the resource in question.

Whilst the Oracle Database Reference contains information on Data Dictionary views in Part II (sections 2 through 6) and Dynamic Performance views in Part III (sections 7 through 9) and contains the various possible values and their meanings one must still find the documentation and navigate to the appropriate view. This may occur when one is creating a filter for example typically “WHERE <COLUMN_NAME> <operator> <VALUE>”  and wish to know the literal for “VALUE>”.

I have often encountered this when querying a view that I seldom use and altough I may know that there is a specific status in a column or a view, I may not remember the value or what other possible values are.

Lets start by using the Static Data Dictionary View “DBA_TABLESPACES“  as an example.  The columns “STATUS”, “LOGGING”, “FORCE_LOGGING” and all the other columns of “varchar2″ data type contain two or more possible values which may be used in the select list or the where clause or both in a DBA query.

Data Dictionary views are stand-alone queries or join queries that access the underlying base tables of the data dictionary and which occasionally join to dynamic performance views as well. Those objects contain metadata in encoded columns and these are decoded by the views. By querying the text of the views, one may see not only the underlying tables and views that are accessed but also the decode functions which contain all the possible values for the intersting status columns.

One may use the following script to access the text of Data Dictionary Views:

set long 20000
col text for a100
col view_name for a20
undefine viewname
select view_name,text from dba_views
where view_name like  upper (‘%&&viewname%’);
undefine viewname

Lets see what we get when looking at DBA_TABLESPACES:

SYS@tutor5d_nledu02 SQL> @sviewtext
SYS@tutor5d_nledu02 SQL> set long 20000
SYS@tutor5d_nledu02 SQL> col text for a100
SYS@tutor5d_nledu02 SQL> col view_name for a20
SYS@tutor5d_nledu02 SQL> undefine viewname
SYS@tutor5d_nledu02 SQL> select view_name,text from dba_views
2  where view_name like  upper (‘%&&viewname%’);
Enter value for viewname: DBA_TABLESPACES
old   2: where view_name like  upper (‘%&&viewname%’)
new   2: where view_name like  upper (‘%DBA_TABLESPACES%’)
Hit Any Key to Continue

VIEW_NAME
——————–
TEXT
——————————————————————————-
DBA_TABLESPACES
select ts.name, ts.blocksize, ts.blocksize * ts.dflinit,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
ts.blocksize * ts.dflincr),
ts.dflminext,
decode(ts.contents$, 1, to_number(NULL), ts.dflmaxext),
decode(bitand(ts.flags, 4096), 4096, ts.affstrength, NULL),
decode(bitand(ts.flags, 3), 1, to_number(NULL), ts.dflextpct),
ts.blocksize * ts.dflminlen,
decode(ts.online$, 1, ‘ONLINE’, 2, ‘OFFLINE’,
4, ‘READ ONLY’, ‘UNDEFINED’),
decode(ts.contents$, 0, (decode(bitand(ts.flags, 16), 16, ‘UNDO’,
‘PERMANENT’)), 1, ‘TEMPORARY’),
decode(bitand(ts.dflogging, 1), 0, ‘NOLOGGING’, 1, ‘LOGGING’),
decode(bitand(ts.dflogging, 2), 0, ‘NO’, 2, ‘YES’),
decode(ts.bitmapped, 0, ‘DICTIONARY’, ‘LOCAL’),
decode(bitand(ts.flags, 3), 0, ‘USER’, 1, ‘SYSTEM’, 2, ‘UNIFORM’,
‘UNDEFINED’),
decode(ts.plugged, 0, ‘NO’, ‘YES’),
decode(bitand(ts.flags,32), 32,’AUTO’, ‘MANUAL’),
decode(bitand(ts.flags,64), 64,’ENABLED’, ‘DISABLED’),
decode(bitand(ts.flags,16), 16, (decode(bitand(ts.flags, 512), 512,
‘GUARANTEE’, ‘NOGUARANTEE’)), ‘NOT APPLY’),
decode(bitand(ts.flags,256), 256, ‘YES’, ‘NO’),
decode(tsattr.storattr, 1, ‘STORAGE’, ‘HOST’),
decode(bitand(ts.flags,16384), 16384, ‘YES’, ‘NO’),
decode(bitand(ts.flags,64), 0, null,
decode(bitand(ts.flags,65536), 65536,’FOR ALL OPERATIONS’,
‘DIRECT LOAD ONLY’))
from sys.ts$ ts, sys.x$kcfistsa tsattr
where ts.online$ != 3
and bitand(flags,2048) != 2048
and ts.ts# = tsattr.tsid

1 row selected.

SYS@tutor5d_nledu02 SQL> undefine viewname
SYS@tutor5d_nledu02 SQL>



We can see that the view is based on SYS.TS$ and SYS.KCFISTSA which is a join between a Data Dictionary Base table and an X$ struct and we can also see the decode functions for various columns with all the values.

Another example is DBA_DATA_FILES:

SYS@tutor5d_nledu02 SQL> @sviewtext
SYS@tutor5d_nledu02 SQL> set long 20000
SYS@tutor5d_nledu02 SQL> col text for a100
SYS@tutor5d_nledu02 SQL> col view_name for a20
SYS@tutor5d_nledu02 SQL> undefine viewname
SYS@tutor5d_nledu02 SQL> select view_name,text from dba_views
2  where view_name like  upper (‘%&&viewname%’);
Enter value for viewname: DBA_DATA_FILES
old   2: where view_name like  upper (‘%&&viewname%’)
new   2: where view_name like  upper (‘%DBA_DATA_FILES%’)
Hit Any Key to Continue

VIEW_NAME
——————–
TEXT
——————————————————————————-
DBA_DATA_FILES
select v.name, f.file#, ts.name,
ts.blocksize * f.blocks, f.blocks,
decode(f.status$, 1, ‘INVALID’, 2, ‘AVAILABLE’, ‘UNDEFINED’),
f.relfile#, decode(f.inc, 0, ‘NO’, ‘YES’),
ts.blocksize * f.maxextend, f.maxextend, f.inc,
ts.blocksize * (f.blocks – 1), f.blocks – 1,
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, ‘SYSOFF’, ‘SYSTEM’),
decode(bitand(fe.festa, 18), 0, ‘OFFLINE’, 2, ‘ONLINE’, ‘RECOVER’))
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
where v.file# = f.file#
and f.spare1 is NULL
and f.ts# = ts.ts#
and fe.fenum = f.file#
union all
select
v.name,f.file#, ts.name,
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
decode(f.status$, 1, ‘INVALID’, 2, ‘AVAILABLE’, ‘UNDEFINED’),
f.relfile#,
decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, ‘NO’, ‘YES’), NULL),
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL),
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, ‘SYSOFF’, ‘SYSTEM’),
decode(bitand(fe.festa, 18), 0, ‘OFFLINE’, 2, ‘ONLINE’, ‘RECOVER’))
from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts, x$kccfe fe
where v.file# = f.file#
and f.spare1 is NOT NULL
and v.file# = hc.ktfbhcafno
and hc.ktfbhctsn = ts.ts#
and fe.fenum = f.file#

1 row selected.

SYS@tutor5d_nledu02 SQL> undefine viewname
SYS@tutor5d_nledu02 SQL>

Here we see that the view does a Union All Query to support multiple blocksizes but that each part of the query also refers to the base tables, X$ structs, and decode functions. Note also that in this view V$DBFILE is joined to FILE$ permitting the query to obtain the full path name of each data file is it appears in the controlfile. This explains why an “ALTER DATABASE RENAME FILE” command may be done with the database in MOUNT state when trhere is no access to the Data Dictionary. Only the CONTROL FILE is modified but once the database is opened DBA_DATA_FILES has the correct path names precisely because it is doing the join between FILE$ and V$DBFILE and thus getting the up to date path name from the control file.

A similar query may be done for Dynamic Performance views as follows:

set long 5000
col view_definition for a50
select view_name, view_definition
from v$fixed_view_definition
where view_name like upper(‘%&viewname%’);
undefine viewname
Lets now see what we get by looking at simple V$ view:

SYS@tutor5d_nledu02 SQL> /
Enter value for viewname: V$OPTION
old   3: where view_name like  ‘%&viewname%’
new   3: where view_name like  ‘%V$OPTION%’
Hit Any Key to Continue

VIEW_NAME            VIEW_DEFINITION
——————– ————————————————–
GV$OPTION            select inst_id,parameter, value from x$option
V$OPTION              select  PARAMETER , VALUE from GV$OPTION where inst_id = USERENV(‘Instance’)

2 rows selected.

SYS@tutor5d_nledu02 SQL>

First note how the V$ view queries the GV$ view of the same name for the current instance. This is generally true for all the V$ views. And note that we can see the X$ structs accessed by the GV$ View as well. Now lets try this with a slightly more complex V$ view:

SYS@tutor5d_nledu02 SQL> select view_name, view_definition
2  from v$fixed_view_definition
3  where view_name like upper(‘%&viewname%’);
Enter value for viewname: v$logfile
old   3: where view_name like upper(‘%&viewname%’)
new   3: where view_name like upper(‘%v$logfile%’)
Hit Any Key to Continue

VIEW_NAME                      VIEW_DEFINITION
—————————— ————————————————–

GV$LOGFILE                     select inst_id,fnfno, decode(fnflg,0,”,   decode(bitand(fnflg,1),1,’INVALID’,   decode(bitand(fnflg,2),2,’STALE’,   decode(bitand(fnflg,4),4,’DELETED’, decode(bitand(fnflg,8+32),8,”,32,”,40,”,’UNKNOWN’))))), decode(bitand(fnflg,8),0,’ONLINE’,'STANDBY’), fnnam, decode(bitand(fnflg, 32),0,’NO’,'YES’) from x$kccfn where fnnam is not null and fntyp=3

V$LOGFILE                      select  GROUP# , STATUS , TYPE , MEMBER, IS_RECOVERY_DEST_FILE from GV$LOGFILE where inst_id = USERENV(‘Instance’)

2 rows selected.

SYS@tutor5d_nledu02 SQL> undefine viewname

Once again the decodes are visible as well as the underlying structs.

In some cases one may also learn additional things by examining the queries in the views.

I find this a useful way to help people learn about important Oracle DBA views and the values one may use for filtering queries and I use this as a teaching tool for basic DBA courses at Oracle University. If you are somewhat new to Oracle DBA work, then have a go at using these and try some of the Data Dictionary or Dynamic Performance views that you commonly use.

Database Links Masterclass Part 2

Today I presented the second in my Database Links Masterclass series on Distributed transactions at the UKOUG Annual Conference. You can see details on my “Articles and Presentations” Page“

RMAN is Piping Hot

The RMAN “PIPE” Interface

Tips and Tricks for the Oracle DBA #1

RMAN is traditionally managed using the command line interface RMAN executable or by using Enterprise Manager. But there is a “third way” known as the “pipes” interface to RMAN, which uses the “DBMS_PIPE” Oracle, supplied package to provide PL/SQL programmes with an interface to RMAN.

The Oracle Documentation contains an example bit of code, which I have wrapped inside a demo to demonstrate the pipes interface. Essentially the RMAN command line executable must be started with the PIPE option and must specify the pipe name. Optionally a timeout value may be specified to terminate the RMAN executable if no command is sent down the named pipe within the timeout period. In my SQL*PLUS demo script RMAN is invoked automatically by shelling out to the host and running RMAN disconnected from the main script so that control returns to the SQL*PLUS script. One could of course drive this all from a shell script as well by running RMAN in the background and then running SQL*PLUS.

Here is an example of invoking RMAN to accept input from a pipe:

	SQL> Host rman pipe demo_pipe timeout 600 target / \&

Two pipes will be created called ORA$RMAN_DEMO_PIPE_IN and ORA$RMAN_DEMO_PIPE_OUT and used for inbound commands to RMAN and output from the commands.

The demo is commented and does the following:

1. Starts the RMAN Session in the background by hosting out
2. Checks that a session is waiting on a “pipe get” event
3. Uses DBMS_PIPE.PURGE to clean the two pipes
4. Uses the code from the documentation to create a stored procedure called RMAN_CMD
5. Checks for compilation errors
6. Uses the V$DB_PIPES view to show the two pipes’ attributes
7. Invokes the RMAN_CMD procedure several times with various RMAN commands
8. Invokes the RMAN_CMD procedure, passing EXIT as the message to terminate the RMAN executable that is running in the background.

Possible used for the pipes interface are:

1. Applications coded to automatically invoke one or more backup and recovery functions to achieve their design objectives
2. Third party tools which whish to provide their own interface to RMAN
3. DBA tools that wish to incorporate some RMAN functionality

The demo and spooled output are:

1. rman_pipes_sql – this contains the script
2. rman_pipes_spool – this is the spooled output of the script running on Linux.

How Good is your “Local” Management?

Whilst delivering a course recently, a delegate asked for my views about tablespace management and the ensuing discussion covered Locally managed tablespaces, bigfile tablespaces, and Automatic Segment Space Management (ASSM)  for tablespaces. During the discussion I surprised the class when I stated “there are actually three types of locally managed tablespaces” as only two were known to anyone in attendance.  It then occurred to me that this was one of those cases where things may have “fallen into the cracks” and that it should be clarified.

Locally managed tablespaces may be created by a DBA with one of the following two options:

• Uniform Size
• Autoallocate

When using the “Uniform Size” option, all free and allocated extents for the tablespace are of the specified size and each bit in the bitmap used to track storage represents an area of that size. This option provides the following advantages:

1. Extent tracking is local, being done in a bitmap metadata area within one of the tablespace files. This is the primary and well known advantage for local management and eliminates the use of the Data Dictionary for tracking free and allocated extents in base tables UET$ and FET$. In doing so there is also a reduction in contention for the Space Transaction enqueue (ST enqueue) which is used to single thread updates to space management tables in the data dictionary.
2. External Fragmentation in the tablespace is totally eliminated since all extents are the same size. Therefore, the first free extent found by reference to the bitmaps is chosen for allocation when space is required for a segment creation or extension.

There are also some important implications for the “Uniform Size” option.

1. Large uniform sizes may waste storage is small segments are created in the tablespace.
2. The DBA may require several different tablespaces, each using a different “Uniform Size” if there is a great variety in segment sizes.

When using the “Autoallocate” option, each bit in the bitmap used to track storage represents an area of 64K. This option provides the following advantages:

1. Extent tracking is local as in the “Uniform Size” option providing the same benefit in reducing ST enqueue overhead.
2. Extents may vary in size on multiples of 64K boundaries, providing for heterogeneous segment sizes in the same tablespace with little or no wasted storage.
3. Although all extents are multiples of 64K, when an extent is dropped the storage it occupies may be reallocated as several separate extents if required. External fragmentation, although possible, may not be as common as for dictionary managed tablespaces where extents are allocated on five block boundaries.

The third option which is not well known is the “Converted” tablespace. DBAs who wish to convert dictionary managed tablespaces to locally managed ones, use the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure.

PROCEDURE TABLESPACE_MIGRATE_TO_LOCAL
Argument Name                  Type                    In/Out Default?
-
TABLESPACE_NAME                VARCHAR2                IN
UNIT_SIZE                      BINARY_INTEGER          IN     DEFAULT
RFNO                           BINARY_INTEGER          IN     DEFAULT

The Parameters passed to this procedure are :

1. The name of the tablespace to convert which is a mandatory parameter.
2. The unit size that will be represented by each bit in the bitmap. This defaults to the highest common factor for all the existing allocated and free extents in the tablespace at the time the conversion is done.
3. The tablespace relative file number in which the bitmap should be created. This defaults to the lowest file number.

When using a “converted” tablespace the only real benefit accrued is the local management as in the two cases listed above. But unlike “Uniform Size” and “Autoallocate”, the fragmentation propensity for a converted tablespace remains as high as it is for Dictionary managed tablespaces. This is caused by the default unit_size calculation being set to the highest common factor of existing extents. Since the extent boundaries in dictionary manages tablespaces are on five block boundaries, it is usually the case that some or many extents in such a tablespace are only five blocks long. The highest common factor therefore is often a size of five blocks. If the tablespace was in a database with 4k blocksize, then the highest common factor will often be 20k unless the DBA had arranged to have larger minimum extent lengths before the conversion using the minimum extent length option available since Oracle 8.0.

It is for this reason that I do not recommend converting tablespaces but instead, prefer to create new ones and then move the contents over. Do do so I recommend the following:

1. Index Tablespaces – Create a new tablespace and then do “online index rebuilds” into the new tablespace and drop the old one when finished.
2. “Table” Tablespaces – Create a new tablespace and then use “online table redefinition” with the DBMS_REDEFINITION package, solely for changing the tablespace without changing the table’s logical or physical properties.

Finally, to determine if your database has any “converted” locally managed tablespace consult the DBA_TABLESPACES data dictionary view:

SQL> select tablespace_name,extent_management, allocation_type from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
-
SYSTEM                         LOCAL      SYSTEM
SYSAUX                         LOCAL      SYSTEM
UNDOTBS1                       LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM
USERS                          LOCAL      SYSTEM
UNDOTBS2                       LOCAL      SYSTEM
EXAMPLE                        LOCAL      USER
FRA                            LOCAL      SYSTEM

If the EXTENT_MANAGEMENT is LOCAL and the ALLOCATION_TYPE is USER then the tablespace has been converted. Creating new ones and moving segments into them can reduce fragmentation in those tablespaces and provide you with proper “Local” management.

Using ASM’s Cluster File System (ACFS) & Dynamic Volume Manager (ADVM) on a Single Node

Oracle 11gR2 ASM supports a new type of file called a “volume file” which may be created in its own DISK GROUP or which may share space in another disk group. Volume files are externalised to the Unix operating system by the oracleadvm device driver and appear dynamically as special files in the /dev/asm directory.

These volumes may be used as block devices, may contain a file system such as an ext3 system or the ASM cluster file system, or ACFS may be used in which case the oracleacfs driver is also used for IO to the file system.

In Single node configurations these Oracle supplied device drivers are not loaded automatically, but the system administrator may use the acfsload utility in the $ORACLE_HOME/bin directory of the Grid Infrastructure installation from where the ASM instance executes. This may be done as follows:

# /u01/app/oracle/product/11.2.0/grid/bin/acfsload start

Alternatively creating a script, which is invoked automatically by init at system startup, and setting the script as a service may automate this. The script which would be placed in the /etc/init.d directory and which could be called for example “acfsdrivers” would look something like this to perform the load silently:

#!/bin/sh

# chkconfig: 2345 30 21

# description: Load Oracle ACFS drivers at system boot

/u01/app/oracle/product/11.2.0/grid/bin/acfsload start -s

To automate this at boot time one would do the following as root:

# chmod u+x /etc/init.d/acfsdrivers

# chkconfig –add acfsdrivers

Accompanying this post is a demonstration script designed to run from SQLPLUS logged in the ASM instance on a single node running 11gR2 under Linux. In order to use it the Grid Infrastructure home owner must be added to /etc/sudoers, permitting execution of the utilities used in the demo with the sudo command, without requiring a password. In addition the name of the disk group should be changed to one that is present in your system and the name of the volume file changed to match the one created.

The demo is commented and does the following:

1.    Demonstrates the command line use of the following utilities

• “acfsload” –  to start and stop the drivers
• “advmutil” – to display ASM dynamic volume details
• “acfsutil” –   to register and unregister acfs mount points, display the “registry” contents, display acfs file system metadata and create and remove acfs snapshots.
• “asmcmd” –   to create and delete asm dynamic volumes, display volume metadata, list disk groups anddisplay volume statistics

2.    Creates an asm dynamic volume and file system

3.    Uses standard Linux utilities on these ASM components:

• “mkfs” – to create the asm file system
• “fsck”  – to check the file system
• “lsmod” – to display the loaded kernel modules
• “mount” – to mount the file system
• “umount” – to unmount the file system
• “mount.acfs” – to mount acfs file systems based on the registry

4.    Uses several new v$ views relevant to ADVM and ACFS.

You may download the script from here and the spooled output from advm_spool

Are you “Chained” to your Tables?

David Kurtz gave a very interesting and useful talk at yesterdays  UKOUG UNIX SIG concerning performance problems that he diagnosed at one of his customers. Whenever the number of logins exceeded 90 there was a correlation between that and the number of waits on “cache buffers chains latch” which he demonstrated visually by creating excel graphs from extracted AWR report data accessed via ODBC.

The diagnosis pointed to tables that had lob columns with the storage enabled in row. Most of the lob cells for most of the rows were short enough at the start to be included “in line”.  Subsequent updates to the lob cells however, sometimes caused the total length of the column to exceed 4096 bytes including the metadata thereby causing the lob data to be moved to the lob segment. This also had the effect of increasing the free space in the data block thereby allowing more rows to be inserted with short lob cells which later on could also have their data moved to the lob segment.

All of these updates caused rows to be partly contained in the data block and partly elsewhere and a discussion ensued about “migrated” rows.

But Oracle is a bit ambiguous about “Chained” rows and “Migrated” rows and so we had a discussion both during the Q&A and afterward, and I thought this topic needed to be revisited due to the ambiguity.

First lets define the terminology:

Chained Rows – Rows where the row headers indicate that the row is contained in two or more “pieces” due to:

1. A row having been inserted or updated such that the row  is larger than the maximum free space in an oracle block after accounting for the block headers. In this case the tablespace blocksize and the row length determine whether the row is chained and if so the “row pieces” are held in different blocks. Such a row uses the first byte of the row header called the flag byte to indicate which piece of the chain is in the current block and if not the last piece, then a rowid of the next piece follows the three byte row header pointing to the next piece in another block. It also uses the third byte of the row header called the column count to indicate how many columns are in each row piece.
2. A row having been inserted or updated such that the number of columns exceeds 255.  In this case the block size and row length relationship only determine whether or not different blocks are used for the pieces but there will be always be two or more “row pieces” depending on the number of columns in the table. This is known as “intra-block chaining” and has been a feature of Oracle since version 8.0 when support for more that 255 columns was added. Such a row uses the flag byte in the row header to indicate each piece of the chain and uses the column count to indicate how many columns are in this row piece.  If the row is chained only due to column count then all the row pieces will be in the same block with a maximum of four pieces and this will not be considered a “chained row” for performance reasons as only one block access is needed to fetch the row. If however the row also is too large then it will be chained as in case 1 and may have as many pieces as required.
3. A row having been updated making the row larger such that the updated version of the existing row is unable to fit into the current block due to insufficient free space. This type of chaining is known as “row migration” but is repairable as will be described later. In this case the original block will contain the original row header and the rowid pointing to where the row has moved but there will be no column data in the original block. If the row is migrated and fits into 1 block then the row header in the new block will indicate in the flag byte that there is only 1 row piece that is entirely in this block. If the row is migrated but is now also too large or has more than 255 columns or both, then the row will be both migrated and chained as described above.
4. A row in an Indexed Organised table (IOT) having an overflow segment and a row has overflowed some or all of the non-key columns. This may occur either because the row is longer than the “PCTTHRESHOLD” value which defines a limit as a percentage of the block space for storing data in IOT btree blocks. It may also occur due to the “INCLUDING” clause which specifies a cutoff column in the table after which all columns are overflowed for each row. If both clauses are used then whichever method results in a smaller row piece in the btree block will be used for that specific row.

Multi-Block rows – Rows where the row headers do NOT indicate that the row is contained in two or more “pieces” due to:

1. A row containing a lob column and either the “disable storage in row” clause was specified for the lob, or the “enable storage in row” clause was specified for the lob but the lob cell length for this row including lob metadata exceeds 4096  bytes.  In this case the lob metadata in the form of a “lob locator” points to the location of the lob data within a block of the lob segment or indirectly via the lob index segment. Note that these rows are NOT considered to be chained in the usual sense so that the “Analyze table xxx list chained rows” command will not report such a row as being chained even though multiple blocks may be required for row access. This is because the clob data is in a different segment not in another block of the same segment and because the row header will indicate in the flag byte that the entire row is in the one block. Furthermore all the columns are represented in the block but the out of line lob has only metadata in line in the block.

How can we tell that chained or migrated rows are affecting performance?

In the AWR or statspack reports or in the v$sysstat view look for the line containing statistic “table fetch continued row”. Here is an example:

select name, value from v$sysstat where name like ‘%table fetch%’;

NAME                                               VALUE

——-                                                 ——-

table fetch continued row                   9293

If the values in this statistic are rising over time then more and more rows that are “chained” in some way are being fetched causing extra latching and block pinning as well as extra I/O from time to time.

To determine if certain heap tables have chained rows due to any of the first three cases listed above one may look at the DBA_TABLES.CHAIN_CNT column but only if you use the ANALYZE command for statistics. Since best practice is to use DBMS_STATS, the solution is as follows:

1. Run the $ORACLE_HOME/rdbms/admin/utlchain.sql script
2. From the same user do “Analyze table xxx list chained rows” where xxx is the name of the table in question.
3. Query the chained_rows table to see details of any row in the table that has row pieces in multiple blocks.
4. Determine if repair or migrated rows is a useful activity

The HEAD_ROWID column contains the rowid for the start of the chain IE the rowid points to the block where the first 3 byte row header resides. If the row is migrated as in case three above then the entire row may be in one or more other blocks as mentioned.

If the row is chained due to either case 1 or 2 above, then the HEAD_ROWID points to the location of the first row piece. Subsequent row pieces may or may not be in the same block depending on the circumstances outlined above.

Only migrated rows as in case 3 may be fixed.  Since a migrated row remains migrated in order to preserve the rowid for index use, the only way to repair this is to delete and then reinsert all the “chained” rows. This would be useful if the average row length of the table is much smaller than the block size or if by describing the table one can determine that the maximum row length is smaller than the block size. In these cases many or most if not all the “chained” rows reported by ANALYZE .. LIST CHAINED ROWS command will be the migrated type which can be repaired.

To effect the repair of a table called TABA do the following:

1. Disable any triggers on the table.
2. Disable any constraints on the table.
3. Do  “CREATE TABLE TABA_TEMP AS SELECT * FROM TABA WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS);” to copy the chained rows to a holding table.
4. Do “DELETE FROM TABA WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS);” to delete the chained rows.
5. Then do “INSERT INTO TABA SELECT * FROM TABA_TEMP” to reinsert the rows as new rows in the table. As they are new they will be handled as any other rows are upon insert and the chained rows caused by the migration process listed above will now no longer be chained.
6. Delete the holding table
7. Re-enable the constrains if any exist
8. Re-enable the triggers if any exist.

To determine if IOTs have chained rows due to row pieces that are in an IOT overflow segment one may look at the DBA_TABLES.CHAIN_CNT column but only if you use the ANALYZE command for statistics. Since best practice is to use DBMS_STATS, the solution is as follows:

1. Run the $ORACLE_HOME/rdbms/admin/utlchn1.sql script. This script differs from the ultchain.sql script by creating a CHAINED_ROWS table with a UROWID data type instead of a ROWID data type in the HEAD_ROWID column to support the universal rowid format used for IOT chaining and IOT secondary indexes.
2. Then do an “ANALYZE TABLE xxx LIST CHAINED ROWS” as described earlier.
3. Then do a “SELECT * FROM CHAINED_ROWS“.

An alternative is to:

1. Use the “DBMS_IOT.BUILD_CHAIN_ROWS_TABLE“  procedure
2. Do the “ANALYZE TABLE xxx LIST CHAINED ROWS INTO IOT_CHAINED_ROWS;“
3. Then do a “SELECT * FROM IOT_CHAINED_ROWS“.

Regarding tables with LOB columns, one may examine the LOB properties from DBA_LOBS or DBA_PART_LOBS using the IN_ROW column. Out of line LOBS always require visits to extra blocks which may cause more I/O and more latching. For LOBS that enable storage in row more I/O and latching may still occur. For example if the lob cells are large but stay in the block, then fewer rows will fit in the block and a random set of row accesses may require more block visits than would occur if the lobs were out of line. This is especially so if the application does not always access the lob column(s).  This is why one may disable the storage in row even if the lobs are not that big. But rows in such tables are not flagged as “chained” even though they may have data in more than one block and in fact in more than one segment.

To sumarise what DBAs may do regarding performance,  “Migrated” rows may be repaired, and one may control LOB overheads to some degree by enabling or disabling storage in row to suit access patterns, but to do all this requires that one know the data and be familiar with the applications to some extent.

Oracle Certification Exam Design

Are you preparing for your Oracle Certification tests? Or perhaps considering whether or not to sit the exams? If so then you might enjoy viewing my 3 part video series produced by my colleague Harold Green who is one of the senior Programme Managers for the Oracle Certification programme. This presentation describes the design philosophy and value of Oracle Certification tests and discusses how the development process has changed over time.
Understanding the process and the distinction between “Learner” and “Practitioner” tasks should assist anyone in preparing for OCA, OCP or OCE “Theory” style exams. If you are a DBA OCM candidate then I cover the development of “Performance” style exams separately in the presentation.

Here is Part 1 covering design philosophy:

Here is Part 2 describing the different style of questions known as “Items”:

Here is Part 3 dealing with OCM Exams.

Do You Think About Your Database Links

I have written and presented a Master Class on Database links this year to two UKOUG DBA SIGs and added it to my “Articles and Presentations” page.  This forms part one of a two part series on DB Links which covers: the various DB Link Options, Remote Queries, Performance implications for DB Links, Security using DB Links and Implications for Administration. Part two will be presented at the UKOUG annual conference and will  cover:  remote and distributed queries, remote and distributed transactions; Oracle two-phase commit processing and recovery implications of distributed systems.

If you are interested in DB Links and Distributed Systems then have a look at Database Links Master Class Part 1.

Interested in the Oracle Scheduler?

If you are working with or plan to use the Oracle Seheduler then a new book by Ronald Rood may help. I have just reviewed his book “Mastering the Oracle Scheduler in Oracle 11g Databases published by Packt Publishing. It is a useful guide to all the scheduler features upto and including Oracle 11gR1.

Here is a link to the review on my new Book Review Page.