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.

Measuring Exadata Offload Efficiency

More customers have begun to look at Exadata since the announcement last autumn and I have been teaching the Oracle Exadata Seminar this year to both an EMEA Audience and audiences in North America.

Exadata permits intelligent offloading of certain types of IO to the Cells where it is processed locally reducing the amount of traffic on the storage network and thereby reducing contention, latency and improving performance. Exadata can offload the following:

1. Filters or where clauses on SQL statements – in this case the SQL is offloaded to the cell which reads the blocks and applies the filters to the rows before returning the results over the Exadata Infiniband Storage Interconnect to the requesting host node.

2. Column Projections on SQL statements – in this case the column list is a subset of the columns of a table and the SQL is offloaded to the cell which reads the blocks and returns only the selected columns to the requesting host node. Of course case 1 and 2 may be combined.

3. Certain Data Warehouse Joins – in this case a cell may join data from multiple tables that are joined in the downloaded SQL using the Bloom filtering technique to reduce the number of rows that are returned over the storage network to be post-processed back in the Oracle Database instance.

4. Data File Creation or Extension formatting – When Create Tablespace DDL statements are issued a  datafile is created. Likewise if a data file is added to a tablespace or a file is resized, or autoextends, then this is offloaded to the cells for formatting the blocks of the file reducing both IO and workload on the server.

5. RMAN Fast Incremental Backups- When Block Change Tracking is used with RMAN and Exadata the Cell does the incremental backups at a granularity of the individual block, rather than at the granularity of a group of blocks as is done without Exadata.

To measure these activities there are several V$ Views and here are some of the ways one may view the benefit of these offloads:

1. Look at the total offload efficiency using system stats. V$SYSSTAT lists 16 statistics involving cells and we can use some of them straight from the view and some require a calculation. Here are the stats:

cell blocks helped by commit cache
cell blocks helped by minscn optimization
cell blocks processed by cache layer
cell blocks processed by data layer
cell blocks processed by index layer
cell blocks processed by txn layer
cell blocks skipped due to chained rows
cell commit cache queries
cell physical IO bytes eligible for predicate offload
cell physical IO bytes saved during optimized RMAN file restore
cell physical IO bytes saved during optimized file creation
cell physical IO interconnect bytes
cell scans
cell simulated physical IO bytes eligible for predicate offload
cell simulated physical IO bytes returned by predicate offload
cell transactions found in commit cache

The Statistic “cell physical IO bytes saved during optimized file creation” tells us how we have benefited using Exadata for Datafile create and extend.

The Statistic “cell physical IO bytes saved during optimized RMAN file restore” is an indication of RMAN benefits using Exadata.

To determine offload benefit for SQL where clauses (filtering), Column Projections and Joins we use the “cell physical IO interconnect bytes” and “cell physical IO bytes eligible for predicate offload” statistics as follows:

select 100 – 100*s1.value/s2.value io_filtering_percentage
from v$mystat s1
, v$mystat s2
, v$statname n1
, v$statname n2
where s1.statistic# = n1.statistic#
and s2.statistic# = n2.statistic#
and n1.name = ‘cell physical IO interconnect bytes’
and n2.name = ‘cell physical IO bytes eligible for predicate offload’;

IO_FILTERING_PERCENTAGE
————————————————–
81.9404485

This measure the number of bytes that were offloaded over the Exadata iterconnect as a percentage of the nuber of bytes theoretcially eligible for predicate offload.

Another way to examine the efficiency is to look at Cell Specific Wait events to see the type, frequency and average duration of the events. There are currently 8 cell oriented wait events for which an Oracle process may wait as follows:

cell multiblock physical read
cell single block physical read
cell smart file creation
cell smart incremental backup
cell smart index scan
cell smart restore from backup
cell smart table scan
cell statistics gather

Just looking at the list is an indication of the types of offloads that may occur and for which a process may wait and this includes table scans, index fast full scans, file creation and RMAN smart Backup and restore.

To see if an instance has waited on these events, how often and for how long one may use queries which exploit the following Exadata Cell v$views:

V$CELL
V$CELL_REQUEST_TOTALS
V$CELL_STATE
V$CELL_THREAD_HISTORY

To see if any sessions are currently waiting, and for which cell:

SELECT w.event, c.cell_path, d.name, w.p3
FROM V$SESSION_WAIT w, V$EVENT_NAME e,
V$ASM_DISK d, V$CELL c
WHERE e.name LIKE ‘cell%’ AND
e.wait_class_id = w.wait_class_id AND
w.p1 = c.cell_hashval AND w.p2 = d.hash_value
order by 1;

