Oracle Database 11g: The Top New Features for DBAs and Developers by Arup Nanda

And Don't Forget...

Download Oracle Database 11g

Finally, I want to provide an introduction to many miscellaneous features that you will come to know and love in Oracle Database 11g.

New Processes

Each new version of Oracle Database brings forth a new set of abbreviations for new processes. Here is the list for Oracle Database 11g Release 1:

Process	Name	Description
ACMS	Atomic Controlfile to Memory Server	Only applicable in a RAC instance. When a distributed SGA update is issued, ACMS ensures that the update happens on all instances. If the update fails on one, this process also rolls it back on all instances. Think of ACMS as a two-phase commit coordinator process for SGA updates in a RAC cluster.
DBRM	Database Resource Manager	Implements the resource plans and other resource manager-related tasks
DIA0	Diagnosibility process 0	Detects hang situations and deadlocks. In the future there may be more than one process, so the name is set as diag0. The other processes, if they come in the future will be named dia1, dia2, etc.
DIAG	Diagnosibility process	Performs the diagnostics, dumps trace files, if necessary and performs global oradebug commands
FBDA	Flashback Data Archiver	Oracle Database 11g has a new “Flashback Archives” to write the changes made to a table (refer to “Transactions Management” installment of this series). This process writes the flashback archives.
GTX0	Global Transaction Process 0	In a RAC cluster, Oracle Database now provides improved handling of XA transactions. This process coordinates XA transactions. If the database load on XA grows, more processes are automatically created with names GTX1, GTX2, upto GTXJ.
KATE	Konductor (Conductor) of ASM Temporary Errands	Undoubtedly the grandest name of all new processes. This is seen on ASM instances; not the database. (Refer to the installment on this series on ASM for the new feature Fast Disk Resync.) When a disk goes offline, this process performs a proxy I/O on its behalf to an ASM metafile.
MARK	Mark AU for Resync Koordinator (coordinator)	Refer to the ASM installment for more details on the resiliency of the ASM diskgroups. When a disk fails, it becomes offline, causing writes to miss. In that case, this process marks the ASM allocation units (AUs) as stale. When the disk comes online, the stale segments will be refreshed.
SMCO	Space Manager	SMCO is the master space management process that dynamically allocates and deallocates space. It spawns slave processes Wnnn to implement the tasks.
VKTM	Virtual Keeper of TiMe process	Provides a wall clock equivalent (with 1 second updates). When running at elevated priority, it provides a 20 ms update.
W000	Space Management Worker Processes	Implements the instructions received from SMCO. More processes are spawned as needed with names like W000, W001 and so on.

Colored SQL

This new feature doesn't paint the SQL statement in color; rather, it sort of marks it as "important."

Suppose you are troubleshooting a performance issue and you suspect a specific SQL statement contributing to the problem. You want the SQL statement to be captured in every Automatic Workload Repository (AWR) snapshot. But AWR snapshots do not capture all SQL statements; just the top ones. How can you force a specific SQL to be captured, regardless of its inclusion in the top SQLs?

The procedure add_colored_sql() in the package dbms_workload_repository marks the SQL as "colored", or important enough to be captured in every AWR snapshot regardless of whether the SQL is in top SQLs. First, identify the SQL statement and then get its SQL_ID. To color it, use:

  
begin
  dbms_workload_repository.add_colored_sql(
     sql_id => 'ff15115dvgukr' 
  );
end;

SQL> SELECT * FROM wrm$_colored_sql;

      DBID SQL_ID             OWNER CREATE_TI
---------- ------------- ---------- ---------
2965581158 ff15115dvgukr          1 05-APR-08

Thereafter the SQL statement with ID ff15115dvgukr will be captured in every snapshot, even if it's not in the top SQL statements. (Of course, the SQL must be present in the library cache to be captured in the AWR snapshots.)

But what if the SQL ceases to be that colorful—that is, not important enough to be captured? You can turn it off by issuing the obverse of the procedure.

begin
  dbms_workload_repository.remove_colored_sql(
    sql_id => 'ff15115dvgukr' 
  );
end;

OLTP Table Compression

Compression has been an Oracle Database feature in one form or other for a long period

Compression is always CPU-intensive and it takes time. Usually, if you compress data, it has to be uncompressed to be usable. While this requirement is acceptable in data warehouse environments, where the SQL typically operates on a large number of rows and large response times are generally tolerable, it may not be acceptable on OLTP environments.

Now, in Oracle Database 11g, you can do this:

