| Tuning Queries Help Required Urgently Oracle 9i [message #65627] |
Wed, 17 November 2004 09:45 |
Pratap Zope
Messages: 23
Registered: July 2004
|
Junior Member |
|
|
Hi All,
Please help me getting answers for following questions.
Thanks in Advance
Best Regards,
Pratap
PS : questions below
--------
137. Which two can be used to monitor locks? (Choose two.)
A. log files
B. Oracle Trace
C. OEM Top Sessions
D. V$LOCK and V$LOCKED_OBJECT
E. OEM Performance Manager
135. Why would you query V$LOCKED_OBJECT?
A. to determine the ID of the block for a lock being held
B. to determine a user's sid and serial# to terminate their session
C. to determine which transaction is causing a lock currently being held
D. to display the number and slot number of the rollback segment and the ID of the table being modified for a lock currently being held
126. Which three statements regarding Automatic Undo Management are true? (Choose three.)
A. No undo segment information is stored in the SYSTEM tablespace.
B. The UNDO_MANAGEMENT initialization parameter should be set to AUTO.
C. Each instance in a Real Application Clusters environment must have its own undo tablespace.
D. All undo space is managed automatically, and undo segments CANNOT be manually created, dropped, or altered.
E. If the initialization parameter file contains values for the ROLLBACK_SEGMENTS parameter, the instance will not start.
F. The undo tablespace named in the UNDO_TABLESPACE initialization parameter is automatically created when the
125. A user reports receiving an “ORA-1555: snapshot too old” error. You are currently using automatic undo management and have an ample amount of disk space. What should you do?
A. Decrease the undo retention.
B. Add additional rollback segments.
C. Increase the size of the undo tablespace.
D. Modify the size of the existing rollback segments.
E. Create a second undo tablespace to handle additional undo segments.
101. You are tuning the sort process. Too many I/Os per second are occurring and the CPUs are relatively idle. What should you do?
A. Increase the SORT_AREA_SIZE parameter.
B. Decrease the SORT_AREA_SIZE parameter.
C. Increase the SORT_MULTIBLOCK_READ_COUNT parameter.
D. Decrease the SORT_MULTIBLOCK_READ_COUNT parameter.
48. The PARALLEL_AUTOMATIC_TUNING initialization parameter is set to 'TRUE'. Which statement is true?
A. Parallel execution buffers are allocated from the shared pool only.
B. Oracle automatically computes a value for the LARGE_POOL_SIZE initialization parameter when it is unset.
C. Parallel execution buffers are allocated from the large pool first, then from the shared pool if necessary.
D. Oracle automatically sets it to a value of 2 megabytes when the LARGE_POOL_SIZE initialization parameter is unset.
13. What are two main OLTP requirements? (Choose two)
A. Use bind variables rather than literals in your SQL code.
B. Analyze your tables regularly to refresh optimizer statistics.
C. Create multiple small rollback segments as opposed to a few big ones.
D. Create indexes on all columns that are regularly used in query predicates.
E. Set up appropriate default storage parameter values for dynamic (implicit) space allocation.
64. 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.
81. To provide more free lists for a number of your database segments, what is one of your options?
A. Modify them with the INSERT_ _FREELIST command.
B. Drop and re-create them with the required FREELIST value.
C. Change the default storage parameter of the tablespace(s) where they are stored.
D. Modify the FREELIST_LIMIT parameter in your installation file and restart the instance.
84. Which statement about the LOG_BUFFER initialization parameter is true?
A. The LOG_BUFFER parameter can be changed dynamically.
B. The minimum value for the LOG_FILE parameter is 512KB.
C. The LOG_BUFFER parameter value must be a multiple of the database block size.
D. The LOG_BUFFER parameter value must be a multiple of the operating system block size.
85. You determined that the values for REQUEST_FAILURES as seen from
V$SHARED_POOL_RESERVED is more than zero and always increasing. Which two actions would
be appropriate? (Choose two)
A. Decrease the value for LARGE_POOL_SIZE parameter.
B. Increase the value for LARGE_POOL_SIZE parameter.
C. Increase the value for SHARED_POOL_SIZE parameter.
D. Decrease the value for SHARED_POOL_SIZE parameter.
E. Increase the value for SHARED_POOL_RESERVED_SIZE parameter.
F. Decrease the value for SHARED_POOL_RESERVED_SIZE parameter.
90. When setting multiple LRU latches in your initialization parameter file, what might you also consider
setting?
A. One buffer pool for each latch.
B. One DBWn process for each latch.
C. At one shared server for each latch.
D. At least two DBWn processes for each latch.
107. In a dictionary-managed tablespace, the SMON background process periodically coalesces
neighboring free extents when which condition is true?
A. When there is more than one data file in the tablespace.
B. When the value of PCTINCREASE for the tablespace is zero.
C. When the value of PCTINCREASE for the tablespace is NOT zero.
D. When the value of INITIAL_EXTENT for the tablespace is greater than 1 MB.
134. You should query the buffer cache hit ratio during normal working loads, but not immediately after
startup. Why?
A. There are no statistics to query because there is no database activity.
B. Statistics are only available once the database has been active for a period of time.
C. Querying the buffer cache statistics immediately after database startup will yield incomplete results.
D. The buffer cache is empty when the instance starts, resulting in more physical reads after startup.
136. Which two statements are true regarding automatic undo segments? (Choose two)
A. Undo tablespaces must be created as DICTIONARY MANAGED.
B. Undo tablespaces must be created as LOCALLY MANAGED.
C. Any tablespace can be used for automatic undo segment management.
You specify which tablespace to use with the init.ora parameter UNDO_TABLESPACE.
D. An undo tablespace can only be dropped if it is NOT the active undo tablespace for the database, and
all transactions using that undo tablespace have committed or rolled back.
141. Which three actions avoid the disadvantages of dynamic extent allocation? (Choose three)
A. Increase the PCTINCREASE on the table.
B. Create locally managed tablespaces for the objects that extend continuously.
C. Coalesce the tablespace to force neighboring free extents to be merged together.
D. Monitor segments ready to extend and manually add the extents during off peak hours.
E. Export and import all the tables in a tablespace to reorganize the blocks in the tables.
F. Choose storage parameters that allocate extents large enough to accommodate all of your data when
you create the object.
146. Which two statements are true regarding tuning the Data Dictionary Cache? (Choose two)
A. The ratio of GETMISSES to GETS found in the V$ROWCACHE view should be less than 5
percent.
B. If the hit ratio for the library cache is acceptable, the hit ratio for the data dictionary should also be
acceptable.
C. On instance startup, the data dictionary cache contains no data, so any SQL statement is likely to
result in cache misses.
D. On instance startup the data dictionary cache is automatically loaded with the most commonly used
information, so many SQL statements will not cause cache misses.
148. Which two statements are true of the buffer cache? (Choose two)
A. In Oracle9i, the buffer caches can be individually sized.
B. Blocks in the buffer cache may be in one of several states.
C. In Oracle9i additional buffer caches can be dynamically added.
D. The blocks in the buffer cache are managed using two or more lists, depending on the number of
buffer caches in use.
E. Blocks in the buffer cache may be in one of two states, either free or dirty.
167. Which is a correct guideline for resolving wait times on the request queue when using Oracle Shared
Servers?
A. Reduce the number of dispatchers, thus reducing the requests to a number that is manageable by the
shared servers.
B. Increase the size of the large pool, so that less time is spent performing I/O connected with the
session data.
C. Increase the size of the shared pool, so that less time is spent performing I/O connected with the
session data.
D. If the value of SERVERS_HIGHWATER reaches the value set for the MAX_SHARED_SERVERS
initialization parameter, consider raising the value of SHARED_SERVERS.
184. 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.
186. 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.
---------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
