Granule size

SGA memory is allocated in unit of contiguous memory chunks called granule. The SGA memory components(shared pool,buffer cache, redo log buffer,java pool,streams pool,large pool) are sized as multiples of granules.

The granule size is determined based on the amount of memory requested at the instance startup. It is based on the SGA_MAX_SIZE. If MEMORY_MAX_TARGET is specified, then SGA_MAX_SIZE defaults to MEMORY_MAX_TARGET for the purpose of sizing the granule. Once set, the granule size does not change for the life of the instance.
However granule maximum size = 16MB on 32-bit platforms.

SQL> show parameter memory_max_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 800M

SQL> select bytes/1024/1024 bytes_Mb from v$sgainfo where name like 'Granule Size';

BYTES_MB
----------
4

RDBMS	SGA_MAX_SIZE (or memory_max_target)	GRANULE SIZE
9.2	<= 128MB	4MB
	> 128MB	16MB
10.2	<= 1GB	4MB
	> 1GB	16MB
11gR1	<= 1GB	4MB
	1Gb - 4GB	16MB
	4Gb - 16GB	64MB
	16Gb - 64GB	256MB
	> 64GB	512MB
11gR2 (and 11gR1 with patch 8813366 applied *)	< 1Gb	4Mb
	1Gb - 8Gb	16Mb
	8Gb - 16Gb	32Mb
	16Gb - 32Gb	64Mb
	32Gb - 64Gb	128Mb
	64Gb - 128Gb	256Mb
	> 128Gb	512Mb

How to test from above table. If I need granule size 16Mb, I have to Memory 1Gb - 4GB for SGA???

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> !free -m
total used free shared buffers cached
Mem: 888 849 38 0 2 740

I have only 900Mb for Physical Memory. Find Idea to test then created /tmp/pfile file for modified MEMORY_MAX_TARGET parameter.

SQL> create pfile='/tmp/pfile' from spfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

