Oracle9i Database:Performance Tuning

1 You executed the following SQL statement to change the goal of the cost-based optimizer (CBO): What will be the result of this statement?
A.The goal of the CBO for the current session is set to best throughput.
B.The optimizer uses a cost-based approach for all SQL statements in the session   provided the statistics are available.
C.It will result in an error because the goal of the CBO cannot be changed at the session level.
D.The goal of the CBO for the current session is set to find the best plan for fast delivery of all the rows while using a mix of cost and heuristics.
ANSWER: A

2. Which two statements are true regarding the statistics used by the optimizer to   generate an optimal plan? (Choose two.)
A.The statistics collected through a histogram are not useful for better selectivity estimates of unevenly distributed data.
B.Dynamic sampling is preferred when the cost of collecting the sample statistics outweighs the advantage of a better execution plan.
C.The maximum and minimum values collected by the column statistics leads to an optimal plan if there is an even distribution of data between those two values.
D.The statistics collected through a histogram are useful if the data distribution is skewed between minimum and maximum values.
ANSWER: CD

3. Notice the following factors about a table:

Which is the least time-consuming method that you would use to deallocate the unused space?
A.follow the EXPORT, DROP, and IMPORT method
B.use the DBMS_SPACE.FREE_BLOCKS procedure
C.follow the EXPORT, TRUNCATE, and IMPORT method
D.use MOVE with the ALTER TABLE command to move the table
E.use DEALLOCATE UNUSED with the ALTER TABLE command
ANSWER: D

4. In your database, you executed the following command to monitor the ind1 index:
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?
A.analyze the index with the ESTIMATE STATISTICS option
B.analyze the index with the COMPUTE STATISTICS option
C.analyze the index with the VALIDATE STRUCTURE option
D.gather the table statistics first, and then analyze the index with any one of the options
ANSWER: C

5. Which statement correctly describes the purpose of the REFRESH COMPLETE   option when used with materialized view?
A.truncates the materialized view and repopulates it with the data from the base table in the query
B.populates the materialized view with the data from the base tables that has changed since the last refresh
C.populates the materialized view with the data from the base tables after each commit to any of the base tables
D.truncates the materialized view and populates it with the data from the base tables that has changed since the last refresh
ANSWER: A

6. You are working on the performance tuning of your database. Which two types of performance tuning-related information would an alert log file provide? (Choose two.)
A.mean time to recover
B.latch contention information
C.instance recovery start and complete times
D.SQL statements that are consuming maximum resources
E.the top five sessions that are consuming maximum resources
ANSWER: AC

7. What is the effect of setting the FAST_START_MTTR_TARGET initialization parameter?
A.A higher value for the FAST_START_MTTR_TARGET initialization parameter results in less time spent on reading redo during instance startup.
B.A lower value for the FAST_START_MTTR_TARGET initialization parameter results in more time spent on reading redo during instance startup.
C.A lower value for the FAST_START_MTTR_TARGET initialization parameter   results in an increase in the frequency of the DBWRn process writing dirty buffers.
D.A higher value for the FAST_START_MTTR_TARGET initialization parameter results in the LGWR process writing redo log buffers faster.
ANSWER: C

8. You have reserved memory within the Shared Pool by using the  SHARED_POOL_RESERVED_SIZE parameter to accommodate large space allocations while compiling PL/SQL blocks and database triggers.
While observing the performance of the reserved area, you find that the value of   the REQUEST_FAILURES column in the V$SHARED_POOL_RESERVED view is continuously increasing. Which statement is true in this scenario?
A.The parsing of the statement fails due to invalidations.
B.The execution of the statement fails due to invalidations.
C.The space allocated for the reserved area is not enough.
D.The syntax in the PL/SQL blocks is causing the compilation failure.
ANSWER: C

9. You have objects that have a variable reference pattern. These are referenced vigorously for some time, and then not referenced at all for a long time. As a result, these objects are being aged out from Oracle memory buffers frequently.
Which two strategies would you recommend to avoid aging out of erratically accessed objects? (Choose two.)
A.increasing PGA size
B.increasing large pool size
C.increasing buffer cache size
D.creating RECYCLE cache using the DB_RECYCLE_CACHE_SIZE parameter
E.creating a KEEP cache using the DB_KEEP_CACHE_SIZE parameter and altering the objects to that cache
ANSWER: CE