To see the waits for the instance since startup use the following:

SELECT d.name, c.cell_path, w.event,
w.total_waits, w.time_waited, w.average_wait
FROM V$SYSTEM_EVENT w, V$ASM_DISK d, V$CELL c
WHERE w.event LIKE ‘%cell%’ AND
d.path like ‘o/%’
order by 1,3,4 desc;

To see the IO occurring from the cell log in to the “cellcli” utility on a cell and use the “list activerequest” command to see active IOs on the cell as follows:

CellCLI> list activerequest detail
name:                   0
asmDiskGroupNumber:     0
asmFileIncarnation:     0
asmFileNumber:          0
consumerGroupID:        11202
consumerGroupName:
dbID:                   3931557617
dbName:
dbRequestID:            0
fileType:
id:                     0
instanceNumber:         1
ioBytes:                1048576
ioBytesSofar:           0
ioGridDisk:             /box/predicate3
ioOffset:               0
ioReason:
ioType:                 “Predicate Pushing”
objectNumber:           -1
parentID:               0
requestState:           “Queued for Predicate Disk”
sessionID:              128
sessionSerNumber:       17
sqlID:                  7wnj4uwqw1yb2
tableSpaceNumber:       0

Another way to examine performance is to list the Cell metrics that are gathered automatically byt the cell using “cellcli“. Each performance metric has a definition which may be listed using  the “list METRICDEFINITION detail” command. An example of some metrics are:

CellCLI> list METRICDEFINITION detail
name:                   CD_IO_BY_R_LG
description:            “Number of megabytes read in large blocks from a cell disk”
metricType:             Cumulative
objectType:             CELLDISK
unit:                   MB

name:                   CD_IO_BY_R_LG_SEC
description:            “Number of megabytes read in large blocks per second from a cell disk”
metricType:             Rate
objectType:             CELLDISK
unit:                   MB/sec

To see the metrics for example for a specific grid disk do:

CellCLI> list metriccurrent where metricObjectName = ‘data_CD_disk01_cell1′
GD_IO_BY_R_LG   data_CD_disk01_cell1    55.4 MB
GD_IO_BY_R_LG_SEC       data_CD_disk01_cell1    0.0 MB/sec
GD_IO_BY_R_SM   data_CD_disk01_cell1    0.7 MB
GD_IO_BY_R_SM_SEC       data_CD_disk01_cell1    0.0 MB/sec
GD_IO_BY_W_LG   data_CD_disk01_cell1    11.9 MB
GD_IO_BY_W_LG_SEC       data_CD_disk01_cell1    0.0 MB/sec
GD_IO_BY_W_SM   data_CD_disk01_cell1    0.1 MB
GD_IO_BY_W_SM_SEC       data_CD_disk01_cell1    0.0 MB/sec
GD_IO_RQ_R_LG   data_CD_disk01_cell1    56 IO requests
GD_IO_RQ_R_LG_SEC       data_CD_disk01_cell1    0.0 IO/sec
GD_IO_RQ_R_SM   data_CD_disk01_cell1    81 IO requests
GD_IO_RQ_R_SM_SEC       data_CD_disk01_cell1    0.0 IO/sec
GD_IO_RQ_W_LG   data_CD_disk01_cell1    24 IO requests
GD_IO_RQ_W_LG_SEC       data_CD_disk01_cell1    0.0 IO/sec
GD_IO_RQ_W_SM   data_CD_disk01_cell1    9 IO requests
GD_IO_RQ_W_SM_SEC       data_CD_disk01_cell1    0.0 IO/sec

There are many other tools in “cellcli” which are beyond the scope of this discussion but if and when you work with exadata this is a good way to get started in performance measurement.

Oracle Certification Benefits

I presented a free lunchtime Web Seminar for Oracle EMEA today to discuss the benefits of certification to employers and employees as well as to describe the exam requirements and design process. There were some requests to access the slides so hewre they are:

>> The Benefits of Oracle Certification

I am the global technical leader of the DBA Certification team and also a lead Subject Matter Expert (SME) reviewer for the DBA, SQL, PL/SQL and Oracle on Linux exams and therefore spend a great deal of time helping and mentoring the exam developers.  In the presentation you can see the project development steps taken from the inception of the “blueprint” for an exam until the exam is ready for production.

If you have any queries please contact me here.

