Q1. Which option is recommended when gathering Optimizer system statistics on a regular basis to ensure optimal performance?
A. lock out user sessions and gather system statistics overnight
B. gather system statistics after a performance issue has been reported
C. gather system statistics for several hours when there is no workload
D. gather system statistics for several hours during normal workload periods
E. shut down any running applications prior to collection
Answer: D
Reference: https://docs.oracle.com/database/121/TGSQL/tgsql_stats.htm#TGSQL398
Q2. Which background process performs Automatic Workload Repository (AWR) tasks such as writing when a metric violates its threshold value, taking snapshots, and capturing statistics for recently modified SQL objects?
A. LGWR
B. DBWR
C. CKPT
D. MMON
E. MMNL
Answer: D
Explanation:
Themanageability monitor process (MMON)performs many tasks related to theAutomatic Workload Repository (AWR). For example, MMON writes when ametricviolates its threshold value, taking snapshots, and capturing statistics value for recently modified SQL objects.
Reference: https://docs.oracle.com/cd/E11882_01/server.112/e40540/process.htm#CNCPT008
Q3. What are three activities an Oracle Database Performance and Tuning solution enables a customer to perform?
A. standardize database troubleshooting and diagnostics
B. control database clouds.
C. automate database performance management
D. perform global database backup
E. outsource database operations
F. replay and test actual workloads to assure database performance and consolidatios.
Answer: C,D,E
Q4. Which issue can be avoided by configuring the PCTFREE parameter?
A. row chaining and setting it has been superseded by Automatic Segment Space Management
B. table fragmentation, and has been superseded by Automatic Storage Management
C. column fragmentation
D. sort operations on disk
E. keeping a reserve area for backup
Answer: A
Q5. For which one would you use an index fast full scan to improve performance?
A. a query having all the columns of an index in the where clause
B. a query that does not need to access table rows
C. a query that needs to access table rows along with an index
D. a column in an index that is compressed
Answer: A
Explanation:
All of the columns required by SQL must reside in the index tree; that is, all columns in the SELECT and WHERE clauses must exist in the index. Reference: http://www.dba-oracle.com/t_index_fast_full_scan.htm
Q6. Which is a prerequisite for using an Oracle Database Performance and Tuning solution?
A. Linux Operating system
B. 8 GB of memory
C. Oracle Database Enterprise Edition
D. Oracle Enterprise Manager
E. deep knowledge of Oracle Storage subsystems
Answer: C
Q7. Which statement is true about implementation of an Oracle Database Performance and Tuning solution?
A. It is available for Oracle Database Standard and Enterprise Editions.
B. It is applicable for Oracle Database 12c and later versions.
C. It builds on capabilities built into Oracle databases.
D. It is recommended for Production environments, but not for Development or QA.
E. It is delivered with Oracle Engineered Systems.
Answer: E
Q8. Which two situations help to identify that a customer as already using Oracle Diagnostics Pack for Oracle Database functions?
A. The customer has Enterprise Manager installed on a separate server and regularly accesses the Databasehome page of some of its Enterprise Edition databases from its console.
B. A customer says that he or she doesn't use Enterprise Manager, but you learn that the customer routinelyexecutesawrrpt.sqlfrom the SQLPlus prompt.
C. A customer has theCONTROL_MANAGEMENT_PACK_ACCESSinitialization parameter set toDIAGNOSTIC +TUNING.
D. A customer says that he or she doesn't use Enterprise Manager, but you learn that the customer has SQLscripts that access theDBA_HIST_ACTIVE_SESS_HISTORYdatabase view.
E. A customer says that he or she doesn't use theEnterprise Manager, but you learn that the customer usescustomized SQL scripts that accessV$SESSIONandV$SQLviews.
Answer: C,D
Q9. For which use case would you utilize SQL Performance Analyzer and optimizer pending statistics functionality?
A. to test performance after an upgrade to an Oracle Real Application Cluster
B. to test performance of an Operating System patch
C. to test effect of new Optimizer statistics
D. to test performance after a database upgrade
E. to test performance just after a database reboot
Answer: A
Q10. Which is a true statement about the difference in the functionality of Active Session History (ASH) and SQL Trace/TKPROF?
A. ASH provides time based data, SQL Trace/TKPROF does not
B. ASH provides detailed session level data, SQL Trace/TKPROF does not
C. ASH is always on, SQL Trace/TKPROF is not
D. ASH identifies Bind variables that are available, SQL Trace/TKPROF does not
E. ASH counts and has occurrence data, SQL Trace/TKPROF does not
Answer: B