1Z0-064 Oracle Database 12c: Performance Management and Tuning

Exam Number: 1Z0-064
Exam Title: Oracle Database 12c: Performance Management and Tuning
Associated Certification Paths Oracle Certified Expert, Oracle Database 12c: Performance Management and Tuning
Duration: 150
Number of Questions: 84
Passing Score: 62%
Validated Against: Exam has been validated for product version 12.1.0.1.0
Format: Multiple Choice

Complete Recommended Training
Oracle Database 12c: Performance Management and Tuning

Additional Preparation and Information
A combination of Oracle training and hands-on experience (attained via labs and/or field experience) provides the best preparation for passing the exam.

Basic Tuning Diagnostics
Describe the time model
Explain wait events
Diagnose performance problems using V$ views

Using Automatic Workload Repository

Analyze database performance using AWR reports

Using Metrics and Alerts

Monitor performance using metric thresholds and alerts

Using Baselines

Use AWR baselines to monitor performance

Using AWR-Based Tools

Diagnose performance issues using ADDM
Diagnose performance issues using ASH reports

Monitoring Real-Time Database Operation

Implement Real-Time Database Operation Monitoring

Monitoring Applications

Configure and use services to monitor database performance

Identifying Problem SQL Statements

Interpret execution plans
Use formatted output of SQL Trace or the optimizer trace to identify poorly performing SQL statements

Influencing the Optimizer
Explain how statistics influence the optimizer
Configure parameters to influence the optimizer

Reducing the Cost of SQL Operations
Use Automatic Segment Space Management to optimize space management
Use table compression
Diagnose and tune space related issues

Using SQL Performance Analyzer
Use SQL Performance Analyzer to test the impact of database changes

SQL Performance Management
Manage optimizer statistics
Use SQL Profiles and Advisors to tune SQL statements
Use SQL Plan Management to tune SQL statements

Using Database Replay
Use Database Replay to check impact of system change on performance

Tuning the Shared Pool
Diagnose and resolve hard and soft parsing problems
Diagnose and resolve performance issues related to the shared pool

Tuning the Buffer Cache
Diagnose and resolve performance issues related to the buffer cache

Tuning PGA and Temporary Space
Diagnose and resolve performance issues related to PGA
Diagnose and resolve performance issues related to temporary tablespace

Using Automatic Memory
Use and tune Automatic Shared Memory Management
Use and tune Automatic Memory Management

Tuning I/O
Diagnose database I/O issues
Configure Automatic Storage Management (ASM) for maximum availability

Using the Database Resource Manager
Understand and configure the Database Resource Manager

---

QUESTION 1
Which two result in the latest fragmentation within segments and the least external fragmentation within tablespaces?(Choose two.)

A. automatic segment space managed segments
B. locally managed tablespaces with uniform extent size
C. freelist managed segments with one freelist
D. dictionary managed tablespace
E. locally managed tablespaces that were converted from dictionary managed tablespaces
F. freelistmanaged segments with multiplefreelist

Answer: A,D
Reference:https://docs.oracle.com/cd/B19306_01/server.102/b14220/logical.htm

---

QUESTION 2
You are administering a database that supports a DSS workload. Automatic Shared Memory Management is enabled for the database instance. Users issue queries toperformlarge soft operations and complain about degraded performance of the queries. On investigation, you notice that the queries are performing multipass work area executions and the I/O contention on one of the temporary tablespaces is very high.
Which two can be possible resolutions for this issue?(Choose two.)

A. Increase the size of the large pool.
B. Increase the value of thePGA_AGGREGATE_TARGETparameter.
C. Create a temporary tablespace group and assign it to users.
D. Increase the value of thePGA_AGGREGATE_LIMITparameter.
E. Create another temporary tablespace and assign it to users.
F. Enable temporary undo.

Answer: A,D

---

QUESTION 3
Examine the structure of theEMPLOYEEStable.

EMPLOYEE_ID is the primary key. No other indexes exist on this table. View the Exhibit to examine the commands and their output.

Which two actions should you perform to make the cursor bind aware?(Choose two.)

A. Create a histogram on theDEPARTMENT_IDcolumn.
B. Change the defaultCURSOR_SHARINGvalue toFORCE.
C. Execute the query with the sameDEPARTMENT_IDvalue multiple times.
D. Create an index on theDEPARTMENT_IDcolumn.
E. Gather statistics for the index.
F. Regather statistics on the table.

Answer: C,D

---

QUESTION 4
Examine the partial AWR report taken for a time period of 60 minutes:

Which two inferences can you draw from this report?(Choose two.)

A. The database user calls are issuing frequent explicit commits.
B. The CPUs are busy executing server processes and background processes for a considerable amount of CPU time.
C. The database user calls are spending most of their time in I/O for single block reads.
D. The database user calls are spending most of their time waiting for sessions that are in more important consumer groups.

Answer: B,C

---

QUESTION 5
Which two statements are true about Compare Period ADDM?(Choose two.)

A. It is automatically invoked whenever the AWR Compare Period report is invoked.
B. It is automatically invoked whenever ADDM is run by default.
C. It verifies if there is any change in the workload or average resource consumption by the SQL executed during the two specified time periods, to ensure 100% accuracy.
D. It can be used to create a comparison report between the Database Replay workload capture report and the replay report.

Answer: B,D

---

QUESTION 6
You are administering a database that supports an OLTP workload. Users complain about the degraded response time of a query. You want to gather new statistics for objects accessed by the query and test query performance with the new statistics without affecting other sessions connected to the instance.
TheSTALE_PERCENTstatistic preference is set to a default value and theSTATISTICS_LEVELparameter is set toTYPICAL.
Which two actions would you take to accomplish the task?(Choose two.)

A. Set theSTALE_PERCENTstatistic preference to a higher value than the default, and then gather statistics.
B. Set theSTATISTICS_LEVELparameter toALLfor the instance.
C. Set theINCREMENTALpreference toTRUE, and then gather statistics.
D. Set theOPTIMIZER_USE_PENDING_STATISTICSparameter toTRUEfor the session in which you want to test the query.
E. Set thePUBLISHstatistic preference toFALSE, and then gather statistics.
F. Set theNO_INVALIDATEstatistic preference toTRUE, and then gather statistics.

Answer: B,E

Click here to view complete Q&A of 1Z0-064 exam
Certkingdom Review, Certkingdom PDF Torrents

Best Oracle 1Z0-064 Certification, Oracle 1Z0-064 Training at certkingdom.com