Oracle Architecture Study Notes

andrea · December 3, 2025, 12:35pm

Oracle Architecture Study Notes

Understanding the Shared Pool, Data Buffer, and Log Buffer

The Oracle architecture is a complex system that involves several key components, including the Shared Pool, Data Buffer, and Log Buffer. These components play a crucial role in the execution of SQL queries and the management of database resources.

The Shared Pool

The Shared Pool is a memory area within the SGA (System Global Area) that stores parsed SQL execution plans. When a SQL query is executed, the Oracle optimizer acquires the execution plan from the Shared Pool, rather than parsing it from scratch. This process is known as “caching” and is an essential feature of the Shared Pool.

To illustrate the importance of the Shared Pool, let’s consider an example. Suppose we have a SQL query that selects data from a table based on a specific condition. If we execute this query multiple times, the Oracle optimizer will acquire the execution plan from the Shared Pool, rather than parsing it from scratch each time. This reduces the overhead of parsing and improves the performance of the query.

Bind Variables

Bind variables are a feature of the Shared Pool that allows us to reuse SQL execution plans for similar queries. By using bind variables, we can reduce the number of SQL queries that need to be parsed and executed, leading to improved performance.

For example, suppose we have a SQL query that selects data from a table based on a specific condition. We can use a bind variable to represent the condition, as follows:

SELECT * FROM sys_users WHERE username = :x

By using a bind variable, we can reuse the SQL execution plan for similar queries, reducing the overhead of parsing and improving the performance of the query.

Tuning the Shared Pool

To optimize the performance of the Shared Pool, we need to understand how it works and how to tune it. Here are some tips for tuning the Shared Pool:

Use bind variables to reduce the number of SQL queries that need to be parsed and executed.
Monitor the Shared Pool size and adjust it as needed to ensure that it is not too small or too large.
Use the ALTER SYSTEM FLUSH BUFFER_CACHE command to clear the buffer cache and ensure that the Shared Pool is updated with the latest data.

The Data Buffer

The Data Buffer is a memory area within the SGA that stores data blocks that are frequently accessed by the database. The Data Buffer is used to improve the performance of database queries by reducing the number of disk I/O operations.

To illustrate the importance of the Data Buffer, let’s consider an example. Suppose we have a SQL query that selects data from a table based on a specific condition. If we execute this query multiple times, the Oracle optimizer will acquire the data from the Data Buffer, rather than accessing it from disk. This reduces the overhead of disk I/O and improves the performance of the query.

Tuning the Data Buffer

To optimize the performance of the Data Buffer, we need to understand how it works and how to tune it. Here are some tips for tuning the Data Buffer:

Monitor the Data Buffer size and adjust it as needed to ensure that it is not too small or too large.
Use the ALTER SYSTEM FLUSH BUFFER_CACHE command to clear the buffer cache and ensure that the Data Buffer is updated with the latest data.
Use the DB_CACHE_ADJUST parameter to adjust the size of the Data Buffer.

The Log Buffer

The Log Buffer is a memory area within the SGA that stores log records that are generated by the database. The Log Buffer is used to improve the performance of database transactions by reducing the overhead of logging.

To illustrate the importance of the Log Buffer, let’s consider an example. Suppose we have a database transaction that involves multiple operations. If we use the Log Buffer to store the log records, we can reduce the overhead of logging and improve the performance of the transaction.

Tuning the Log Buffer

To optimize the performance of the Log Buffer, we need to understand how it works and how to tune it. Here are some tips for tuning the Log Buffer:

Monitor the Log Buffer size and adjust it as needed to ensure that it is not too small or too large.
Use the ALTER SYSTEM FLUSH LOG_BUFFER command to clear the Log Buffer and ensure that it is updated with the latest log records.
Use the LOG_BUFFER_ADJUST parameter to adjust the size of the Log Buffer.

Conclusion

In conclusion, the Shared Pool, Data Buffer, and Log Buffer are critical components of the Oracle architecture that play a crucial role in the execution of SQL queries and the management of database resources. By understanding how these components work and how to tune them, we can optimize the performance of the database and improve the efficiency of database transactions.

Example Code

Here is an example of how to use bind variables to reduce the number of SQL queries that need to be parsed and executed:

CREATE TABLE t (x int);

-- Do not use binding traversal
BEGIN
  FOR i IN 1 .. 1000
  LOOP
    EXECUTE IMMEDIATE 'INSERT INTO t VALUES (' || i || ')';
    COMMIT;
  END LOOP;
END;

-- Use binding traversal with bind variables
BEGIN
  FOR i IN 1 .. 100
  LOOP
    EXECUTE IMMEDIATE 'INSERT INTO t VALUES (:x)' USING i;
    COMMIT;
  END LOOP;
END;

Tuning the Shared Pool

Here is an example of how to tune the Shared Pool:

ALTER SYSTEM FLUSH BUFFER_CACHE;

Tuning the Data Buffer

Here is an example of how to tune the Data Buffer:

ALTER SYSTEM FLUSH BUFFER_CACHE;
DB_CACHE_ADJUST;

Tuning the Log Buffer

Here is an example of how to tune the Log Buffer:

ALTER SYSTEM FLUSH LOG_BUFFER;
LOG_BUFFER_ADJUST;

SQL Query

Here is an example of a SQL query that uses bind variables:

SELECT * FROM sys_users WHERE username = :x;

Oracle Programming Algorithms

Here is an example of an Oracle programming algorithm that uses bind variables:

CREATE TABLE t (x int);

-- Do not use binding traversal
BEGIN
  FOR i IN 1 .. 1000
  LOOP
    EXECUTE IMMEDIATE 'INSERT INTO t VALUES (' || i || ')';
    COMMIT;
  END LOOP;
