Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. Which are three use cases in which SQL Performance Analyzer can be used?
A. find and flashback explain plan anomalies
B. simulate Exadata on existing hardware
C. forecast impact of a system change on aSQL workload
D. compare performance of SQL statements before and after a change
E. debug SQL code syntax before and after a change
F. JSQL Heap Memory overflow testing
Answer: B,C,E
Q2. An Oracle Database customer's VP of Development mentions that they've moved to AGILE development methodology and DevOps requires an ability to view, test, or validate performance in their own development environment. Which two do you implement?
A. Oracle Application Infrastructure Tester
B. Oracle Application Testing Suite
C. SQL Developer
D. Oracle Real Application Testing
E. APEX and SQL Developer
F. Oracle Testing Pack
Answer: B,D
Q3. When planning Oracle Database Architecture, which option can be used to reduce the number of Oracle processes waiting in the Operating System run queue and to ensure that database CPU load is healthy?
A. Exadata
B. Oracle Job Scheduler
C. DATABASE_MAXCPUS=YES
D. Oracle Database Resource Manager
E. DB_RAC_MAXCPU=YES
F. Oracle Grid Control
Answer: D
Reference: http://www.oracle.com/technetwork/database/performance/resource-manager-twp-133705.pdf
Q4. You need to load SQL statements from a time period of optimal performance to compare against a time period that performance was reported as poor. Which functionality would you use to accomplish that task?
A. Optimizer Statistics Comparator
B. Dynamic Baseline Advisor
C. Automatic Workload Repository Baselines
D. SQL Tuning Sets
E. Database Replay
Answer: D
Q5. When managing multiple database instances on a single server, which initialization parameter limits the number of CPU's that an instance can use simultaneously?
A. CPU_NUM
B. NUM_CPU
C. MAX_CPU
D. LIMIT_CPU
E. CPU_COUNT
Answer: E
Reference: http://docs.oracle.com/html/E25494_01/dbrm008.htm
Q6. IT Operations had observed short CPU spikes and I/O storms prior to a retail website outage. Which functionality of Diagnostics Pack for Oracle Databases would you implement to diagnose and resolve the customers issue?
A. Active Workload History to collect SQL at the time of its execution.
B. SQL Performance Analyzer to capture the information in the SGA, the sessions active, and analyze theSQL executing.
C. Oracle Workload Advisor that automatically turns on and captures the offending SQL Statements whenthresholds are reached.
D. Active Session History to capture sessions and dump the SQL executing, performance attributes andrelevant information into a buffer for analysis.
E. Active SQL History Analyzer to doperform a comparison of SQL statements before and after.
F. Oracle Exadata, as it already includes Diagnostic Pack for Oracle Database.
Answer: F
Q7. SQL Access Advisor takes database workload as input and recommends adding various access structures. Which three does it consider the impact of adding when it generates recommendations?
A. Materialized views
B. Indexes
C. Partitions
D. Tablespaces
E. V$ performance views
F. Hints
Answer: B,D,E
Q8. To analyze all database instances in an Oracle Real Application Cluster, in which mode would you run Automatic Database Diagnostic Monitor?
A. INSTANCE
B. DATABASE
C. FULL
D. CLUSTER
E. PARTIAL
F. ALL
Answer: A
Reference: http://docs.oracle.com/cd/E25178_01/server.1111/e16638/diag.htm
Q9. Which option would you use to validate performance when migrating an Oracle Database from HPUX Itanium to Oracle Linux 6 architecture?
A. Oracle Diagnostic Pack for Database
B. Real Application Testing
C. Oracle Migration Pack for Databases
D. Oracle Migration Performance Advisor
E. SQL Performance Testing Suite
Answer: C
Q10. 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
Q11. You have been asked to provide a “technology update” to a long time Oracle Database customer. During the meeting, you are asked about Oracle Enterprise ManagerDatabase Express in Oracle Database 12c.
Identify three of its capabilities you would share with the customer.
A. Once Oracle Database 12c is installed, a DBA runs the separate Oracle Enterprise Manager DatabaseExpress installer to add it into the database console.
B. The Oracle Enterprise Manager Database Express Performance HUB organizes and provides aconsolidated viewof real-time and historical performance data.
C. The Database Express Performance HUB displays Oracle RAC specific metrics.
D. Oracle Enterprise Manager Database Express can be extended by the user to also display Oracle 7-10gdatabases.
E. Oracle Enterprise Manager Database Express does not require any separate middle-tier componentsbecause it uses XDB and SQL*NET.
F. The Oracle enterprise Manager Database Express Performance Hub displays Oracle RAC specific metricswhen the optional RAC Pack is licensed.
Answer: A,E,F
Q12. A customer asked you about Database Time (DB Time). Which are two correct statements you would include in your description of it?
A. It is the total time spent in database calls by all sessions in the database.
B. It is the total time spent in database calls by foreground sessions in the database.
C. It is the total time spent in database calls by background sessions in the database.
D. It includes non-idle wait time.
E. It is the overall response time.
F. It includes login, idle, background, and Zombie session wait time.
Answer: B,C
Q13. 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
Q14. A customer not familiar with Active Session History (ASH) asked you what it was used for. Which are two statements you can use to accurately describe (ASH) capabilities?
A. It enables you to conduct performance analysis of long-running sessions.
B. It enables you to conduct performance analysis of transient issues.
C. In memory, active sessions are sampled every second.
D. It accesses database kernel structures indirectly via the buffer cache.
E. It enables you to look at all database sessions since the database was created.
Answer: A,C
Reference: http://docs.oracle.com/cd/B19306_01/server.102/b28051/tdppt_method.htm#TDPPT015
Q15. To use Automatic Database Diagnostic Management capability on a database using a SQL script, command line, database views, or from a GUI, which option needs to be licensed?
A. Oracle Diagnostics Pack for Oracle Databases
B. Oracle Tuning Pack for Oracle Databases
C. Oracle Diagnostics and Tuning Pack for Oracle Databases
D. Oracle ADDM Pack
E. Nothing else needs to be licensed.
Answer: C
Q16. Which two statements are true about Oracle Enterprise Manager Database Express?
A. It uses internal infrastructure components, such as XDB and SQL*NET.
B. It can perform actions inside and outside the database.
C. Performance Hub and Composite Active Reports are its key functionalities.
D. It is not supported on Oracle Standard Edition or Express Edition.
E. It replaces Oracle Grid Control.
Answer: A,C
Reference: http://www.oracle.com/technetwork/database/manageability/emx-intro-1965965.html