Posts Tagged 12c New Features

How Partial Indexing helps you save space in #Oracle 12c

partial

Over time certain partitions may become less popular. In 12c, you don’t have to index these partitions anymore! This can save huge amounts of space and is one of the best 12c New Features in my opinion. Really a big deal if you are working with range partitioned tables where the phenomenon of old ranges becoming unpopular is very common. Let’s have a look, first at the problem:

SQL> select segment_name,partition_name ,bytes/1024/1024 from user_segments 
     where segment_name like '%OLD';   

SEGMENT_NA PARTITION_ BYTES/1024/1024
---------- ---------- ---------------
GLOBAL_OLD			   96
LOCAL_OLD  Q4			   22
LOCAL_OLD  Q3			   22
LOCAL_OLD  Q2			   22
LOCAL_OLD  Q1			   22

Without the New Feature, every part of the table is being indexed like shown on the below picture:

Ordinary Indexes on a partitioned table

Ordinary Indexes on a partitioned table

Say partitions Q1, Q2 and Q3 are not popular any more, only Q4 is accessed frequently. In 12c I can do this:

SQL> alter table sales_range modify partition q1 indexing off;

Table altered.

SQL> alter table sales_range modify partition q2 indexing off;

Table altered.

SQL> alter table sales_range modify partition q3 indexing off;

Table altered.

This alone doesn’t affect indexes, though. They must be created with the new INDEXING PARTIAL clause now:

SQL> drop index local_old;

Index dropped.

SQL> drop index global_old;

Index dropped.

SQL> create index local_new on sales_range(time_id) indexing partial local nologging;

Index created.

SQL> create index global_new on sales_range(name) global indexing partial nologging;

Index created.

You may notice that these commands execute much faster now because less I/O needs to be done. And there is way less space consumed:

SQL> select segment_name,partition_name ,bytes/1024/1024 from user_segments
     where segment_name like '%NEW';
SEGMENT_NA PARTITION_ BYTES/1024/1024
---------- ---------- ---------------
LOCAL_NEW Q4 22
GLOBAL_NEW 24

That’s because the indexes look like this now:

Partial Indexes

Partial Indexes

Instead of dropping the old index you can also change it into using the New Feature:

SQL> select segment_name,partition_name ,bytes/1024/1024 from user_segments 
     where segment_name like '%OLD%';

SEGMENT_NA PARTITION_ BYTES/1024/1024
---------- ---------- ---------------
GLOBAL_OLD			   96
LOCAL_OLD  Q4			   22
LOCAL_OLD  Q3			   22
LOCAL_OLD  Q2			   22
LOCAL_OLD  Q1			   22

SQL> alter index LOCAL_OLD indexing partial;

Index altered.

For a LOCAL index, that frees the space from the unpopular partitions immediately:

SQL> select segment_name,partition_name ,bytes/1024/1024 from user_segments 
     where segment_name like '%OLD%';

SEGMENT_NA PARTITION_ BYTES/1024/1024
---------- ---------- ---------------
GLOBAL_OLD			   96
LOCAL_OLD  Q4			   22

That is different with a GLOBAL index:

SQL> alter index GLOBAL_OLD indexing partial;

Index altered.

SQL> select segment_name,partition_name ,bytes/1024/1024 from user_segments 
     where segment_name like '%OLD%';

SEGMENT_NA PARTITION_ BYTES/1024/1024
---------- ---------- ---------------
GLOBAL_OLD			   96
LOCAL_OLD  Q4			   22

Still uses as much space as before, but now this releases space from unpopular parts of the index:

SQL> alter index global_old rebuild indexing partial;

Index altered.

SQL> select segment_name,partition_name ,bytes/1024/1024 from user_segments 
     where segment_name like '%OLD%';

SEGMENT_NA PARTITION_ BYTES/1024/1024
---------- ---------- ---------------
LOCAL_OLD  Q4			   22
GLOBAL_OLD			   24

Cool 12c New Feature, isn’t it?🙂

