Introduction
The new Oracle database 12c R1 (12.1.0.1.0) release was finally available and published on 06/25/2013 by Oracle. SAP usually do not certify the R1 database releases for their software (exceptions are always possible), but it is still worth to investigate and explore the new Oracle database release right now for future intended SAP use or currently non-SAP use.
I downloaded it on the same evening and already tested a few enhancements, features or the new database architecture on Linux (x86_64) and Solaris (x86_64).
This blog should be a summary of all the interesting features (for me) and a documentation of my current and future researching cycles. Some of the smaller topics will be explained in this one, otherwise they will be placed in a separate blog and linked here.
... so stay tuned and crosscheck this blog from time to time for more results.
[UPDATE 1] 07/10/13: Added content to section "1.8.2.2 Tracking I/O Outliers"
[UPDATE 2] 07/11/13: Added content to section "1.5.5.3 Invisible Columns"
[UPDATE 3] 07/15/13: Added new section "1.1.6.11 SQL Translation Framework" and linked content
[UPDATE 4] 07/17/13: Added content to section "1.2.3.5 Partial Indexes for Partitioned Tables"
[UPDATE 5] 07/18/13: Added content to section "1.2.3.1 Asynchronous Global Index Maintenance for DROP and TRUNCATE Partition"
[UPDATE 6] 09/21/13: Added content to section "1.8.2.2 Tracking I/O Outliers" related to missing data in view V$KERNEL_IO_OUTLIER
[UPDATE 7] 09/24/13: Added content to section "1.2.4 Performance With Zero Effort (Full)"
Exploring enhancements / features
The following feature / enhancement list is based on the Oracle Database New Features Guide 12c Release 1 (12.1), but it covers only the key topics, that i am particularly interested in.
1.1.6.11 SQL Translation Framework
I already took part in some re-searching and a discussion about this feature on Kerry Osborne's blog. So i will just copy & paste the external source for more detailed information and a test case of this feature.
1.2.3.1 Asynchronous Global Index Maintenance for DROP and TRUNCATE Partition
Global index maintenance is decoupled from the DROP and TRUNCATE partition maintenance operation without rendering a global index unusable. Index maintenance is done asynchronously and can be delayed to a later point-in-time. Delaying the global index maintenance to off-peak times without impacting the index availability makes DROP and TRUNCATE partition and subpartition maintenance operations faster and less resource intensive at the point-in-time of the partition maintenance operation. In pre-Oracle 12c times a DROP or TRUNCATE PARTITION DML statement makes the index unusable or took much more time and resources. This was especially critical in OLTP environments when data was partitioned for performance or data management reasons.
The partition maintenance operations DROP PARTITION and TRUNCATE PARTITION are optimized by making the index maintenance for metadata only. Asynchronous global index maintenance for DROP and TRUNCATE is performed by default; however, the UPDATE INDEXES clause is still required for backward compatibility.
The following list summarizes the limitations of asynchronous global index maintenance:
- Only performed on heap tables
- No support for tables with object types
- No support for tables with domain indexes
- Not performed for the user SYS
Maintenance operations on indexes can be performed with the automatic scheduler job SYS.PMO_DEFERRED_GIDX_MAINT_JOB to clean up all global indexes. This job is scheduled to run at 2:00 A.M. on a daily basis by default. You can run this job at any time using DBMS_SCHEDULER.RUN_JOB if you want to proactively clean up the indexes. You can also modify the job to run with a different schedule based on your specific requirements. However, Oracle recommends that you do not drop the job.
You can also force cleanup of an index needing maintenance using one of the following options:
- DBMS_PART.CLEANUP_GIDX - This PL/SQL procedure gathers the list of global indexes in the system that may require cleanup and runs the operations necessary to restore the indexes to a clean state.
- ALTER INDEX REBUILD [PARTITION] – This SQL statement rebuilds the entire index or index partition as is done in releases previous to Oracle Database 12c Release 1 (12.1). The resulting index (partition) does not contain any stale entries.
- ALTER INDEX [PARTITION] COALESCE CLEANUP – This SQL statement cleans up any orphaned entries in index blocks.
The following demo shows the difference between Oracle 11g R2 (11.2.0.3.6) and Oracle 12c R1 (12.1.0.1) on OEL 6.4 (2.6.39-400.109.1.el6uek.x86_64).
-- Oracle 11g R2 & Oracle 12c R1 database structures
SQL> CREATE TABLE TAB_PART (MANDT VARCHAR2(3), TEXT VARCHAR2(40))
PARTITION BY LIST (MANDT)
(PARTITION MANDT_000 VALUES ('000'), PARTITION MANDT_066 VALUES ('066'),
PARTITION MANDT_100 VALUES ('100'));
SQL> CREATE INDEX TAB_PART_I ON TAB_PART(MANDT);
SQL> insert into TAB_PART VALUES ('000', 'MAN 000');
SQL> insert into TAB_PART VALUES ('066', 'MAN 066');
SQL> insert into TAB_PART VALUES ('100', 'MAN 100');
SQL> commit;Let's test the DML behavior on Oracle 11g R2 first (as a base line test)
TEST@T11DB:15> select PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
from DBA_TAB_PARTITIONS where TABLE_NAME = 'TAB_PART';
PARTITION_NAME PARTITION_POSITION HIGH_VALUE
------------------------------ ------------------ ------------------------------
MANDT_000 1 '000'
MANDT_066 2 '066'
MANDT_100 3 '100'
TEST@T11DB:15> select OWNER, STATUS, PARTITIONED from DBA_INDEXES
where INDEX_NAME = 'TAB_PART_I';
OWNER STATUS PAR
-------------------- -------- ---
TEST VALID NO
-- Let's assume that we want to get the rid of the data partition for client 100
TEST@T11DB:15> alter table TAB_PART drop partition MANDT_100 UPDATE INDEXES;
TEST@T11DB:15> select PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
from DBA_TAB_PARTITIONS where TABLE_NAME = 'TAB_PART';
PARTITION_NAME PARTITION_POSITION HIGH_VALUE
------------------------------ ------------------ ------------------------------
MANDT_000 1 '000'
MANDT_066 2 '066'
TEST@T11DB:15> select OWNER, STATUS, PARTITIONED from DBA_INDEXES
where INDEX_NAME = 'TAB_PART_I';
OWNER STATUS PAR
-------------------- -------- ---
TEST VALID NOThe index was maintained and the DML operation has to perform the "index clean up" work right now as no "delay option" is available as in Oracle 12c.
Let's check out what happens, if we omit the "UPDATE INDEXES" clause (Use the update_index_clauses to update the indexes on table as part of the table partitioning operation. When you perform DDL on a table partition, if an index is defined on table, then Oracle Database invalidates the entire index, not just the partitions undergoing DDL. This clause lets you update the index partition you are changing during the DDL operation, eliminating the need to rebuild the index after the DDL.).
TEST@T11DB:15> alter table TAB_PART drop partition MANDT_066;
TEST@T11DB:15> select OWNER, STATUS, PARTITIONED from DBA_INDEXES
where INDEX_NAME = 'TAB_PART_I';
OWNER STATUS PAR
-------------------- -------- ---
TEST UNUSABLE NOThe global index become unusable and is not valid anymore for data access.
Now let's do the same procedures on Oracle 12c R1
TEST@T12DB:175> select PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
from DBA_TAB_PARTITIONS where TABLE_NAME = 'TAB_PART';
PARTITION_NAME PARTITION_POSITION HIGH_VALUE
-------------------- ------------------ --------------------
MANDT_000 1 '000'
MANDT_066 2 '066'
MANDT_100 3 '100'
TEST@T12DB:175> select OWNER, STATUS, PARTITIONED, INDEXING, ORPHANED_ENTRIES
from DBA_INDEXES where INDEX_NAME = 'TAB_PART_I';
OWNER STATUS PAR INDEXIN ORP
-------------------- -------- --- ------- ---
TEST VALID NO FULL NO
-- Let's assume that we want to get the rid of the data partition for client 100
TEST@T12DB:175> alter table TAB_PART drop partition MANDT_100 UPDATE INDEXES;
TEST@T12DB:175> select PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
from DBA_TAB_PARTITIONS where TABLE_NAME = 'TAB_PART';
PARTITION_NAME PARTITION_POSITION HIGH_VALUE
-------------------- ------------------ --------------------
MANDT_000 1 '000'
MANDT_066 2 '066'
TEST@T12DB:175> select OWNER, STATUS, PARTITIONED, INDEXING, ORPHANED_ENTRIES
from DBA_INDEXES where INDEX_NAME = 'TAB_PART_I';
OWNER STATUS PAR INDEXIN ORP
-------------------- -------- --- ------- ---
TEST VALID NO FULL YESCheck the column ORPHANED_ENTRIES (= Indicates whether a global index contains stale entries because of deferred index maintenance during DROP/TRUNCATE PARTITION, or MODIFY PARTITION INDEXING OFF operations.). The index was not maintained and the DML operation itself is pretty fast without making the index unusable.
-- Manual clean up of orphaned index entries
TEST@T12DB:175> exec DBMS_PART.CLEANUP_GIDX('TEST','TAB_PART');
TEST@T12DB:175> select OWNER, STATUS, PARTITIONED, INDEXING, ORPHANED_ENTRIES
from DBA_INDEXES where INDEX_NAME = 'TAB_PART_I';
OWNER STATUS PAR INDEXIN ORP
-------------------- -------- --- ------- ---
TEST VALID NO FULL NOLet's check out what happens if we omit the "UPDATE INDEXES" clause (Use the update_index_clauses to update the indexes on table as part of the table partitioning operation. When you perform DDL on a table partition, if an index is defined on table, then Oracle Database invalidates the entire index, not just the partitions undergoing DDL. This clause lets you update the index partition you are changing during the DDL operation, eliminating the need to rebuild the index after the DDL.).
TEST@T12DB:175> alter table TAB_PART drop partition MANDT_066;
TEST@T12DB:175> select PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
from DBA_TAB_PARTITIONS where TABLE_NAME = 'TAB_PART';
PARTITION_NAME PARTITION_POSITION HIGH_VALUE
-------------------- ------------------ --------------------
MANDT_000 1 '000'
TEST@T12DB:175> select OWNER, STATUS, PARTITIONED, INDEXING, ORPHANED_ENTRIES
from DBA_INDEXES where INDEX_NAME = 'TAB_PART_I';
OWNER STATUS PAR INDEXIN ORP
-------------------- -------- --- ------- ---
TEST UNUSABLE NO FULL NOThe global index become unusable and is not valid anymore for data access. (= same behavior as in older Oracle releases)
At last here is the time and work difference of the primary DML operation (drop partition update indexes) between Oracle 11g R2 and Oracle 12c R1 on partition"MANDT_100" with 1.000.000 rows in it.
-- Oracle 11g R2
TEST@T11DB:15> alter system flush buffer_cache;
TEST@T11DB:15> alter table TAB_PART drop partition MANDT_100 UPDATE INDEXES;
-- Oracle 12c R1
TEST@T11DB:175> alter system flush buffer_cache;
TEST@T12DB:175> alter table TAB_PART drop partition MANDT_100 UPDATE INDEXES;
The main advantage of this feature is that it decouples the index maintenance task from the DML operation itself and makes such (primary) DML operations much faster without invalidating the (global) indexes.
1.2.3.5 Partial Indexes for Partitioned Tables
A partial index is an index that is correlated with the indexing properties of an associated partitioned table. The correlation enables you to specify which table partitions are indexed. You can turn indexing on or off for the individual partitions of a table. A partial local index does not have usable index partitions for all table partitions that have indexing turned off. A global index, whether partitioned or not, excludes the data from all partitions that have indexing turned off. The database does not support partial indexes for indexes that enforce unique constraints.
Restrictions on Partial Indexes:
- The underlying table of a partial index cannot be a non-partitioned table.
- Unique indexes cannot be partial indexes. This applies to indexes created with the CREATE UNIQUE INDEX statement and indexes that are implicitly created when you specify a unique constraint on one or more columns.
So maybe you wonder when this can be useful? Let's assume that you have partitioned your IDoc table EDIDC by the IDoc status (it is not practical and useable with this implementation in real the real word - it is just used for a simple illustration of the partial index feature). Nearly all of your queries (based on the status) select IDocs with status "02" (= error) and so you don't want to maintain a global or local index with all the other status values for performance (DML) reasons or just because of saving disk space.
SYS@T12DB:9> CREATE TABLE TAB_PART (MANDT VARCHAR2(3), IDOC VARCHAR2(16),
STATUS VARCHAR2(2))
INDEXING OFF
PARTITION BY LIST (STATUS)
(PARTITION STATUS_ERR_02 VALUES ('02') INDEXING ON,
PARTITION STATUS_OK_12 VALUES ('12'),
PARTITION STATUS_OK_13 VALUES ('13') INDEXING OFF,
PARTITION STATUS_OK_14 VALUES ('14'));
SYS@T12DB:9> insert into TAB_PART values ('100','0000000000000001', '02');
SYS@T12DB:9> insert into TAB_PART values ('100','0000000000000002', '12');
SYS@T12DB:9> insert into TAB_PART values ('100','0000000000000003', '13');
SYS@T12DB:9> insert into TAB_PART values ('100','0000000000000004', '14');
SYS@T12DB:9> commit;
SYS@T12DB:9> select PARTITION_NAME, HIGH_VALUE, PARTITION_POSITION, INDEXING
from DBA_TAB_PARTITIONS
where TABLE_NAME = 'TAB_PART';
PARTITION_NAME HIGH_VALUE PARTITION_POSITION INDE
-------------------- -------------------- ------------------ ----
STATUS_ERR_02 '02' 1 ON
STATUS_OK_12 '12' 2 OFF
STATUS_OK_13 '13' 3 OFF
STATUS_OK_14 '14' 4 OFF
SYS@T12DB:9> create index TAB_PART_STATUS_ALL on TAB_PART(STATUS);
SYS@T12DB:9> select INDEX_NAME, INDEXING from DBA_INDEXES
where INDEX_NAME = 'TAB_PART_STATUS_ALL';
INDEX_NAME INDEXIN
------------------------------ -------
TAB_PART_STATUS_ALL FULLIn the example above i created a partitioned table called TAB_PART and set the default attribute to "INDEXING OFF" on table level, which means that indexing is not enabled by default for the table partitions. I explicitly set "INDEXING ON" for table partition STATUS_ERR_02, which overrides the table default and enables indexing on this partition. I also explicitly set "INDEXING OFF" for table partition STATUS_OK_13 (for demonstration purpose only), but this is not necessary at all due to the table level default.
You can still create the "old fashion full global index" on the status column, even if the new partition attribute "INDEXING" is set on table or table partition level. But let's go on and check out the new partial feature.
SYS@T12DB:9> drop index TAB_PART_STATUS_ALL;
SYS@T12DB:9> create index TAB_PART_STATUS_PART on TAB_PART(STATUS) indexing partial;
SYS@T12DB:9> select INDEX_NAME, INDEXING, PARTITIONED from DBA_INDEXES
where INDEX_NAME = 'TAB_PART_STATUS_PART';
INDEX_NAME INDEXIN PAR
------------------------------ ------- ---
TAB_PART_STATUS_PART PARTIAL NONow only the data with status = '02' is globally indexed and maintained - all the other partitions (and the data) are not included in the index (cross check this with column INDEXING from the query on DBA_TAB_PARTITIONS). Here is just a short verification of that.
SYS@T12DB:9> select count(*) from TAB_PART where STATUS = '02';
SYS@T12DB:9> select count(*) from TAB_PART where STATUS = '14';
At last let's create a local partial index instead of a global partial index.
SYS@T12DB:9> drop index TAB_PART_STATUS_PART;
SYS@T12DB:9> create index TAB_PART_STATUS_PART on TAB_PART(STATUS) local indexing partial;
SYS@T12DB:9> select INDEX_NAME, INDEXING, PARTITIONED from DBA_INDEXES
where INDEX_NAME = 'TAB_PART_STATUS_PART';
INDEX_NAME INDEXIN PAR
------------------------------ ------- ---
TAB_PART_STATUS_PART PARTIAL YES
SYS@T12DB:9> select INDEX_NAME, PARTITION_NAME, STATUS from DBA_IND_PARTITIONS
where INDEX_NAME = 'TAB_PART_STATUS_PART';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ -------------------- --------
TAB_PART_STATUS_PART STATUS_OK_14 UNUSABLE
TAB_PART_STATUS_PART STATUS_OK_13 UNUSABLE
TAB_PART_STATUS_PART STATUS_OK_12 UNUSABLE
TAB_PART_STATUS_PART STATUS_ERR_02 USABLEAs you can see the index partition STATUS_ERR_02 is the only one that is useable - the other unusable partitions have no corresponding database segment and are not maintained at all.
You can also create a concatenated partial index like "(STATUS, IDOC)", which can make more sense from an application point of view, but this test case should only provide a basic demo of the partial indexing feature.
1.2.3.6 Partition Maintenance Operations on Multiple Partitions
Did you ever need to re-partition some of your SAP BI fact tables after a while and the "MAXVALUE" partition already contained several data? I guess you already know the pretty well known issues and procedures (like "SAPnote #895539 - Appending/extending partitions to an E fact table" or "SAPnote #1016184 - ORA repartitioning: Attaching partitions by re-partitioning") then. Starting with Oracle 12c you can split up one partition (like the MAXVALUE one) into several different ones without a "step by step" or "additional logic" approach. Just one SQL command for splitting up the data partition into several ones.
Let's assume you have initially created 3 partitions for your E-Fact table based on a monthly partitioning interval and have already inserted data for 5 months now.
SYS@T12DB:182> CREATE TABLE TPART_BI
(MANDT VARCHAR2(3), DATEVALUE VARCHAR2(8), TEXT VARCHAR2(20))
PARTITION BY RANGE (DATEVALUE)
(
PARTITION TPART_BI_RANGE_201301 VALUES LESS THAN ('20130201')
TABLESPACE USERS,
PARTITION TPART_BI_RANGE_201302 VALUES LESS THAN ('20130301')
TABLESPACE USERS,
PARTITION TPART_BI_RANGE_MAXVALUE VALUES LESS THAN (MAXVALUE)
TABLESPACE USERS
)
TABLESPACE USERS;
SYS@T12DB:182> INSERT INTO TPART_BI VALUES ('100','20130115', 'January');
SYS@T12DB:182> INSERT INTO TPART_BI VALUES ('100','20130215', 'February');
SYS@T12DB:182> INSERT INTO TPART_BI VALUES ('100','20130315', 'March');
SYS@T12DB:182> INSERT INTO TPART_BI VALUES ('100','20130415', 'April');
SYS@T12DB:182> INSERT INTO TPART_BI VALUES ('100','20130515', 'May');
SYS@T12DB:182> commit;
SYS@T12DB:182> select PARTITIONING_TYPE, PARTITION_COUNT
from DBA_PART_TABLES where TABLE_NAME = 'TPART_BI';
PARTITION PARTITION_COUNT
--------- ---------------
RANGE 3
SYS@T12DB:182> select PARTITION_POSITION, PARTITION_NAME, HIGH_VALUE
from DBA_TAB_PARTITIONS where TABLE_NAME = 'TPART_BI';
PARTITION_POSITION PARTITION_NAME HIGH_VALUE
------------------ ------------------------- ---------------
1 TPART_BI_RANGE_201301 '20130201'
2 TPART_BI_RANGE_201302 '20130301'
3 TPART_BI_RANGE_MAXVALUE MAXVALUENow we want to re-construct our previous partitioning logic (one partition per month). This could be done easily with one SQL command now and does not need an additional logic (like described in SAPnote #1016184) as before by splitting up the MAXVALUE partition into several ones.
SYS@T12DB:182> ALTER TABLE TPART_BI SPLIT PARTITION TPART_BI_RANGE_MAXVALUE INTO
( PARTITION TPART_BI_RANGE_201303 VALUES LESS THAN ('20130401'),
PARTITION TPART_BI_RANGE_201304 VALUES LESS THAN ('20130501'),
PARTITION TPART_BI_RANGE_MAXVALUE
);
SYS@T12DB:182> select PARTITIONING_TYPE, PARTITION_COUNT
from DBA_PART_TABLES where TABLE_NAME = 'TPART_BI';
PARTITION PARTITION_COUNT
--------- ---------------
RANGE 5
SYS@T12DB:182> select PARTITION_POSITION, PARTITION_NAME, HIGH_VALUE
from DBA_TAB_PARTITIONS where TABLE_NAME = 'TPART_BI';
------------------ ------------------------- ---------------
1 TPART_BI_RANGE_201301 '20130201'
2 TPART_BI_RANGE_201302 '20130301'
3 TPART_BI_RANGE_201303 '20130401'
4 TPART_BI_RANGE_201304 '20130501'
5 TPART_BI_RANGE_MAXVALUE MAXVALUE Re-partitioning finished with one SQL command and without any additional logic for multiple partitions.
1.2.4 Performance With Zero Effort (Full)
1.2.4.1 Adaptive Query Optimization
... stay tuned - more to come
1.3.3 Information Lifecycle Management (Full)
... stay tuned - more to come
1.4.1 Database Consolidation (Full)
1.4.1.1 Integrate With Operating System Processor Groups
Some of my clients are running a highly consolidated Oracle database infrastructure (like on VMware, Solaris containers or IBM pSeries). One of the challenges is to "isolate" each database instance as far as possible, so that a "going crazy" database can not harm the other ones. Mostly this is done by (OS) resource managers like WLM or Solaris resource management. Now you can limit and bind the database instance to a corresponding CPU pool set on Linux or Solaris by using OS (kernel) features very easily and it is fully integrated.
This feature allows the DBA to specify a parameter, PROCESSOR_GROUP_NAME, to bind the database instance to a named subset of the CPUs on the server. On Linux, the named subset of CPUs can be created using a Linux feature called control groups (cgroups). On Solaris, the named subset of CPUs can be created using a Solaris feature called resource pools
The following demo is run on an Oracle Enterprise Linux 6.4 (2.6.39-400.109.1.el6uek.x86_64) with cgroups and 3 vCPUs (Intel Core i7-2675QM CPU @ 2.20GHz) on Oracle Virtual Box.
--- The following PL/SQL is run in 3 different sessions / dedicated server processes to use up all vCPUs
SYS@T12DB:182> declare
x number;
begin
loop
x := x+1;
end loop;
end;
/The result of this "CPU burning" PL/SQL procedure is represented in the following graphic (without setting init parameter PROCESSOR_GROUP_NAME).
Now let's create and assign a cgroup (cpu set) to the Oracle instance. Check the previously mentioned PDF or the kernel documentation for details about cgroups and how to configure them.
-- My custom defined cgroup called oracle is allowed to use only 1 vCPU
shell> cat /cgroup/oracle/cpuset.cpus
1
shell> cat /cgroup/oracle/cpuset.mems
0
-- Both parameters are important for dynamically adding the Oracle PIDs to /cgroup/oracle/tasks at startup
-- Otherwise you will get an unnoticed error like this (traced with strace by Oracle instance startup):
-- open("/cgroup/oracle/tasks", O_WRONLY|O_CREAT|O_APPEND, 0666) = 25
-- write(25, "2260\n", 5) = -1 ENOSPC (No space left on device)
SQL> alter system set PROCESSOR_GROUP_NAME = 'oracle' scope=spfile sid='*';
SQL> startup force;
shell> ps -fu oracle
...
oracle 2868 1 2 14:21 ? 00:00:00 oracleT12DB (LOCAL=NO)
...
shell> cat /proc/2868/cpuset
/oracle
-- The dedicated Oracle shadow processes are running in the corresponding croupThe result of the previous "CPU burning" PL/SQL procedure is represented in the following graphic (with setting init parameter PROCESSOR_GROUP_NAME to "oracle").
The Oracle database/instance T12DB is restricted to and using 1 vCPU only (even if the PL/SQL procedure is run in multiple session like before), but what if we want to adjust these limits on "on-the-fly". No problem at all, because of the cgroups are "dynamic". Let's verify this at last.
shell> echo 0-1 > /cgroup/oracle/cpuset.cpus
Works as designed - a really cool and dynamic feature for (highly) consolidated Oracle database environments.
...stay tuned - more to come for the other sub topics
1.4.3.1 Cloning a Database
... stay tuned - more to come
1.5.4.1 Oracle ASM Disk Scrubbing
... stay tuned - more to come
1.5.5.3 Invisible Columns
With Oracle 12c you can make individual table columns invisible. Any generic access of a table does not show the invisible columns in the table.
For example, the following operations do not display invisible columns in the output:
- SELECT * FROM statements in SQL
- DESCRIBE commands in SQL*Plus
- %ROWTYPE attribute declarations in PL/SQL
- Describes in Oracle Call Interface (OCI)
You can use a SELECT statement to display output for an invisible column only if you explicitly specify the invisible column in the column list. Similarly, you can insert a value into an invisible column only if you explicitly specify the invisible column in the column list for the INSERT statement. If you omit the column list in the INSERT statement, then the statement can only insert values into visible columns. You can make a column invisible during table creation or when you add a column to a table, and you can later alter the table to make the same column visible. You can also alter a table to make a visible column invisible. Virtual columns can be invisible. Also, you can use an invisible column as a partitioning key during table creation. (The last statement can enhance the custom partitioning of tables in a SAP environment drastically).
The following restrictions apply to invisible columns:
- The following types of tables cannot have invisible columns:
- External tables
- Cluster tables
- Temporary tables
- Attributes of user-defined types cannot be invisible.
Let's do a short simple demo of such invisible columns with some SAP reference (especially for the new custom partitioning possibility based on virtual and invisible columns).
SYS@T12DB:184> create table TAB1 (JINUM VARCHAR2(10),
TEXT VARCHAR2(40), JINUM2 NUMBER INVISIBLE);
SYS@T12DB:184> desc TAB1;
Name Null? Type
-------- -------- ----------------
JINUM VARCHAR2(10)
TEXT VARCHAR2(40)
SYS@T12DB:184> INSERT INTO TAB1 VALUES ('0000000010', 'TEST', 10);
ORA-00913: too many values
SYS@T12DB:184> INSERT INTO TAB1 VALUES ('0000000010', 'TEST');
1 row created.
SYS@T12DB:184> select * from TAB1;
JINUM TEXT
---------- ----------------------------------------
0000000010 TEST
SYS@T12DB:184> select JINUM, TEXT, JINUM2 from TAB1;
JINUM TEXT JINUM2
---------- ---------------------------------------- ----------
0000000010 TEST
SYS@T12DB:184> INSERT INTO TAB1(JINUM,TEXT,JINUM2) VALUES ('0000000010', 'TEST', 10);
1 row created.
SYS@T12DB:184> select * from TAB1;
JINUM TEXT
---------- ----------------------------------------
0000000010 TEST
0000000010 TEST
SYS@T12DB:184> select JINUM, TEXT, JINUM2 from TAB1;
JINUM TEXT JINUM2
---------- ---------------------------------------- ----------
0000000010 TEST
0000000010 TEST 10So you can see that an invisible column is completely hidden from the application layer (except from explicitly using it in SQL statements).
I have previously mentioned something about enhancing the custom partitioning in SAP environments. SAP has written its own partitioning framework called "SAP Partitioning Engine for Oracle", because of some Oracle features like interval partitioning were not usable due to the used data types in a SAP environment (like VARCHAR2 for numeric values only). The other partitioning possibilities need some "manual maintenance" to keep the partitions actual and fitting and so SAP needed to implement an "application based solution".
However by using invisible columns (in combination with virtual columns) you can remove such interval partitioning restrictions without thinking about already existing SQL statements or harming the data integrity. Here is just a tiny example of this (you can adapt this to your own business cases like on JITIT, BSIS or whatever).
SYS@T12DB:184> CREATE TABLE TAB1_PART (JINUM VARCHAR2(10),
TEXT VARCHAR2(40), JINUM2 NUMBER INVISIBLE AS (TO_NUMBER(JINUM)) VIRTUAL)
PARTITION BY RANGE(JINUM2)
INTERVAL (5000)
( PARTITION P5000 VALUES LESS THAN (5000),
PARTITION P10000 VALUES LESS THAN (10000),
PARTITION P15000 VALUES LESS THAN (15000)
);
SYS@T12DB:184> desc TAB1_PART;
Name Null? Type
-------- -------- ----------------
JINUM VARCHAR2(10)
TEXT VARCHAR2(40)
SYS@T12DB:184> select TABLE_NAME, PARTITION_POSITION, PARTITION_NAME, HIGH_VALUE
from DBA_TAB_PARTITIONS where TABLE_NAME='TAB1_PART';
TABLE_NAME PARTITION_POSITION PARTITION_NAME HIGH_VALUE
-------------------- ------------------ -------------------- --------------------
TAB1_PART 1 P5000 5000
TAB1_PART 2 P10000 10000
TAB1_PART 3 P15000 15000
SYS@T12DB:184> INSERT INTO TAB1_PART VALUES ('0000000010', 'TEST');
SYS@T12DB:184> INSERT INTO TAB1_PART VALUES ('0000006000', 'TEST');
SYS@T12DB:184> INSERT INTO TAB1_PART VALUES ('0000012000', 'TEST');
SYS@T12DB:184> select * from TAB1_PART where JINUM = '0000000010';
The optimizer is not clever enough to rewrite the query on partition level in such cases (partition pruning works only on column JINUM2) - so no performance improvement here, but you can manage your large data on partition level much better (= same target by using the table limited SAP Partitioning Engine).
At last let's add data, that does not fit in the currently created partitions.
SYS@T12DB:184> INSERT INTO TAB1_PART VALUES ('0000016000', 'TEST');
SYS@T12DB:184> select TABLE_NAME, PARTITION_POSITION, PARTITION_NAME, HIGH_VALUE
from DBA_TAB_PARTITIONS where TABLE_NAME='TAB1_PART';
TABLE_NAME PARTITION_POSITION PARTITION_NAME HIGH_VALUE
-------------------- ------------------ -------------------- --------------------
TAB1_PART 1 P5000 5000
TAB1_PART 2 P10000 10000
TAB1_PART 3 P15000 15000
TAB1_PART 4 SYS_P461 20000Partition management works perfectly and the application (SQL) does not need to be rewritten by using virtual hidden columns in such "hidden partitioning scenarios". The possible performance impact by such implementations need to be tested first of course, but you have a valid possibility in SAP environments now.
1.5.5.5 Metadata-Only DEFAULT Column Values for NULL Columns
... stay tuned - more to come
1.5.5.6 Move a Data File Online
Did you ever need to migrate a large Oracle database from an old storage subsystem to a new one (or ASM) with a nearly zero downtime requirement and the storage infrastructure was not virtualized to move it "on-the-fly"? Did you ever need to redesign the database file system layout on OS with a nearly zero downtime requirement? I guess every Oracle DBA got such requirements in the pasts and usually used Oracle Data Guard for that tasks. Starting with Oracle 12c it is getting much easier to move your database around with nearly zero downtime.
A data file can now be moved online while it is open and being accessed. Being able to move a data file online means that many maintenance operations, such as moving data to another storage device or moving databases into Oracle Automatic Storage Management (Oracle ASM), can be performed while users are accessing the system.
By default, when you run the ALTER DATABASE MOVE DATAFILE statement and specify a new location for a data file, the statement moves the data file. However, you can specify the KEEP option to retain the data file in the old location and copy it to the new location. In this case, the database only uses the data file in the new location when the statement completes successfully.
When you rename or relocate a data file with ALTER DATABASE MOVE DATAFILE statement, Oracle Database creates a copy of the data file when it is performing the operation. Ensure that there is adequate disk space for the original data file and the copy during the operation.
SYS@T12DB:18> select NAME, STATUS, ENABLED from V$DATAFILE where TS# = 4;
NAME STATUS ENABLED
---------------------------------------- ------- ----------
/oracle/T12DB/oradata/users01.dbf ONLINE READ WRITE
SYS@T12DB:18> ALTER DATABASE MOVE DATAFILE '/oracle/T12DB/oradata/users01.dbf'
TO '/oracle/T12DB/oradata/users02.dbf';
SYS@T12DB:18> select NAME, STATUS, ENABLED from V$DATAFILE where TS# = 4;
NAME STATUS ENABLED
---------------------------------------- ------- ----------
/oracle/T12DB/oradata/users02.dbf ONLINE READ WRITE
shell> ls -la /oracle/T12DB/oradata/user*
-rw-r----- 1 oracle dba 5251072 Jul 4 18:08 /oracle/T12DB/oradata/users02.dbf1.5.5.9 Single Command REDEF_TABLE to Redefine Table or Partition
... stay tuned - more to come
1.5.6.1 Advanced Data Guard Broker Manageability
... stay tuned - more to come
1.5.6.4 Single Command Role Transitions
... stay tuned - more to come
1.5.6.11 Active Data Guard Far Sync
... stay tuned - more to come
1.5.9.2 Cross-Platform Backup and Restore
... stay tuned - more to come
1.5.9.6 Network-Enabled RESTORE
... stay tuned - more to come
1.6.1.3 Real-Time Database Operations Monitoring
... stay tuned - more to come
1.7.1.1 Oracle Flex ASM
... stay tuned - more to come
1.8.1 Database Performance Enhancements (Full)
... stay tuned - more to come
1.8.2.2 Tracking I/O Outliers
Starting with version 12c Oracle automatically tracks long time I/O requests and populates it in 3 different views (V$IO_OUTLIER, V$KERNEL_IO_OUTLIER, V$LGWRIO_OUTLIER). Long time I/O requests are defined as requests that take more than 500 ms. So this is usually nothing for I/O variability in the milliseconds area, but it will help a lot by troubleshooting serious I/O issues. You still have to use tools like dtrace/strace/truss or doing a SQL trace with wait event analysis, if you need to track down "low latency" I/O variability. You already got some basic information about such I/O outliers in the LGWR trace files (for example) in pre-Oracle 12c times, but it included the timestamp and I/O duration only.
One of these three mentioned views is currently useful on Solaris only - so please check the details here:
- V$IO_OUTLIER (X$KSFDSTLL WHERE IO_COMPONENT_ID != 2) - Supported on all OS platforms
- V$LGWRIO_OUTLIER (X$KSFDSTLL WHERE IO_COMPONENT_ID = 2) - Supported on all OS platforms
- V$KERNEL_IO_OUTLIER (X$KSFDKLL) - Available on all OS platforms, but populated only on Solaris
The following demo was performed on Solaris 11.1 (x86_64) on Oracle Virtual Box and the database was stored in a ZFS pool / file system. I used Solaris for this test, so that i am able to demonstrate all of these views. Unfortunately the view V$KERNEL_IO_OUTLIER was not working as expected and populating no values at all (even not on Solaris). I stressed my I/O "sub system" with a RMAN VALIDATE procedure to delay the usual database I/Os accordingly.
shell> uname -a
SunOS SOL 5.11 11.1 i86pc i386 i86pc
shell> rman target /
RMAN> VALIDATE DATABASE;
SYS@S12DB:23> select * from V$IO_OUTLIER;
SYS@S12DB:23> select * from V$LGWRIO_OUTLIER;
SYS@S12DB:23> select * from V$KERNEL_IO_OUTLIER;
no rows selectedBut wait - what's wrong here - why is the view V$KERNEL_IO_OUTLIER empty and not populated on Solaris as described? It seems to be reasonable, that this feature is based on DTrace probes as it is currently available on Solaris (hopefully it will be ported to OEL as well like DTrace) only. So let's verify DTrace for the oracle (instance/database) OS user.
-- Just for documentation how the oracle user was created in the past
shell> usermod -K defaultpriv=basic,dtrace_proc,dtrace_user,dtrace_kernel oracle
-- DTrace test
shell> id -a
uid=100(oracle) gid=100(dba) groups=100(dba),101(oper)
shell> /usr/sbin/dtrace -n 'syscall::exece:return'
dtrace: description 'syscall::exece:return' matched 1 probe
CPU ID FUNCTION:NAME
0 113 exece:return Looks good as DTrace is working in general for the Oracle user. I have started a discussion about this behavior in the comment section of a blog post called "12c I/O debug" by Jonathan Lewis .. hopefully we will get some interesting insights into this in the near future.
Luckily Andrey S. Nikolaev took notice of the discussion in the comment section of Jonathan Lewis blog post and did some re-engineering of view V$KERNEL_IO_OUTLIER. Please check out the details (why and when this view populates values) in his blog post V$KERNEL_IO_OUTLIER .. it is pretty interesting.
1.9.2.6 Last Login Time Information
In the past years i sometimes needed to know when a user was created, a password was changed, a user was locked or when the user logged on the last time. The information to the first three demands was already available in previous Oracle releases (column CTIME, PTIME and LTIME in table USER$), but for the fourth one you needed to create a logon trigger or enable auditing on such actions. Such changes can be very time-consuming in some environments (ITIL, etc.) and even you are able to do it immediately, you don't have a valid conclusion from the beginning. Starting with Oracle 12c such tracking is "built-in" and can be evaluated at any time as well.
This new information is stored in column SPARE6 in table USER$. Be aware of the time zone - something like this "alter session set TIME_ZONE='+02:00'" is done by SQL*Plus "on-the-fly" by logon.
The last login time for database users is recorded in the USER$ table and displayed when connecting to the database using Oracle SQL*Plus.
The last login time for non-SYS users is displayed when you log on by SQL*Plus. This feature is on by default.
SYS@T12DB:24> create user TESTME identified by TESTME DEFAULT TABLESPACE USERS;
SYS@T12DB:24> grant connect to TESTME;
SYS@T12DB:24> select CTIME, PTIME, LTIME, SPARE6 from USER$ where NAME = 'TESTME';
CTIME PTIME LTIME SPARE6
------------------- ------------------- ------------------- -------------------
04.07.2013 16:55:39 04.07.2013 16:55:39 04.07.2013 15:23:00
shell> sqlplus TESTME/TESTME@T12DB
…
Last Successful login time: Thu Jul 04 2013 17:23:00 +02:00
…Summary
If you have any further questions - please feel free to ask or get in contact directly, if you need assistance by implementing complex Oracle database landscapes, new Oracle database releases or by troubleshooting Oracle (performance) issues.
- SAP Managed Tags:
2 Comments
