Thursday, November 24, 2011

Invisible Index 11g, how to utilize it


Invisible index gives you the  ability to create an index on production without impacting the queries being fired by application but at the same time test the impact an index creation can cause. Invisible indexes are useful alternative to making an index unusable or to drop it. 
The optimizer ignores the index that are marked “Invisible” unless you set the initialization parameter “OPTIMIZE_USE_INVISIBLE_INDEXES” to TRUE. This parameter can be set both at a session level as well as system level.

Usage of Invisible Indexes

One can use invisible index for testing the impact of removing an index. Instead of dropping the index we can make it invisible and its effect.

One can speed up operations by creating invisible indexes for infrequent scenarios. Invisible index will make sure that the overall performance of the application is not affected.
Gives you the flexibility to have both b-tree (to guarantee unique PK) as well as bitmap indexes (on FK columns) in a data warehouse application.

How to create?
Multiple options – either mention “INVISIBLE” clause at the time of index creation or use
ALTER command to make an index “INVISIBLE”.
CREATE INDEX FNADVI.idx_FIELD1 ON FNADVI.T1 (FIELD1) NOLOGGING NOPARALLEL INVISIBLE;
OR
ALTER INDEX IDX_FIELD1 INVISIBLE;

To make the Index “VISIBLE”
ALTER INDEX IDX_FIELD1 VISIBLE;

A new column “VISIBILITY” is available in *_INDEXES data dictionary views to know if an index is visible or invisible.

Example:

select index_name,visibility from user_indexes where table_name='T1';

INDEX_NAME       VISIBILITY
IDX_FIELD1         INVISIBLE
T1_PK                 VISIBLE

     
explain plan for select * from t1 where FIELD1='test';
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    26 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    26 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FIELD1"='test')

Optimizer didn’t pickup the INDEX into consideration as it’s invisible.
Let’s make it visible in Session level and see the impact

ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;

explain plan for select * from t1 where FIELD1='test';
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
Plan hash value: 2538775970
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     2 |    26 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1         |     2 |    26 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_FIELD1 |     2 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FIELD1"='test')


crs_stat deprecated in 11gR2


The most used to command "crs_stat -t" is gone (deprecated) on 11gR2. Instead you

crsctl status resource [the output will be status of all resources]

You can still use the "-t" option to get a tabular report though ;)

crsctl status resource -t

Specify resource name "oracle.mydb.vip" to get the status of any particular resource -
         
crsctl status resource oracle.mydb.vip

In fact, quite a few commands have been deprecated in 11gR2; 

Deprecated Command
Replacement Commands       
crs_stat

                      
crsctl check cluster -all                                   
crsctl stat res -t                                                    
                                                                    
crs_register



crsctl add resource                                                 
crsctl add type                                                     
crsctl modify resource                                              
crsctl modify type                                                   
crs_unregister
crsctl stop resource                                                

crsctl modify resource resource_name -attr
"AUTO_START=never"       
crs_start


crsctl start resource                                               
crsctl start crs                                                    
crsctl start cluster                                                
crs_stop


crsctl stop resource                                                
crsctl stop crs                                                     
crsctl stop cluster                                                 
                      
                                                                    
                      
                                                                    
crs_getperm

crsctl getperm resource                                             
crsctl getperm type                                                 
                      
                                                                    
crs_profile





crsctl add resource                                                 
crsctl add type                                                     
crsctl status resource                                              
crsctl status type                                                   
crsctl modify resource                                              
crsctl modify type                                                  
                      
                                                                     
crs_relocate
crsctl relocate resource                                            
crs_setperm

                      
crsctl setperm resource                                              
crsctl setperm type                                                 
                                                                    
crsctl check crsd
crsctl check crs                                                     
crsctl check cssd
crsctl check css                                                    
crsctl check evmd
crsctl check evm                                                    
                      
                                                                     
                                                                    
crsctl debug res  log resource_name:level
crsctl set log                                                      
                      
                                                                     
crsctl set css votedisk
crsctl add css votedisk                                             

crsctl delete css votedisk                                          

crsctl query css votedisk                                           

crsctl replace css votedisk                                         
                      
                                                                    
crsctl start resources
crsctl start resource -all                                          
crsctl stop resources 
crsctl stop resource -all                                           

Friday, November 18, 2011

Remove OS file using oracle pl/sql: dbms_backup_restore/utl_file

1.    Dbms_backup_restore function to delete a file test.txt from OS.
$ ls -ltr
-rw-r--r-- 1 oracle oinstall 0 Nov 18 09:25 test.txt

$ sqlplus / as sysdba
SQL> begin
dbms_backup_restore.deletefile('/home/oracle/BKP/test.txt');
end;
 /
PL/SQL procedure successfully completed.
Now check the file.

$ ls -ltr
total 0
Gone!!!!
  
2.    utl_file.fremove to remove file from Oracle directory. 
CREATE DIRECTORY DIR AS '/oracle/admin';

ls -ltr
-rw-r--r--  1 oracle oinstall    0 Nov 18 09:35 directory_test.txt

exec utl_file.fremove('DIR','directory_test.txt');
PL/SQL procedure successfully completed.
Check the file again…..
$ ls -ltr
total 0

Gone!!!