Latesttests
2018 Jan Oracle Official New Released 1z0-064
100% Free Download! 100% Pass Guaranteed!
http://www.Latesttests.com/1z0-064.html

Oracle Database 12c: Performance Management and Tuning

Question No: 41

Which three methods can you use to create a pre-change SQL trial to capture performance data by using SQL Performance Analyzer? (Choose three.)

  1. executing SQL statements in an SQL Tuning Set (STS) on a test database by using database links to the production database.

  2. generating only execution plans on a test database without actually running SQL statements.

  3. generating an execution plan and statistics for selective SQL statements captured in an STS

  4. loading performance data and execution plans from an STS.

  5. generating both execution plans and statistics for each SQL statement in an STS by actually running the SQL statements on a test database.

Answer: B,D,E

Reference: https://docs.oracle.com/cd/E11882_01/server.112/e41481/spa_pre_change.htm#RATUG1 81

Question No: 42

You are administering a database that supports a mixed workload. You upgrade your database from Oracle Database 11g to 12c and after the upgrade, users complain about degraded performance of some queries. The SQL plan baselines imported from the previous version are present for the queries and are loaded to the SQL Management Base as accepted plans. On further investigation, you find that better plans are generated but not used by the optimizer.

Examine the parameters set for the instance:

Latesttests 2018 PDF and VCE

Which three tasks would you perform to improve the performance of these queries? (Choose three.)

  1. Gather statistics for the objects used in the queries.

  2. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to evolve new plans and fix the plans for the statements.

  3. Create an SQL Tuning Set (STS) and run it through the SQL Access Advisor to generate recommendations.

  4. Create an STS and run it through the SQL Tuning Advisor to generate recommendations.

  5. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE.

  6. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function to alter the accepted plans as fixed plans.

Answer: A,B,C

Question No: 43

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.)

  1. Set the STALE_PERCENT statistic preference to a higher value than the default, and then gather statistics.

  2. Set the STATISTICS_LEVEL parameter to ALL for the instance.

  3. Set the INCREMENTAL preference to TRUE, and then gather statistics.

  4. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to TRUE for the session in which you want to test the query.

  5. Set the PUBLISH statistic preference to FALSE, and then gather statistics.

  6. Set the NO_INVALIDATE statistic preference to TRUE, and then gather statistics.

Answer: B,E

Question No: 44

Examine the Time Model Statistics section of an AWR report:

Latesttests 2018 PDF and VCE

Which two inferences can be definitely derived from this section? (Choose two.)

  1. The available CPU resources were not utilized to their maximum capacity.

  2. All sequence numbers used during this AWR time interval were cached.

  3. A large number of connected user sessions were idle.

  4. New child cursors were created because of new bind values or usage of literal values as well as different bind types or sizes.

  5. The DB CPU time was not spent exclusively for processing SQL statements.

Answer: D,E

Question No: 45

Which three actions should you perform to reduce shared pool fragmentation and avoid the “ORA-04031: unable to allocate …. bytes of shared memory” error for the shared pool? (Choose three.)

  1. Configure the Server Result Cache.

  2. Configure shared server mode.

  3. Identify the packages or procedures that are causing the “ORA-04031: unable to allocate …. bytes of shared memory” error and use the DBMS_SHARED_POOL.KEEP to keep them in the shared pool.

  4. Use DBMS_SHARED_POOL.KEEP to keep the SYS.STANDARD, SYS.DBMS_STANDARD, and SYS.DIUTIL packages and frequently executed compiled triggers.

  5. Use more anonymous procedures.

  6. Standardize the type, size, and naming conventions for bind variables and spacing conventions for SQL statements and PL/SQL blocks.

Answer: B,C,D

Question No: 46

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.)

  1. Check whether direct path load operations are used to insert rows in the table.

  2. Check whether the table is frequently queried using full table scans as column store compression only minimizes I/O during full table scans.

  3. Check whether the table is frequently updated because it will have overhead for insert and update operations.

  4. Check whether the table has LOB columns as it will minimize I/O for the queries.

  5. Check whether the table blocks are sparsely populated as this will defragment the blocks.

Answer: A,B,D

Question No: 47

Examine the initialization parameters set for a database instance:

Latesttests 2018 PDF and VCE

The database supports an OLTP workload. Applications connect to the instance using shared server connections and perform small, random I/Os. All the data files are on the same disk. You notice free buffer wait events for sessions in the database instance.

To solve the problem, you increase the size of the buffer cache. But after some time, you notice sessions waiting again on free buffer waits.

What will you recommend to alleviate the issue? (Choose the best answer.)

  1. Run the I/O calibration tool.

  2. Configure the database instance to make asynchronous I/O available to DBWR.

  3. Spread the data files over multiple disks, controllers, and I/O buses to ensure that there are no hotspots in the I/O subsystem.

  4. Configure dedicated server connections for the applications.

Answer: B

Question No: 48

Which two statements are true about DB time in V$SYS_TIME_MODEL? (Choose two.)

  1. DB time cannot exceed the total elapsed time (walk clock time) since the database instance started.

  2. DB time cannot exceed the maximum number of concurrent sessions multiplied by the actual elapsed time for each session.

  3. DB time includes the time spent on client processes and background processes.

  4. Reducing DB time allows a database instance to support more user requests by using the same resources.

  5. DB time is always greater than or equal to the DB CPU time.

Answer: D,E

Question No: 49

Which two situations can lead to sparsely populated index blocks? (Choose two.)

  1. Data is frequently inserted using direct path load into a table with an index.

  2. Indexed columns in a table are frequently updated.

  3. Values in an indexed column are inserted using monotonically incrementing sequences.

  4. Bulk delete operations are performed on a table with indexes.

  5. Online table move operations are performed frequently on a table with indexes.

Answer: B,D

Question No: 50

Which two statements are true about ADDM or Real-Time ADDM? (Choose two.)

  1. ADDM can be run manually by selecting any range of AWR snapshots available within the AWR retention period, provided they do not cover a time period when the instances were restarted.

  2. ADDM runs in Partial mode to analyze any hung database issues.

  3. Real-Time ADDM can proactively detect and diagnose transient performance issues that last for a few seconds.

  4. Real-Time ADDM is automatically invoked by ADDM at the end of every hour.

Answer: A,C

100% Free Download!
Download Free Demo:1z0-064 Demo PDF
100% Pass Guaranteed!
Download 2018 Latesttests 1z0-064 Full Exam PDF and VCE

Latesttests ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

2018 Latesttests IT Certification PDF and VCE