Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. Starting with Oracle Database 11gR2, which are two additional partitioning capabilities that aid Oracle Database Performance and Tuning?
A. Hash Partitioning
B. Reference Partitioning
C. Composite Partitioning
D. Interval Partitioning
E. Disk Partitioning
Answer: C,D
Q2. Which are two kinds of statistics collected and processed by the Automatic Workload Repository (AWR) you would utilize when tuning an Oracle Database instance?
A. Wait Event statistics
B. Session and System statistics
C. User privileges statistics
D. Standby Database throughput statistics
E. Interconnect statistics
Answer: A,D
Reference: https://docs.oracle.com/cd/E11882_01/server.112/e41573/autostat.htm#PFGRF027
Q3. When planning an Oracle Database architecture, which are three caching functionalities that would improve application response time and speed up query processing?
A. Result Cache
B. Mega Cache
C. Oracle In-Memory Database Cache
D. Database Smart Flash Cache
E. Oracle In-Memory DBSTATS Cache
Answer: A,C,D
Q4. Which functionality should be used to recommend materialized views and view logs, indexes, SQL profiles,and partitions for a specific workload use?
A. SQL Performance Analyzer
B. Top SQL
C. SQL Tuning Advisor
D. SQL Access Advisor
E. Workload Advisor
Answer: D
Reference: http://docs.oracle.com/cd/E25054_01/fusionapps.1111/e14496/monitor_db.htm
(12.2.4.5 Using SQL Access Advisor)
Q5. Which two statements are true about Automatic Statistics collection by the Optimizer?
A. Statistics are collected during automatic maintenance tasks.
B. Any fixed objects are disregarded.
C. Any external tables are incorporated.
D. All tables in the data dictionary are included by defaults.
E. Statistics are gathered after table statistics are marked stale.
Answer: A,E
Reference: https://docs.oracle.com/cd/B28359_01/server.111/b28274/stats.htm
Q6. When reviewing a customer's database configuration, you find thatOPTIMIZER_USE_SQL_PLAN_BASELINESis set asTRUE. What does the Optimizer do?
A. compares plans that itdevelops with accepted plans in baselines
B. develops plans and adds them to baselines as verified
C. doesn't develop an execution plan, but uses an accepted plan from baselines
D. doesn't develop an execution plan, but uses enabled plans in the baseline
Answer: B
Q7. In an Active Session History report, which response is correct?
A. 60 minutes of ASH data can be held in memory, so only 1 hour can be reported.
B. 60 minutes of ASH data can be held in memory, but more than 1 hour can be reported.
C. ASH data is available 24 hoursbecause AWR snapshots refresh every night.
D. As ASH data is flushed every 60 seconds, you need to set up a snapshot withDBA_JOBSfor what you want to keep.
Answer: A
Q8. Which are two strategies to maintain performance and improve a table segment's compression ratio when you are using Advanced Row Compression on a table segment?
A. Use smaller database block size.
B. Use larger database block size.
C. Use direct-path inserts wherepossible.
D. IncreasePCTFREEon the table segment.
E. Increase the size of the tablespace.
Answer: A,E
Q9. Which are two characteristics of “invisible” index functionality that would help tune a database having thousands of legacy indexes?
A. An invisible index is ignored by the optimizer.
B. After an index becomes invisible, it is no longer maintained during insertsand updates.
C. I/O bottlenecks are easy to spot when invisible indexes are used.
D. An index only needs to be rebuilt for it to be visible again.
E. The session or system parameter needs to be changed toOPTIMIZER_USE_INVISIBLE_INDEXEStoTRUEfor an invisible index to become visible to the Optimizer.
Answer: A,E
Q10. 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
Q11. Which option do customers need to license in order to implement SQL Performance Analyzer and Database Replay functionalities?
A. Oracle Database Diagnostics Pack for Oracle Database
B. Oracle Database Replay and Testing Suite
C. Oracle SQL Developer
D. Oracle Real Application Testing
E. Oracle SQL Tuning Pack for Oracle Database
Answer: D
Reference: https://docs.oracle.com/cd/B19306_01/license.102/b14199/options.htm
Q12. What are three reasons an implementation of an Oracle Database Performance and Tuning solution is superior to that of scripts or third-party tools?
A. It is a part of the database, which means no added installations, configurations, extra user accounts,privilege granting, etc.
B. It eliminates the need to reverify compatibility, upgrade third-party tools, or rewrite scripts after a patch ordatabase upgrade.
C. It has the largest library of custom scripts and procedures and includes an assembly builder to maintainthem and add more.
D. Results come from the Oracle Database Optimizer and not rules or heuristics trying to predict what theoptimizer may do.
E. It is the most inexpensive tool in the market.
Answer: B,D,E
Q13. Oracle databases come with a built-in health monitor to run diagnostic checks on components of the database. Which three capabilities can be used to automate reporting?
A. variety of real-time reports once the Business Intelligence option is installed
B. reports in response to critical errors
C. reports with findings and recommendations to resolve problems
D. reports examining memory and process usage
E. historical reports after the Database Diagnostic Repository (DDR) is configured
F. reports examining user and developer SQL programming errors
Answer: B,E,F
Q14. When performing performance analysis of Oracle Real Application Cluster (RAC) databases, what are three Automatic Database Diagnostics Monitoring (ADDM) analysis modes that are available?
A. Database
B. Instance
C. Partial
D. Sample
E. Average
Answer: A,B,C
Q15. Which action can beperformed by using Oracle Enterprise Manager Database Express Performance Hub?
A. View health status and performance of an Oracle Database instance via Database Control.
B. Launch and view STATSPACK reports.
C. View real-time and historical Oracle Database performance information.
D. View only real-time Oracle Database performance information.
E. View only historical Oracle Database performance information.
F. Consolidate ADDM, AWR, and ASH repositories within an enterprise and enable centralized performancereporting.
Answer: C
Q16. Which is a capability that Active Session History (ASH) reports enable you to perform?
A. Compare database performance between two periods of time, and resolve performance degradation thatmay happen from one time period to another.
B. Analyze transient performance problems with the database that are short-lived and do not appear in theADDM analysis.
C. Optimize the performance of data access paths by creating the proper set of materialized views,materialized view logs, and indexes for a given workload.
D. Analyze transient performance problems that have happened in the database since it has been started.
Answer: A