Exam Code: 1Z0-033
Exam Name: Oracle 9i Database: Performance Tuning
Free Download Oracle 1Z0-033 VCE and PDF Dumps
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.)
A. Both the KEEP and RECYCLE pools must be configured.
B. The RECYCLE pool needs to be configured before dropping an object.
C. The DEFAULT pool needs to be removed if multiple buffer pools are being configured.
D. Either the KEEP pool or the RECYCLE pool can be configured along with the DEFAULT pool.
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.
You execute the following query to check resource consumption in the current plan used by the database:
SQL> SELECT name,active_sessions,requests,queue_length,current_undo_consumption “UNDO CONSUMPTION” FROM V$rsrc_consumer_group;
NAME ACTIVE_SESSIONS REQUESTS QUEUE_LENGTH UNDO
————— ———————— ————– ——————————- ——————- OTHER_GROUPS 1 1 0
GROUP1 2 4 2
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.
You require that a warning is written to a trace file without impacting the application when the session’s Java state exceeds 2GB. Which parameter would you modify?
C. SESSIONS and JAVA_SOFT_SESSIONSPACE_LIMIT
D. LOG_ARCHIVE_TRACE and JAVA_MAX_SESSIONSPACE_SIZE
You use the following query to monitor the Shared Servers:
SQL>SELECT maximum_connections “MAX CONN”,
servers_highwater “HIGHWATER” FROM v$shared_server_monitor; MAX CONN STARTED TERMINATED HIGHWATER
————— ———- —————- ———- 3 6 6 3
With every execution, you observe that the values of the STARTED and TERMINATED columns are growing quickly. What action would you take?
A. The number of Shared Server sessions is too high, so you should decrease the value of SHARED_SERVER_SESSIONS.
B. The number of Shared Servers is too high, so you should decrease the number of Shared Servers.
C. No action is required because this growth is a normal behavior in a Shared Server environment.
D. The number of Shared Servers is too low, so you should increase the number of Shared Servers.
As part of the performance-tuning activity, you executed the utllockt.sql script. What does this script do? (Choose all that apply.)
A. populates the v$locked_object view
B. creates the dba_locks and dba_blockers views
C. displays information about the sessions that are waiting for locks
D. displays information about the locks for which sessions are waiting
Which two statements are valid regarding row migration? (Choose two.)
A. It can be reduced by decreasing the PCTFREE value.
B. The row migration causes more than one block to be read when a row is accessed.
C. It can be resolved by using the ANALYZE TABLE…ESTIMATE STATISTICS command.
D. It is caused by inserting a row into an empty data block where the row is too large to fit into that empty block.
E. 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.
While tuning the shared server in your database, you executed the following command:
SQL> ALTER SYSTEM SET DISPATCHERS = ‘(PROTOCOL=TCP)(DISPATCHERS=5)’;
How does this statement affect the shared server configuration? (Choose two.)
A. If there are currently less than five dispatchers, new dispatchers are created.
B. If there are currently more than five dispatchers, additional dispatchers are terminated after the connected users disconnect.
C. If there are currently more than five dispatchers, Oracle server terminates additional dispatchers immediately.
D. If there are currently less than five dispatchers, Oracle server terminates all of them and creates five new dispatchers.
Which two actions could you perform to reduce busy buffer waits caused on segment header blocks? (Choose two.)
A. configure multiple I/O slaves
B. increase the number of free lists
C. increase the size of the redo log buffer
D. use multiple database writer (DBWn) processes
E. use the Automatic Segment Space Management feature
In an online transaction processing (OLTP) system, which is configured in shared server mode, you execute the following query to examine the sort performance:
SQL> SELECT d.value “Disk”, m.value “Mem”, (d.value/m.value)*100 “Ratio” FROM v$sysstat m, v$sysstat d
WHERE m.name=’sorts (memory)’ and d.name=’sorts (disk)’; Disk Mem Ratio
———- ———- ———
23 206 11.165049
What action would you take after observing the output?
A. increase the value of SORT_AREA_SIZE
B. increase the size of the temporary tablespace
C. increase the value of PGA_AGGREGATE_TARGET
D. no action required because the ratio is perfect for an OLTP system
You issued the following query on the V$INSTANCE_RECOVERY dynamic performance view:
SELECT target_mttr, estimated_mttr FROM v$instance_recovery;
You noticed that the value for the ESTIMATED_MTTR column is greater than the value for the TARGET_MTTR. What would happen in this scenario?
A. A checkpoint is initiated.
B. A warning is written in the alert log file.
C. The number of archiver processes is increased.
D. The control file is updated to mark another checkpoint record.
E. DBWn is requested to write enough dirty buffers to allow the ESTIMATED_MTTR to fall back below the TARGET_MTTR.
View the Exhibit and examine the execution plan of the following query:
SQL> SELECT s.prod_id
2 , t.fiscal_month_number
3 , sum(s.amount_sold) AS sum_amount
4 FROM sales s, times t
5 WHERE s.time_id = t.time_id
6 AND t.fiscal_year = 2000
7 GROUP BY s.prod_id, t.fiscal_month_number
8 ORDER BY s.prod_id, t.fiscal_month_number;
Which statement regarding the SALES_PROD object in the execution plan is true?
A. It is an index.
B. It is a complex view.
C. It is a mapping table.
D. It is a materialized view.
E. It is an index-organized table (IOT).
Which type of lock is held by SQL statements and PL/SQL objects in the Library Cache?
A. breakable parse locks
B. row-level (TX type) lock
C. table-level manual locks
D. table-level (TM type) locks
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?
A. 3, 2, 4, 1; 5 and 6 are not required
B. 5, 2, 1, 4; 3 and 6 are not required
C. 3, 2, 1, 4; 5 and 6 are not required
D. 5, 2, 1, 6; 3 and 4 are not required
E. 3, 4, 2, 1; 5 and 6 are not required
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
View the Exhibit and examine the TKPROF output. While studying the TKPROF output, you find that the number of disk reads is close to the total number of buffers used (query + current) for some statements. What is the reason for this?
A. shared pool is not large enough
B. database buffer cache is not large enough
C. too few DBWn processes
D. fragmentation in the database buffer cache
You have the following requirements:
– Your transaction only queries the table, and requires a consistent set of the table’s data for the duration of the transaction.
– You can hold up other transactions that try to update the locked table, until all transactions that hold SHARE locks on the table either commit or roll back.
– Other transactions may acquire concurrent SHARE table locks on the same table, also allowing them the option of transaction-level read consistency.
Which type of locking strategy would you select to achieve this?
C. ROW SHARE
D. ROW EXCLUSIVE
You are working in a dedicated server environment. You discovered that your database performance is degraded due to a huge number of sort operations that are using the disk space instead of the memory area. The WORKAREA_SIZE_POLICY parameter is set to AUTO. What would be one of the solutions to improve database performance?
A. setting the *_AREA_SIZE parameters to higher values
B. setting the PGA_AGGREGATE_TARGET parameter to a higher value
C. setting the SORT_AREA_RETAINED_SIZE parameter to a lower value
D. setting the SORT_AREA_RETAINED_SIZE parameter to a higher value
You observed a lot of unused space below the high-water mark for a table. Which three methods could you use to deallocate the unused space? (Choose three.)
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
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.