10. Which two statements are correct regarding users in a shared server environment? (Choose two.)
A.Connections can share dispatcher processes.
B.Multiplexing and pooling can be used for user connections.
C.It is advantageous for sessions performing batch operations.
D.The CPU overhead is reduced for each individual user request.
E.Multiple users cannot share the dispatcher processes to access the Oracle server.
ANSWER: AB

11. ABC Corp. plans to build an online transaction processing (OLTP) system. The company plans to tune all the components during the development life cycle.
From the list given below, identify the order in which the components should be tuned.

A.1, 2, 3, 5, 4
B.1, 3, 2, 4, 5
C.1, 2, 3, 4, 5
D.1, 2, 5, 3, 4
ANSWER: A

12. ABC Corp. has an online transaction processing (OLTP) system that has just gone into production. The system was rigorously tested during the development phases.
What is the first recommended activity that the DBA should perform before starting to monitor the performance of the system on a regular basis?
A.running SQL*Trace
B.collecting baseline statistics
C.analyzing indexes and user tables
D.arranging meetings with users once each two weeks to discuss performance
ANSWER: B

13. When tuning a database server, which three goals should you focus on? (Choose three.)
A.minimize response time for OLTP users
B.maximize throughput for batch processing
C.minimize response time for batch processing
D.increase load capability for the decision-support systems
E.minimize parallelism for online transaction processing (OLTP) systems
ANSWER: ABD

14. Smith is a DBA with ABC Corp. He is in the process of tuning the database. He has received various problem statements from the customers. Identify three appropriately defined problem statements that Smith can use to tune the database correctly. (Choose three.)
A.The system is too slow.
B.Online response is very slow.
C.Sometimes the batch process throws errors.
D.The weekly backup took 30 minutes, and earlier it used to take 5 minutes.
E.The batch process is taking 2 hours, whereas it should take only 30 minutes.
F.The system currently has 100 users. We wish to increase the number of users to 250. The users' response time should not be affected.
ANSWER: DEF

15. Smith is a DBA with XYZ Corp. There are 500 data entry users in an online transaction processing (OLTP) environment. The current response time is 15-20 seconds. The company wants the response time to be brought down to 10 seconds or less.
What is the first thing that Smith should do to diagnose the cause of the slow response time?
A.determine whether there is contention for locks
B.determine whether the file system is fast enough
C.determine whether more memory needs to be allocated to PGA
D.determine whether more memory needs to be allocated to SGA
E.determine whether the slow response is wait bound or CPU bound
ANSWER: E

16. Which two activities would adversely affect database performance? (Choose two.)
A.doing a soft parse
B.performing archiving
C.less frequent checkpointing
D.defining multiple buffer pools
E.taking online back up of data files
ANSWER: BE

17. The following are the tasks that you need to perform to create a resource plan:

In which order would you perform these tasks?
A.C, D, A, E, B, F
B.C, B, F, E, A, D
C.C, B, E, D, A, F
D.C, F, E, A, D, B
ANSWER: A

18. 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?
A.This privilege cannot be granted to a role.
B.This privilege is granted through the SQL GRANT command.
C.This privilege is granted through the dbms_resource_manager package.
D.This privilege cannot be granted to SMITH; only SYS can have this privilege.
E.This privilege is granted through the dbms_resource_manager_privs package.
ANSWER: E

19. View the Exhibit and examine the steps to create a database resource plan.

Execution of the following procedure resulted in an error:
 
What could be the reason for this?
A.The ADMINISTER_RESOURCE_MANAGER privilege is not granted to the user.
B.The procedure should be executed before creating the resource plan directive.
C.The SYS_GROUP resource consumer group is not included in the resource plan directive.
D.The OTHER_GROUPS resource consumer group is not included in the resource plan directive. 
ANSWER: D

20. Which three factors can be controlled by the use of the Database resource manager? (Choose three.)
A.undo quota for a group of sessions
B.the password history for a group of sessions
C.the number of transactions by a group of sessions
D.the number of active sessions within a specific group of sessions
E.the maximum estimated execution time for an operation in a specific group of sessions
ANSWER: ADE

21. View the Exhibit and examine the output to check the resource plan directives associated with the groups in a plan.

