INIT.ORA Parameters

From: Rniemic <rniemic_at_aol.com>
Date: 9 Jun 1999 17:57:45 GMT
Message-ID: <19990609135745.26839.00002440@ng-fg1.aol.com>

A lot of people ask questions about the init.ora, so I figured that I would give a listing of my top parameters (for V8). If this is helpful to people, let me know and I will post more things like it.

Other Init.ora Parameters - My top 25 init.ora parameters (from the Performance Tuning Tips and Techniques book - more detail is in the book):

1. NLS_DATE_FORMAT - Used for Y2K compliance
2. DB_BLOCK_BUFFERS - Blocks of Memory allocated for the data
3. SHARED_POOL_SIZE - Bytes of Memory allocated for data dictionary and SQL &

PL/SQL

4. OPTIMIZER_MODE - Choose, Rule, First_Rows or All_Rows
5. SORT_AREA_SIZE - Memory used for sorting and merging tables
6. SORT_AREA_RETAINED_SIZE - Memory held after the sort for a session
7. LOG_CHECKPOINT_INTERVAL - How often that committed transactions are archived
8. OPEN_CURSORS - Holds user statements to be processed (private area)
9. SHARED_POOL_RESERVED_SIZE - Memory held for future big PL/SQL can help avoid

a shared pool ORA-error
10. DB_BLOCK_SIZE - Size of the blocks (db_block_size x db_block_buffers=bytes for data)
11. DBWR_IO_SLAVES - In Oracle7 DB_WRITERS. More writers from memory to disk 12. DB_FILE_MULTIBLOCK_READ_COUNT - For a DWHSE full scans read more blocks per I/O

13. SORT_DIRECT_WRITES - Writes to temporary segments bypass the buffer cache
14. LOG_BUFFER - Buffer for uncommitted transactions in memory
15. BUFFER_POOL_KEEP - How many buffers to have for pinned objects
16. BUFFER_POOL_RECYCLE - How many buffers to have for new stuff
17. LOG_ARCHIVE_DUPLEX_DEST - Write archives to multiple places
18. LOG_ARCHIVE_MIN_SUCCEED_DEST  - Min. number of archive copies that must

succeed
19. LOCK_SGA - Keeps SGA from being pushed out of memory (not all platforms) 20. PRE_PAGE_SGA - Causes all of the SGA pages to be touched and brought into memory
21. LOCK_SGA_AREAS - Can lock only selective portions of the SGA (not all platforms)
22. COMPATIBLE - Set this or you may miss out on some features 23. LARGE_POOL_SIZE - Total blocks in the large pool allocation for large PL/SQL
24. LARGE_POOL_MIN_ALLOC - Min. bytes for large PL/SQL to allocate in shared pool
25. LOG_SIMULTANEOUS_COPIES - Max. redo buffer copy latches for simultaneous writes

Rich Niemiec
Oracle Performance Tips and Techniques, Oracle Press ISBN: 0078824346 Received on Wed Jun 09 1999 - 12:57:45 CDT