| 1. | Which of the following should be a tuning goal? |
| a. | | Access the least possible number of blocks from disk |
| b. | | Use as much memory as possible |
| c. | | Use multiple copies of the code in memory |
| d. | | Access the most possible number of blocks from disk
|
|
|
|
|
|
|
| 2. | Which of the following statements are true regarding the effect of frequent check-pointing on a database? |
| a. | | Can slow down a commit |
| b. | | Can affect instance recovery time |
| c. | | Can cause the CKPT process to hang |
| d. | | Can be influenced by redo log file size
|
|
|
|
|
|
|
| 3. | There is a 512-byte OS block size. Every time 10K of data I written from the Redo Log Buffer to the online redo log, a checkpoint event should occur. Which initialization parameter setting will achieve this? |
| a. | | LOG_BUFFER=10240000 |
| b. | | LOG_CHECKPOINT_BYTES=10 |
| c. | | LOG_CHECKPOINT_TIMEOUT=10 |
| d. | | LOG_CHECKPOINT_INTERVAL=20
|
|
|
|
|
|
|
| 4. | Which view shows the number of full table scans? |
| a. | | V$SYSSTAT |
| b. | | V$FILESTAT |
| c. | | V$SESSION |
| d. | | V$DATAFILE
|
|
|
|
|
|
|
| 5. | The KEEP buffer pool and issue ANALYZE…. ESTIMATE STATISTIC command are being sized. Which data dictionary tables should be queried to obtain the total number of blocks required for an object? |
| a. | | DBA_TABLES |
| b. | | DBA_INDEXES |
| c. | | DBA_SEGMENTS |
| d. | | DBA_CLUSTERS
|
|
|
|
|
|
|
| 6. | Summing the system statistics, (db blocks gets + consistent gets) gives the total number of requests. What is the other system that statistic required to calculate the buffer cache hit ratio? |
| a. | | Physical reads |
| b. | | Session logical gotten |
| c. | | Table scan blocks gotten |
| d. | | DBWR buffers scanned
|
|
|
|
|
|
|
| 7. | Which tablespace is used as the temporary tablespace if 'TEMPORARY TABLESPACE' is not specified for a user? |
| a. | | TEMP |
| b. | | SYSTEM |
| c. | | DATA |
| d. | | ROLLBACK
|
|
|
|
|
|
|
| 8. | When a parallel query is used to perform a sort, what is the total amount of memory a factor to? |
| a. | | SORT_AREA_SIZE * 2 |
| b. | | SORT_AREA_SIZE * degree of parallelism |
| c. | | SORT_AREA_SIZE * divided up equally among the parallel query servers |
| d. | | SORT_AREA_SIZE *2* degree of parallelism
|
|
|
|
|
|
|
| 9. | Which types of tuning session scopes can Oracle Expert provide? |
| a. | | Session |
| b. | | Instance |
| c. | | Structure |
| d. | | Operating system
|
|
|
|
|
|
|
| 10. | Which statement is true when evaluating the buffer cache hit ratio? |
| a. | | The buffer cache hit ratio is unaffected by data or application design |
| b. | | The buffer cache hit ratio will improve the use of full table scans |
| c. | | The buffer cache hit ratio will always improve when the number of db block buffers in the SGA is increased |
| d. | | Minimizing physical reads will improve the buffer cache hit
|
|
|
|
|
|
|
| 11. | Why can a value of zero be NEVER achieved in the GETMISSES column of V$ROWCACHE? |
| a. | | The database buffer cache can never be empty |
| b. | | Recursive SQL has to be reparsed each time it is used |
| c. | | Object definition must be loaded into the shared pool following instance startup |
| d. | | An object cannot be pinned in the shared pool with the DBMS_SHARED_POOL package until it has been at least once
|
|
|
|
|
|
|
| 12. | When the archive process encounters an error, which parameter determines the directory where trace files are written? |
| a. | | BACKGROUND_DUMP_DEST |
| b. | | UTL_FILE_DIR |
| c. | | CORE_DUMP_DEST |
| d. | | LOG_ARCHIVE_DEST
|
|
|
|
|
|
|
| 13. | Which statement about the PCTFREE parameter is true? |
| a. | | If zero update activity is expected on the data, the PCTFREE value should be set to 100 to maximize data block utilization |
| b. | | The PCTFREE parameter sets the minimum percentage of a data block that can be used for future inserts of new rows in that block |
| c. | | A smaller PCTFREE is suitable for segments that are frequently updated, and will improve update performance |
| d. | | The PCTFREE parameter sets the minimum percentage of a data block to be reserved for future updates to rows that already exist in that block
|
|
|
|
|
|
|
| 14. | Which condition causes the message "ORA - 29554: unhandled Java out of memory condition"? |
| a. | | JAVA_MAX_SESSIONSPACE_SIZE is reached |
| b. | | JAVA_SOFT_SESSIONSPACE_LIMIT is reached |
| c. | | A user's session-duration state exceeds 1 M |
| d. | | Java Pool 'memory in use' is equal to the JAVA_POOL_SIZE setting
|
|
|
|
|
|
|
| 15. | What is the correct syntax to ensure an object is not aged out of the shared pool? |
| a. | | EXECUTE dbms_shared_pool.keep('package_name'); |
| b. | | EXECUTE dbms_shared_pool.pin('package_name'); |
| c. | | EXECUTE dbms_shared_pool.unkeep('package_name'); |
| d. | | EXECUTE dbms_shared_pool.retain('package_name'); |
| e. | | EXECUTE dbms_shared_pool.refresh('package_name');
|
|
|
|
|
|
|
| 16. | Which actions can help avoid the disadvantages of dynamic extent allocation? |
| a. | | Use locally managed tablespaces |
| b. | | Use Oracle default sizing |
| c. | | Size the segments appropriately |
| d. | | Use globally managed tablespaces |
| e. | | Use recursive space management operations
|
|
|
|
|
|
|
| 17. | There are several tables which are updated frequently. These updates are populating null columns with data. Which sizing would be least likely to cause migration? |
| a. | | DB_BLOCK_SIZE = 8192; PCTFREE 5 |
| b. | | DB_BLOCK_SIZE = 4096; PCTFREE 10 |
| c. | | DB_BLOCK_SIZE = 8192; PCTFREE 0 |
| d. | | DB_BLOCK_SIZE = 2048; PCTFREE 20
|
|
|
|
|
|
|
| 18. | Which of the following commands force DBWR to write dirty buffers to disk? |
| a. | | CMOOT |
| b. | | ROLLBACK |
| c. | | SHUTDOWN IMMEDIATE |
| d. | | ALTER TABLESPACE A BEGIN BACKUP
|
|
|
|
|
|
|
| 19. | Which dynamic view is most useful for determining the hit ratio for multiple buffer pools? |
| a. | | V$CACHE |
| b. | | V$SYSSTAT |
| c. | | V$BUFFER_POOL |
| d. | | V$BUFFER_POOL_STATISTICS
|
|
|
|
|
|
|
| 20. | It is determined that the value for REQUEST_FAILURES as seen from V$SHARED_POOL_RESERVED is more than zero and always increasing. What action would be appropriate? |
| a. | | Increase the value for the LARGE_POOL_SIZE parameter |
| b. | | Decrease the value for the LARGE_POOL_SIZE parameter |
| c. | | Increase the value for the SHARED_POOL_RESERVED_SIZE parameter |
| d. | | Decrease the value for the SHARED_POOL_RESERVED_SIZE parameter
|
|
|
|
|
|
|
| 21. | STATSPACK report shows a buffer busy waits. Detailed analysis shows that it's related with freelists contention. What should be done to fix this issue and add freelists? |
| a. | | Search for a segment header, causing contention and issue the command ALTER TABLE…FREELIST |
| b. | | Search for a data block, causing contention and issue the command ALTER TABLE … FREELIST |
| c. | | Search for a data block, causing contention and issue the command ALTER TABLESPACE … FREELIST |
| d. | | Search for a segment header, causing contention and issue the command ALTER TABLESPACE … FREELIST
|
|
|
|
|
|
|
| 22. | What is PGA_AGGREGATE_TARGET? |
| a. | | Initialization parameter to enable automatic PGA memory management |
| b. | | Initialization parameter for sort-based operators |
| c. | | Initialization parameter for hash-join operations |
| d. | | Initialization parameter for bitmap merge and bitmap create operations
|
|
|
|
|
|
|
| 23. | By setting which init.ora parameter can the DBA modify the default locking mechanism? |
| a. | | ROW_LOCKING=DEFAULT |
| b. | | ROW_LOCKING=INTENT |
| c. | | ROW_LOCKING=ROW |
| d. | | ROW_LOCKING=TABLE
|
|
|
|
|
|
|