Which three statements are true regarding the output? (Choose three.)
A.The users belonging to OTHER_GROUPS will never be able to run a query.
B.Even if there is only one active session running, that session is allowed to utilize   all the available CPU resources according to the directives of the plan.
C.It is possible to raise the priority for the online transaction processing (OLTP) group from 80% to 90% without changing the priority for any other group.
D.In a fully loaded system, sessions for OTHER_GROUPS have to wait for other sessions to finish work if all CPU is used by first three groups.
E.In a heavily used system, the sessions in the OLTP group have the ability to consume 80% or more of the total CPU resources available.
ANSWER: BDE

22. You execute the following query to check resource consumption in the current plan used by the database:

Which statement is correct when a user belonging to GROUP1 tries to start a session?
A.The user can login but the session will be queued.
B.The user's attempt to start a session fails with an error.
C.The session starts successfully and allows all the operations.
D.The session starts successfully but cannot perform data definition language (DDL) operations.
E.The session opens successfully but cannot perform data manipulation language (DML) operations.
ANSWER: A

23. You executed the following query to know about users and their consumer groups:

If the user SCOTT starts a session, to which consumer group will the session belong?
A.The session belongs to LOW_GROUP.
B.The session belongs to SYS_GROUP.
C.The session belongs to DEFAULT_CONSUMER_GROUP.
D.The session does not belong to any group because the user is not assigned to any group.
ANSWER: C

24. The values for the resource plan directives are set as follows:

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.)
A.These settings are useful to limit the resources consumed by short-running operations.
B.The resource manager switches the session to the OLAP group if the session is   active for more than 1,000 seconds and after the operation the session becomes part of the OLAP group.
C.The resource manager can switch the session to the OLAP group before an operation even starts running.
D.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.
ANSWER: CD

25. Oracle Shared Server is best used when _____ and _____. (Choose two.)
A.the database is experiencing database-intensive work
B.the database is primarily used for batch or DSS operations
C.CPU usage on your machine is consistently at 90% or higher
D.an OLTP application is running on a machine approaching memory resource limits
E.you do not need to scale up the number of concurrent connections to the database
F.the database is primarily used for an interactive application where dedicated servers are mainly idle
ANSWER: DF

26. You have online application users who are interested in seeing a few initial results of a query. Therefore you require the query to produce a few initial rows quickly regardless of the presence of statistics. Which optimizer mode would you choose?
A.RULE
B.CHOOSE
C.ALL_ROWS
D.FIRST_ROWS_n
ANSWER: D

27. You increased the value of the OPTIMIZER_MAX_PERMUTATIONS parameter from 4000 to 8000. What would be the impact of this increment on the queries that are being executed? (Choose all that apply.)
A.The query parse time increases.
B.The query execution time increases.
C.The scope of the optimizer increases to select a good execution plan.
D.The scope of the optimizer increases to select between rule-based and cost-based optimization.
ANSWER: AC

28. In which three scenarios would you use stored outlines? (Choose three.)
A.You need to ensure that whenever a new index is created on a table, the execution plan is automatically changed to include the new index.
B.You plan to upgrade to a new Oracle database version. You need to ensure that the same set of execution plans is used in the new database version when the same statements are executed.
C.You need to ensure that the recently collected statistics are used by the optimizer while creating an execution plan.
D.You need to ensure that all the customers should access the same execution plan while using the same SQL statement.
E.You need to ensure that the same execution plan is used for a particular SQL statement regardless of the changes in the volume of data.
ANSWER: BDE

29. Which two statements are correct regarding stored outlines? (Choose two.)
A.The outlines are stored in the SYS schema.
B.The stored outlines always use the cost-based optimizer.
C.You cannot create several stored outlines for a single SQL statement.
D.The USE_STORED_OUTLINES parameter cannot be set at the session level.
E.When you set USE_STORED_OUTLINES to false and CREATE_STORED_OUTLINES to true, the Oracle database creates outlines but does not use them.
ANSWER: BE
30. The USER1 user executed the following command to create a private outline:

How should the error be resolved?
A.USER1 should create plan_table by using the utlxplan.sql script.
B.The CREATE_STORED_OUTLINE initialization parameter should be set to true at the session level.
C.The OUTLN user should execute the dbms_outln_edit.create_edit_tables procedure to create edit tables.
D.USER1 should execute the ANALYZE TABLE sales COMPUTE STATISTICS command before creating the stored outline.
E.USER1 should execute the dbms_outln_edit.create_edit_tables procedure to   create edit tables in the USER1 schema.
ANSWER: E