END;

-- Use binding traversal with bind variables
BEGIN
  FOR i IN 1 .. 100
  LOOP
    EXECUTE IMMEDIATE 'INSERT INTO t VALUES (:x)' USING i;
    COMMIT;
  END LOOP;
END;

Cache Report

Here is an example of a cache report that shows the performance of the Shared Pool, Data Buffer, and Log Buffer:

SELECT
  s.snap_date,
  DECODE (s.redosize, NULL, '--shutdown or end--', s.currtime) "TIME",
  TO_CHAR (ROUND (s.seconds / 60, 2)) "elapse (min)",
  ROUND (t.db_time / 1000000 / 60, 2) "DB time (min)",
  s.redosize redo,
  ROUND (s.redosize / s.seconds, 2) "redo / s",
  s.logicalreads logical,
  ROUND (s.logicalreads / s.seconds, 2) "logical / s",
  s.physicalreads physical,
  ROUND (s.physicalreads / s.seconds, 2) "phy / s",
  s.executes execs,
  ROUND (s.executes / s.seconds, 2) "execs / s",
  s.parse,
  ROUND (s.parse / s.seconds, 2) "parse / s",
  s.hardparse,
  ROUND (s.hardparse / s.seconds, 2) "hardparse / s",
  s.transactions trans,
  ROUND (s.transactions / s.seconds, 2) "trans / s"
FROM (
  SELECT
    curr_redo - last_redo redosize,
    curr_logicalreads - last_logicalreads logicalreads,
    curr_physicalreads - last_physicalreads physicalreads,
    curr_executes - last_executes executes,
    curr_parse - last_parse parse,
    curr_hardparse - last_hardparse hardparse,
    curr_transactions - last_transactions transactions,
    ROUND (((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds,
    TO_CHAR (currtime, 'yy / mm / dd') snap_date,
    TO_CHAR (currtime, 'hh24: mi') currtime,
    curr_snap_id endsnap_id,
    TO_CHAR (startup_time, 'yyyy-mm-dd hh24: mi: ss') startup_time
  FROM (
    SELECT
      a.redo last_redo,
      a.logicalreads last_logicalreads,
      a.physicalreads last_physicalreads,
      a.executes last_executes,
      a.parse last_parse,
      a.hardparse last_hardparse,
      a.transactions last_transactions,
      LEAD (a.redo, 1, NULL) OVER (PARTITION BY b.startup_time ORDER BY b.end_interval_time) curr_redo,
      LEAD (a.logicalreads, 1, NULL) OVER (PARTITION BY b.startup_time ORDER BY b.end_interval_time) curr_logicalreads,
      LEAD (a.physicalreads, 1, NULL) OVER (PARTITION BY b.startup_time ORDER BY b.end_interval_time) curr_physicalreads,
      LEAD (a.executes, 1, NULL) OVER (PARTITION BY b.startup_time ORDER BY b.end_interval_time) curr_executes,
      LEAD (a.parse, 1, NULL) OVER (PARTITION BY b.startup_time ORDER BY b.end_interval_time) curr_parse,
      LEAD (a.hardparse, 1, NULL) OVER (PARTITION BY b.startup_time ORDER BY b.end_interval_time) curr_hardparse,
      LEAD (a.transactions, 1, NULL) OVER (PARTITION BY b.startup_time ORDER BY b.end_interval_time) curr_transactions,
      b.end_interval_time lasttime,
      LEAD (b.end_interval_time, 1, NULL) OVER (PARTITION BY b.startup_time ORDER BY b.end_interval_time) currtime,
      LEAD (b.snap_id, 1, NULL) OVER (PARTITION BY b.startup_time ORDER BY b.end_interval_time) currsnap_id,
      b.startup_time
    FROM (
      SELECT
        snap_id,
        dbid,
        instance_number,
        SUM (DECODE (stat_name, 'redo size', value, 0)) redo,
        SUM (DECODE (stat_name, 'Session logical reads', value, 0)) logicalreads,
        SUM (DECODE (stat_name, 'Physical reads', value, 0)) physicalreads,
        SUM (DECODE (stat_name, 'execute count', value, 0)) executes,
        SUM (DECODE (stat_name, 'Parse count (total)', value, 0)) parse,
        SUM (DECODE (stat_name, 'Parse count (hard)', value, 0)) hardparse,
        SUM (DECODE (stat_name, 'User rollbacks', value, 'User commits', value, 0)) transactions
      FROM dba_hist_sysstat
      WHERE stat_name IN ('Redo size', 'Session logical reads', 'Physical reads', 'Execute count', 'User rollbacks', 'User commits', 'Parse count (hard)', 'Parse count (total)')
      GROUP BY snap_id, dbid, instance_number
    ) a, dba_hist_snapshot b
    WHERE a.snap_id = b.snap_id
    AND a.dbid = b.dbid
    AND a.instance_number = b.instance_number
    ORDER BY end_interval_time
  ) s,
  (SELECT LEAD (a.value, 1, NULL) OVER (PARTITION BY b.startup_time ORDER BY b.end_interval_time) - a.value db_time,
          LEAD (b.snap_id, 1, NULL) OVER (PARTITION BY b.startup_time ORDER BY b.end_interval_time) endsnap_id
   FROM dba_hist_sys_time_model a, dba_hist_snapshot b
   WHERE a.snap_id = b.snap_id
   AND a.dbid = b.dbid
   AND a.instance_number = b.instance_number
   AND a.stat_name = 'DB time')
  t
WHERE s.endsnap_id = t.endsnap_id
ORDER BY s.snap_date, time DESC;

Note: The code snippets and SQL queries provided are for illustrative purposes only and may not be suitable for production use without modification.