To View Sort Area Information

Using the following query we can get some information about the sorting happening on a particular database.

SELECT *
  FROM v$sysstat
 WHERE NAME LIKE '%sorts%'

STATISTIC# NAME         CLASS VALUE         STAT_ID
341         sorts (memory) 64 27568047 2091983730
342         sorts (disk) 64 158         2533123502
343         sorts (rows) 64 9867427817 3757672740

sorts (memory) - If the number of disk writes is non-zero for a given sort operation, then this statistic is incremented. Sorts that require I/O to disk are quite resource intensive. Try increasing the initialization parameter SORT_AREA_SIZE.

sorts (disk) - If the number of disk writes is zero, then the sort was performed completely in memory and this statistic is incremented. This is more an indication of sorting activity in the application workload. You can't do much better than memory sorts, except maybe no sorts at all. Sorting is usually caused by selection criteria specifications within table join SQL operations.

The sorting algorithms and resources used have improved with every release of oracle and I see future releases improving further in this regard.

To View SGA Information

The (SGA) System Global Area is shared memory structures that are created at instance startup. They hold information about

the instance, and control its behavior. The following query gives a window into the various memory pools available in the

SGA.

SELECT NAME, VALUE
  FROM v$parameter
 WHERE NAME IN
          ('shared_pool_size', 'java_pool_size', 'streams_pool_size',
           'log_buffer', 'db_cache_size', 'db_2k_cache_size',
           'db_4k_cache_size', 'db_8k_cache_size', 'db_16k_cache_size',
           'db_32k_cache_size', 'db_keep_cache_size', 'db_recycle_cache_size',
           'large_pool_size');

and the sizes of the various pools in use.

SELECT NAME, pool, ROUND (BYTES / 1024 / 1024, 2) free_mb
  FROM v$sgastat
 WHERE NAME IN
          ('%free memory%', 'parameters', 'memory in use', 'db_block_buffers',
           'log_buffer', 'dictionary_cache,', 'sql area', 'library cache');

Finding the Global Database Name

The full name of the database which uniquely identifies it from any other database. The global database name is of the

form "database_name.database_domain," for example, sales.us.acme.com.

The database name portion, sales, is a simple name you wish to call your database. The database domain portion,

us.acme.com, specifies the database domain in which the database is located, making the global database name unique. When

possible, Oracle Corporation recommends that your database domain mirror the network domain.

The global database name is the default service name of the database, as specified by the SERVICE_NAMES parameter in the

initialization parameter file.

SELECT NAME, value$
  FROM SYS.props$
 WHERE NAME = 'GLOBAL_DB_NAME';

or

SELECT *
  FROM GLOBAL_NAME;