High quality 1Z0-064 Exam Dumps 2019
for Oracle certification, Real Success Guaranteed with Updated . 100% PASS 1Z0-064 Oracle Database 12c: Performance Management and Tuning exam Today!
Online Oracle 1Z0-064 free dumps demo Below:
NEW QUESTION 1
You plan to upgrade your production database from Oracle Database 11g to 12c. As part of the upgrade, you want to introduce new indexes and materialized views. You have already created a test system with Oracle Database 12c, having the same structure and data as the production database, along with new schema objects to be added to the production database.
You want to identify regressed SQL statements, if any, which may have been caused by schema changes and the change in the optimizer version.
Which two methods would you use to achieve this? (Choose two.)
- A. Create an SQL Tuning Set (STS) for the SQL statements on the production database and submit as input to the SQL Tuning Advisor on the test database.
- B. Create an STS for the SQL statements on the production database and submit as input to the SQL Performance Analyzer with the OPTIMIZER_FEATURES_ENABLE parameter first set to 188.8.131.52, and then to 184.108.40.206 on the test database.
- C. Generate an Automatic Workload Repository (AWR) compare periods report with snapshots taken before and after schema changes on the test database.
- D. Capture the production database workload, replay it on the test system by using Database Replay, and analyze by using the workload replay compare period report.
- E. Create an STS for the SQL statements on the production database and submit as input to the SQL Access Advisor on the test database.
- F. Create an STS for the SQL statements on the production database before and after changes and submit as input to the SQL Performance Analyzer on the test database.
NEW QUESTION 2
Examine the partial Activity Over Time section of an Active Session History (ASH) report:
Which two inferences are correct? (Choose two.)
- A. In the first time slot, five different sampled sessions were connected to the database instance.
- B. In the second time slot, out of the nine sampled sessions connected to the database instance, only one sampled session was idle at the time of report generation.
- C. In the first time slot, only one sampled session was using the CPU.
- D. In the second time slot, five different sampled sessions were using the CPU.
- E. In the second time slot, 0.14% of the time was spent on the CPU.
NEW QUESTION 3
For your database some users complain about not being able to execute transactions. Upon investigation, you find that the problem is caused by some users performing long- running transactions that consume huge amounts of space in the UNDO tablespace.
You want to control the usage of the UNDO tablespace only for these user sessions. How would you avoid the issue from repeating in future? (Choose the best answer.)
- A. Create a profile for the users with the LOGICAL_READS_PER_SESSION and LOGICAL_READS_PER_CALL limits defined.
- B. Create external roles to restrict the usage of the UNDO tablespace and assign them to the users.
- C. Set the threshold for UNDO tablespace usage for the users.
- D. Implement a Database Resource Manager plan by mapping the users to a resource consumer group with limits defined for UNDO tablespace usage.
NEW QUESTION 4
Your database supports a mixed workload. The ERP application creates short sessions and performs small, random I/Os; the REPORTING application executes long-running DSS queries.
You want to set a priority for the workload generated by the ERP application and optimize resource usage for them.
Which three objectives can be achieved by the Resource Manager? (Choose three.)
- A. limiting the amount of time that a session is idle and blocking other sessions of the ERP application
- B. limiting the amount of undo generated by operations performed by sessions created by the ERP application
- C. creating two resource plans with resource limits defined for the workload generated by the applications and automatically changing resource plans based on the workload
- D. allocating a lower percentage of CPU to sessions used by the REPORTING application than to those used by the ERP application
- E. limiting the physical I/O performed by the sessions or users of the ERP application that are connected to the database
NEW QUESTION 5
Examine the parameters set for a database instance supporting a mixed workload:
The database instance supports shared server and dedicated server connections simultaneously. Users complain about increased response times of a few DSS queries. During investigation, you execute the queries:
Based on the output, which two courses of action would you recommend to improve query performance? (Choose two.)
- A. Use a parallel hint in the queries.
- B. Increase the number of DBWn processes.
- C. Increase the value of the SORT_AREA_SIZE initialization parameter.
- D. Increase the size of the temporary tablespace or add a new temporary tablespace.
- E. Increase the value of the PGA_AGGREGATE_TARGET initialization parameter.
- F. Increase the size of the large pool.
NEW QUESTION 6
Which two situations can lead to sparsely populated index blocks? (Choose two.)
- A. Data is frequently inserted using direct path load into a table with an index.
- B. Indexed columns in a table are frequently updated.
- C. Values in an indexed column are inserted using monotonically incrementing sequences.
- D. Bulk delete operations are performed on a table with indexes.
- E. Online table move operations are performed frequently on a table with indexes.
NEW QUESTION 7
You plan to upgrade your production database from Oracle Database 11g to 12c and also to introduce new objects to the database. You also want to upgrade the hardware. You have already created a test system with the upgrades to be made to the production database. As part of the testing, you want to:
✑ analyze and compare the overall database workload with concurrency and transaction characteristics
✑ find SQL statements that might get regressed because of the upgrade
✑ analyze execution plans for SQL statements for which performance might get regressed
✑ analyze the impact of new schema objects on database performance
Which two tools would you recommend to achieve the objective? (Choose two.)
- A. Database Replay
- B. SQL Tuning Advisor
- C. SQL Access Advisor
- D. Automatic Database Diagnostic Monitor (ADDM) compare periods report
- E. SQL Performance Analyzer
- F. Automatic Workload Repository (AWR) compare periods report
NEW QUESTION 8
You have been asked to assess if using column store compression (previously known as hybrid columnar compression or HCC) would help improve the performance of queries on some large tables.
Which three aspects should you consider before you choose this compression method? (Choose three.)
- A. Check whether direct path load operations are used to insert rows in the table.
- B. Check whether the table is frequently queried using full table scans as column store compression only minimizes I/O during full table scans.
- C. Check whether the table is frequently updated because it will have overhead for insert and update operations.
- D. Check whether the table has LOB columns as it will minimize I/O for the queries.
- E. Check whether the table blocks are sparsely populated as this will defragment the blocks.
NEW QUESTION 9
In the CUSTOMERS table, the values in the CUST_STATE column are dependent on the values in the COUNTRY_ID column. You want to make the optimizer aware of this dependency when these columns are used together in WHERE clause predicates that contain equalities or in-lists.
Which two methods achieve this? (Choose two.)
- A. gathering statistics on the CUSTOMERS table and its dependent objects, and then locking the statistics
- B. using SQL plan directives to generate an optimal plan
- C. setting the dynamic statistics level to 4 and setting the OPTIMIZER_USE_PENDING_STATISTICS initialization parameter to true
- D. creating column group statistics, regathering statistics, and ensuring that histograms exist on both these columns
NEW QUESTION 10
Which two statements are true about server-generated alerts? (Choose two.)
- A. They are always logged in the alert log.
- B. They are written to a trace file if the TRACE_ENABLED initialization parameter is set toTRUE.
- C. They are generated only when the STATISTICS_LEVEL initialization parameter is set to ALL.
- D. They can be generated for user-defined metric thresholds.
- E. They appear in the DBA_ALERT_HISTORY view whenever corrective action is taken for an alert.
NEW QUESTION 11
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. freelist managed segments with multiple freelist
Explanation: Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14220/logical.htm
NEW QUESTION 12
In your database, the measured 99th percentile value is used as the maximum value. You set a warning threshold level of 110% of maximum trigger as an alert.
What is the outcome? (Choose the best answer.)
- A. It generates an error because the warning threshold cannot exceed 100%.
- B. It generates an error because the percentage of maximum threshold cannot be set with a significance-level threshold value.
- C. It generates an alert when an observed metric is 99% of the 99th percentile value as measured over the moving window baseline.
- D. It generates an alert when an observed metric is 110% of the 99th percentile value as measured over the moving window baseline.
- E. It generates an alert when 1 in 100 observations for an observed metric exceeds the 99th percentile value as measured over the fixed baseline.
NEW QUESTION 13
Examine the parameters:
Your database supports a mixed workload and users have dedicated server connections. Users complain about the increased response time of a few queries that are performing large sort operations. On investigation, you notice an increase in the number of multipass work area executions and high number of direct path write wait events.
Which two actions could improve the performance? (Choose two.)
- A. increasing the value of the SORT_AREA_SIZE parameter
- B. increasing the value of the PGA_AGGREGATE_TARGET parameter
- C. enabling Automatic Memory Management for the instance
- D. increasing the size of the default temporary tablespace
- E. using parallel hint in queries performing large sort operations
- F. enabling Automatic Shared Memory Management for the instance
NEW QUESTION 14
You want to capture AWR data to monitor performance variation every Monday between 9:00 AM and 12:00 PM for three months and automatically remove the older AWR data every fortnight.
How would you achieve this? (Choose the best answer.)
- A. Create AWR baselines.
- B. Create SQL plan baselines.
- C. Create repeating baseline templates.
- D. Create database services and make sure that user connections use them to connect to the database instance.
- E. Create a single baseline template.
NEW QUESTION 15
You are administering a database that supports an OLTP workload. The CURSOR_SHARING parameter is set to EXACT for the instance. The performance of queries issued by one of the modules has degraded. The queries executed by the module are almost identical in syntax. To investigate, you analyze the latest AWR report and find a large number of latch:shared pool wait events and also a high percentage of the hard parse elapsed time.
Which two can be reasons for this? (Choose two.)
- A. The I/O performance is slow.
- B. Bind variables are not used for similar queries, causing hard parses.
- C. Repeated access to a small number of blocks.
- D. Excessive time is spent on finding cached cursors in the library cache.
- E. The CURSOR_SHARING parameter is set to EXACT, which does not allow similar queries to share a cursor.
NEW QUESTION 16
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.
The STALE_PERCENT statistic preference is set to a default value and the STATISTICS_LEVEL parameter is set to TYPICAL.
Which two actions would you take to accomplish the task? (Choose two.)
- A. Set the STALE_PERCENT statistic preference to a higher value than the default, and then gather statistics.
- B. Set the STATISTICS_LEVEL parameter to ALL for the instance.
- C. Set the INCREMENTAL preference to TRUE, and then gather statistics.
- D. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to TRUE for the session in which you want to test the query.
- E. Set the PUBLISH statistic preference to FALSE, and then gather statistics.
- F. Set the NO_INVALIDATE statistic preference to TRUE, and then gather statistics.
100% Valid and Newest Version 1Z0-064 Questions & Answers shared by Certleader, Get Full Dumps HERE: https://www.certleader.com/1Z0-064-dumps.html (New 84 Q&As)