The Oracle Instructor

I have just finished an Oracle Database 11g New Features course in Vienna. There are many amazing New Features in the 11g version, one of them is the possibility to cache the result sets of statements, that access large tables but return relatively few rows. Think of it like automagically created materialized views inside the SGA. Example:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select bytes/1024/1024 as mb from user_segments where segment_name='SALES';

 MB
----------
 560

SQL> alter table sales result_cache (mode force);

Table altered.

The ALTER TABLE statement is a new feature of 11g Release 2. In Release 1, you can control the feature only with the following parameters:

SQL> show parameter result_cache_m

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

result_cache_max_result              integer     5

result_cache_max_size                big integer 2080K

result_cache_mode                    string      MANUAL

Another possibility, already introduced in 11g R1 is the RESULT_CACHE hint. Now let’s look at the effect of the ALTER TABLE statement:

SQL> set timing on

SQL> select channel_id,sum(amount_sold) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)

---------- ----------------

 2        421541477

 4        219308832

 3        926004170

 9       4438820.16

Elapsed: 00:00:03.72

This was the first time, the sales table was accessed after the ALTER TABLE above. The runtime signalizes we have got a full table scan here (there are no indexes on the table anyway). Of course, blocks of the table are now cached in the database buffer cache – as in previous versions. But now, also the result set is cached!

SQL> select channel_id,sum(amount_sold) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)

---------- ----------------

 2        421541477

 4        219308832

 3        926004170

 9       4438820.16

Elapsed: 00:00:00.01

That is obvious by runtime already, so I ommit AUTOTRACE here. If the table gets changed, the result set gets “stale”, similar like a materialized view would:

SQL> update sales set amount_sold=1 where rownum<2;

1 row updated.

Elapsed: 00:00:00.02

SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

SQL> select channel_id,sum(amount_sold) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)

---------- ----------------

 2        421541477

 4        219308832

 3        926002938

 9       4438820.16

Elapsed: 00:00:03.08

Second access after the DML will again use the (newly cached) result set – even if the statement is slightly different, in the same way as materialized views can be used for query rewrite, even if the SELECT differs from the query that built the materialized view. That gives me the opportunity to introduce the new SUPERFAST hint

SQL> select /*+ superfast */ channel_id,sum(amount_sold) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)

---------- ----------------

 2        421541477

 4        219308832

 3        926002938

 9       4438820.16

Elapsed: 00:00:00.00

The “hint” is just a strange commentar for the engine and is simply ignored, of course. The speed comes from using the cached result set, stored in the SGA by the previous SELECT with the 3 seconds runtime.

We have a probably very needful new feature introduced in 11g Release 2, related to parallel query: Automatically determined Degree of Parallelism (DOP). In earlier versions of the Oracle Database, we had to determine the DOP more or less manually, either with a parallel hint or by setting a parallel degree with alter table:

select /*+ parallel (sales,2) */ * from sales;

or

alter table sales parallel 2;

There was an automatic computation of the DOP available, derived from the simple formula CPU_COUNT * PARALLEL_THREADS_PER_CPU. That is what’s done internally if we would have said

select /*+ parallel (sales) */ * from sales;

or

alter table sales parallel;

The drawback with these approaches was always, that we could hardly be sure, whether the DOP is appropriate or not for the table, the statement and the hardware, we are running on. It was mostly a case of try & error. Especially problematic was the alter table approach, as this leads to the parallelization of each and every following select on those tables, even if totally inappropriate. A popular pitfall is the creation of tables with a parallel clause on OLTP-systems, because those tables inherit the parallel degree of their creation, which leads to parallel query for every statement accessing the tables afterwards – most likely not desirable for OLTP. In so far, parallelization was quite dumb (from a system-internal perspective) in versions before 11gR2. Now to the new feature:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select bytes/1024/1024 as mb from user_segments where segment_name='SALES';

 MB
----------
 563

SQL> select degree from user_tables where table_name='SALES';

DEGREE
----------------------------------------
 1

For my tiny machine, this table is huge. I allow automatic determination of the DOP with the following new dynamic parameter:

SQL> alter session set parallel_degree_policy=auto;

Session altered.

SQL> set autotrace on explain

SQL> select sum(amount_sold) from sales

SUM(AMOUNT_SOLD)
----------------
 1571293299

Execution Plan
----------------------------------------------------------
Plan hash value: 3130505568

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |     5 | 10798   (1)| 00:02:10 |
|   1 |  SORT AGGREGATE        |          |     1 |     5 |            |          |
|   2 |   PX COORDINATOR       |          |       |       |            |          |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |     5 |            |          |
|   4 |     SORT AGGREGATE     |          |     1 |     5 |            |          |
|   5 |      PX BLOCK ITERATOR |          |    14M|    70M| 10798   (1)| 00:02:10 |
|   6 |       TABLE ACCESS FULL| SALES    |    14M|    70M| 10798   (1)| 00:02:10 |
-----------------------------------------------------------------------------------