create table my_compressed_table (
  col1 number(20),
  col2 varchar2(300),
  ...
)
compress for all operations

Will that slow the DML? Not necessarily. This is where the new feature works best. The compression does not occur when the row is inserted into the table. Instead, the rows are inserted uncompressed and in the routine way. When a certain number of rows are inserted (or updated) in the uncompressed manner, the compression algorithm kicks in and compresses all the uncompressed rows in the block. In other words, the block is compressed, not the row. The threshold at which the compression occurs is internally defined inside the RDBMS code.

Mechanics of Compression

Consider a table, ACCOUNTS, with records shown below:

Because compression occurs as a triggered event, not when the row is inserted, the performance impact of the compression is nil during the normal DML process. When the compression is triggered, the demand on the CPU becomes high, of course,but at all other times the CPU impact is zero, making the compression suitable for OLTP applications as well—which is the sweetspot for compression in Oracle Database 11g.

Apart from reduced space consumption, the compressed data takes less time to go across the network, uses less space for backup, and makes it feasible to maintain full copies of the production database in QA and testing.

COPY is Undead!

Contrary to the rumors, the COPY command is still around. And it is still the only way to copy LONG datatypes. But as it has been made deprecated, it has not kept pace with the newer developments in Oracle Database.

For instance, it does not know about the presence of a new datatype called BFILE. If you try to copy the table DOCS, which has a column as BFILE shown in the earlier example:

SQL> copy to sh@oradba11 -
> replace docs_may08 -
> using select * from docs
> where creation_dt between '1-may-08' and '31-may-08'; 
Enter TO password: 
 
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
 
CPY-0012: Datatype cannot be copied

SQL>create table mytab1 as select * from mytab where col1 = 'A';

create table mytab1 as select * from mytab
                              *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
 
 
SQL> insert /*+ APPEND */ into mytab 
  2> select * from mytab where col1 = 'A';
select * from mytab
       *
ERROR at line 2:
ORA-00997: illegal use of LONG datatype

Exports and Import: Hanging by a Thread

When Data Pump was introduced in Oracle Database 10g, the eventual obsolescence of the traditional Export and Import tools were expected. Well, keep waiting: in Oracle Database 11g, these old tools are very much there. Being deprecated, they no longer receive any more enhancements, but they are far from useless.

In terms of features Data Pump outdoes the Regular Export/Import many times over but in one simple case the latter is still immensely useful: when you have to create a dump file on the client and not the server. There is no need to create a directory before performing the operations, so in many ways a simple data export is probably easier through the Regular Export/Import.

But, again, these tools are deprecated, so there are three risks to using them:

• Oracle will not add any functionality to them so future datatypes, etc. may not be supported.
• They may disappear from a future release without advanced warning, forcing you to rewrite the code later.
• If you encounter a bug, Oracle Support may refuse to fix that code.

Incrementally Updated Global Statistics

In partitioned tables, each partition can have optimizer statistics. In addition, there is a global statistic on the entire table that is not partition dependent—the number of distinct values across the table, for example. This global statistic is collected only when instructed in the dbms_stats.gather_*_stats procedures; not by default. If the global stats parameter is not specified, they are not collected and the optimizer derives them from the partition stats. Of course, the calculated global stats are always more accurate than the derived ones so it's always advisable to collect them.

But there used to be a little problem:. When a partition data changes but other partitions remain unchanged, the global stats might become stale. Thus the stats collection program had to go through the entire table to collect the global stats even though only one partition has a change.

Not so anymore. In Oracle Database 11g, you can instruct the optimizer to collect the global stats incrementally from the changed partition alone, not by doing another full table scan. This is done by setting a table parameter for stats collection—INCREMENTAL—to TRUE, as shown below:

begin
   dbms_stats.set_table_prefs(
           'SH','SALES','INCREMENTAL','TRUE');
end;
/

begin
   dbms_stats.gather_table_stats (
           ownname 	       => 'SH',
           tabname         => 'SALES',
           partname        => 'SALES_1995',
           granularity     => 'AUTO'
   );
end;

Data Pump Gets Better

As I mentioned previously, Data Pump has been the tool of choice for moving large amounts of data, or for taking "logical" backups of the data efficiently, since the previous release. Similar to Export/Import, it's platform independent (for instance you can export from Linux to import into Solaris). It got a few enhancements in Oracle Database 11g.

One of the big issues with Data Pump was that the dumpfile couldn't be compressed while getting created. That was something easily done in the older Export/Import utility. In Oracle Database 11g, Data Pump can compress the dumpfiles while creating them. This is done via a parameter COMPRESSION in the expdp command line. The parameter has three options:

• METDATA_ONLY - only the metadata is compressed; the data is left as it is (available in Oracle Database 10.2 as well).
• DATA_ONLY - only the data is compressed; the metadata is left alone.
• ALL - both the metadata and data are compressed.
• NONE - this is the default; no compression is performed.

$ expdp global/global directory=dump_dir tables=units_fact dumpfile=units_fact_comp.dmp compression=all

$ expdp global/global directory=dump_dir tables=units_fact dumpfile=units_fact_uncomp.dmp

$ ls -l 
-rw-r-----  1 oracle dba  2576384 Jul  6 22:39 units_fact_comp.dmp
-rw-r-----  1 oracle dba 15728640 Jul  6 22:36 units_fact_uncomp.dmp

If you compress the dumpfile using gzip:

$ gzip units_factl_uncomp.dmp

-rw-r-----  1 oracle dba 3337043 Jul  6 22:36 units_fact_uncomp.dmp.gz

The two other enhancements in Data Pump are:

• Encryption: the dumpfile can be encrypted while getting created. The encryption uses the same technology as TDE (Transparent Data Encryption) and uses the wallet to store the master key. This encryption occurs on the entire dumpfile, not just on the encrypted columns as it was in the case of Oracle Database 10g.
• Masking: when you import data from production to QA, you may want to make sure sensitive data such as social security number, etc. are obfuscated (altered in such a way that they are not identifiable). Data Pump in Oracle Database 11g enables you do that by creating a masking function and then using that during import.

Truly Online Index Rebuild

Remember the ONLINE clause while rebuilding an index?

alter index in_tab_01 rebuild online;

In Oracle Database 11g, the online rebuild is truly online: it does not hold an exclusive lock. The DMLs are not affected.

Different Tablespace for Temporary Tables

When you create a global temporary table, where does the allocation come from for the space occupied? It comes from the user's temporary tablespace. Usually this is not going to be an issue, but in some special cases, you may want to free up the temporary tablespace for the purpose it is supposed to be for (sorting, mostly). Sometimes you may want create the temporary tables to use another temporary tablespace on faster, more efficient disks to make the data access faster. In those cases you had no choice but to make that tablespace the user's temporary tablespace.

In Oracle Database 11g you can use another temporary tablespace for your global temporary tables. Let's see how. First you create another temporary tablespace:

SQL> create temporary tablespace etl_temp
  2> tempfile '+DG1/etl_temp_01.dbf'
  3> size 1G;
  
Tablespace created.  

SQL> create global temporary table data_load (
  2>    input_line varchar2 (2000)
  3>  )
  4> on commit preserve rows
  5> tablespace etl_temp;

Table created.

SQL*Plus Error Logging

Suppose you have a SQL script called myscript.sql:

set puase on
set lines 132 pages 0 trimsppol on
select * from nonexistent_table
/

Oracle Database 11g has a perfect solution: Now you can log the errors coming from SQL*Plus on a special table. You should issue, as a first command:

SQL> set errorlogging on

SQL> @myscript

SP2-0158: unknown SET option "puase"
SP2-0158: unknown SET option "trimsppol"
select * from nonexistent_table
              *
ERROR at line 1:
ORA-00942: table or view does not exist

sql> col timestamp format a15
sql> col username format a15
sql> col script format a10
sql> col identifier format a15
sql> col statement format a20
sql> col message format a20
sql> select timestamp, username, script, statement, message
  2> from sperrorlog;
 
TIMESTAMP       USERNAME        SCRIPT     STATEMENT            MESSAGE
--------------- --------------- ---------- -------------------- --------------------
05-JUL-08 06.08 SH              myscript.s set puase on         SP2-0158: unknown SE
.41.000000 PM                   ql                              T option "puase"
 
05-JUL-08 06.08 SH              myscript.s set lines 132 pages  SP2-0158: unknown SE
.41.000000 PM                   ql         0 trimsppol on       T option "trimsppol"
 
05-JUL-08 06.08 SH              myscript.s select * from nonexi ORA-00942: table or
.41.000000 PM                   ql         stent_table          view does not exist

The table SPERRORLOG is a special table that is created for this purpose only. You can also create your own table and populate that with errors from SQL*Plus. This table should be created as:

SQL> create table my_sperror_log
  2  (
  3     username varchar2(256),
  4     timestamp       timestamp,
  5     script          varchar2(1024),
  6     identifier      varchar(256),
  7     message         clob,
  8     statement       clob
  9  )
 10  /
 
