Profiling the new SIMPLE_INTEGER type

Oracle 11g comes with a new PLSQL type called SIMPLE_INTEGER. The official documentation says that this type yield significant performance compared to PLS_INTEGER type. Because I want to see this with my own eyes I’ve decided to test it using another new 11g component called hierarchical profiler which I also want to see how it’s working.

First of all, let’s setup the environment:

1. on the database server create a new directory to be used for creating profiler trace files:

oracle@obi:oracle$ mkdir profiler
oracle@obi:oracle$ chmod o-rx profiler/

2. create the DIRECTORY object in the database too, and grant read/write privileges to the testing user (in our case TALEK user):

SQL> create directory profiler_dir as '/opt/oracle/profiler';

Directory created.

SQL> grant read, write on directory profiler_dir to talek;

Grant succeeded.

3. grant execute privilege for DBMS_HPROF package to the TALEK user:

SQL> grant execute on dbms_hprof to talek;

Grant succeeded.

4. connect using TALEK user and create the following package (the only difference between the first and second approach is the type of the l_count variable):

create or replace package trash is

  procedure approach_1;
  
  procedure approach_2;

end trash;
/

create or replace package body trash is

  procedure approach_1 as
    l_count pls_integer := 0;
  begin
    for i in 1..10000 loop
      l_count := l_count + 1;
    end loop;
    dbms_output.put_line(l_count);
  end;
  
  procedure approach_2 as
    l_count simple_integer := 0;
  begin
    for i in 1..10000 loop
      l_count := l_count + 1;
    end loop;
    dbms_output.put_line(l_count);
  end;

end trash;
/

5. Profile the approaches:

SQL> exec dbms_hprof.start_profiling(location => 'PROFILER_DIR', filename => 'test.trc');
 
PL/SQL procedure successfully completed
 
SQL> exec trash.approach_1;
 
PL/SQL procedure successfully completed
 
SQL> exec trash.approach_2;
 
PL/SQL procedure successfully completed
 
SQL> exec dbms_hprof.stop_profiling;
 
PL/SQL procedure successfully completed

6. Analyze the generated trace file. For this we’ll use the "plshprof" command line utility.

oracle@obi:profiler$ plshprof -output report test.trc
PLSHPROF: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
[8 symbols processed]
[Report written to 'report.html']

Aaaaand, the WINNER is:

TALEK.TRASH.APPROACH_1 -> 5713 (microseconds)
TALEK.TRASH.APPROACH_2 -> 100706 (microseconds)

Well… this is unexpected. According to Oracle docs, the SIMPLE_INTEGER should be faster. Ok, back to official doc: "The new PL/SQL SIMPLE_INTEGER data type is a binary integer for use with native compilation which is neither null checked nor overflow checked". Ahaaa… native compilation! Let’s check this:

SQL> show parameter plsql_code_type

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_code_type                      string      INTERPRETED

So, we have a first clue and a first conclusion. If the database doesn’t use NATIVE compilation the SIMPLE_INTEGER type is actually much slower.

Let’s switch to native compilation. This can be easily done because the "plsql_code_type" parameter is dynamic:

SQL> alter system set plsql_code_type=native scope=both;

System altered.

It is important to compile once again the package because otherwise the old PLSQL byte code will be used (you can use "alter package trash compile plsql_code_type=native;"), then repeat the profiler tests.

The new results are:

TALEK.TRASH.APPROACH_2 -> 3927 (microseconds)
TALEK.TRASH.APPROACH_1 -> 12556 (microseconds)

Now, the second approach with SIMPLE_INTEGER is much faster and, interestingly, the PLS_INTEGER approach is slightly slower on native compilation compared with the same approach on the initial PLSQL interpreted environment.

Okey, one more thing. I really enjoy using the new 11g hierarchical profiler. From my point of view is a big step forward compared with the old DBMS_PROFILER, and the provided HTML reports produced by "plshprof" are quite lovely.

TKPROF Everywhere