, ,

3 Comments

How to speed up Data Pump Import by suppressing redo generation in #Oracle 12c

fast

With your database in archive log mode, a Data Pump Import may be severely slowed down by the writing of much redo into online logs and the the generation of many archive logs. A 12c New Feature enables you to avoid that slow down by suppressing redo generation for the import only. You can keep the database in archive log mode the whole time. Let’s see that in action!

First without the new feature:

[oracle@uhesse ~]$ sqlplus adam/adam

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 25 20:25:25 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Oct 25 2016 20:24:55 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select log_mode from v$database;

LOG_MODE
------------------------------------
ARCHIVELOG

SQL> select * from v$recovery_area_usage;

FILE_TYPE		       PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES	CON_ID
------------------------------ ------------------ ------------------------- --------------- ----------
CONTROL FILE					0			  0		  0	     0
REDO LOG					0			  0		  0	     0
ARCHIVED LOG				    15.37			 15		 10	     0
BACKUP PIECE				    25.17			  0		  2	     0
IMAGE COPY					0			  0		  0	     0
FLASHBACK LOG					0			  0		  0	     0
FOREIGN ARCHIVED LOG				0			  0		  0	     0
AUXILIARY DATAFILE COPY 			0			  0		  0	     0

8 rows selected.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@uhesse ~]$ impdp adam/adam tables=sales directory=DPDIR

