Archive for category TOI

Multiple invisible indexes on the same column in #Oracle 12c

After invisible indexes got introduced in 11g, they have now been enhanced in 12c: You can have multiple indexes on the same set of columns with that feature. Why would you want to use that? Actually, this is always the first question I ask when I see a new feature – sometimes it’s really hard to answer :-)

Here, a plausible use case could be that you expect a new index on the same column to be an improvement over the existing old index, but you are not 100% sure. So instead of just dropping the old index, you make it invisible first to see the outcome:

 

[oracle@uhesse ~]$ sqlplus adam/adam

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 28 08:11:16 2015

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

Last Successful login time: Tue Jul 28 2015 08:00:34 +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> col index_name for a10
SQL> select index_name,index_type,visibility from user_indexes;

INDEX_NAME INDEX_TYPE		       VISIBILIT
---------- --------------------------- ---------
BSTAR	   NORMAL		       VISIBLE

SQL> col segment_name for a10
SQL> select segment_name,bytes/1024/1024 from user_segments;

SEGMENT_NA BYTES/1024/1024
---------- ---------------
BSTAR		       160
SALES		       600

SQL> set timing on
SQL> select count(*) from sales where channel_id=3;

  COUNT(*)
----------
   2000000

Elapsed: 00:00:00.18
SQL> set timing off
SQL> @lastplan

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID	b7cvb9nu10qdb, child number 0
-------------------------------------
select count(*) from sales where channel_id=3

Plan hash value: 2525234362

---------------------------------------------------------------------------
| Id  | Operation	  | Name  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	  |	  |	  |  3872 (100)|	  |
|   1 |  SORT AGGREGATE   |	  |	1 |	3 |	       |	  |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN| BSTAR |  2000K|  5859K|  3872   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CHANNEL_ID"=3)


19 rows selected.

So I have an ordinary B* index here that supports my query, but I suspect that it would work better with a bitmap index. In older versions, you would get this if you try to create it with the old index still existing:

SQL> create bitmap index bmap on sales(channel_id) nologging;
create bitmap index bmap on sales(channel_id) nologging
                                  *
ERROR at line 1:
ORA-01408: such column list already indexed

Enter the 12c New Feature:

SQL> alter index bstar invisible;

Index altered.

SQL> create bitmap index bmap on sales(channel_id) nologging;

Index created.

Now I can check if the new index is really an improvement while the old index remains in place and is still being maintained by the system. So in case the new index turns out to be a bad idea – no problem to fall back on the old one!

SQL> select index_name,index_type,visibility from user_indexes;

INDEX_NAME INDEX_TYPE		       VISIBILIT
---------- --------------------------- ---------
BMAP	   BITMAP		       VISIBLE
BSTAR	   NORMAL		       INVISIBLE

SQL> select segment_name,bytes/1024/1024 from user_segments;

SEGMENT_NA BYTES/1024/1024
---------- ---------------
BMAP			 9
BSTAR		       160
SALES		       600

SQL> set timing on
SQL> select count(*) from sales where channel_id=3;

  COUNT(*)
----------
   2000000

Elapsed: 00:00:00.01
SQL> @lastplan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID	b7cvb9nu10qdb, child number 0
------------------------------------------------------------------------------------
select count(*) from sales where channel_id=3

Plan hash value: 3722975061

------------------------------------------------------------------------------------
| Id  | Operation		    | Name | Rows  | Bytes | Cost (%CPU)| Time	   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	   |	   |	   |   216 (100)|	   |
|   1 |  SORT AGGREGATE 	    |	   |	 1 |	 3 |		|	   |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
|   2 |   BITMAP CONVERSION COUNT   |	   |  2000K|  5859K|   216   (0)| 00:00:01 |
|*  3 |    BITMAP INDEX SINGLE VALUE| BMAP |	   |	   |		|	   |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CHANNEL_ID"=3)


20 rows selected.

Looks like everything is better with the new index, right? Let’s see what the optimizer thinks about it:

SQL> alter index bmap invisible;

Index altered.

SQL> select index_name,index_type,visibility from user_indexes;