Note
-----
 - automatic DOP: Computed Degree of Parallelism is 2 because of degree limit

I have got a moderate DOP, most likely appropriate for my relatively weak hardware, but still speeding up the query on the relatively big table. I could always override the automatic DOP determination by specifying a parallel hint as in earlier versions. Also, the parameter defaults to manual, so unless we change it, automatic parallelization will not take place. In order to demonstrate the quite intelligent computation of the DOP, compared to ealier versions, i will access another, much smaller table in the same session:

SQL> select count(*) from customers;

 COUNT(*)
----------
 30501

Execution Plan
----------------------------------------------------------
Plan hash value: 296924608

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   218   (0)| 00:00:03 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| CUSTOMERS | 30501 |   218   (0)| 00:00:03 |
------------------------------------------------------------------------

Note
-----
 - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

As you can see, in this case, the system does not think that parallelization is appropriate for the select. So it is much smarter than the old force parallel query:

SQL> alter session set parallel_degree_policy=manual;

Session altered.

SQL> alter session force parallel query;

Session altered.

SQL>  select count(*) from customers;

 COUNT(*)
----------
 30501

Execution Plan
----------------------------------------------------------
Plan hash value: 1221513835

----------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |   121   (0)| 00:00:02 |
|   1 |  SORT AGGREGATE        |           |     1 |            |          |
|   2 |   PX COORDINATOR       |           |       |            |          |
|   3 |    PX SEND QC (RANDOM) | :TQ10000  |     1 |            |          |
|   4 |     SORT AGGREGATE     |           |     1 |            |          |
|   5 |      PX BLOCK ITERATOR |           | 30501 |   121   (0)| 00:00:02 |
|   6 |       TABLE ACCESS FULL| CUSTOMERS | 30501 |   121   (0)| 00:00:02 |
----------------------------------------------------------------------------

Also, we have a remedy now against inappropriate parallel degrees on tables:

SQL> alter session enable parallel query -- the default, no force;

Session altered.

SQL> alter table customers parallel -- would cause parallel query before 11gR2;

Table altered.

SQL> set autotrace on explain

SQL> select count(*) from customers;

 COUNT(*)

----------

 30501

Execution Plan

----------------------------------------------------------

Plan hash value: 296924608

------------------------------------------------------------------------

| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |

------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |     1 |   218   (0)| 00:00:03 |

|   1 |  SORT AGGREGATE    |           |     1 |            |          |

|   2 |   TABLE ACCESS FULL| CUSTOMERS | 30501 |   218   (0)| 00:00:03 |

------------------------------------------------------------------------

Note

-----

 - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

With Oracle Database 11g, we have a new Option available, called Total Recall. This option extends the possibility to do Flashback Query, introduced in 9i already.

We can now designate particular tables for being able to track back all the changes on them even after years. This option addresses especially legal requirements to keep history for certain data for several years. With Total Recall resp. Flashback Data Archive, this can be achieved quite comfortable and efficient. In order to demonstrate that, I create a demo user, giving him the DBA role (kind of “quick & dirty” of course, but makes the demo easier):

SQL> grant dba to quaid identified by quaid;

Grant succeeded.

SQL> connect quaid/quaid
Connected.

SQL> create tablespace flatbs
 datafile '/u01/app/oracle/oradata/orcl/flatbs01.dbf' size 50m;

Tablespace created.

This tablespace is just an ordinary tablespace, but I am going to use it to hold the Flashback Archive, created now:

SQL> create flashback archive fla_10y
 tablespace flatbs retention 10 year;

Flashback archive created.

If I designate a table to fla_10y, I will be able to do Flashback Query on that table even after 10 years – provided I have the space to hold that history in the tablespace(s), associated to that Flashback Archive. Following creates two ordinary tables with ordinary rows in them:

SQL> create table norecall
 (id number, name varchar2(50));

Table created.

SQL> create table totalrecall
 (id number, name varchar2(50));

Table created.

SQL> insert into norecall values (1,'QUAID');

1 row created.

SQL> insert into  totalrecall values (1,'QUAID');

1 row created.

SQL> commit;

Commit complete.

I want to be able to keep the history of the second table for 10 years:

SQL> alter table totalrecall flashback archive fla_10y;

Table altered.

SQL> select to_char(systimestamp,'yyyy-mm-dd:hh24:mi:ss')
 from dual;