then faked /dev/shm size (this's just test for granule size to fake memory_max_target parameter)

Filesystem Size Used Avail Use% Mounted on
tmpfs 4.0G 0 4.0G 0% /dev/shm

then modified /tmp/pfile (MEMORY_MAX_TARGET=4G) - If MEMORY_MAX_TARGET is specified, then SGA_MAX_SIZE defaults to MEMORY_MAX_TARGET.

SQL> startup pfile='/tmp/pfile';
ORACLE instance started.

Total System Global Area 2058981376 bytes
Fixed Size 1300968 bytes
Variable Size 1644168728 bytes
Database Buffers 402653184 bytes
Redo Buffers 10858496 bytes
Database mounted.
Database opened.

SQL> show parameter memory_max_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 4G

SQL> select bytes/1024/1024 bytes_Mb from v$sgainfo where name like 'Granule Size';

BYTES_MB
----------
16

Used granule size 16M.
If we set a value in the spfile that is not a multiple of the granule size, the actual size allocated will be rounded up to the nearest granule.

SQL> select bytes/1024/1024 bytes_Mb from v$sgainfo where name like 'Granule Size';

BYTES_MB
----------
4

SQL> alter system set java_pool_size=4M;

System altered.

SQL> show parameter java_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 4M

SQL> alter system set MEMORY_MAX_TARGET=4G scope=spfile;

System altered.

SQL> shutdown

SQL> startup

SQL> select bytes/1024/1024 bytes_Mb from v$sgainfo where name like 'Granule Size';

BYTES_MB
----------
16

SQL> show parameter java_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 16M

The java_pool_size 4M in the spfile, after increased granule size, then the actual allocation for the java_pool_size will be rounded up to 16M.

Flashback Transaction Backout

The dbms_flashback.transaction_backout procedure uses logminer data to backout transactions, so your data in that transaction was rolled back.
Requirements:
1. Database must be in Archive Mode

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 48
Next log sequence to archive 50
Current log sequence 50

2. Enable supplemental logging at Database level

SQL> alter database add supplemental log data;

Database altered.

Example:

SQL> delete from tb_test where object_id=11112;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from tb_test where object_id=11112;

COUNT(*)
----------
0

SQL> select versions_xid , object_id from tb_test versions between scn minvalue and maxvalue where object_id=11112;

VERSIONS_XID OBJECT_ID
---------------- ----------
08001600CC040000 11112

SQL> declare
v_xid sys.xid_array;
begin
v_xid := sys.xid_array('08001600CC040000');
dbms_flashback.transaction_backout(numtxns=>1,
xids=>v_xid, options=>dbms_flashback.cascade);
end;
/
declare
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 5

Problem:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

Solve:
grant create any table to user_name;

SQL> declare
v_xid sys.xid_array;
begin
v_xid := sys.xid_array('08001600CC040000');
dbms_flashback.transaction_backout(numtxns=>1,
xids=>v_xid, options=>dbms_flashback.cascade);
end;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from tb_test where object_id=11112;

COUNT(*)
----------
1

Now... data was rolled back. We can review on...
- [DBA, USER]_FLASHBACK_TXN_STATE
- [DBA, USER]_FLASHBACK_TXN_REPORT

SQL> SELECT * FROM USER_FLASHBACK_TXN_STATE;

COMPENSATING_XID XID DEPENDENT_XID BACKOUT_MODE
---------------- ---------------- ---------------- ----------------
09000B00D6040000 08001600CC040000 CASCADE

SQL> SELECT * FROM USER_FLASHBACK_TXN_REPORT;

read more

ORA-03001: unimplemented feature

Just something I learned with oracle error. Thank you for Coskan's comment on my post. then tested to create index with numeric.

SQL> create index a_indx1 on a (x,y,1);

Index created.

SQL> exec dbms_stats.gather_index_stats(USER,'A_INDX1');
BEGIN dbms_stats.gather_index_stats(USER,'A_INDX1'); END;

*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 14037
ORA-06512: at "SYS.DBMS_STATS", line 14060
ORA-06512: at line 1

then...

SQL> drop index A_INDX1;

Index dropped.

SQL> create index a_indx1 on a (x,y,'1');

Index created.

SQL> exec dbms_stats.gather_index_stats(USER,'A_INDX1');

PL/SQL procedure successfully completed.

Time to check more error on Oracle. they told this bug(5767661) and Idea.

Replace the numeric literal with character literal to recreate the index.

and then check with my old post.

SQL> explain plan for select * from a order by x,y;

Explained.

SQL> select * from TABLE(dbms_xplan.display(NULL, NULL , 'ALL'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2241847120
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43 | 3311 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | A_INDX1 | 43 | 3311 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

ORDER BY to use Index

It's difficult thing, If we try to learn something every days, every days. However It's a good thing, If we love and enjoy to learn.

So I planed to learn something (new) every days. I hope so.
I learned... (It's not new... just something we might forgot).

If a user requires the optimizer to consider using an index to drive an ORDER BY statement instead of sorting, then the indexed columns must be NOT NULL as otherwise they are not considered.

SQL> create table a (x VARCHAR2(128), y VARCHAR2(19));

Table created.

SQL> desc A
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
X VARCHAR2(128)
Y VARCHAR2(19)

SQL> insert into a select OBJECT_NAME, OBJECT_TYPE from user_objects;

43 rows created.

SQL> commit;

Commit complete.

SQL> create index a_indx1 on a (x,y);

Index created.

SQL> exec dbms_stats.gather_index_stats(USER,'A_INDX1');

PL/SQL procedure successfully completed.

SQL> explain plan for select * from a order by x,y;

Explained.

SQL> select * from TABLE(dbms_xplan.display(NULL, NULL , 'ALL'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3819873049
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43 | 3311 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 43 | 3311 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| A | 43 | 3311 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

Indexes Columns be NULL, then change to be Not NULL.

SQL> alter table a modify (x not null, y not null);

Table altered.

SQL> desc A
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
X NOT NULL VARCHAR2(128)
Y NOT NULL VARCHAR2(19)

SQL> explain plan for select * from a order by x,y;

Explained.

SQL> select * from TABLE(dbms_xplan.display(NULL, NULL , 'ALL'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2241847120
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43 | 3311 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | A_INDX1 | 43 | 3311 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

After changed to be Not NULL, the optimizer consider an index.