INDEX_NAME INDEX_TYPE		       VISIBILIT
---------- --------------------------- ---------
BMAP	   BITMAP		       INVISIBLE
BSTAR	   NORMAL		       INVISIBLE

SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> select count(*) from sales where channel_id=3;

  COUNT(*)
----------
   2000000

SQL> @lastplan

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID	b7cvb9nu10qdb, child number 0
-------------------------------------------------------------------------------------
select count(*) from sales where channel_id=3

Plan hash value: 3722975061

------------------------------------------------------------------------------------
| Id  | Operation		    | Name | Rows  | Bytes | Cost (%CPU)| Time	   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	   |	   |	   |   216 (100)|	   |
|   1 |  SORT AGGREGATE 	    |	   |	 1 |	 3 |		|	   |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
|   2 |   BITMAP CONVERSION COUNT   |	   |  2000K|  5859K|   216   (0)| 00:00:01 |
|*  3 |    BITMAP INDEX SINGLE VALUE| BMAP |	   |	   |		|	   |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CHANNEL_ID"=3)


20 rows selected.

The optimizer agrees that the new index is better. I could keep both indexes here in place, but remember that the old index still consumes space and requires internal maintenance. Therefore, I decide to drop the old index:

SQL> drop index bstar;

Index dropped.

SQL> alter index bmap visible;

Index altered.

Hope that helped to answer the question why you would want to use that 12c New Feature. As always: Don’t believe it, test it! :-)

,

Leave a comment

AUDIT_SYS_OPERATIONS defaults to TRUE in #Oracle 12c

A small but remarkable change in Oracle Database 12c is the default value of AUDIT_SYS_OPERATIONS has changed to TRUE now. In other words, all actions done by the superuser sys are being audited now by default!

[oracle@uhesse ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 24 15:23:10 2015

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> select name,value from v$spparameter where isspecified='TRUE';

NAME                                     VALUE
---------------------------------------- --------------------------------------------------
memory_target                            1073741824
control_files                            /u01/app/oracle/oradata/prima/control01.ctl
db_block_size                            8192
compatible                               12.1.0.2
db_recovery_file_dest                    /u02/fra
db_recovery_file_dest_size               2147483648
undo_management                          auto
undo_tablespace                          undotbs1
remote_login_passwordfile                exclusive
db_name                                  prima
diagnostic_dest                          /u01/app/oracle

11 rows selected.


SQL> show parameter sys_oper

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     TRUE
SQL> select count(*) from scott.dept;

  COUNT(*)
----------
         4

SQL> show parameter audit_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/product/12.1.0
                                                 /dbhome_1/rdbms/audit
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 ~]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit

[oracle@uhesse audit]$ cat prima_ora_6204_20150724152310753136143795.aud
Audit file /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit/prima_ora_6204_20150724152310753136143795.aud
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_HOME = /u01/app/oracle/product/12.1.0/dbhome_1
System name:    Linux
Node name:      uhesse
Release:        3.8.13-68.2.2.el7uek.x86_64
Version:        #2 SMP Tue May 12 14:38:58 PDT 2015
Machine:        x86_64
Instance name: prima
Redo thread mounted by this instance: 1
Oracle process number: 41
Unix process pid: 6204, image: oracle@uhesse (TNS V1-V3)

Fri Jul 24 15:23:10 2015 +02:00
LENGTH : '160'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[10] '2113606181'
[Output shortened...]
Fri Jul 24 15:23:56 2015 +02:00
LENGTH : '185'
ACTION :[31] 'select count(*) from scott.dept'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[10] '2113606181'

Something you might need to know as a DBA, don’t you think? :-)

,

2 Comments

Become an #Oracle Certified Expert for Data Guard!

It is with great pride that I can announce a new certification being available – Oracle Database 12c: Data Guard Administration.

We wanted this for years and finally got it now, after having put much effort and expertise into the development of the exam. It is presently in beta and offered with a discount. Come and get it!

,

1 Comment

Follow

Get every new post delivered to your Inbox.

Join 3,454 other followers

%d bloggers like this: