Oracle 1Z0-064 Exam Dumps

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 11.2.0.1, and then to 12.1.0.1on 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.

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.

Which two statements are true about gathering statistics? (Choose two.)

A. If an application has only SQL statements with bind variables, it is better to drop existing histograms, disable creation of histograms, and allow the optimizer to select the best execution plans.

B. If end users query newly inserted data, it is possible to get a suboptimal execution plan even if the automatic statistics gathering job is enabled.

C. If concurrent statistics gathering is done by using parallel execution, the Resource Manager should be used for efficient resource management.

D. For each session that is accessing a global temporary table, the optimizer uses only the shared statistics.