1Z0-033 - Oracle9i Database: Performance Tuning

Go back to Oracle

Example Questions

When a checkpoint occurs, the CKPT process ______. 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? 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? Which are three possible causes of lock contention? (Choose three) 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.) 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.) 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? Which statement requires an exclusive DDL lock? When should you recommend changing the application in order to reuse more SQL? Which type of table is the best candidate to be cached? 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.) 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? 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? 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); Which two statements are valid regarding row migration? (Choose two.) 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? Which type of change to an application is most likely to improve performance of the library cache? 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.) 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? 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? 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? What is the main reason to create a reverse key index on a column? What are three indications of contention for this rollback segment header? (Choose three) 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? 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? 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? 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.) 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? 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.) Which of these columns are used to calculate the library cache Hit Ratio? 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? After running a query using V$DISPATCHER, you increase the number of dispatchers. What would cause you to take this action? What does this statement do? SQL.ANALYZE INDEX index_name VALIDITY STRUCTURE; 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? When does the Oracle server put index segment blocks back on the free list? 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? 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? 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.) 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.) 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? For which reason would you query V$SYSSTAT? 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?