1Z0-033 - Oracle9i Database: Performance Tuning
Go back to Oracle
When a checkpoint occurs, the CKPT process ______.
Updates the control files to record the details of the checkpoint.
The ORDERS table has millions of rows and is accessed very often with an index (ORDID_NDX) on a primary key (ORD_ID). Where should ORDERS and ORDID_NDX be stores?
Different tablespaces on different disks.
You decide to create an index-organized table in your database. The table would hold a large number of rows and different departments would work only with the relevant rows in the table concurrently. Which statement regarding the above table can enhance performance?
It can be partitioned.
Which are three possible causes of lock contention? (Choose three)
Developers coding unnecessarily long transactions.
Developers coding unnecessarily high locking levels.
Applications converted from a non-Oracle database imposing higher locking levels.
You noticed that the LGWR process is slow in freeing the redo log buffers. On querying the V$SYSTEM_EVENT view, you find that some time is spent on waiting for the log file switch (archiving needed) event. The archiving directories are not full. What should you do to minimize the waits? (Choose all that apply.)
increase the maximum number of archive processes by using the LOG_ARCHIVE_MAX_PROCESSES parameter
add redo log groups
The values for the resource plan directives are set as follows: SWITCH_GROUP = OLAP SWITCH_TIME = 1000 SWITCH_ESTIMATE = True What are the two implications of these values on a running session belonging to the consumer group for which these directives are set? (Choose two.)
The resource manager can switch the session to the OLAP group before an operation even starts running.
The resource manager switches the session to the online analytical processing (OLAP) group if the session is active for more than 1,000 seconds and after the operation the session is moved back to the original group.
After analyzing an index segment, you find that the B-level is high and most of the index blocks contain very few entries due to a large number of DELETE operations. Which statement is true?
Index segment blocks with 100% deleted entries are put into the free list.
Which statement requires an exclusive DDL lock?
DROP TABLE statement
When should you recommend changing the application in order to reuse more SQL?
When the ratio of GETHITS to GETS in the V$LIBRARYCACHE view is less then 0.9.
Which type of table is the best candidate to be cached?
Small table frequently retrieved with a full table scan.
You are working on the performance tuning of your database. What kind of performance tuning-related information would an alert log file provide? (Choose all that apply.)
values of the non-default initialization parameters when the instance was started
the time taken to archive logs
Which latch would be required when dirty blocks are written to the disk or when a server process is searching for blocks to write to?
Cache buffers LRU chain latch.
You moved your test database to the production environment. As a performance measure, you want to create a performance baseline for the production database that would have general performance statistics, SQL plans and usage, segment-level statistics, and parent and child latches. Which option would you use to create the baseline?
use the Statspack with the highest snap level
Which two views can be used to detect lock contention? (Choose two)
You are working in an online transaction processing (OLTP) environment. The NORTH_SALES table contains one million rows. In which case would you issue the following command: SQL> ALTER TABLE north_sales STORAGE(BUFFER_POOL recycle);
The NORTH_SALES table is accessed once a week.
Which two statements are valid regarding row migration? (Choose two.)
It is caused by an UPDATE statement that increases the data in a row so that the new larger version of the row no longer fits in its data block.
The row migration causes more than one block to be read when a row is accessed.
You executed the following query to know about users and their consumer groups: SQL> SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS; GRANTEE GRANTED_GROUP GRANT_OPTION INITIAL_GROUP --------------- --------------------------- -------------------- -------------------- SCOTT LOW_GROUP NO NO PUBLIC LOW_GROUP NO NO PUBLIC DEFAULT_CONSUMER_GROUP YES YES SYSTEM SYS_GROUP NO YES If the user SCOTT starts a session, to which consumer group will the session belong?
The session belongs to DEFAULT_CONSUMER_GROUP.
Which type of change to an application is most likely to improve performance of the library cache?
Reusing as much generic code as possible.
You observe performance degradation during INSERT and UPDATE operations. Investigation reveals that it is due to several recursive SQL statements that are generated for finding free space and adding the extent. Identify two solutions to avoid these recursive calls. (Choose two.)
create locally managed tablespaces
monitor the segments ready to extend and extend if required
You are working on a test database. The DML monitoring feature is enabled for all the tables that support monitoring. You executed the following command to gather statistics: exec dbms_stats.gather_schema_stats( ownname => NULL, options => 'GATHER AUTO' ); Which statement is correct regarding the outcome of this command?
It implicitly determines which objects need new statistics and gathers all necessary statistics automatically.
Where can you find the nondefault parameters when the instance is started?
Jones and a couple of other users complain that their transactions on one of the application tables, TECH, are waiting for a response. On investigation, you find that one of the users, Smith, has not committed his transaction on the TECH table and he is not at his desk. What would you do to release the lock irrespective of the end result of Smith's transaction?
kill Smith's session
Your session runs out of memory while loading Java classes. The loadjava tool reports failure as "connection lost." Which two initialization parameters should you modify to avoid the failure? (Choose two.)
You want SMITH to create database resource plans using database resource manager. To enable this, you decide to grant the ADMINISTER_RESOURCE_MANAGER privilege to SMITH. Which statement regarding this privilege is true?
This privilege is granted through the dbms_resource_manager_privs package.
What is the main reason to create a reverse key index on a column?
The column is populates using sequential numbers.
What are three indications of contention for this rollback segment header? (Choose three)
A nonzero value in the WAITS column of the V$ROLLSTAT view.
A nonzero value in the UNDO HEADER column of the V$WAITSTAT view.
A nonzero value in the Undo Segment TX Slot event of the V$SYSTEM_EVENT view.
When tuning the dictionary cache, which init.ora parameter should you increase if there are too many cache misses due to the dictionary cache being incorrectly sized?
What is a potential reason for a "snapshoot too old" error message?
An ITL entry in a data block has been reused.
While you were on vacation, several new applications were added to a database that you manage. Another DBA did his best to tune the database while you were gone, and increased the buffer cache hit ratio from 85 percent to 94 percent after increasing the size of the DB_CACHE_SIZE from 96 MB to 128 MB. As a result, the size of the SGA is now 256 MB, which is the maximum amount of memory that can be allocated for this database. You have set the init.ora parameter SGA_MAX_SIZE = 256 MB to meet that restriction. In addition, the init.ora parameter SHARED_POOL_SIZE is set to 48 MB. Your problem is that the OLTP applications are not performing with their normal 3-second response time as they did in the past. What can you do to try to get the response time for OLTP applications to less than three seconds?
Check the buffer cache hit ratio to make sure that it is greater than 95 percent. If it is not greater than 95 percent, decrease the SHARED_POOL_SIZE to 32 MB and allocate the remaining 16 MB to the buffer cache by setting the DB_CACHE_SIZE parameter to 144 MB.
Repeatedly deleting large number of rows from tables over a period of time will result in massively wasted extents because unused space below each High-Water Mark is never released. You are unable to issue TRUNCATE <tablename> DROP STORAGE to reset the High-Water Mark because you need to sustain some rows in the tables. What should you do to release the unused space?
Issue ALTER TABLE <tablename> MOVE to move tables in Qto healthy tablespace(s).
Which statements are true regarding configuring multiple buffer pools in the Database Buffer Cache that is using only the standard block size? (Choose all that apply.)
Either the KEEP pool or the RECYCLE pool can be configured along with the DEFAULT pool.
You executed the following query to view the status of a materialized view. SQL> SELECT mview_name,rewrite_capability,refresh_method FROM user_mviews; MVIEW_NAME REWRITE_CAPABILITY REFRESH_METHOD ITMV TEXTMATCH FORCE Which statement is true regarding the rewrite and refresh mechanism?
The query of the materialized view contains restrictions on the use of query rewrite and the database performs a fast refresh if possible; otherwise, it does a complete refresh.
You word for a manufacturing company that frequently runs its production lines 24 hours a day. The accounting department is working with the development staff on plans to create an application to track manufacturing costs at the shop floor level. As a senior DBA, you have been assigned the task of ensuring that the new application will run with sub-second response time as information is entered into the application via terminals installed in the manufacturing area. Daily, weekly and monthly reports will also be run overnight detailing the previous period's activity. The reports must run during a time of high user interaction with the database. Choose two tasks that you should perform to make sure that the new application meets its sub-second response time requirement. (Choose two.)
Use performance-monitoring tools to gather information about the current workload on the system and again later while testing the application to identify potential bottlenecks within the database.
Help with the design of the tables used in the application to guarantee that every table is normalized to the third normal form for good relational design, and then de-normalize the tables as necessary to achieve the best performance of the application.
Which of these columns are used to calculate the library cache Hit Ratio?
PINS and RELOADES
You executed the following commands to view the statistics: SQL> SELECT table_name,blocks,num_rows FROM dba_tables WHERE table_name='CUST' and owner='SCOTT'; TABLE_NAME BLOCKS NUM_ROWS -------------------- ------------ ------------------ CUST 399 8192 SQL> SELECT index_name,blevel,leaf_blocks,clustering_factor FROM dba_indexes WHERE index_name='CUIND' and owner='SCOTT'; INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR -------------------- ---------- ------------------- -------------------------------- CUIND 2 50 7946 Which statement is true regarding the output?
The index entries randomly point at different data blocks.
After running a query using V$DISPATCHER, you increase the number of dispatchers. What would cause you to take this action?
Users are waiting in dispatch processes.
What does this statement do? SQL.ANALYZE INDEX index_name VALIDITY STRUCTURE;
It places information into the INDEX_STATS view and allows for the monitoring of space used by an index.
Which two parameters could result in problems when starting more shared servers? (Choose two)
You determine that a long-running transaction has used an unnecessarily high-level user-defined lock and is causing contention. Which of these courses of action will eliminate the contention problem without disrupting the transaction?
What is the initialization parameter PGA_AGGREGATE_TARGET used for?
Enabling the automatic sizing of SQL working areas used by memory-intensive SQL operators such as sort, group-by, hash-join, bitmap merge and bitmap create.
When does the Oracle server put index segment blocks back on the free list?
When you delete 100% of the entries from an index block.
The steps involved in copying statistics from the source database to the target database are given below in random order: 1)Export the statistics table from the source database and then import it into the target database. 2)Copy the statistics to a table in the source database. 3)Create a table to hold the statistics in the source database. 4)Copy the statistics into the data dictionary in the target database. 5)Run the Oracle-supplied csminst.sql script to create a schema and a table to hold the statistics in the source database. 6)Collect the system statistics in the target database. What is the correct order to accomplish this task?
3, 2, 1, 4; 5 and 6 are not required
What should you set a small value in a Shared Server environment, because Oracle automatically adapts it?
You configured your database to run in Shared Server mode. You did not configure the large pool by using the LARGE_POOL_SIZE parameter. Which memory component would be used to store users' session information?
the Shared Pool
You are using Oracle Shared Server. If the large pool is not configured, which two are stored in the shared pool instead of in private user memory? (Choose two.)
user session information
cursor state information
You executed the following command. SQL> SELECT paddr,type,wait,totalq FROM v$queue; PADDR TYPE WAIT TOTALQ 00 COMMON 852 6819 709E687C DISPATCHER 21 3444 709E6FFC DISPATCHER 12 3381 For the process 00, the wait column shows a significant increase in the value. Identify two possible solutions for this. (Choose two.)
check the system memory capacity; a low system memory can cause the Shared Servers to run out of memory
increase the value of the MAX_SHARED_SERVERS parameter
Your database is running in Shared Server mode. You have set the LARGE_POOL_SIZE parameter to 1 MB. While the database is functioning, you realize that the large pool is running out of space because of the increasing number of user requests. What will the effect be if there is no more free space left in the large pool to store new information?
User requests will fail but the database instance will continue to function.
For which reason would you query V$SYSSTAT?
Number of disk sorts performed since startup.
When creating a local statistics table with the DBMS_STAT.CREATE_STAT_TABLE procedure, which of the following fields are required? (Choice all that apply.)
In your database, you executed the following command to monitor the ind1 index: SQL> SELECT lf_rows,del_lf_rows,del_lf_rows_len,lf_rows_len FROM INDEX_STATS 2 WHERE NAME = 'IND1'; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN LF_ROWS_LEN -------------- --------------------- --------------------------- -------------------- 14 0 0 202 After this, the table is updated by a large transaction. Now, you want to check the index statistics. What would you do to update the INDEX_STATS view with the latest statistics?
analyze the index with the VALIDATE STRUCTURE option