TO_CHAR(SYSTIMESTAM
-------------------
2009-10-21:10:55:47

I will do DML on the two tables now. As you probably know, this leads to the creation of before images in the undo tablespace, which we can use to look into the past of the tables – but most likely not after 10 years, because the before images in the undo tablespace are getting overwritten somewhen.

SQL> delete from norecall;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from norecall as of timestamp
 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');

 ID NAME
---------- --------------------------------------------------
 1 QUAID

SQL>  delete from  totalrecall;

1 row deleted.

SQL> commit;

Commit complete.

The select above is a Flashback Query, as it is possible since 9i. I will now make sure, that there are no before images left in the undo tablespace by creating a new one and dropping the old one.

SQL> create undo tablespace undonew datafile

 '/u01/app/oracle/oradata/orcl/undonew01.dbf' size 50m;

Tablespace created.

SQL> alter system set undo_tablespace='UNDONEW';

System altered.

SQL> select * from norecall as of timestamp

 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');

 ID NAME

---------- --------------------------------------------------

 1 QUAID

As long as the old undo tablespace is still there, it may still get used for a conventional Flashback Query. But no longer after the drop tablespace. The before images of the history-tracked table, though, are saved into the Flashback Archive (getting compressed during the transfer), which would be the same, if the content of the old undo tablespace gets overwritten by new before images. That’s why I still can do Flashback Query with totalrecall:

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate

SQL> startup

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL> connect quaid/quaid
Connected.

SQL> select * from norecall as of timestamp
 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');  
select * from norecall as of timestamp
 *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number  with name "" too small

SQL> select * from totalrecall as of timestamp
 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');   

 ID NAME
---------- --------------------------------------------------
 1 QUAID

I will insert a new row into the ordinary table to demonstrate further differences between it and the history tracked table:

SQL> insert into norecall values (2,'John Doe');

1 row created.

SQL> commit;

Commit complete.

SQL> select to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2009-10-23:02:14:28

Upto this point of the story, the shown features are the same in 11g release 1 already available. Following are new features of 11g release 2: We are now able to do DDL statements on the history-tracked tables and can still do Flashback Query!

SQL> alter table norecall drop column name;

Table altered.

SQL> select * from norecall  as of timestamp
 2  to_timestamp('2009-10-23:02:14:28','yyyy-mm-dd:hh24:mi:ss');
select * from norecall  as of timestamp
 *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

SQL> select to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2009-10-23:02:17:06

SQL> truncate table norecall;

Table truncated.

SQL> select * from norecall  as of timestamp
 2  to_timestamp('2009-10-23:02:17:06','yyyy-mm-dd:hh24:mi:ss');
select * from norecall  as of timestamp
 *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

Above demonstrated that DDL on an ordinary table makes Flashback Query behind the DDL impossible. That was nothing new. New is:

SQL> alter table totalrecall drop column name;

Table altered.

SQL> select * from totalrecall as of timestamp
 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');  

 ID NAME
---------- --------------------------------------------------
 1 QUAID

SQL> truncate table totalrecall;

Table truncated.

SQL> select * from totalrecall as of timestamp
 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');   

 ID NAME
---------- --------------------------------------------------
 1 QUAID

With 11g release 2, we can do Flashback Query behind DDL on tables in the Flashback Archive! In 11g release 1, DDL on history-tracked tables was prohibited, which was a show-stopper for many customers.  One DDL, we still can’t get through is of course:

SQL> drop table totalrecall;
drop table totalrecall
 *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

Another Oracle Database 11g Release 2 “Enterprise Manager New Feature”  is Real-Time SQL Monitoring. On the command line, that was already possible with 11g Release 1, where the views V$SQL_MONITOR and V$SQL_PLAN_MONITOR have been introduced. Now with the new Release, we can do that with the GUI. I have prepared a little demonstration for that by creating a larger sales table (560 M), so that the select statement takes more than 30 seconds on my tiny system. That gives me enough time to watch it real-time and capture the screen:

First we go to the Performance page, Top Activity as usual to spot our High Load statements. Notice the additional card named SQL Monitoring

The statement did run several times before, so there are already statistics from previous runs on the screen. Now while the statement is running, we can leave the refresh rate to 15 seconds or refresh manually:

The statement is shown with a duration of 20 seconds and still running. After completion, the screen looks like that:

Now isn’t that a nice feature to observe the most critical and long running statements in Real-Time?

P.S.: As I just got aware (thanks to Niall Litchfield) this was already possible with Database Control in Release 1 – just from a less obvious place in that tool. So just take it as a 11g New Feature, not as 11g R2 New Feature

I just did a Webinar about Oracle Database Security (free of charge, one hour duration). We have  had some technical problems with the audio and therefore a delay, though, for which I have to apologize. I have talked there about the following:

Standard Auditing (AUDIT_TRAIL parameter)

sys Auditing into an OS file, owned by root (AUDIT_SYS_OPERATIONS & AUDIT_SYSLOG_LEVEL parameter)

Fine Grained Auditing (DBMS_FGA package)

Transparent Data Encryption for columns (10g New Feature) and tablespaces (11g New Feature)

You may download the presentation of that webinar together with my demonstrations as PDFs from here, if you are interested.

Also, I have placed a link on the downloads page to the recorded Webinar. By the way, I usually do those Webinars & LVC courses with a Webcam, but during the troubleshooting measurements in order to get my Headset running under WebEx, we switched the Webcam off.

Click here to view the Security webinar recording online