The SHOW command may be used to review information regarding the current state of your database. Use SHOW SGA to see the information about the size of your System Global Area, or SGA. With SHOW PARAMETER, you can look at the current settings for the database initialization parameters. SHOW LOGSOURCE and SHOW AUTORECOVERY give you information about archive logging and recovery.
Tip
Remember that the SHOW command options described in this chapter are new with release 8.1 of SQL*Plus, which was shipped with Oracle8i. In previous versions of Oracle, these SHOW commands were implemented only in Server Manager.
The SHOW SGA command may be used to display information regarding the size of the System Global Area. The SGA is shared by all Oracle processes and contains the database buffer cache, the redo log buffer, shared SQL statements, and a number of other items. Here is an example of SHOW SGA being used:
SQL> SHOW SGA
Total System Global Area 5508612 bytes
Fixed Size 45584 bytes
Variable Size 4529140 bytes
Database Buffers 409600 bytes
Redo Buffers 524288 bytesThe values returned by SHOW SGA are as follows:
- Total System Global Area
The total maximum size of the SGA.
- Fixed Size
This is the minimum size of the SGA, and is the size used when the SGA is first allocated.
- Variable Size
This is the amount by which the SGA may expand.
- Database Buffers
The number of bytes allocated for buffering of data blocks from the data files. Oracle tries to keep the most recently used data in memory, in case it is needed again.
- Redo Buffers
The number of bytes allocated for buffering of redo log data. The log writer (LGWR) process reads these buffers and writes the information they contain to the redo log files.
For more information about the SGA, the structures within it, and the purpose of each of those structures, see Chapter 7 of the Oracle8 Concepts manual.
Each Oracle database has a number of initialization parameters that control its behavior. These parameters control a wide range of things, such as the number of job queue processes to run, the database block size, the shared pool size, and more. When an Oracle database starts, the initial settings for these parameters are read from an initialization file. Some of these parameters may be changed dynamically, while the database is up and running. Others may only be changed while the instance is shut down. Still others, such as the database block size, may never be changed at all.
The SHOW PARAMETERS command may be used to display the value of one or more selected parameters, or of all parameters. SHOW PARAMETERS with no arguments will get you a listing of everything, as shown in the following example:
SQL> SHOW PARAMETERS
NAME TYPE VALUE
----------------------------------- ------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean TRUE
allow_partial_sn_results boolean FALSE
always_anti_join string NESTED_LOOPS
aq_tm_processes integer 0
arch_io_slaves integer 0
audit_trail string NONE
b_tree_bitmap_plans boolean FALSE
background_dump_dest string %RDBMS80% race
backup_disk_io_slaves integer 0
backup_tape_io_slaves boolean FALSE
bitmap_merge_area_size integer 1048576
blank_trimming boolean FALSE
buffer_pool_keep string
buffer_pool_recycle string
cache_size_threshold integer 20
cleanup_rollback_entries integer 20
...You can narrow down the results by using a specific parameter name as an argument, for example:
SQL> SHOW PARAMETERS log_checkpoint_interval
NAME TYPE VALUE
----------------------------------- ------- ------------------------------
log_checkpoint_interval integer 10000A useful variation on this theme is that you may specify any string you like as an argument to SHOW PARAMETERS, and SQL*Plus will display all the parameters whose names contain that string. The following example shows all the LOG-related parameters, plus a few others containing the string “LOG”:
SVRMGR> SHOW PARAMETERS log
NAME TYPE VALUE
----------------------------------- ------- ------------------------------
dblink_encrypt_login boolean FALSE
delayed_logging_block_cleanouts boolean TRUE
log_archive_buffer_size integer 127
log_archive_buffers integer 4
log_archive_dest string %RDBMS80%
log_archive_duplex_dest string
log_archive_format string ARC%s.%t
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_block_checksum boolean FALSE
log_buffer integer 8192
log_checkpoint_interval integer 10000
log_checkpoint_timeout integer 0
log_checkpoints_to_alert boolean FALSE
log_file_name_convert string
log_files integer 255
log_simultaneous_copies integer 0
log_small_entry_max_size integer 80
mts_rate_log_size string
remote_login_passwordfile string SHAREDSQL*Plus implements three commands that let you view information about archive logging and recovery. The SHOW LOGSOURCE command may be used to find out what directory your database’s archive log files are being written to. Here’s an example:
SQL> SHOW LOGSOURCE
Logsource G:INSTANCESPLUMLOGSIf you want to see more information about the status of archive logging, use the ARCHIVE LOG LIST command as shown in this example:
SQL> ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination G:INSTANCESPLUMLOGS
Oldest online log sequence 35
Next log sequence to archive 36
Current log sequence 36The output from ARCHIVE LOG LIST is as follows:
- Database log mode
Tells you whether or not your database is running in archive mode. The value will be either “Archive Mode” or “NOArchive Mode”.
- Automatic archival
Tells you whether or not redo logs are automatically being archived. If automatic archival is enabled, that means you have an archiver process (ARCH) running, and your redo logs are automatically being copied to another device as each one is closed.
- Archive destination
Tells you the archive destination. This is the same information you get from the SHOW LOGSOURCE command.
- Oldest online log sequence
Tells you the sequence number of the oldest redo log group that hasn’t been archived yet. Oracle increments this sequence each time a log switch occurs.
- Next log sequence to archive
Tells you the sequence number of the next group of log files to be archived. If automatic archiving is disabled, this line won’t be displayed. This value will always be less than the current log sequence.
- Current log sequence
Tells you the sequence number of the redo log group currently in use.
You can get an idea of how well the archiving process, which is named ARCH, is keeping up by looking at the values for the next log sequence archive and the current log sequence. A large difference between these two values indicates that the ARCH process is falling behind. If the archiver falls too far behind, the log writer process won’t be able to open another group, and database users will have to wait for one to be archived. If this happens frequently, you may be able to resolve the situation by creating more redo log groups, making your existing groups larger, or starting more archive processes.