Lately, I was searching for a solution to let developers to “tkprof” their statements in the most non-intrusive way possible. Of course, a quite appealing solution I found was the one suggested by Tom Kyte in his “Effective Oracle by Design” book. The solution involves reading the user trace file from the server and returning the content of that trace through a pipelined table function. The result may be spooled into a file on the client side and, after that, “tkprof” may be executed for this local file. However, the problem I have is that me, personally, I use oracle instant client and I don't have tkprof on my local machine. Furthermore, I don't use sqlplus all the time, therefore I would really like to get the formatted tkprof output directly within a simple SQL-SELECT statement.

The solution is quite simple: instead of returning the raw trace file it's enough to run tkprof utility on the server with the user dump trace file as a parameter and, eventually, to return the final output.

In order to setup the needed environment we'll have to:

1.create the user which will own the “tkprof” framework:

grant create session, alter session, create procedure to tools identified by xxx;

2.grant additional privileges for this user:

grant select on sys.v_$process to tools;
grant select on sys.v_$session to tools;
exec dbms_java.grant_permission( 'TOOLS', 
  'SYS:java.lang.RuntimePermission', writeFileDescriptor', '' );
exec dbms_java.grant_permission( 'TOOLS', 
  'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '' );

3.create the following java source object. This is needed in order to execute an external program in the OS environment (thanks again Tom for this, see the Java procedure for host calls on Unix environment question.):

create or replace and compile java source named util as
import java.io.*;
import java.lang.*;

public class Util extends Object
{

 public static int RunThis(String args)
 {
 Runtime rt = Runtime.getRuntime();
 int        rc = -1;

 try
 {
    Process p = rt.exec(args);

    int bufSize = 4096;
    BufferedInputStream bis =
     new BufferedInputStream(p.getInputStream(), bufSize);
    int len;
    byte buffer[] = new byte[bufSize];

    // Echo back what the program spit out
    while ((len = bis.read(buffer, 0, bufSize)) != -1)
       System.out.write(buffer, 0, len);

    rc = p.waitFor();
 }
 catch (Exception e)
 {
    e.printStackTrace();
    rc = -1;
 }
 finally
 {
    return rc;
 }
 }
}

4.Create the package which embeds the whole tracing logic:

create or replace package tkprof 
/**
  Provides the tkprof tracing feature to developers. This package is
  supposed to be used in developing environments only.
  
  Required rights (the owner of this package is supposed to be TOOLS):
  
  grant alter session to tools;
  grant select on sys.v_$process to tools;
  grant select on sys.v_$session to tools;
  
  exec dbms_java.grant_permission( 'TOOLS', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '' );
  exec dbms_java.grant_permission( 'TOOLS', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '' );

*/
is

  /**
    The complete path along the name of the tkprof utility. Change this constant to fit to your
    environment. After that the following right is required:
    
    exec dbms_java.grant_permission( 'TOOLS', 'SYS:java.io.FilePermission', TKPROF.TKPROF_EXECUTABLE, 'execute' );
    
    If a RAC configuration is used than the path should be the same accross all nodes. If the ORACLE_HOME
    env variable is different between the RAC nodes then the value of the ORACLE_HOME should be get dynamically
    by using, for example, the DBMS_SYSTEM.get_env procedure or symbolinc links may be created in the OS
    environment of every RAC node.
  */
  TKPROF_EXECUTABLE constant varchar2(300) := '/opt/oracle/product/10.2.0/db_1/bin/tkprof';

  /**
    The directory where the user trace files are stored. May be found out using:

    show parameter user_dump_dest
    
    If a RAC configuration is used than the path should be the same accross all nodes. If not, then the value
    should be fetch dynamically from v$parameter view. Another approach would be to create symbolic links in the
    OS environment of every RAC node.
  */
  UDUMP_PATH constant varchar2(300) := '/opt/oracle/admin/rfd/udump';

  /**
    The name of the oracle directory object which points out to the above
    path. The owner of this package must have read privileges on this
    directory:
    
    create directory UDUMP_DIR as '/opt/oracle/admin/rfd/udump';
    grant read on directory UDUMP_DIR to tools;
  */
  UDUMP_DIR constant varchar2(30) := 'UDUMP_DIR';
  
  /**
    A simple type used to return the tkprof_output.
  */
  type tkprof_output is table of varchar2(4000);

  /**
    Enable tracing for the current session. 
  */
  procedure enable;
  
  /**
    Disable tracing for the current session.
  */
  procedure disable;
  
  /**
    Get the status of the tracing for the current session.
    
    #return 'TRUE' if the trace is enabled, 'FALSE' otherwise.
  */
  function is_enabled return varchar2;
  
  /**
    Get the tkprof content thorough a pipelined table function.

    #pi_tkprof_params additional parameters to tkprof
  */
  function output(pi_tkprof_params varchar2 := '') return tkprof_output pipelined;

end tkprof;
/

create or replace package body tkprof is

  g_unique_session_id varchar2(100);
  g_trace_file_name   varchar2(4000);
  g_is_enabled        boolean;

  function run(pi_cmd in varchar2) return number as
    language java name 'Util.RunThis(java.lang.String) return integer';

  procedure enable as
  begin
    execute immediate 'alter session set tracefile_identifier=''' ||
                      g_unique_session_id || '''';
    execute immediate 'alter session set timed_statistics=true';
    execute immediate 'alter session set max_dump_file_size=unlimited';
    execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
    g_is_enabled := true;
    dbms_output.put_line('session tracing enabled under ' ||
                         g_unique_session_id || ' identifier.');
  end;
  
  function is_enabled return varchar2 as
  begin
    if g_is_enabled then
      return 'TRUE';
    else
      return 'FALSE';
    end if;
  end;

  procedure disable as
  begin
    execute immediate 'alter session set events ''10046 trace name context off''';
    g_is_enabled := false;
    dbms_output.put_line('session tracing disabled');
  end;

  function output(pi_tkprof_params varchar2 := '') return tkprof_output
    pipelined as
    l_status  pls_integer;
    l_bfile   bfile := bfilename(UDUMP_DIR, g_trace_file_name || '.prf');
    l_last    pls_integer := 1;
    l_current pls_integer;
  begin
    dbms_java.set_output(10000);
    l_status := run(TKPROF_EXECUTABLE || ' ' || UDUMP_PATH || '/' ||
                    g_trace_file_name || ' ' || UDUMP_PATH || '/' ||
                    g_trace_file_name || '.prf ' || pi_tkprof_params);
    if l_status != 0 then
      dbms_output.put_line('status: ' || l_status);
      dbms_output.put_line('ERROR: cannot produce the tkprof trace!');
      return;
    else
      dbms_lob.fileopen(l_bfile);
      loop
        l_current := dbms_lob.instr(l_bfile, '0A', l_last, 1);
        exit when(nvl(l_current, 0) = 0);
        pipe row(utl_raw.cast_to_varchar2(dbms_lob.substr(l_bfile,
                                                          l_current -
                                                          l_last + 1,
                                                          l_last)));
        l_last := l_current + 1;
      end loop;
      dbms_lob.fileclose(l_bfile);
    end if;
  exception
    when others then
      if dbms_lob.isopen(l_bfile) != 0 then
        dbms_lob.fileclose(l_bfile);
      end if;
      raise;
  end;

begin
  g_unique_session_id := sys_context('userenv', 'sessionid');
  select sys_context('userenv', 'instance_name') || '_ora_' ||
         ltrim(to_char(a.spid)) || '_' || g_unique_session_id || '.trc'
    into g_trace_file_name
    from v$process a, v$session b
   where a.addr = b.paddr
     and b.audsid = sys_context('userenv', 'sessionid');
end tkprof;
/

5.Change the TKPROF_EXECUTABLE and UDUMP_PATH constants to fit with your environment.
6.Create the UDUMP_DIR directory:

create directory UDUMP_DIR as '/opt/oracle/admin/rfd/udump';
grant read on directory UDUMP_DIR to tools;

7.Grant execute rights for tkprof executable:

exec dbms_java.grant_permission( 'TOOLS', 'SYS:java.io.FilePermission', 
TKPROF.TKPROF_EXECUTABLE, 'execute' );

8.Make the TKPROF package available to everybody:

grant execute on tools.tkprof to public;
create public synonym tkprof for tools.tkprof;

Okey, that's it! Now, let's test it:

fits_cr@RFD> exec tkprof.enable

session tracing enabled under 7154134 identifier.

PL/SQL procedure successfully completed.

fits_cr@RFD> select count(1) from rolmda;

  COUNT(1)
----------
        95

fits_cr@RFD> exec tkprof.disable;
session tracing disabled

PL/SQL procedure successfully completed.


fits_cr@RFD> select * from table(tkprof.output);

COLUMN_VALUE
--------------------------------------------------------------------------------------
TKPROF: Release 10.2.0.3.0 - Production on Thu Mar 27 15:13:06 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Trace file: /opt/oracle/admin/rfd/udump/rfd3_ora_26618_7154134.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece
from
 idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           0

... and so on ...

I'm sure this package can be improved but as a proof of concept I think that it's more than enough. Likewise, I have to mention that this was tested on a 10gR2 database therefore I don't know if you can use it without any changes on a different Oracle database version.

PGA Diagnostics

Do not forget a very important Oracle memory buffer which is the PGA (Program Global Area)! Most of us are tempted to look first of all at the SGA, especially at the library cache, whenever users complain that their SQLs run poorly. Of course, the library cache is a proper place to look at for problems, as well as the disk activity, CPU load and so many, many others. However, do NOT forget the PGA, because an inappropriate configuration can lead to a lot of performance issues.

So, reading through the "Database Performance Tuning Guide" I have found the section which talk about the tuning of the PGA. I am reading it quite often but it's still difficult to remember all the related fixed views and queries which are in connection with this topic. Therefore I have decided to put all the nice queries into a script which should give me a rough image of the PGA configuration.

Below is the script which I would like to share with all whom might be interested:

-- PGA Report
-- Displays various statistics regarding the PGA usage

set linesize 140
set pagesize 9999
set heading off
set feedback off

-- general statistics
select 'SECTION 1: GENERAL STATISTICS FOR THE PGA' from dual;
select '=================================================================='
  || '=========================' from dual;

set heading on
select name,
       decode(unit, 'bytes', round(value / 1024 / 1024, 2), value) value,
       decode(unit, 'bytes', 'MB', '') unit
  from v$pgastat;

set heading off
select 'Check the following:' from dual;
select '  - "aggregate PGA auto target" should not be too small in comparison ' 
  || 'with the "aggregate PGA target parameter"' from dual;
select '  - "global memory bound" should not be lower than 1M' from dual;
select '  - "over allocation count" should be near 0 or should not increasing in time' from dual;
select '  - the bigger "cache hit percentage", the better' from dual;
select '' from dual;

set heading on
SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
    FROM (SELECT name, value cnt, (sum(value) over ()) total
    FROM V$SYSSTAT 
    WHERE name like 'workarea exec%');
set heading off

-- PGA used by all DB processes
select 'SECTION 2: PGA USED BY CURRENT DB PROCESSES (IN MB), ORDER BY PGA_ALLOC_MEM' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on

break on report;
compute sum label '--> TOTAL' of pga_used_mem on report;
compute sum label '--> TOTAL' of pga_alloc_mem on report;
compute sum label '--> TOTAL' of pga_freeable_mem on report;
compute sum label '--> TOTAL' of pga_max_mem on report;

SELECT PROGRAM,
       round(PGA_USED_MEM / 1024 / 1024, 2) pga_used_mem,
       round(PGA_ALLOC_MEM / 1024 / 1024, 2) pga_alloc_mem,
       round(PGA_FREEABLE_MEM / 1024 / 1024, 2) pga_freeable_mem,
       round(PGA_MAX_MEM / 1024 / 1024, 2) pga_max_mem
  FROM V$PROCESS
 order by pga_alloc_mem desc;
  
set heading off
select 'The columns have the following meaning:' from dual;
select '  - PGA_USED_MEM = PGA memory currently used by the process' from dual;
select '  - PGA_ALLOC_MEM = PGA memory currently allocated by the process (including free ' 
  || 'PGA memory not yet released to the operating system by the server process)' from dual;
select '  - PGA_FREEABLE_MEM = Allocated PGA memory which can be freed' from dual;
select '  - PGA_MAX_MEM = Maximum PGA memory ever allocated by the process' from dual;
select '' from dual;

set feedback off;
select 'SECTION 3: USED PGA MEMORY BY CATHEGORIES (VALUES IN MB ORDER DESC BY ALLOCATED_MB).' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on
select category,
       round(sum(allocated) / 1024 / 1024, 2) allocated_mb,
       round(sum(used) / 1024 / 1024, 2) used_mb,
       round(sum(max_allocated) / 1024 / 1024, 2) max_allocated_mb
  from v$process_memory
 group by category
 order by 2 desc;

set heading off
select '' from dual;

-- workareas histogram
select 'SECTION 4: SQL WORKAREAS HISTOGRAM' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on
SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
       (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
       OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
  FROM V$SQL_WORKAREA_HISTOGRAM
 WHERE TOTAL_EXECUTIONS != 0;

set heading off
select '' from dual;

-- active workareas
select 'SECTION 5: CURRENTLY ACTIVE WORKAREAS' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on
set feedback on
SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
       operation_type OPERATION,
       trunc(EXPECTED_SIZE/1024) ESIZE,
       trunc(ACTUAL_MEM_USED/1024) MEM,
       trunc(MAX_MEM_USED/1024) "MAX MEM",
       NUMBER_PASSES PASS,
       trunc(TEMPSEG_SIZE/1024/1024) TSIZE_MB
  FROM V$SQL_WORKAREA_ACTIVE
 ORDER BY 1,2;
set heading off
set feedback off
select 'The meaning of the above columns is:' from dual;
select '  - SID = the active session identifier' from dual;
select '  - OPERATION = the type of the operation' from dual;
select '  - ESIZE = the expected size for the sql workarea' from dual;
select '  - MEM = Amount of PGA memory (in KB) currently allocated on behalf of this work area.' from dual;
select '  - MAX MEM = Maximum memory amount (in KB) used by this work area' from dual;
select '  - PASS = Number of passes corresponding to this work area (0 if running in OPTIMAL mode)' from dual;
select '  - TSIZE_MB = Size (in megabytes) of the temporary segment used on behalf of this work area. '
  || 'This column is NULL if this work area has not (yet) spilled to disk.' from dual;
select '' from dual;

-- top 10 sql with gurmand sql areas
select 'SECTION 6: OP 10 WORK AREAS REQUIRING MOST CACHE MEMORY' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on
SELECT *
  FROM (SELECT distinct s.sql_id,
                        substr(s.SQL_TEXT, 1, 50) sql_text,
                        operation_type,
                        estimated_optimal_size,
                        max_tempseg_size
          FROM V$SQL_WORKAREA a, V$SQLSTATS s
         WHERE a.SQL_ID = s.SQL_ID
         ORDER BY estimated_optimal_size)
 WHERE ROWNUM <= 10;

set heading off
select 'SECTION 7: SQLs WITH WORK AREAS THAT HAVE BEEN EXECUTED IN ONE OR EVEN MULTIPLE PASSES' from dual;
select '=================================================================='
  || '=========================' from dual;
set heading on
set feedback on
SELECT sql_text, sum(ONEPASS_EXECUTIONS) onepass_cnt,
       sum(MULTIPASSES_EXECUTIONS) mpass_cnt 
FROM V$SQL s, V$SQL_WORKAREA wa 
WHERE s.address = wa.address 
GROUP BY sql_text 
HAVING sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0;
set feedback off
set heading off

select 'SECTION 8: PGA TARGET ADVCE' from dual;
select '=================================================================='
  || '=========================' from dual;
show parameter pga_aggregate_target;
set heading on
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
  FROM V$PGA_TARGET_ADVICE;


set heading off
select '' from dual;
pause press any key to exit...
exit

Have fun!

Automatic Shared Memory Management

Well, it's been a while since the last post... However, I'm back and I am going to dig a little bit on the “Automatic Shared Memory Management” 10g feature.

As you already (probably) know, every Oracle instance has a big shared buffer called SGA (System Global Area). This big memory buffer is further divided in several slices, each of them being used for special kind of memory allocations. Till Oracle 9i, the only possibility to adjust their sizes were to manually set a couple of initialization parameters like: DB_CACHE_SIZE, SHARED_POOL_SIZE, JAVA_POOL_SIZE etc. However, in 10g some buffers from the SGA were endowed with a nice capability to resize themselves according to the current workload and other parameters.

The figure below depicts a regular section through the SGA.

As you can see the maximum size upon to which the whole SGA may extend is given by the SGA_MAX_SIZE parameter. However, the SGA_TARGET parameter is the one which actually set the amount of memory to be used by all SGA buffers.
The correlation between the values of the SGA_MAX_SIZE and the SGA_TARGET parameters is important mostly when you query the V$SGA_DYNAMIC_FREE_MEMORY.
Lets have a look onto the following output:

SQL> show parameter sga_target;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
sga_target                           big integer                      1504M

SQL> select current_size/1024/1024 size_M from v$sga_dynamic_free_memory;

    SIZE_M
----------
       544

SQL> show parameter sga_max_size

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
sga_max_size                         big integer                      2G

At the very first sight you may think that from the SGA_TARGET which is 1504M, about 544M are free which, of course, is not true because the Oracle server will always try to expand SGA buffers so that the whole SGA_TARGET amount to be allocated. The free memory reported by the V$SGA_DYNAMIC_FREE_MEMORY view is the difference between the SGA_MAX_SIZE and the SGA_TARGET, which should be read as: “I still have 544M free from the whole SGA_MAX_SIZE which I can use for further expansions of the SGA_TARGET”.

A nice view which I also like to query when I want to find out a brief summary of the SGA composition is the V$SGAINFO.

SQL> select * from v$sgainfo;

NAME                                     BYTES RES
----------------------------------- ---------- ---
Fixed SGA Size                         2074152 No
Redo Buffers                          14700544 No
Buffer Cache Size                    671088640 Yes
Shared Pool Size                     838860800 Yes
Large Pool Size                       16777216 Yes
Java Pool Size                        16777216 Yes
Streams Pool Size                     16777216 Yes
Granule Size                          16777216 No
Maximum SGA Size                    2147483648 No
Startup overhead in Shared Pool      100663296 No
Free SGA Memory Available            570425344

11 rows selected.

Please notice that I can also look at the size of the so-called “granules”, which are the allocation units for the SGA memory. The size of one granule varies according to the whole size of the SGA, operation system and platform. You can also find the size of the granule by querying the V$SGA_DYNAMIC_COMPONENTS.

SQL> select granule_size from v$sga_dynamic_components where rownum <= 1;

GRANULE_SIZE
------------
    16777216

Another important thing to remember is that if you set values for the parameters which controls the size of the dynamic buffers into the SGA then, those values will be taken as minimum thresholds which means that Oracle server will try to adjust the size of the buffers when necessary but will never decrease them under the manually set value.
Of course, there is also the possibility to disable the “Automatic Shared Memory Management” feature by simply setting the SGA_TARGET value to zero. Likewise, this feature will also be disabled if the STATISTICS_LEVEL parameter is NONE, but this is not recommended at all as it disables other Oracle features too (ADDM, for example).

Well, that's it... not very new information but just a short review.