Q. Which four activities cause a sort? (Choose four)
A. Creation of an index.
B. Execution of the ANALYZE command.
C. Use of the ORDER BY and GROUP BY clauses.
D. Use of insert operations involving index maintenance.
E. Use of the UNION, INTERSECT and MINUS operators.
Answer: A, C, D, E
Explanation:
All these activities can cause a sort operation. Only if you are creating an index with NOSORT option sorting will not have place.
B: Execution of the ANALYZE command does not require sorting.
Q. The Accounting department has contacted you concerning their perceived poor performance of the new accounting package that was installed last week. Which three actions would help diagnose the problem? (Choose three)
A. Query the DBA_ROLLBACK_SEGs table to see if there is contention for rollback segment headers while the accounting application is being run.
B. Query DBA_DATA_FILES while the accounting application is being used to determine if any of the data files are being used excessively.
C. Query the V$SQL_PLAN table to determine which, if any, indexes are being used for the accounting tables.
D. Query the V$FILESTAT table while the accounting application is being used to determine if any of the data files are being used excessively.
E. Query the V$LOGFILE table to see if an excessive amount of redo is being generated while the accounting application is being run.
F. Generate performance monitoring reports using UTLBSTAT and UTLESTAT or the STATSPACK while the account application is being used and compare them to your previously generated base line reports from the same tool.
Answer: C, D, F
Explanation:
The V$SQL_PLAN view provides a way of examining the execution plan for cursors that were executed and are still cached. The V$FILESTAT view contains detailed file I/O statistics for each file, including the number of I/Os for each file and the average read time. Generate performance monitoring reports using UTLBSTAT and UTLESTAT or the STATSPACK will also help to find the reason of poor performance.
A: The DBA_ROLLBACK_SEGS data dictionary view stores information about the rollback segments of a database. But this information cannot be used to check contention for rollback segment headers.
B: The DBA_DATA_FILES data dictionary view provides descriptive information about each datafile,including the tablespace to which it belongs and the file id. The file id can be used to join with other views for detail information, but itself it will not give you information if any of the data files are being used excessively.
E: This view just identifies redo log groups and members and member status.
Q. Which three are used when administering systems with the database Resource Manager? (Choose three)
A. Histograms
B. Explain Plan
C. Resource Plan
D. Resource User Group
E. Resource Consumer Group
F. Resource Plan Directives
G. Resource Plan Assignments
Answer: C, E, F
Explanation:
Resource Plan, Resource Consumer Group and Resource Plan Directives are used when administering systems with the database Resource Manager.
A: Histograms are not used with the database Resource Manager.
B: Explain Plan has no relation with the database Resource Manager.
D: There is Resource Consumer Group, not Resource User Group in Resource Manager.
G: There is no Resource Plan Assignments for Resource Manager.
Q. There are two users, John and Susan, who are updating the EMPLOYEE table in the following order:
1. First, John issues this SQL update:UPDATE EMPLOYEE SET LAST_NAME = ‘SMITH’ where ID=200;
2. Next, Susan issues this SQL update:UPDATE EMPLOYEE SET SALARY=50000 WHERE ID=250;
3. Next, John issues this SQL update:UPDATE EMPLOYEE SET LAST_NAME ‘BAKER’ WHERE ID=250;
4. FINALLY, Susan issues this SQL update:UPDATE EMPLOYEE SET SALARY=60000 WHERE ID=200;What will be the result?
A. Oracle kills Susan’s session to prevent a deadlock.
B. Oracle will detect a deadlock and roll back Susan’s session.
C. Oracle kills both John’s and Susan’s statements to prevent a deadlock.
D. Both John’s and Susan’s sessions would hang indefinitely because of a deadlock.
E. Oracle will detect a deadlock and roll back the statement causing the deadlock.
Answer: B
Explanation:
Oracle will detect a deadlock and roll back the Susan’s session: session detected a deadlock.
A: Oracle will detect a deadlock FIRST and roll back the statement detected a deadlock. It will be Susan’s session.
C: Oracle will not kill both statements, only one, detecting a deadlock.
D: Both John’s and Susan’s sessions would not hang indefinitely: Oracle will handle a deadlock in most cases.
E: Oracle will detect a deadlock and roll back the statement DETECTED the deadlock, not causing it.
Q. Which two statements are valid settings for the initialization parameter ‘USE_STORED_OUTLINES’? (Choose two)
A.SQL> alter session set USE_STORED_OUTLINES = TRUE;
B.SQL> alter session set USE_STORED_OUTLINES = PUBLIC;
C.SQL> alter session set USE_STORED_OUTLINES = PRIVATE;
D.SQL> alter session set USE_STORED_OUTLINES = DEFAULT;
D.SQL> alter session set USE_STORED_OUTLINES =;
F.SQL> alter session set USE_STORED_OUTLINES = ;
Answer: A, F
Explanation:
There are two valid settings for the initialization parameter USE_STORED_OUTLINES: TRUE or category name.
B: This statement is incorrect.
C: This statement is incorrect.
D: This statement is incorrect.
E: This initialization can use category name, not outline name.
Q. Which two statements are true regarding the use of DB_CACHE_ADVICE init.ora parameter?(Choose two)
A. Setting the parameter to READY reserves space in the buffer cache to store information about different buffer cache sizes, but no CPU overhead is incurred.
B. Setting the parameter to READY reserves space in the shared pool to store information about different buffer cache sizes, but no CPU overhead is incurred.
C. Setting the parameter to ON reserves space in the buffer cache to store information about different buffer cache sizes, and CPU overhead is incurred as statistics are collected.
D. The V$DB_CACHE_ADVICE view contains information that predicts the estimated number of physical reads for different cache sizes for each buffer cache setup in the SGA.
Answer: C, D
Explanation:
Each cache size simulated has its own row in this view, with the predicted physical I/O activity that would take place for that size. The DB_CACHE_ADVICE parameter is dynamic, so the advisory can be enabled and disabled dynamically to allow you to collect advisory data for a specific workload.Two minor overheads are associated with this advisory:• CPU: When the advisory is enabled, there is a small increase in CPU usage, because additional bookkeeping is required.Memory: The advisory requires memory to be allocated from the shared pool (about 100 bytes for each buffer).The V$DB_CACHE_ADVICE view is populated when the DB_CACHE_ADVICE parameter is set to ON. This view shows the simulated miss rates for a range of potential buffer cache sizes.
A: The DB_CACHE_ADVICE init.ora parameter should be set to ON, not READY.
B: The DB_CACHE_ADVICE init.ora parameter should be set to ON, not READY. Also it reserves space in the buffer cache, not the shared pool.
Q. Which three statements are true with respect to has clusters? (Choose three)
A. Full table scans are generally faster on cluster tables than on non-clustered tables.
B. Hash clusters may be desirable when the number of key values is predictable and key values are evenly distributes.
C. If tables in a hash cluster require more space than the initial allocation for the cluster, performance degradation can be substantial because overflow blocks are required.
D. Storing a single table in a hash cluster can be useful regardless of whether the table is joined frequently with other tables or not, provided other criteria for choosing a hash cluster are met.
Answer: A, B, C
Explanation:
These statements are true with respect to clusters.
D: Storing a single table in a hash cluster cannot be useful in some cases. It’s better to use clusters if table is joined frequently with other tables.
Q. Which script creates views that will tell you who is holding or waiting for which tables?
A. CATPROC.sql
B. CATLOCK.sql
C. CATPERF.sql
D. CATBLOCK.sql
E. CATBPROC.sql
Answer: D
Explanation:
A CATBLOCK.sql script creates the lock views that UTLOCKT.sql script needs, so you must run it before running UTLOCKT.sql.
A: CATPROC.sql script is required to load the initial PL/SQL environment.
B: There is no CATLOCK.sql script in Oracle9i.
C: There is no CATPERF.sql script in Oracle9i.
E: There is no CATBPROC.sql script in Oracle9i.
Q. You configured your database to use the Oracle Shared Server, but you did not configure the large pool. Where in memory will Oracle store user information?
A. PGA
B. JAVA POOL
C. LARGE POOL
D. SHARED POOL
E. SHARED SQL AREA
F. SHARED POOL RESERVED AREA
Answer: D
Explanation:
It will be stored in SHARED POOL area if it is not LARGE POOL is configured.
A: PGA will not be used for this purpose.
B: SHARED POOL area will be used to store user information, not JAVA POOL.
C: LARGE POOL is not configured as question states.
E: There is no SHARED SQL AREA in Oracle9i.
F: SHARED POOL area will be used to store user information, not SHARED POOL RESERVED AREA.
Q. You have a 512-byte OS block size. You want to cause a checkpoint event to occur every time 10K of data has been written from the Redo Log Buffer to the online redo log.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
E. LOG_CHECKPOINT_INTERVAL=10
F. LOG_CHECKPOINT_TIMEOUT=5120
Ans: D
Q. You just created a resource plan and placed this line in the init.ora RESOURCE_MANAGER_PLAN = day_oltp What does ‘day_oltp’ specify?
A. Resource plan.
B. Plan directive.
C. Consumer group.
D. Resource manager privilege.
Ans: A
Q. Why do these steps eliminate row migration?
Step 1: Run ANALYSE TABLE ….. LIST CHAINED ROWS command
Step 2: Copy the rows to another table
Step 3: Delete the rows from the original table
Step 4: Insert the rows from step 2 back into the original table
A. Migration only occurs during an UPDATE operation.
B. The migrated rows are removed with the DELETE command.
C. Migration is automatically removed with the ANALYZE command..
Ans: A
Q. After running a query using V$DISPATCHER, you increase the number of dispatchers. What would cause you to take this action?
A. Users are waiting on a listener process.
B. Users are waiting in dispatch processes.
C. Users are waiting on shared server processes.
D. Users are waiting on their dedicated connection process.
Ans: B
Q. Which three statements regarding the SECONDS_IN_WAIT value for the log buffer space event in the V$SESSION_WAIT view are true? (Choose three).
A. A SECONDS_IN_WAIT value close to zero is ideal.
B. A nonzero value in the SECONDS_IN_WAIT may indicate disk I/O contention on the redo log files.
C. The SECONDS_IN_WAIT value of the log buffer space event indicated time spent waiting for space in the redo log buffer.
D. A nonzero value in the SECONDS_IN_WAIT may be an indication the redo log buffers are too large and log switchers are not occurring fast enough.
Ans: A, C & D
Q.Which two parameters significantly impact the manual stripe size of the data files? (Choose two)
A. DB_BLOCK_SIZE
B. REDO_LOG_BUFFERS
C. DB_BLOCK_BUFFERS
D. DB_BLOCK_MAX_DIRT_TARGET
E. DB_FILE_MULTIEBLOCK_READ_COUNT
Ans: A & E
Q. What are the two main benefits of index-organized tables? (Choose two)
A. More concurrency.
B. Faster full table scans.
C. Fast primary key-bases access.
D. Less contention on the segment header.
E. No duplication of primary key values storage.
Ans: C & E
Q. Which view shows the number of full table scan?
A. V$SYSSTAT
B. V$FILESTAT
C. V$SESSION
D. V$DATAFILE
Ans: A
Q. Which two views would you query to monitor sessions relates statistics? (Choose two)
A. V$SESSTAT
B. V$SESSION_EVENT
C. V$SESSION_STATS
D. V$SESSION_STATUS
E. V$WAITS_PER_SESSION
Ans: A & B
Q. 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 *2* degree of parallelism.
D. SORT_AREA_SIZE * divided up equally among the parallel query servers.
E. SORT_AREA_SIZE * parallel query server take turns at using the memory..
Ans: C
Q. What is the least number of buffers an LRU latch must cover in the database buffer cache?
A. 5
B. 10
C. 30
D. 50
E. 100
Ans: D
Continue.....