ASM cant start because of miscommunication between compute and cell nodes
[oracle@exdbadm01 ~]$ oerr ora 56865
56865, 00000, "Invalid IP address in CELLINIT.ORA"
// *Document: YES
// *Cause: One or more of the specified IP addresses in CELLINIT.ORA is not
// valid.
// *Action: Check that all IP addresses in CELLINIT.ORA are valid.
Solution:
Verify the ipaddress in cellinit.ora file on all cell nodes
You try to modify a parameter online without saving the changes to spfile
02096, 00000, "specified initialization parameter is not modifiable with this option"
// *Cause: Though the initialization parameter is modifiable, it cannot be
// modified using the specified command.
// *Action: Check the DBA guide for information about under what scope
// the parameter may be modified
Solution:
There are two types of parameters
one is static and other is dynamic
Dynamic parameters can be modified,when the instance is up and running without a database bounce and can be modified at memory level
Static parameters can be modified,when the instance is up and running but require a reboot for the changes to get reflected on database and it can be modified only at spfile level not in memory
For example,you can see the parameters which can and cant be modified in ISINSTANCE_MODIFIABLE column of v$parameter view
SQL> set lines 200
SQL> set pages 1000
SQL> select name,ISINSTANCE_MODIFIABLE from v$parameter;
NAME ISINS <------
-------------------------------------------------------------------------------- -----
lock_name_space FALSE(static)
processes FALSE(static)
sessions TRUE(dynamic)
If i try to modify the static parameter,i get the error
SQL> show parameter processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 200
SQL> alter system set processes=300;
alter system set processes=300
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified <---------
SQL> alter system set processes=300 scope=spfile; <-------
System altered.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1572861600 bytes
Fixed Size 8910496 bytes
Variable Size 1132462080 bytes
Database Buffers 423624704 bytes
Redo Buffers 7864320 bytes
Database mounted.
Database opened.
In alertlog, you can monitor the number of process gets modified with the value which was set earlier
I can modify the dynamic parameter in fact without a reboot!
SQL> select name,ISINSTANCE_MODIFIABLE from v$parameter where name='optimizer_use_invisible_indexes';
NAME ISINS
-------------------------------------------------------------------------------- -----
optimizer_use_invisible_indexes TRUE <---(Dynamic)
SQL> show parameter optimizer_use_invisible_indexes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean TRUE
SQL> alter system set optimizer_use_invisible_indexes=FALSE;
System altered.
SQL> show parameter optimizer_use_invisible_indexes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE
Appropriate permissions are not in place for the procedure,function or package for user!
SQL> exec dbms_system.set_sql_trace_in_session(sid=>123,serial#=>1234,sql_trace= >TRUE);
BEGIN dbms_system.set_sql_trace_in_session(sid=>123,serial#=>1234,sql_trace=>TRU E); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> exec dbms_system.set_sql_trace_in_session(sid=>123,serial#=>1234,sql_trace=TRUE);
BEGIN dbms_system.set_sql_trace_in_session(sid=>123,serial#=>1234,sql_trace=TRUE); END;
*
ERROR at line 1:
ORA-06550: line 1, column 67:
PLS-00201: identifier 'SQL_TRACE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Solution:
Even though the user has dba privilege,still the user couldnot execute the packages of SYS and you can also create a public synonym for this package
Grant the privilege using sysdba account
SQL> conn / as sysdba
Connected.
SQL> create public synonym dbms_system for dbms_system;
Synonym created.
SQL> grant execute on dbms_system to test;
Grant succeeded.
SQL> grant all on dbms_system to test;
Grant succeeded.
Check the privilege given
SQL> select distinct 'GRANT '||privilege||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE from DBA_TAB_PRIVS where grantee='TEST';
'GRANT'||PRIVILEGE||'ON'||OWNER||'.'||TABLE_NAME||'TO'||GRANTEE
--------------------------------------------------------------------------------
GRANT EXECUTE ON SYS.DBMS_SYSTEM TO TEST
GRANT DEBUG ON SYS.DBMS_SYSTEM TO TEST
It works fine now
SQL> exec dbms_system.set_sql_trace_in_session(45,14530,TRUE);
PL/SQL procedure successfully completed.
SQL> show user
USER is "TEST"
While creating function based index,you should give deterministic keyword for oracle to understand the function you create
You should not give sysdate,user etc.. inside a function
01743, 00000, "only pure functions can be indexed"
// *Cause: The indexed function uses SYSDATE or the user environment.
// *Action: PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS). SQL
// expressions must not use SYSDATE, USER, USERENV(), or anything
// else dependent on the session state. NLS-dependent functions
// are OK.
Solution:
The function you give to functional based index should return the same result for same parameters
eg:
Don’t
SQL> create index fn_order_date on sales(trunc(sysdate));
create index fn_order_date on sales(trunc(sysdate))
*
ERROR at line 1:
ORA-01743: only pure functions can be indexed
Do
SQL> create index fn_order_date on sales(trunc(order_date));
Index created.
The solution for this error by oerr utility is not detailed
However,this issue is due to function based indexes existing on the table and we try to shrink the space in table to reduce fragmentation
SQL> !oerr ora 10631 10631, 00000, “SHRINK clause should not be specified for this object” // *Cause: It is incorrect to issue shrink on the object // *Action: Verify the object name and type and reissue the command
SQL> alter table test1.sales shrink space;
alter table test1.sales shrink space
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object
Solution:
Only way to overcome this error is to drop the index and recreate it
Identify the function index and drop it
SQL> select index_name from dba_indexes where owner='TEST1' and table_name='SALES';
INDEX_NAME
--------------------------------------------------------------------------------
PK_SALES_ID
FN_ORDER_DATE
SQL> set long 1000000
SQL> select dbms_metadata.get_ddl('INDEX','FN_ORDER_DATE') from dual;
DBMS_METADATA.GET_DDL('INDEX','FN_ORDER_DATE')
--------------------------------------------------------------------------------
CREATE INDEX "TEST1"."FN_ORDER_DATE" ON "TEST1"."SALES" (TRUNC("ORDER_DATE"))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
Drop the index
SQL> alter index test1.FN_ORDER_DATE invisible;
Index altered.
SQL> alter table test1.sales shrink space;
alter table test1.sales shrink space
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object
SQL> alter index test1.FN_ORDER_DATE unusable;
Index altered.
SQL> alter table test1.sales shrink space;
alter table test1.sales shrink space
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object
SQL> drop index test1.FN_ORDER_DATE;
Index dropped.
SQL> alter table test1.sales shrink space;
Table altered.
After space shrink,rebuild the index
SQL> create index fn_order_date on sales(trunc(order_date));
Index created.
Make sure you give the right parameter in the advisor procedure
SQL> BEGIN
2 dbms_advisor.set_task_parameter(
3 task_name => 'Advisor',
4 parameter => 'RECOMMEND ALL', <----- _underscore missed
5 value => 'TRUE');
6 END;
7 /
BEGIN
*
ERROR at line 1:
ORA-13602: The specified parameter RECOMMEND ALL is not valid for task or
object Advisor.
ORA-06512: at "SYS.PRVT_ADVISOR", line 5010
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.PRVT_ADVISOR", line 1248
ORA-06512: at "SYS.PRVT_ADVISOR", line 4902
ORA-06512: at "SYS.DBMS_ADVISOR", line 401
ORA-06512: at line 2
Solution:
Check if you give correct parameter in set task parameter
This parameter stores various attributes that affect workload collection, tuning decisions and reporting.
While dropping an index,all the constraints cascaded to that index should be either disabled or dropped
SQL> !oerr ora 02429
02429, 00000, "cannot drop index used for enforcement of unique/primary key"
// *Cause: user attempted to drop an index that is being used as the
// enforcement mechanism for unique or primary key.
// *Action: drop the constraint instead of the index.
Solution:
Check the constraints related to that index
SQL> select constraint_name from dba_constraints where table_name in ('T','S');
CONSTRAINT_NAME
--------------------------------------------------------------------------------
PK_T
FK_T
PK_S
Primary key and foreign key for the table is disabled and respective index is wiped!
SQL> alter table t disable constraint FK_T;
Table altered.
SQL> alter table s disable constraint PK_S;
Table altered.
In this article,we will look at the difference,advantages and disadvantages of using btree(default) and bitmap index
Btree index has a balanced tree like structure with root,branch and leaf nodes.It is the most commonly used index for performance improvement.The index itself store the rowid and column values in a table.If we want to unleash the full potential of btree index,then all the table rowid and column values which needs to be fetched should be stored in index blocks.There are row pointers which points to the table directly from the leaf blocks in index.Basic btree structure looks something like below figure
BTREE STRUCTURE
Like btree index,bitmap index also has similar structure but stores the column values in the form of binary values with combination of 0 and 1.We can say dummy variables created for the low distinct values making an intersection point. The index key points to multiple rows in a table. The structure of bitmap looks similar to below figure.
The figure shows that we try to select a shirt detail from shirt table where the shirt size is medium and i want shirt colour which should be both red and orange. Here columns red and orange are unique columns in the bitmap index
BITMAP STRUCTURE
CARDINALITY :
Btree – high cardinality (high distinct values)
SQL> select count(distinct order_id) as HIGH_CARDINALITY_COLUMN from sales;
HIGH_CARDINALITY_COLUMN
-----------------------
1048576
SQL> select count(*) from sales;
COUNT(*)
----------
1048576
Bitmap – low cardinality (less number of distinct values)
SQL> select count(distinct order_priority) as LOW_CARDINALITY_COLUMN from sales;
LOW_CARDINALITY_COLUMN
----------------------
4
SQL> select count(*) from sales;
COUNT(*)
----------
1048576
SIZE:
Btree – Consume more space
SQL> select segment_name,bytes/1024/1024 as SIZE_MB from dba_segments where segment_name in ('BTREE_SALES_IT','BTREE_SALES_PRIORI');
SEGMENT_NAME SIZE_MB
---------------------------------------- ----------
BTREE_SALES_IT 25 >---- 25MB
BTREE_SALES_PRIORI 16 >---- 16MB
Bitmap – Consume less space compared to Btree provided there should be less distinct values and if high distinct values are present,then consume more space
SQL> column segment_name format a40
SQL> select segment_name,bytes/1024/1024 as SIZE_MB from dba_segments where segment_name in ('BTM_SALES_IT','BTM_SALES_PRIORI');
SEGMENT_NAME SIZE_MB
---------------------------------------- ----------
BTM_SALES_PRIORI .8125 <----- 0.8MB
BTM_SALES_IT 2 <----- 2MB
INDEX USAGE ON NULL VALUES :
Btree – Null values with indexed columns are not stored in Btree
Oracle ignores the index and goes for full table scan when there is a btree indexed null column
create index index_name on table_name(column_name);
SQL> create index btree_SALES_PRIORI on sales(ORDER_PRIORITY);
Index created.
Elapsed: 00:00:03.23 <------- Observe time taken to create btree index on less distinct column
SQL> create index btree_SALES_IT on sales(ITEM_TYPE);
Index created.
Elapsed: 00:00:05.75 <------- Highly time consuming when btree index created on less distinct columns!! :(
Bitmap:
create bitmap index index_name on table_name(column_name) nologging;
SQL> create bitmap index btm_sales_priori on sales(order_priority) nologging;
Index created.
Elapsed: 00:00:00.71 <---- less time taken to create bitmap in less distinct column :)
SQL> create bitmap index btm_sales_it on sales(item_type) nologging;
Index created.
Elapsed: 00:00:00.49 <--- less time taken to create bitmap in less distinct column :)
PURPOSE :
Btree – used for OLTP specifically for heavy DML transactions like INSERT,UPDATE and DELETE
Bitmap – used for OLAP specifically for STAR SCHEMAS with fact and dimension tables with frequent data retrieval like select queries with AND and OR operator in the where clause
DRAWBACKS:
Btree – Foreign key columns should be indexed which is referenced to a primary key column if not it cause locking issues
You will encounter below wait event with Row Exclusive Table Lock on parent table when there is an unindexed foreign key on a child table ,if the user forget to commit the data in case of btree index
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
enq: TM - contention 1 6.75 6.75
Bitmap – DML operations in bitmap indexed columns cause locking issues
So it is better to make bitmap index invisible or unusable when there is a DML load
creating bitmap index on high cardinality columns will
impact performance negatively
increase index size
index rebuild operation is time consuming
SUPPORTED EDITION:
Btree – default index supported in all editions
Bitmap – Supported in enterprise edition
EXECUTION PLAN:
Btree index on high distinct column: <—- See the time taken to create index ,number of physical IO,cost and size when btree created on high distinct column
SQL> create index btree_sales_id on sales(order_id);
Index created.
Elapsed: 00:00:02.98 <----
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='BTREE_SALES_ID';
SEGMENT_NAME BYTES/1024/1024
---------------------------------------- ---------------
BTREE_SALES_ID 19 <---
SQL> set timing on
SQL> set autot traceonly
SQL> select /*+index(btree_sales_id)*/ country,units_sold,unit_price,unit_cost,total_profit from sales where order_id between 123 and 1234;
1112 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 1484275115
--------------------------------------------------------------------------------
----------------------
| Id | Operation | Name | Rows | Bytes | C
ost (%CPU)| Time |
--------------------------------------------------------------------------------
----------------------
| 0 | SELECT STATEMENT | | 1113 | 48972 |
36 (3)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 1113 | 48972 |
36 (3)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | BTREE_SALES_ID | 1113 | |
5 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
179 consistent gets
24 physical reads <----
0 redo size
46477 bytes sent via SQL*Net to client
1309 bytes received via SQL*Net from client
77 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1112 rows processed
Bitmapon high distinct column:<—- See the time taken to create,number of physical IO,cost and size when bitmap created on high distinct column
SQL> create bitmap index btm_sales_id on sales(order_id) nologging;
Index created.
Elapsed: 00:00:09.89 <------
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='BTM_SALES_ID';
SEGMENT_NAME BYTES/1024/1024
---------------------------------------- ---------------
BTM_SALES_ID 30 <-----
SQL> select /*+btm_sales_id*/ country,units_sold,unit_price,unit_cost,total_profit from sales where order_id between 123 and 1234;
1112 rows selected.
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
Plan hash value: 1651313649
--------------------------------------------------------------------------------
--------------------
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time |
--------------------------------------------------------------------------------
--------------------
| 0 | SELECT STATEMENT | | 1113 | 48972 | 2
54 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 1113 | 48972 | 2
54 (1)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | |
| |
|* 3 | BITMAP INDEX RANGE SCAN | BTM_SALES_ID | | |
| |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
239 recursive calls
0 db block gets
559 consistent gets
87 physical reads <----
0 redo size
46278 bytes sent via SQL*Net to client
1300 bytes received via SQL*Net from client
76 SQL*Net roundtrips to/from client
52 sorts (memory)
0 sorts (disk)
1112 rows processed
Btree:
Use btree index most of the time 99% unless there are specific requirement to use other indexes
Below is an example of highly distinct column
SQL> select count(order_id) from sales group by order_id having count(order_id) = 1 fetch next 10 rows only;
COUNT(ORDER_ID)
---------------
1
1
1
1
1
1
1
1
1
1
10 rows selected.
Bitmap:
Use bitmap index when a column has 1% distinct values from overall column
SQL> select count(order_priority) as ORDER_PRIORITY from sales group by order_priority having count(*) > 1;
ORDER_PRIORITY
--------------
261964
262329
262160
262123
Btree index on a low distinct column:<—- See number of physical IO and cost when btree created on low distinct column
SQL> select /*+index(sales btree_SALES_PRIORI) index(sales btree_SALES_IT)*/ country,ITEM_TYPE,SALES_CHANNEL,ORDER_PRIORITY from sales where item_type='Vegetables' and order_priority='L';
21769 rows selected.
Elapsed: 00:00:00.56
Execution Plan
----------------------------------------------------------
Plan hash value: 329813273
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21795 | 659K| 12458 (2)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 21795 | 659K| 12458 (2)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | BTREE_SALES_PRIORI | 262K| | 519 (8)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
15143 consistent gets
12305 physical reads <-----
0 redo size
705620 bytes sent via SQL*Net to client
16504 bytes received via SQL*Net from client
1453 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21769 rows processed
Bitmap index on a low distinct column:<—- See effect of number of physical IO and cost when bitmap created on low distinct column
Creating bitmap indexes in multiple columns would enhance the data retrieval with lightning performance!
SQL> select country,ITEM_TYPE,SALES_CHANNEL,ORDER_PRIORITY from sales where item_type='Vegetables' and order_priority='L';
21769 rows selected.
Elapsed: 00:00:00.34
Execution Plan
----------------------------------------------------------
Plan hash value: 948039403
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21845 | 661K| 2743 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 21845 | 661K| 2743 (1)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | BTM_SALES_IT | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE | BTM_SALES_PRIORI | | | | |
------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
35 recursive calls
5 db block gets
12123 consistent gets
10022 physical reads <-----
956 redo size
705400 bytes sent via SQL*Net to client
16439 bytes received via SQL*Net from client
1453 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
21769 rows processed
From this post, it has been practically observed that both btree and bitmap index are very efficient in their own way and solve the purpose! we also learnt the basic differences between both indexes and where to effectively use them
