1. DB_CACHE_SIZE Initial memory allocated to data cache or memory used for data itself.
2. SGA_TARGET If you use Oracle’s Automatic Shared Memory Management, this
parameter is used to automatically determine the size of your data cache, shared pool,
large pool, and Java pool (see Chapter 1 for more information). Setting this to 0 disables it.
3. PGA_AGGREGATE_TARGET Soft memory cap for total of all users’ PGAs.
4. SHARED_POOL_SIZE Memory allocated for data dictionary and SQL and PL/SQL.
5. SGA_MAX_SIZE Maximum memory that the SGA can dynamically grow to.
6. OPTIMIZER_MODE CHOOSE, RULE, FIRST_ROWS, FIRST_ROWS_n or ALL_ROWS.
Although RULE is definitely desupported and obsolete and people are often scolded for
even talking about it, I was able to set the mode to RULE in 10g.
7. CURSOR_SHARING Converts literal SQL to SQL with bind variables, reducing parse
8. OPTIMIZER_INDEX_COST_ADJ Coarse adjustment between the cost of an index scan
and the cost of a full table scan. Set between 1 and 10 to force index use more frequently.
Setting this parameter to a value between 1 and 10 would pretty much guarantee index
use, even when not appropriate, so be careful, since it is highly dependent on the index design and implementation being correct. Please note that if you using Applications 11i:
Setting OPTIMIZER_INDEX_COST_ADJ to any value other than the default (100) is not
supported (see Note 169935.1). Also, see bug 4483286.
9. QUERY_REWRITE_ENABLED Used to enable Materialized View and Function-Based-
Index capabilities and other features in some versions.
10. DB_FILE_MULTIBLOCK_READ_COUNT For full table scans to perform I/O more
efficiently, this reads this many blocks in a single I/O.
11. LOG_BUFFER Buffer for uncommitted transactions in memory (not dynamic; set in pfile).
12. DB_KEEP_CACHE_SIZE Memory allocated to keep pool or an additional data cache
that you can set up outside the buffer cache for very important data that you don’t want pushed out of the cache.
13. DB_RECYCLE_CACHE_SIZE Memory allocated to a recycle pool or an additional data
cache that you can set up outside the buffer cache and in addition to the keep cache
described in the preceding item. Usually, DBAs set this up for ad hoc user query data that has queries that are poorly written.
14. DBWR_IO_SLAVES (also DB_WRITER_PROCESSES if you have async I/O) Number
of writers from SGA to disk for simulated async I/O. If you have async I/O, then you
use DB_WRITER_PROCESSES to set up multiple writers to more quickly write out dirty
blocks during a database write (DBWR).
15. LARGE_POOL_SIZE Total blocks in the large pool allocation for large PL/SQL and a few other Oracle options less frequently used.
16. STATISTICS_LEVEL Used to enable advisory information and optionally keep
additional OS statistics to refine optimizer decisions. TYPICAL is the default.
17. JAVA_POOL_SIZE Memory allocated to the JVM for Java stored procedures.
18. JAVA_MAX_SESSIONSPACE_SIZE Upper limit on memory that is used to keep track
of user session state of JAVA classes.
19. MAX_SHARED_SERVERS Upper limit on shared servers when using shared servers.
20. WORKAREA_SIZE_POLICY Used to enable automatic PGA size management.
21. FAST_START_MTTR_TARGET Bounds time to complete a crash recovery. This is
the time (in seconds) that the database will take to perform crash recovery of a single instance. If you set this parameter, LOG_CHECKPOINT_INTERVAL should not be set to 0. If you don’t set this parameter, you can still see your estimated MTTR (mean time to recovery) by querying V$INSTANCE_RECOVERY for ESTIMATED_MTTR.
22. LOG_CHECKPOINT_INTERVAL Checkpoint frequency (in OS blocks—most OS blocks
are 512 bytes) where Oracle performs a database write of all dirty (modified) blocks to the datafiles in the database. Oracle will also perform a checkpoint if more the one quarter of the data buffers are dirty in the db cache and also on any log switch. The LGWR (log writer) also updates the SCN in the control files and datafiles with the SCN of the checkpoint.
23. OPEN_CURSORS Specifies the size of the private area used to hold (open) user
statements. If you get “ORA-01000: maximum open cursors exceeded,” you may need
to increase this parameter, but make sure you are closing cursors that you no longer need. Prior to 188.8.131.52, these open cursors were also cached and at times caused issues (ORA-4031) if OPEN_CURSORS was set too high. In 9.2.05, SESSION_CACHED_CURSORS now controls the setting of the PL/SQL cursor cache. Do not set the
parameter SESSION_CACHED_CURSORS as high as you set OPEN_CURSORS or
you may experience ORA-4031 or ORA-7445 errors.
24. DB_BLOCK_SIZE Default block size for the database. A smaller block size will reduce contention by adjacent rows, but a larger block size will lower the number of I/Os needed to pull back more records. A larger block size will also help in range scans where the blocks desired are sequentially stored.
25. OPTIMIZER_DYNAMIC_SAMPLING Controls the number of blocks read by the dynamic
sampling query. Very useful with systems that are using Global Temporary Tables.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13750068/viewspace-611233/，如需转载，请注明出处，否则将追究法律责任。