Import: Release 12.1.0.2.0 - Production on Tue Oct 25 20:26:45 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "ADAM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ADAM"."SYS_IMPORT_TABLE_01":  adam/******** tables=sales directory=DPDIR 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ADAM"."SALES"                              510.9 MB 10000000 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "ADAM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Oct 25 20:29:46 2016 elapsed 0 00:03:00
[oracle@uhesse ~]$ sqlplus adam/adam

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 25 20:30:03 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Oct 25 2016 20:26:45 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from v$recovery_area_usage;

FILE_TYPE		       PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES	CON_ID
------------------------------ ------------------ ------------------------- --------------- ----------
CONTROL FILE					0			  0		  0	     0
REDO LOG					0			  0		  0	     0
ARCHIVED LOG				    30.63			 15		 17	     0
BACKUP PIECE				    25.17			  0		  2	     0
IMAGE COPY					0			  0		  0	     0
FLASHBACK LOG					0			  0		  0	     0
FOREIGN ARCHIVED LOG				0			  0		  0	     0
AUXILIARY DATAFILE COPY 			0			  0		  0	     0

8 rows selected.

The import took 3 minutes and generated 7 archive logs. Now with the new feature:

SQL> drop table sales purge;

Table dropped.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@uhesse ~]$ impdp adam/adam tables=adam.sales directory=DPDIR transform=disable_archive_logging:Y

Import: Release 12.1.0.2.0 - Production on Tue Oct 25 20:31:20 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "ADAM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ADAM"."SYS_IMPORT_TABLE_01":  adam/******** tables=adam.sales directory=DPDIR transform=disable_archive_logging:Y 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ADAM"."SALES"                              510.9 MB 10000000 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "ADAM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Oct 25 20:32:15 2016 elapsed 0 00:00:54

[oracle@uhesse ~]$ sqlplus adam/adam

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 25 20:32:25 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Oct 25 2016 20:31:20 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from v$recovery_area_usage;

FILE_TYPE		       PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES	CON_ID
------------------------------ ------------------ ------------------------- --------------- ----------
CONTROL FILE					0			  0		  0	     0
REDO LOG					0			  0		  0	     0
ARCHIVED LOG				    30.63			 15		 17	     0
BACKUP PIECE				    25.17			  0		  2	     0
IMAGE COPY					0			  0		  0	     0
FLASHBACK LOG					0			  0		  0	     0
FOREIGN ARCHIVED LOG				0			  0		  0	     0
AUXILIARY DATAFILE COPY 			0			  0		  0	     0

8 rows selected.

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

LOGGING
---------
YES

About three times faster and no archive log generated! The table is still in logging mode after the import. Keep in mind to take a backup of the datafile that contains the table now, though! For the same reason you should take a backup after a NOLOGGING operation🙂

, ,

5 Comments

How to safeguard against malicious developers in #Oracle 12c

safe

AUTHID_CURRENT_USER can be misused by developers. The new object privilege INHERIT PRIVILEGES ON was introduced in 12c to prevent that. I start with a demo to explain the problem that the new features solves. The playground:

[oracle@uhesse ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 7 10:36:28 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> grant dba to adam identified by adam; 

Grant succeeded.

SQL> grant create session,create procedure to devel identified by devel;

Grant succeeded.

SQL> connect adam/adam
Connected.

SQL> create table important_table as select * from dual;

Table created.

SQL> connect devel/devel
Connected.
SQL> drop table adam.important_table;
drop table adam.important_table
                *
ERROR at line 1:
ORA-00942: table or view does not exist

DEVEL has no privileges that allow him to do much harm, apparently. But he may trick ADAM into doing that for him:

SQL> create or replace procedure trustmeplease
     authid current_user
     as
     begin
      execute immediate 'drop table adam.important_table';
     end;
     /

SQL> Procedure created.

SQL> exec trustmeplease
BEGIN trustmeplease; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "DEVEL.TRUSTMEPLEASE", line 5
ORA-06512: at line 1

Without AUTHID_CURRENT_USER, the procedure runs with creators rights. With it, it runs with callers rights. No difference if DEVEL calls it. But maybe he can convince ADAM to call it:

SQL> grant execute on trustmeplease to adam;

Grant succeeded.

SQL> connect adam/adam
Connected.
SQL> exec devel.trustmeplease

PL/SQL procedure successfully completed.

SQL> select * from important_table;
select * from important_table
              *
ERROR at line 1:
ORA-00942: table or view does not exist

ADAM got tricked into dropping that table! Assume that the DROP TABLE was hidden within some apparently useful code. So this is the problem why INHERIT PRIVILEGES was introduced. As a default, the behavior of the 12c Database is the same regarding AUTHID_CURRENT_USER as in previous releases. But there is a change under the covers. Whenever a new user gets created, we grant INHERIT PRIVILEGES to PUBLIC:

SQL> col privilege for a20
SQL> select grantee,privilege,grantor from user_tab_privs_made;

GRANTEE    PRIVILEGE		GRANTOR
---------- -------------------- ----------
PUBLIC	   INHERIT PRIVILEGES	ADAM

You saw me creating that user and I did not grant anything about ADAM myself. In order to safeguard, this privilege needs to be revoked from public:

SQL> revoke inherit privileges on user adam from public;

Revoke succeeded.

SQL> flashback table important_table to before drop;

Flashback complete.


SQL> exec devel.trustmeplease
BEGIN devel.trustmeplease; END;

*
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "DEVEL.TRUSTMEPLEASE", line 1
ORA-06512: at line 1

The error means that the procedure I call wants to exercise one of my privileges that the creator of the procedure lacks. After an investigation of that procedure, I may grant that:

SQL> grant inherit privileges on user adam to devel;

Grant succeeded.

SQL> exec devel.trustmeplease

PL/SQL procedure successfully completed.

SQL> flashback table important_table to before drop;

Flashback complete.

It is always a good idea to be aware of privileges that have been granted to PUBLIC. In this case, we keep up the old behavior towards AUTHID_CURRENT_USER that way. Because developers are not necessarily malicious and procedures would otherwise fail with errors that did run successful in the past.

My advice is to revoke INHERIT PRIVILEGES on all users from PUBLIC on a test system and then check thoroughly the procedures that fail now with ORA-06598. Afterwards – if the check turns out okay – grant it on the caller to the developer of these procedures and not to PUBLIC. Because, you know:

Don’t believe it, test it!🙂

,

Leave a comment

%d bloggers like this: