Parameter Descriptions Lookup (xparmdef.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

Parameter Descriptions Lookup

This script provides an interactive way to look up Oracle parameter descriptions along with their modification status, helping DBAs understand parameter purposes and current settings.

Script: xparmdef.sql

rem  xparmall.sql
rem
ttitle 'All System Parameters'
rem
col num format 999 heading 'ID'
col name format a36 heading 'NAME'
col type format 9 heading 'T'
col value format a36 heading 'VALUE'
col isdefault format a5 heading 'ISDEF'
col m format a1 heading 'M'
col isses_modifiable format a5 heading 'IS|SESS|MOD'
col issys_modifiable format a9 heading 'IS|SYSTEM|MOD'
col ismodifiable format a8 heading 'IS|MOD'
col isadjusted format a5 heading 'ISADJ'
col description format a41 heading 'DESCPIPTION'
rem
select
/*
       num,
       type,
       decode( isses_modifiable, 'FALSE', null, isses_modifiable ) isses_modifiable,
       decode( issys_modifiable, 'FALSE', null, issys_modifiable ) issys_modifiable,
       decode( ismodifiable, 'FALSE', null, ismodifiable ) ismodifiable,
       decode( isadjusted, 'FALSE', null, isadjusted ) isadjusted,
       description
*/
       name,
       decode( isdefault, 'TRUE', null, 'FALSE', '*', '?' ) m,
       description
  from x$system_parameters
 where name like '&name'
 order by name;

What This Script Does

This script provides an interactive parameter description lookup that shows:

• Parameter Names: Oracle system parameter names
• Modification Status: Visual indicator of whether parameter is modified
• Descriptions: Oracle’s internal parameter descriptions
• Pattern Matching: Supports wildcards for parameter searches

Prerequisites

This script requires the x$system_parameters view created by:

@xparmall.run

See Advanced System Parameters View (xparmall.run) for setup instructions.

Usage

-- Run the script and enter parameter pattern when prompted
@xparmdef.sql

-- Example patterns to search for:
-- sga%           (all SGA parameters)
-- %optimizer%    (all optimizer parameters)
-- %memory%       (all memory parameters)
-- processes      (specific parameter)
-- _%             (all hidden parameters)

Required Privileges

• SELECT privilege on X$SYSTEM_PARAMETERS view
• The view must exist (created by xparmall.run)
• Typically requires DBA role

Sample Output

Enter value for name: %optimizer%

                              All System Parameters

NAME                                 M DESCPIPTION
------------------------------------ - -----------------------------------------
optimizer_adaptive_features            enable adaptive query optimization
optimizer_adaptive_plans               enable adaptive plans
optimizer_adaptive_statistics        * controls adaptive statistics
optimizer_capture_sql_plan_baselines   enable/disable capturing of SQL plan
optimizer_dynamic_sampling           * controls dynamic sampling
optimizer_features_enable            * optimizer plan compatibility parameter
optimizer_index_caching                optimizer percent index caching
optimizer_index_cost_adj                optimizer index cost adjustment
optimizer_mode                       * optimizer mode
optimizer_secure_view_merging           optimizer secure view merging
optimizer_use_invisible_indexes         optimizer use invisible indexes
optimizer_use_pending_statistics        optimizer use pending statistics
optimizer_use_sql_plan_baselines        optimizer use sql plan baselines

Key Output Columns

Column	Description
NAME	Parameter name (up to 36 characters)
M	Modification indicator: blank=default, *=modified, ?=unknown
DESCRIPTION	Oracle’s internal description of the parameter (up to 41 characters)

Common Search Patterns

1. Memory Parameters

-- When prompted, enter: %memory%
-- Shows all memory-related parameters with descriptions

2. Performance Parameters

-- When prompted, enter: %cpu%
-- Shows CPU-related parameters

-- When prompted, enter: %parallel%
-- Shows parallel execution parameters

3. Database Features

-- When prompted, enter: %enable%
-- Shows feature enablement parameters

-- When prompted, enter: %compatible%
-- Shows compatibility parameters

4. Hidden Parameters

-- When prompted, enter: _%
-- Shows all underscore (hidden) parameters

5. Security Parameters

-- When prompted, enter: %audit%
-- Shows audit-related parameters

-- When prompted, enter: %encrypt%
-- Shows encryption parameters

Advanced Usage Examples

Create Custom Parameter Documentation

-- Generate parameter documentation with descriptions
SET PAGESIZE 1000
SET LINESIZE 120

SELECT name,
       CASE WHEN isdefault = 'FALSE' THEN value ELSE '(default)' END as current_value,
       description
FROM x$system_parameters
WHERE UPPER(description) LIKE '%&search_term%'
ORDER BY name;

Find Parameters by Description

-- Search parameters by description content
SELECT name, value, description
FROM x$system_parameters
WHERE UPPER(description) LIKE '%PARALLEL%'
   OR UPPER(description) LIKE '%PROCESS%'
ORDER BY name;

Modified Parameters with Descriptions

-- Show all modified parameters with their descriptions
SELECT name, value, description
FROM x$system_parameters
WHERE isdefault = 'FALSE'
ORDER BY name;

Parameter Research Examples

1. Research Optimizer Behavior

-- Find all optimizer-related parameters
SELECT name, value,
       DECODE(isdefault, 'FALSE', '*', ' ') as modified,
       description
FROM x$system_parameters
WHERE UPPER(name) LIKE '%OPTIMIZER%'
   OR UPPER(description) LIKE '%OPTIMIZER%'
ORDER BY name;

2. Memory Configuration Analysis

-- Research memory parameters
SELECT name, value,
       DECODE(isdefault, 'FALSE', '*', ' ') as modified,
       description
FROM x$system_parameters
WHERE UPPER(name) LIKE '%SGA%'
   OR UPPER(name) LIKE '%PGA%'
   OR UPPER(name) LIKE '%MEMORY%'
   OR UPPER(description) LIKE '%MEMORY%'
ORDER BY name;

3. Performance Feature Discovery

-- Find performance-related features
SELECT name, value, description
FROM x$system_parameters
WHERE UPPER(description) LIKE '%PERFORMANCE%'
   OR UPPER(description) LIKE '%FAST%'
   OR UPPER(description) LIKE '%SPEED%'
   OR UPPER(description) LIKE '%EFFICIENT%'
ORDER BY name;

4. Troubleshooting Parameters

-- Find diagnostic and troubleshooting parameters
SELECT name, value, description
FROM x$system_parameters
WHERE UPPER(description) LIKE '%DEBUG%'
   OR UPPER(description) LIKE '%TRACE%'
   OR UPPER(description) LIKE '%LOG%'
   OR UPPER(description) LIKE '%DUMP%'
ORDER BY name;

Creating Parameter Reference Guides

Complete Parameter Handbook

-- Generate comprehensive parameter reference
SET PAGESIZE 0
SET LINESIZE 200
SPOOL parameter_handbook.txt

SELECT 'PARAMETER: ' || name || chr(10) ||
       'VALUE: ' || NVL(value, '(not set)') || chr(10) ||
       'MODIFIED: ' || DECODE(isdefault, 'FALSE', 'YES', 'NO') || chr(10) ||
       'DESCRIPTION: ' || description || chr(10) ||
       RPAD('-', 80, '-') || chr(10)
FROM x$system_parameters
ORDER BY name;

SPOOL OFF

Category-Based Documentation

-- Create categorized parameter guide
SELECT
  CASE
    WHEN UPPER(name) LIKE '%SGA%' OR UPPER(name) LIKE '%MEMORY%' THEN 'MEMORY MANAGEMENT'
    WHEN UPPER(name) LIKE '%OPTIMIZER%' THEN 'QUERY OPTIMIZER'
    WHEN UPPER(name) LIKE '%PARALLEL%' THEN 'PARALLEL EXECUTION'
    WHEN UPPER(name) LIKE '%PROCESS%' OR UPPER(name) LIKE '%SESSION%' THEN 'PROCESS MANAGEMENT'
    WHEN UPPER(name) LIKE '%AUDIT%' OR UPPER(name) LIKE '%SECURITY%' THEN 'SECURITY'
    WHEN UPPER(name) LIKE '%TRACE%' OR UPPER(name) LIKE '%DEBUG%' THEN 'DIAGNOSTICS'
    ELSE 'OTHER'
  END as category,
  name,
  value,
  DECODE(isdefault, 'FALSE', 'Modified', 'Default') as status,
  description
FROM x$system_parameters
ORDER BY 1, 2;

Integration with Other Scripts

This script works well with:

• Advanced Parameters View (xparmall.run) - Required prerequisite
• Parameter Query Tool (xparm80.sql) - For parameter value lookups
• Database Health Check (db.sql) - For comprehensive analysis
• System Configuration Scripts - For complete system analysis

Automation Examples

Parameter Change Documentation

-- Create documentation for parameter changes
CREATE OR REPLACE PROCEDURE document_parameter_changes AS
BEGIN
  FOR param IN (
    SELECT name, value, description
    FROM x$system_parameters
    WHERE isdefault = 'FALSE'
    ORDER BY name
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('Parameter: ' || param.name);
    DBMS_OUTPUT.PUT_LINE('Value: ' || param.value);
    DBMS_OUTPUT.PUT_LINE('Purpose: ' || param.description);
    DBMS_OUTPUT.PUT_LINE('------------------------');
  END LOOP;
END;
/

Parameter Research Assistant

-- Create a function to search parameter descriptions
CREATE OR REPLACE FUNCTION find_parameters_by_keyword(p_keyword VARCHAR2)
RETURN SYS_REFCURSOR AS
  result_cursor SYS_REFCURSOR;
BEGIN
  OPEN result_cursor FOR
    SELECT name, value, description
    FROM x$system_parameters
    WHERE UPPER(name) LIKE '%' || UPPER(p_keyword) || '%'
       OR UPPER(description) LIKE '%' || UPPER(p_keyword) || '%'
    ORDER BY CASE WHEN UPPER(name) LIKE '%' || UPPER(p_keyword) || '%' THEN 1 ELSE 2 END, name;

  RETURN result_cursor;
END;
/

-- Usage example:
-- SELECT * FROM TABLE(find_parameters_by_keyword('memory'));

Best Practices

1. Use for Research: Ideal for understanding unfamiliar parameters
2. Document Changes: Always document parameter modifications with their purposes
3. Combine with Testing: Research parameter descriptions before making changes
4. Regular Reviews: Periodically review parameter settings and their purposes
5. Version Awareness: Parameter descriptions may vary between Oracle versions

This script serves as an essential reference tool for understanding Oracle’s vast parameter system and making informed configuration decisions based on Oracle’s own parameter descriptions.