Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 26 June 2012

Oracle undocumented Parameters

Undocumented parameters also known as hidden parameter which starts with underscore (–) sign. These parameters are not consistent across versions. It changed from version to version. It is recommended the use of these parameter only at the request of oracle support.
The X$KSPPI Table contain all the parameters list (documented + undocumented).
select count(*) from X$KSPPI;
select count(*) from v$parameter;
797-540 =540, parameter is undocumented or hidden in Oracle version 9i R2.
Many of these parameter cause side effects that if you are not aware of them could seriously damage your database. Few of them are helpful (bringing up corrupted database at minimum data loss). So it is important to take complete backup before using them as well as suggested to apply first on test environment before being used on PROD database.
List of undocumented Paramters:
SELECT a.ksppinm "Parameter", a.ksppdesc "Description", b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b,x$ksppsv c
WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '/_%' escape '/'
Some of the useful undocumented parameters and their usage.
Note: Use of undocumented parameters without the knowledge of Oracle Support is a violation of the Support agreement with Oracle.
This parameter will allow the database to be opened with resetlogs even if it causes logical corruption.
During an incomplete recovery the most recent changes are not applied to the database. Then the following steps are performed when opening the database using resetlogs.
  1. Changes in the current redo logs are discarded (not applied to the database) 
  2. The log history in the control file is removed. The previous log files cannot be applied to the database in the future. 
  3. The contents and the current redologs are erased during the ALTER DATABASE OPEN RESETLOGS. 
  4. The online data files are updated with the new resetlogs SCN along with the current checkpoint SCN. 
  5. All offline datafiles must be dropped since redo can not be applied to the database once it is opened using the resetlogs. 
  6. Read only data files have no effect in the resetlogs
This parameter defines what percentage of the shared pool is reserved for the objects greater than the size defined by the underscore parameter shared_pool_reserved_min_alloc. This memory is allocated to large objects when required. This is usually used to avoid the ORA-04031 errors.
This parameter limits the lower size of the PL/SQL objects that are eligible to stay in the reserved area of the shared pool. This avoids excessive fragmentation inside the shared memory and the ORA-04031 errors.
This parameter controls the space allocation inside the large pool. Only objects greater than the size defined by these parameters will be allowed to use the large pool reserved memory.
select * from X$KSMLS; Memory allocation inside the large pool
This parameter is usually used in benchmarking to get more transactions per hour. During a commit, oracle must write the contents of the log buffers to the log files before returning the control to the foreground process
select * from V$system_event;
Setting this parameter to FALSE reduce the waits for log file syncs and improves the OLTP performance. At the same time there may be an inconsistency (or data loss) during the database crash or shutdown abort. Be warned that you will not be able to recover all committed changes and the database may be in an unusable state if it crashes during the benchmark.
For query on undocumented parameter, visit the link:


Post a Comment