Tested on Oracle AI Database 26ai Enterprise Edition 23.26.3.0.0, connected to a PDB. Every list and count in this post was read from the live database. Oracle adds, renames, and removes hidden parameters in every release, so run the queries on your own version.
Companion post: Initialization Parameters in 26ai: Groups and Modify Levels, which explains the SESSION / SYSTEM / STATIC / PDB levels used below.
Warning: hidden parameters are undocumented and unsupported unless Oracle Support asks you to set one. Everything here is read-only exploration.
The appendix at the end has the complete list of all 7,076 hidden and 61 auto-maintained parameters, grouped by area, with type, value, level, PDB/RAC modifiability, and description.
What is a hidden parameter?
| Kind | Name | Count in 26ai | Who sets it |
|---|---|---|---|
| Documented | name | 568 | You |
| Hidden | _name | 7,076 | Oracle defaults. Change only on Oracle Support's advice |
| Auto-maintained | __name | 61 | The instance itself |
| Total | 7,705 |
About 92% of all parameters are hidden (_), and another 61 (__) are records the instance keeps for itself. Hidden parameters don't show in V$PARAMETER unless someone has set them.
How to see them
X$ tables are only visible to SYS. Even with the DBA role and SELECT ANY DICTIONARY, a normal user gets:
SELECT COUNT(*) FROM x$ksppi;
ORA-00942: table or view "DONGHUA"."X$KSPPI" does not exist
As SYSDBA, join the name table (X$KSPPI) to the current value table (X$KSPPCV). The modify level comes from bit flags in KSPPIFLG. I took the bit decoding from Oracle's own definition of GV$PARAMETER:
SELECT view_definition FROM v$fixed_view_definition WHERE view_name = 'GV$PARAMETER';
-- ISSES_MODIFIABLE : BITAND(ksppiflg/256,1) 1 = TRUE
-- ISSYS_MODIFIABLE : BITAND(ksppiflg/65536,3) 1,3 = IMMEDIATE, 2 = DEFERRED, 0 = FALSE
-- ISPDB_MODIFIABLE : BITAND(ksppiflg/524288,1) 1 = TRUE
Full query: name, value, default flag, level, PDB flag, and description:
SELECT i.ksppinm AS name,
v.ksppstvl AS value,
v.ksppstdf AS is_default,
CASE WHEN BITAND(i.ksppiflg/256,1) = 1 THEN 'SESSION'
WHEN BITAND(i.ksppiflg/65536,3) IN (1,3) THEN 'SYSTEM_IMMEDIATE'
WHEN BITAND(i.ksppiflg/65536,3) = 2 THEN 'SYSTEM_DEFERRED'
ELSE 'STATIC' END AS lvl,
DECODE(BITAND(i.ksppiflg/524288,1),1,'YES','NO') AS pdb_modifiable,
i.ksppdesc AS description
FROM x$ksppi i
JOIN x$ksppcv v ON i.indx = v.indx
WHERE i.ksppinm LIKE '\_%' ESCAPE '\'
ORDER BY i.ksppinm;
Use X$KSPPSV in place of X$KSPPCV for instance-level (system) values instead of session values.
Modify levels of hidden parameters
| Kind | Level | Count | PDB-modifiable |
|---|---|---|---|
_ hidden | SESSION | 3,019 | 2,776 |
_ hidden | SYSTEM (immediate) | 2,611 | 605 |
_ hidden | STATIC (spfile + restart) | 1,426 | 51 |
_ hidden | SYSTEM (deferred) | 20 | 4 |
__ auto | SYSTEM (immediate) | 54 | 8 |
__ auto | STATIC | 6 | 0 |
__ auto | SESSION | 1 | 1 |
Hidden parameters are more likely than documented ones to be session-modifiable (43% vs 40%). That's because many of them are fine-grained optimizer and execution switches, which Oracle Support can have you test in a single session.
Hidden parameters grouped by area
Oracle doesn't store a category, and the name prefix alone is noisy: there are 1,142 distinct first words, many of them generic like _enable_, _disable_, _max_. So I mapped names into areas with patterns (for example, _lm*, _gc*, _ges* → RAC). The grouping is my own, and 3,929 (55%) don't match a pattern and stay in "Other".
| Area (name patterns) | Total | SESSION | SYSTEM imm. | SYSTEM def. | STATIC | PDB-mod |
|---|---|---|---|---|---|---|
RAC / cluster (_lm, _gc, _gcs, _gcr, _ges, _lms, _ksxp, _ksipc, …) | 699 | 16 | 385 | 0 | 298 | 28 |
Optimizer / cursor (optim, _unnest, _hash_join, _nlj, _cursor, _bloom, …) | 381 | 348 | 24 | 1 | 8 | 355 |
In-Memory (_inmemory, _imcs, …) | 255 | 91 | 142 | 0 | 22 | 179 |
Diagnostics / monitoring (_diag, _hang, _sqlmon, _trace, _ash, _awr, …) | 255 | 39 | 176 | 0 | 40 | 47 |
ASM (_asm) | 239 | 91 | 127 | 6 | 15 | 12 |
OLAP / analytic views (_hcs, _olap, _xsolapi) | 206 | 193 | 9 | 0 | 4 | 195 |
Memory / buffer cache (_shared_pool, _kgh, _kgl, _pga, _sga, _db_block, …) | 183 | 19 | 95 | 0 | 69 | 37 |
Parallel execution (_px, _parallel) | 171 | 141 | 17 | 0 | 13 | 139 |
AI Vector Search (_vector) | 171 | 119 | 48 | 0 | 4 | 161 |
LOB / compression (_kdli, _lob, _kdz) | 98 | 98 | 0 | 0 | 0 | 97 |
Multitenant (_pdb, _cdb) | 95 | 65 | 17 | 0 | 13 | 71 |
Redo / recovery (_log, _lgwr, _redo, _recovery, …) | 91 | 3 | 53 | 0 | 35 | 6 |
Exadata smart scan (_kcfis, _cell) | 88 | 85 | 0 | 0 | 3 | 85 |
AQ / replication (_aq, _bufq, _streams, _xstream, …) | 68 | 3 | 51 | 0 | 14 | 9 |
Backup / restore (_backup, _restore, _rman) | 57 | 47 | 7 | 1 | 2 | 48 |
Undo / flashback (_undo, _smu, _rollback, _flashback) | 57 | 5 | 32 | 0 | 20 | 13 |
Data Guard (_dg, _standby, _adg) | 33 | 1 | 27 | 0 | 5 | 2 |
| Other | 3,929 | 1,655 | 1,401 | 12 | 861 | 1,952 |
What the table shows:
- RAC / cluster is the largest named group (699), and it's almost entirely instance-wide: SYSTEM or STATIC, with only 16 SESSION. Cluster messaging and lock management can't differ per session.
- Optimizer: 348 of 381 are SESSION and 355 are PDB-modifiable. This is the one area where a hidden parameter can be tested in a single session, for example with
ALTER SESSIONor theOPT_PARAMhint. - AI Vector Search already has 171 hidden parameters, which shows how much internal tuning surface the newest 26ai feature has.
- Redo / recovery and Undo are mostly SYSTEM or STATIC and rarely PDB-modifiable. They belong to the CDB's shared redo and undo layer.
- Exadata smart scan has 88 parameters even on this non-Exadata install. The parameters exist in every build whether or not the hardware is present.
The query I used:
WITH hp AS (
SELECT i.ksppinm name,
CASE
WHEN REGEXP_LIKE(i.ksppinm,'^_(lm|gc|gcs|gcr|ges|lms|ksxp|ksipc|rac|ipc|cgs|kjdd|dlm|imr)(_|$)') THEN 'RAC / cluster'
WHEN REGEXP_LIKE(i.ksppinm,'^_asm') THEN 'ASM'
WHEN REGEXP_LIKE(i.ksppinm,'^_(kcfis|cell)') THEN 'Exadata smart scan'
WHEN REGEXP_LIKE(i.ksppinm,'^_(inmemory|imado|imcs|imu)') THEN 'In-Memory'
WHEN REGEXP_LIKE(i.ksppinm,'^_vector') THEN 'AI Vector Search'
WHEN REGEXP_LIKE(i.ksppinm,'^_(px|parallel)') THEN 'Parallel execution'
WHEN REGEXP_LIKE(i.ksppinm,'(optim|_unnest|_hash_join|_index_join|_nlj|_like_|_cursor|_bloom|_complex_view|_push_|_gby|_query_rewrite|_cost)') THEN 'Optimizer / cursor'
WHEN REGEXP_LIKE(i.ksppinm,'^_(pdb|cdb)') THEN 'Multitenant (PDB/CDB)'
WHEN REGEXP_LIKE(i.ksppinm,'^_(hcs|olap|xsolapi)') THEN 'OLAP / analytic views'
WHEN REGEXP_LIKE(i.ksppinm,'^_(kdli|lob|kdz)') THEN 'LOB / compression'
WHEN REGEXP_LIKE(i.ksppinm,'^_(aq|bufq|streams|xstream|goldengate)') THEN 'AQ / replication'
WHEN REGEXP_LIKE(i.ksppinm,'^_(log|lgwr|redo|recovery|rcv|fast_start|instance_recovery)') THEN 'Redo / recovery'
WHEN REGEXP_LIKE(i.ksppinm,'^_(backup|restore|rman)') THEN 'Backup / restore'
WHEN REGEXP_LIKE(i.ksppinm,'^_(undo|smu|rollback|flashback)') THEN 'Undo / flashback'
WHEN REGEXP_LIKE(i.ksppinm,'^_(shared_pool|kgh|kgl|pga|sga|memory|mem|large_pool|db_cache|buffer|db_block)') THEN 'Memory / buffer cache'
WHEN REGEXP_LIKE(i.ksppinm,'^_(diag|hang|sqlmon|trace|dump|ash|awr|kse|dbg)') THEN 'Diagnostics / monitoring'
WHEN REGEXP_LIKE(i.ksppinm,'^_(dg|dataguard|standby|adg)') THEN 'Data Guard'
ELSE 'Other' END area,
CASE WHEN BITAND(i.ksppiflg/256,1)=1 THEN 'SESSION'
WHEN BITAND(i.ksppiflg/65536,3) IN (1,3) THEN 'SYSTEM_IMMEDIATE'
WHEN BITAND(i.ksppiflg/65536,3)=2 THEN 'SYSTEM_DEFERRED'
ELSE 'STATIC' END lvl,
BITAND(i.ksppiflg/524288,1) pdb
FROM x$ksppi i
WHERE i.ksppinm LIKE '\_%' ESCAPE '\' AND i.ksppinm NOT LIKE '\_\_%' ESCAPE '\')
SELECT area, COUNT(*) total,
SUM(DECODE(lvl,'SESSION',1,0)) ses,
SUM(DECODE(lvl,'SYSTEM_IMMEDIATE',1,0)) sys_imm,
SUM(DECODE(lvl,'SYSTEM_DEFERRED',1,0)) sys_def,
SUM(DECODE(lvl,'STATIC',1,0)) static,
SUM(pdb) pdb_mod
FROM hp GROUP BY area ORDER BY total DESC;
Which hidden parameters are actually set here?
Filter on KSPPSTDF = 'FALSE':
| Name | Value | Level | PDB-mod |
|---|---|---|---|
__db_cache_size | 1811939328 | SYSTEM (immediate) | NO |
__shared_pool_size | 905969664 | SYSTEM (immediate) | NO |
__java_pool_size | 251658240 | SYSTEM (immediate) | NO |
__large_pool_size | 16777216 | SYSTEM (immediate) | NO |
__shared_io_pool_size | 134217728 | SYSTEM (immediate) | NO |
__sga_target | 3154116608 | STATIC | NO |
__pga_aggregate_target | 1056964608 | SYSTEM (immediate) | NO |
__oracle_base | /u01/app/oracle | STATIC | NO |
__data_transfer_cache_size, __datamemory_area_size, __inmemory_ext_roarea, __inmemory_ext_rwarea, __streams_pool_size, __unified_pga_pool_size | 0 | SYSTEM (immediate) | NO |
_instance_recovery_bloom_filter_size | 1048576 | SYSTEM (immediate) | NO |
None of these can be changed at PDB level. They're all instance-wide memory and environment settings owned by the CDB.
- The
__parameters are the instance recording the current sizes chosen by automatic memory management (about 1.7 GB buffer cache and 864 MB shared pool), persisted in the spfile so the next startup begins from the same place. Nobody "set" these, and you leave them alone. - Only one
_parameter is explicitly set:_instance_recovery_bloom_filter_size. Everything else in this database runs on defaults, which is how it should be.
Hidden parameters behind plan behavior
These are the switches behind behavior I tested in my plan statistics notes. All of them are SESSION level and PDB-modifiable in 26ai:
| Parameter | Value | Controls | Seen in the plan notes |
|---|---|---|---|
_optim_peek_user_binds | TRUE | Bind peeking | Same SQL: full scan for 'NAME_0', index scan for 'NAME_5' |
_optimizer_adaptive_cursor_sharing | TRUE | Different plans per bind value | |
_optimizer_extended_cursor_sharing | UDO | Extended cursor sharing | |
_optimizer_extended_cursor_sharing_rel | SIMPLE | Same, for relational operators | |
_nlj_batching_enabled | 1 | Nested loop batching | The two stacked NESTED LOOPS lines |
_optimizer_batch_table_access_by_rowid | TRUE | Rowid batching | TABLE ACCESS BY INDEX ROWID BATCHED |
_unnest_subquery | TRUE | Subquery unnesting | Needed NO_UNNEST to force the slow correlated subquery |
_index_join_enabled | TRUE | Index joins | VIEW index$_join$_001 |
_hash_join_enabled | TRUE | Hash joins | |
_optimizer_skip_scan_enabled | TRUE | Index skip scan | |
_optimizer_use_feedback | TRUE | Statistics feedback | |
_like_with_bind_as_equality | FALSE | Treat LIKE :b as = | |
_optimizer_gather_stats_on_conventional_dml | FALSE | Real-time stats during DML | Stale num_rows after inserting 90K rows |
_optimizer_use_stats_on_conventional_dml | FALSE | Use those real-time stats | |
_optimizer_cost_model | CHOOSE | Cost model selection | |
_sqlmon_threshold | 5 | Seconds before automatic SQL Monitor | Needed /*+ MONITOR */ for a 0.0004s query |
_sqlmon_max_planlines | 1000 | Longest plan SQL Monitor will track | The talk's 213-line plan fits |
Real-time statistics is shown as FALSE here. Oracle enables that feature only on Exadata and Oracle Cloud platforms, so it's off on this on-premises install.
Prefer the supported ways of changing behavior
| Instead of setting… | Use |
|---|---|
_optim_peek_user_binds | Histograms, adaptive cursor sharing, SQL plan baselines |
_unnest_subquery | UNNEST / NO_UNNEST hints |
_optimizer_batch_table_access_by_rowid | BATCH_TABLE_ACCESS_BY_ROWID / NO_BATCH_... hints |
_hash_join_enabled, _index_join_enabled | USE_HASH, NO_USE_HASH, INDEX_JOIN hints |
_sqlmon_threshold | MONITOR / NO_MONITOR hints |
If Oracle Support does ask you to test one, use the narrowest scope its level allows. For a SESSION-level parameter, that's a single statement with OPT_PARAM:
SELECT /*+ OPT_PARAM('_optimizer_skip_scan_enabled' 'false') */ ...
or ALTER SESSION. Use ALTER SYSTEM only if it's needed, with the SR number recorded. STATIC ones need the spfile and a restart, so plan them as a change window.
Takeaways
- 26ai has 7,076 hidden
_parameters plus 61__auto-maintained ones, against 568 documented. - You need SYS to see them. The modify level is encoded in
X$KSPPI.KSPPIFLG, andGV$PARAMETER's own view definition shows how to decode it. - RAC is the largest named group and is instance-wide. Optimizer is the most session-friendly group (91% SESSION level).
- A healthy database has very few
_parameters set. This one has one. Checking for non-default hidden parameters is a good first step after inheriting a database or upgrading. - Because the list changes every release, re-run the queries on your version rather than copying lists from blogs, including this one.
SQLPlus gotcha I hit while writing this: a line ending in
-, such asPROMPT --- title ---, is treated as a continuation character, so the next SQL line gets swallowed into the prompt. Don't end SQLPlus lines with a hyphen.