Table created.

SQL> set errorlogging on table sh.my_sperror_log;
SQL> @myscript

SQL> set errorlogging on truncate

SQL> set errorlogging on identifier MYSESSION1

select timestamp, username, script, statement, message
from sperrorlog
where identifier = 'MYSESSION1';

Shrinking the Temporary Tablespace

You probably already know that temporary tablespaces are special; normal rules of space management may not apply to them. When a temporary segment is allocated, it is not deallocated. This is not really a problem, since the temporary segments (which are what the temporary tablespaces are for) are not part of the schema and are not stored across database recycles. The space is reused for another user or another query. Anyway, since the space is not deallocated, the temporary tablespaces just keep growing. But what if you want to trim them to make room for other tablespaces?

Until now the only option was to drop and recreate the tablespace—a rather trivial task that can be done almost always online. However there is a little "but": What if you can't afford to accept anything other than 100-percent uptime? In Oracle Database 11g, you can easily do that, by shrinking the temporary tablespace.

Here is how the tablespace TEMP1 is shrunk:

alter tablespace temp1 shrink space;

SQL> select * from dba_temp_free_space;
 
TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                 179306496       179306496  178257920

SQL> alter tablespace temp shrink space keep 100m;
 
Tablespace altered.
 
SQL> select * from dba_temp_free_space;
 
TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                 105906176         1048576  104857600

SQL*Plus Shows BFILE

The datatype BFILE in Oracle Database stores the pointer to an external file, not the contents of the file itself. It serves a specific purpose: the database space is conserved while the users have access to the data in the files, which is great for some applications such as ones using content and multi-media rich databases.

If you have a column with BFILE in a table and you want to select the rows of the tale in SQL*Plus, you used to get an error. In Oracle Database 11g, SQL*Plus shows you the location of the file as a value in the column.

Let's see an example. First you have to create a directory to store the BFILE.

SQL> create directory doc_dir as '/u01/oracle/docs';

Directory created.

 SQL> create table docs
   2  (
   3     doc_id          number(20),
   4     doc_file        bfile,
   5     doc_type        varchar2(10)
   6  );

Table created.

SQL> insert into docs values
  2  (
  3     1,
  4     bfilename('DOC_DIR','metric_daily_report.pdf'),
  5     'PDF'
  6  );
 
1 row created.

SQL> col doc_file format a50
SQL> select * from docs;
 
    DOC_ID DOC_FILE                                           DOC_TYPE
---------- -------------------------------------------------- ----------
         1 bfilename('DOC_DIR', 'metric_daily_report.pdf')    PDF

Parameter Files from Memory

Consider this scenario: in the course of diagnosing some database issue, you modified a lot of parameters, all in memory. Later you forget which parameters you changed. Unless you remembered to put these parameters in the initialization parameter file (pfile or spfile), these changes will be lost. Most likely you didn't put them there as you were experimenting with the values and weren't sure which specific value to use. Of course, you can go through the alert log and pick out all the changes made—a procedure not only painful but error-prone as well.

In Oracle Database 11g, you can use a very simple command to create a pfile or an spfile from the parameter values from memory.

SQL> create spfile from memory;

File created.
 
SQL> create pfile from memory;
 
File created.

# Oracle init.ora parameter file generated by instance ODEL11 on 08/28/2007 14:52:14

SQL> create pfile='/tmp/a' from memory;

Reset with Care

In course of a performance tuning process you are examining the impact of changing several dynamic initialization parameters directly on the spfile, and suddenly you are concerned that you have lost track of what you changed. Since these are changed in the spfile, you can't count on the spfile to tell you what values you changed.

Well, here comes the cavalry: The ALTER SYSTEM RESET command resets the value to the default value, overriding what you have the spfile:

alter system reset license_max_sessions;

Real-time SQL Monitoring

Performance diagnosis takes a giant leap with this feature. Consider this: someone is running a bunch of SQL (perhaps inside a PL/SQL code). How do you know how much resource (CPU, I/O, etc.) each step of the access path is taking? You can trace the session or analyze the tracefile using Trace Analyzer or the plain old tkprof, but that's after the fact. You would have loved to get a real time window into what's going in the session, wouldn't you?

In Oracle Database 11g, a new feature, Real-time SQL Monitoring, does exactly what the name implies: it allows you to see the different metrics of the SQL being executed in real time. The stats are exposed through the dynamic performance view V$SQL_MONITOR, which is refreshed every second.

