Free Sharing Lead2pass Exam Dumps

Free Download All The Latest Cisco, Microsoft, CompTIA, Oracle, VCP, CISSP Questions And Answers

Free Download Pass4sure Oracle 1Z0-033 Braindumps

Oracle 9i Database: Performance Tuning: 1Z0-033 Exam
1Z0-033 Questions & Answers
Exam Code: 1Z0-033
Exam Name: Oracle 9i Database: Performance Tuning
Q & A: 222 Q&As

Part: A
1: 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.
Correct Answers: D

2: 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.
Correct Answers: D E F

3: You execute the following query to check resource consumption in the current plan used by the
SQL> SELECT name,active_sessions,requests,queue_length,current_undo_consumption “UNDO
CONSUMPTION” FROM V$rsrc_consumer_group;
————— ———————— ————– ——————————- ——————-
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)
Correct Answers: A

4: 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?
Correct Answers: B

5: You use the following query to monitor the Shared Servers:
SQL>SELECT maximum_connections “MAX CONN”,
servers_started “STARTED”,
servers_terminated “TERMINATED”,
servers_highwater “HIGHWATER” FROM v$shared_server_monitor;
————— ———- —————- ———-
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
B.The number of Shared Servers is too high, so you should decrease the number of Shared
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.
Correct Answers: D

6: 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
Correct Answers: C D

7: 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.
Correct Answers: B E

8: 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
D.If there are currently less than five dispatchers, Oracle server terminates all of them and creates
five new dispatchers.
Correct Answers: A B

9: 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
Correct Answers: B E

10: 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’sorts (memory)’ and’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 action required because the ratio is perfect for an OLTP system
Correct Answers: A

11: You issued the following query on the V$INSTANCE_RECOVERY dynamic performance
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.
Correct Answers: E

12: 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).
Correct Answers: D

13: 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
Correct Answers: A

14: 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
Correct Answers: C

15: 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
Correct Answers: A

16: 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
Correct Answers: B

17: 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?
Correct Answers: A

18: 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
Correct Answers: B

19: 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
Correct Answers: A C D

20: Which two statements are correct regarding users in a shared server environment? (Choose
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.
Correct Answers: A B

…go to to download the full version, ensure a passing score in the test.

Lead2pass Testking Pass4sure Actualtests Others
$99.99 $124.99 $125.99 $189 $29.99-$49.99
Real Questions
Error Correction
Printable PDF
Premium VCE
VCE Simulator
One Time Purchase
Instant Download
Unlimited Install
100% Pass Guarantee
100% Money Back