Skills and Training Plans for New Oracle DBAs

After co-authoring an article on Performing a DBA 1.0 to DBA 2.0 Upgrade with Harald van Breederode last year,  we had many requests to share our views on entry level Oracle DBAs who may be totally new, or who may have moved into the DBA role from Operations, Development or from other technology areas.  We have now written another article called Installing Oracle  DBA 1.0 to share our own experiences in learning Oracle and to provide advice based on our experience in the Oracle DBA Classroom.

Is Your Audit Trailing Behind?

Here is a recent twist to an old problem. The Oracle Audit Trail is written to a data dictionary table “sys.aud$” which is in the system tablespace. Over the years and releases many DBAs have wished to move “sys.aud$” (or “system.aud$” if OLS is installed) to another tablespace when auditing is heavily used to reduce system tablespace I/O. Since the advent of Fine Grain Auditing a similar desire has existed for “sys.fga_log$” which is used to contain fine grain auditing rows. Technically these are sys owned tables but do not contain metadata of the same sort as contained in other base tables and some do not consider them to be proper data dictionary tables.

Moving one or both of these tables to another tablespace is technically possible, but the situation is somewhat risky. Published technote 72460.1 explains how this is done :

1.  Restart the instance with AUDIT_TRAIL = none.

2.  Log in to SQL/PLUS as sys.

connect sys/<password>

3. Copy aud$ to system schema (or another schema) in another tablespace.

create table system.aud$ tablespace <tablespace name>as select * from aud$;

4.Create an index on the correct columns.

create index system.i_aud1 on system.aud$(sessionid, ses$tid);

5. Temporarily rename sys.aud$.

rename sys.aud$ to aud$_temp;

6. Create a view belonging to sys called aud$ based on the new aud$ table.

create view sys.aud$ as select * from system.aud$;

7. Login as system.

connect system/<password>

8. Grant appropriate privileges on the new view and table.

grant all on system.aud$ to sys with grant option;
grant delete on system.aud$ to delete_catalog_role;

But the same technote also contains other important statements:

1. That relocation of aud$ is not supported, although changing the default storage parameters except for “INITIAL” is supported.

2. That the technote was published externally because of “customer demand”.

3. Errors may occur if the tablespace containing the audit is offline.

In effect this is saying ” DBAs, you are on their own if you move aud$ but here is how to do it and it works in most cases”.

But there is good news. One of the requirements of the 10g and 11g Audit Vault feature was to provide the facility to manipulate the Audit Trail and this resulted in the creation of package DBMS_AUDIT_MGMT which provides the needed functionality for all audit trail managmenet including purging, table relocation and a few others. But use of the package outside of Audit Vault was not originally supported.  But from 11.1.0.7 some of the restrictions have been relaxed so that support now exists for moving aud$ and fga_log$ to another tablespace. Furthermore there are patches for older versions from 10.2.0.3 onwards to install this package as well.

Here is the procedure to move the audit trails:

PROCEDURE SET_AUDIT_TRAIL_LOCATION
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
AUDIT_TRAIL_TYPE               BINARY_INTEGER          IN
AUDIT_TRAIL_LOCATION_VALUE     VARCHAR2                IN

The possible values for audit_trail_type are listed below but not all these types are suitable for tablespace relocation as some are used in other procedures within the package such as when purging which is beyond the scope of this post:

1. AUDIT_TRAIL_AUD_STD  - in sys.aud$

2. AUDIT_TRAIL_FGA_STD  - in sys.fga_log$

3. AUDIT_TRAIL_DB_STD   - both 1 and 2 above

4. AUDIT_TRAIL_OS       - OS audit trail

5.  AUDIT_TRAIL_XML      - Audit in XML OS files

6. AUDIT_TRAIL_FILES    - both 4 and 5 above

7. AUDIT_TRAIL_ALL      - 1,2,4,5 above


In addion to the DBMS_AUDIT_MGMT package there are also some dictionary views and the one we are interested in is:

Current Audit location Settings:

SQL> select audit_trail,parameter_name,parameter_value
from  DBA_AUDIT_MGMT_CONFIG_PARAMS;

AUDIT_TRAIL                  PARAMETER_NAME       PARAMETER_VALUE
—————————- ——————– ——————–
FGA AUDIT TRAIL              DB AUDIT TABLESPACE  SYSAUX
STANDARD AUDIT TRAIL         DB AUDIT TABLESPACE  SYSAUX

Have a look at technote 731908.1 for full details and don’t leave your audit behind.