To demonstrate, let's run a large query and monitor the real time stats. We know that the SID of the session running the large query is 103. While selecting from the view I have used Tom Kyte's famous print_table tool ,which displays the output in the vertical format for better readability.

SQL> exec print.tbl('select * from v$sql_monitor where sid = 103')

KEY                           : 476741369968
STATUS                        : EXECUTING
FIRST_REFRESH_TIME            : 07-jul-2008 14:13:38
LAST_REFRESH_TIME             : 07-jul-2008 14:26:27
REFRESH_COUNT                 : 48043
SID                           : 103
PROCESS_NAME                  : ora
SQL_ID                        : 2xj1nram1k1x0
SQL_EXEC_START                : 07-jul-2008 14:13:31
SQL_EXEC_ID                   : 16777216
SQL_PLAN_HASH_VALUE           : 1759042790
SQL_CHILD_ADDRESS             : 38837734
SESSION_SERIAL#               : 32668
PX_SERVER#                    :
PX_SERVER_GROUP               :
PX_SERVER_SET                 :
PX_QCINST_ID                  :
PX_QCSID                      :
ELAPSED_TIME                  : 42638722
CPU_TIME                      : 9199624
FETCHES                       : 48032
BUFFER_GETS                   : 3238
DISK_READS                    : 12096
DIRECT_WRITES                 : 13419
APPLICATION_WAIT_TIME         : 0
CONCURRENCY_WAIT_TIME         : 134534
CLUSTER_WAIT_TIME             : 0
USER_IO_WAIT_TIME             : 148436
PLSQL_EXEC_TIME               : 0
JAVA_EXEC_TIME                : 0
-----------------

Another view ,V$SQL_PLAN_MONITOR, shows the optimizer plan of the SQL statement executed updated in real time. Here is how you can use the view to see the various steps in the plan and the associated stats in real time.

select plan_line_id,
plan_operation,
plan_options,
output_rows
from v$sql_plan_monitor
where sid = 103
order by plan_line_id;

PLAN_LINE_ID PLAN_OPERATION                 PLAN_OPTIONS                   OUTPUT_ROWS
------------ ------------------------------ ------------------------------ -----------
           0 SELECT STATEMENT                                                   809994
           1 HASH                           GROUP BY                            809994
           2 HASH JOIN                                                          918845
           3 PART JOIN FILTER               CREATE                                1826
           4 TABLE ACCESS                   FULL                                  1826
           5 HASH JOIN                                                          918845
           6 TABLE ACCESS                   FULL                                   503
           7 HASH JOIN                                                          918845
           8 TABLE ACCESS                   FULL                                    72
           9 HASH JOIN                                                          918845
          10 TABLE ACCESS                   FULL                                     5
          11 HASH JOIN                                                          918845
          12 TABLE ACCESS                   FULL                                 55500
          13 PARTITION RANGE                JOIN-FILTER                         918845
          14 TABLE ACCESS                   FULL                                918845

Another useful part of the real time SQL monitoring is the SQL Monitor Report. Using this report, you can get a visual report of various metrics on the SQL and the plan steps. This report is generated as a CLOB output from the function REPORT_SQL_MONITOR in the package DBMS_SQLTUNE. Here is how you invoke that function:

SQL> set long 99999 lines 3000 pages 0 trimspool on
SQL> select dbms_sqltune.report_sql_monitor (
  2          event_detail => 'YES',
  3          report_level => 'ALL',
  4          type            => 'HTML'
  5  )
  6  from dual;

The lower part of the screen shows the optimizer plan for the query and the time taken in each step, under categories such as CPU activity, Waits, and so on. Colored bars show the relative values of each metric. If you hover your mouse above those bars you will see the values and time periods for each metric.

This report is nothing but a snapshot of the real-time SQL monitor view you saw earlier presented in a graphical format. The data, presented graphically, helps you understand the components of the query and measuring the times spent in each of them, making performance diagnosis far easier.

End Note

This concludes the 20-week-long, whirlwind tour of the new features introduced in Oracle Database 11g. Like any author, I struggled to maintain the delicate balance between depth and breadth. But in the previous article series on Oracle Database 10g Rel 1 and Rel 2, your feedback was loud and clear: you loved the example-based story telling approach, so I did not stray from that format in this series.

My sincere appreciation goes out to the reviewers who validated this series. I couldn't thank Justin Kestelyn enough, for the support and the help in making this project a success. And, thank you, dear reader, for making it all worthwhile.