To see the size of the SGA, you can issue the show SGA command from SQL*DBA:

SQLDBA> show sga

Total System Global Area          8252756 bytes
Fixed Size                        48260 bytes
Variable Size                     6533328 bytes
Database Buffers                  1638400 bytes
Redo Buffers                      32768 bytes

Fortunately, only a few parameters are really important to the overall performance of Oracle:

•  db_block_buffers—This parameter determines the number of database block buffers in the Oracle SGA and represents the single most important parameter to Oracle memory.

•  db_block_size—The size of the database blocks can make a huge improvement in performance. While the default value is 2,048 bytes, databases that have large tables with full-table scans will see a tremendous improvement in performance by increasing db_block_size to a larger value.

•  log_buffer—This parameter determines the amount of memory to allocate for Oracle’s redo log buffers. The higher the amount of update activity, the more space needs to be allocated to the log_buffer.

•  shared_pool_size—This parameter defines the pool that is shared by all users in the system, including SQL areas and data dictionary caching.

---

NOTE:  For more information on init.ora parameters, see Chapter 6, Oracle DBA Performance And Tuning.

---

Using The db_block_size Parameter With db_file_multiblock_read_count

As I’ll discuss in Chapter 6, the db_block_size parameters can have a dramatic impact on system performance. Minimizing I/O is essential to performance, so the less physical I/O incurred by Oracle, the faster the database will run.

In general, db_block_size should never be set to less than 8 K, regardless of the type of application. Even online transaction processing systems (OLTP) will benefit from using 8 K blocks, while systems that perform many full-table scans may benefit from even larger block sizes. Depending upon the operating system, Oracle can support 16 K block sizes. Systems that perform full-table scans may benefit from this approach.

Also, note the relationship between db_block_size and the multi_block_read_count parameter. At the physical level in Unix, Oracle always reads in a minimum of 64 K blocks. Therefore, the values of multi_block_read_count and db_block_size should be set such that their product is 64 K. For example:

Note that the block size for Oracle is not immutable. Eventually, all Oracle databases should be compressed (export/import) to reduce fragmentation, and it becomes trivial at this time to alter the value of db_block_size.

Remember that increasing the size of db_block_size will increase the size of the Oracle SGA. The values of db_block_size are multiplied by the value of db_block_buffers to determine the total amount of memory to allocate for Oracle’s I/O buffers.

Tuning Oracle Sorting

As a small but very important component of SQL syntax, sorting is a frequently overlooked aspect of Oracle tuning. In general, the Oracle database will automatically perform sorting operations on row data as requested by a CREATE INDEX or an SQL ORDER BY or GROUP BY statement. In Oracle, sorting occurs under the following circumstances:

•  Using the ORDER BY clause in SQL

•  Using the GROUP BY clause in SQL

•  When an index is created

•  When a MERGE SORT is invoked by the SQL optimizer because inadequate indexes exist for a table join

At the time a session is established with Oracle, a private sort area is allocated in memory for use by the session for sorting. Unfortunately, the amount of memory must be the same for all sessions—it is not possible to add additional sort area for tasks that are sort intensive. Therefore, the designer must strike a balance between allocating enough sort area to avoid disk sorts for the large sorting tasks, keeping in mind that the extra sort area will be allocated and not used by tasks that do not require intensive sorting.

The size of the private sort area is determined by the sort_area_size init.ora parameter. The size for each individual sort is specified by the sort_area_retained_size init.ora parameter. Whenever a sort cannot be completed with this assigned space, a disk sort is invoked using the temporary tablespace for the Oracle instance. As a general rule, only index creation and ORDER BY clauses using functions should be allowed to use a disk sort.

Disk sorts are expensive for several reasons. First, they consume resources in the temporary tablespaces. Oracle must also allocate buffer pool blocks to hold the blocks in the temporary tablespace. In-memory sorts are always preferable to disk sorts, and disk sorts will surely slow down the individual task, as well as impacting other concurrent tasks on the Oracle instance. Also, excessive disk sorting will also cause a high value for free buffer waits, paging other tasks’ data blocks out of the buffer. To see the amount of disk and in-memory sorts, issue the following query against the v$sysstat table:

sorts.sql - displays in-memory and disk sorts
SPOOL /tmp/sorts
COLUMN VALUE FORMAT 999,999,999
SELECT NAME, VALUE FROM v$sysstat
  WHERE NAME LIKE 'sort%';
SPOOL OFF;

Here is the output:

SQL> @sorts

NAME                         VALUE
--------                    ------------
sorts (memory)                  7,019
sorts (disk)                       49
sorts (rows)                3,288,608

Here we see that there were 49 sorts to disk. Out of a total of 3.2 million sorts, this is well below 1 percent and is probably acceptable for the system.

---

NOTE:  For tips on avoiding disk sorts, see Chapter 4, Tuning Oracle SQL, where we’ll take a look at specific techniques for ensuring in-memory sorts.

---