- about 4 questions on user_dump_dest, background_dump_dest & alert.log
- know when to use V$SYSSTAT, V$SYSTEM_EVENT, V$SESSION_WAIT etc. Pando provide a good explanation on these view on the Performace Tuning Preparers Thread.
These are type of que u will get in exam.
1. The most useful measure of database buffer cache performance is ______.
a. Consistent gets
b. Free buffer inspected
c. Buffer busy rates
d. Latch free
e. Cache hit ratio
f. Physical reads
2. To issue an alter system kill session statement, which two values would you retrieve from V$SESSION?
b. PROCESS ID
d. SESSION NAME
3. In the analysis report generated by Oracle Expert for a tuning session, what information is included?
b. All collected data to be analyzed.
c. Detailed information about the collected data in the repository for a tuning session.
d. Listing of all attributes for the instance for which you want tuning recommendations to be generated.
4. You suspect redo latch contention in the PROD instance and query V$LATCH and V$LATCHNAME to obtain statistics. The ideal percentage of latch contention time should be ______.
a. Less than 1%
b. Less than 5%
c. Less than 10%
d. Less than 90%
5. Given the following:
What is the purpose of this parameter?
a. Increase the number of blocks in the database buffer cache.
b. Compute the cache hit ratio.
c. Allow evaluation of the effect of increasing the size of the buffers in the database buffer cache.
d. Allow evaluation of the effect of adding buffers to the database buffer cache.
6. Where are temporary segments created if a temporary table space is not specified for users?
a. RBS table space
b. SYSTEM table space
c. USERS table space
d. TEMP table space
e. INDEX table space
7. What is the minimum number of extends required in a rollback segment?
8. Users are reporting slow response time on your system. You think that the problem can be solved by increasing the size database buffer cache. But you want to make sure your solution is correct. Which two steps are necessary to evaluate the impact of adding buffers to database buffer cache?
a. Set the parameter DB_BLOCK_LRU_EXPANDED_STATISTICS to the number of buffers you want to add.
b. Set the parameter DB_BLOCK_LRU_EXPANDED_STATISTICS=True.
c. Query the V$RECENT_BUCKET view.
d. Query V$FILESTAT.
9. You suspect there is I/O contention with the log files. Which view would you query for confirmation?
10. You are monitoring rollback segment usage for the PROD database to determine if you need to add additional rollback segments. Frequent queries of V$WAITSTAT during normal processing hours indicates an average value of 115 for the undo header column. What does the average value of this column indicate to you?
a. Rollback segment header blocks are being used efficiently.
b. Rollback segments are sized too large.
c. The largest rollback segment for the prod database is too small for normal transaction processing.
d. Contention exists for rollback segment header blocks in the transaction table.
11. Trace files for deadlock situations are recorded where?
12. When creating temporary table space, which two storage parameters should be set as integer multiples of SORT_AREA_SIZE?
13. When will the change to a storage parameter be used if you are changing the storage parameter for the EMP table by setting PCTUSED to 250?
b. When the further queries are made on the table
c. When future DML activity occurs on the table
d. When the database is shut down and restarted
14. In your efforts to improve performance you altered several frequently accessed small tables with the CACHE clause. To prevent over-crowding in the buffer cache, which initialization parameter you can set?
15. The alert log file contains which type of information?
a. Default initialization parameter
b. Messages and errors
c. Name and location of all databases
d. SQL statement error messages generated by applications
16. The database includes tables with static data that are used for queries only. For this type of table to which size should you set PCTFREE?
17. When are packages loaded into library cache?
a. At instance startup
b. After all parts of the package have been accessed
c. As soon as any part of them is accessed
d. When an application using the packet is started
18. You have just implemented the new financials application and want to track usage of various modules. To track the usage of modules in the application, what should be used?
b. SQL* trace
c. EXPLAIN plan
19. You have been asked to develop a tuning plan for an application that has been in production for a year. Your most important tuning goal should be to ______.
a. Tune the internal database structure so that you can see performance improvements.
b. Tune all elements necessary for users to see noticeable performance improvements.
c. Tune the database so that you can perform backups more quickly.
d. Reorganize the data files on the system to take advantage of available disk space.
20. Payroll application users are complaining about the length of time it takes to run the earnings calculation program, you have enabled tracing for this process and trace is now complete. To format information about the execution of the SQL statements for the process, what should be used?
a. SQL Plus Trace.
21. To find out how a statement would be executed if you ran it at a particular moment what, what should be used in SQL*Plus?
22. You query INDEX_STAT because you suspect there is a problem with the ACCT_NO_IDX index. What information is returned?
a. Information about the current state of all indexes
b. Information about the current state of an index
c. Information about the index from the time of creation
d. Information from the last analyze index validate structure command issued
23. Performance for queries in your decision support system has degraded and you decide to create indexes for the relevant tables. The queries usually involve multiple predicates on a low cardinality column and tables are read only. Which index type should you create?
b. Reverse key
c. Bit mapped
24. You have created keep, recycle and default buffer pools for the PROD instance. However when you start up the instance an error occurs and the database is not mounted. What could cause this?
a. Total allocation to the buffer pools has exceeded the allocation to the PGA.
b. Multiple buffer pools can only be created when a database is created.
c. Total allocation to the buffer pools is less than the value for DB_BLOCK_BUFFER.
d. Total allocation to the buffer pools has exceeded the DB_BLOCK_BUFFER and /or DB_BLOCK_LRU_LATCHES parameters.
25. Your company is planning to design and implement a custom payroll application. During this proces,s at which time is it most important for performance to be addressed?
c. Application development
26. What should you use as a sizing guideline when creating a recycle buffer pool?
a. It should be large enough to retain blocks for the duration of a session.
b. It should be large enough to retain blocks for the duration of the application process.
c. It should be large enough to retain blocks for the duration of all sessions.
d. It should be large enough to retain blocks for the duration of a transaction
27. You have determined that row migration is occurring for several tables in the PROD database. To prevent further migration for the involved tables and any other tables where you do not want this to occur, which storage parameter can you set?
28. What will a trace file contain if you have enabled SQL Tracing at the session level?
a. Statistics for buffer usage for the instance
b. Statistics for all processes during that time
c. Statistics for the user application process
d. Statistics for SQL statements for the session
29. You created a test database recently. You think you may not have made the redolog buffer large enough. Which view should be queried to see if waits are occurring for the event log buffer space?
e. V$ SYSTEM_EVENT
30. You are performing a direct load to the TEMP_DATATABLE using SQL*Loader. You issue this command prior to the task, ALTER TABLE temp_date NOLOGGING; which task is accomplished?
a. Redo log entries will not be generated for the load to the temp_data table.
b. Redo log entries will be generated but will not be archived for the load to temp_data.
c. Redo log entries will not be archived until the load to the temp_data table is complete.
d. Log entries will not be generated until the load to the temp_data table is complete.
I will be taking the networking exam soon...any tips from yourself would be greatly appreciated.
I used ILT, STS and BFQ. Some of teh questions are on teh previous threads here. You should understand concepts of NEt8. Since there is not so much material to cover for 59 questions, there are asking a lot of details. Still it was teh easiest exam for me yet.