Pass4itsure > Oracle > Oracle Database > 1Z0-064 > 1Z0-064 Online Practice Questions and Answers

1Z0-064 Online Practice Questions and Answers

Questions 4

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.

Buy Now
Questions 5

Examine the structure of the EMPLOYEES table.

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 the DEPARTMENT_ID column.

B. Change the default CURSOR_SHARING value to FORCE.

C. Execute the query with the same DEPARTMENT_ID value multiple times.

D. Create an index on the DEPARTMENT_ID column.

E. Gather statistics for the index.

F. Regather statistics on the table.

Buy Now
Questions 6

Which two statements are true about ADDM? (Choose two.)

A. It analyzes the performance of a database instance based on the time period covered by the most recent AWR snapshot, and generates recommendations based on hard-coded criteria.

B. It can analyze performance issues that occurred in past events provided they fall within the AWR retention period.

C. ADDM resource utilization and cost of analysis depends on the actual load on the database and the number of performance problems analyzed.

D. It first identifies the performance symptoms, and then refines them to reach the root cause with the singular aim of reducing the DB CPU metric.

E. It documents only those components and wait classes that are significantly impacting the performance of the database.

Buy Now
Questions 7

You are administering a database that supports an OLTP workload. CURSOR_SHARING is set to EXACT for the instance. An application is frequently executing almost identical queries that vary in literal values in the WHERE clause, causing a large number of hard parses to occur.

Which four statements would be true if you use bind variables for these queries? (Choose four.)

A. Mutex contention in the library cache will be reduced.

B. The optimizer will use one parent cursor and one child cursor for each SQL statement with different literal values.

C. Hard parses will be reduced for the queries.

D. The optimizer will use bind peeking and subsequent execution of the queries will always generate the same plans irrespective of the cardinality.

E. The optimizer will generate the same plan for all bind values if no histograms exist on the columns used in the WHERE clause of these queries.

F. The optimizer will use bind peeking and use the literal value to determine the execution plan for these queries.

Buy Now
Questions 8

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

A. Configure the Server Result Cache.

B. Configure shared server mode.

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

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

E. Use more anonymous procedures.

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

Buy Now
Questions 9

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

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

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

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

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

Buy Now
Questions 10

Which two statements are true about the interpretation of Buffer Cache Hit Ratio in the Instance Efficiency Percentages section of an AWR report? (Choose two.)

A. A high value indicates that the buffer cache is adequately sized for the current workload.

B. Poor hit ratios indicate that a large number of indexed lookups or small table scans are being performed.

C. A low hit ratio does not necessarily imply that increasing the size of the buffer cache will improve performance.

D. A high hit ratio may indicate that repeated scanning of the same large table or index is being performed.

E. A low hit ratio indicates that a KEEP buffer pool should be configured based on the size of the largest object accessed in the buffer cache.

Buy Now
Questions 11

In which three scenarios can you use ADDM to get recommendations?

A. to determine the SQL statements that are performing poorly before you launch the SQL Tuning Advisor to tune them

B. to determine the segments on which waits are occurring when the database has a high number of buffer busy waits

C. to determine whether SQL profiling needs to be done to tune SQL statements transparently without requiring any change to the application code

D. to determine if the SGA_TARGET parameter value needs to be changed when your database is configured for Automatic Shared Memory Management and additional physical memory is added to the database server

E. to analyze problems in an unresponsive database, which requires a database restart

Buy Now
Questions 12

In which three scenarios would you recommend the use of the SQL Performance Analyzer?

A. to identify SQL statements that require SQL profiles

B. to analyze the impact of network and interconnect changes on database workload

C. to identify SQL statements whose performance may have regressed due to a hardware upgrade

D. to analyze the impact of new indexes and materialized views on the SQL statements executed by an application

E. to identify SQL statements in an application whose performance may have regressed due to migration from Oracle Database 11g to 12c

F. to analyze the impact of using bind variables for syntactically similar SQL statements

Buy Now
Questions 13

Your database supports an OLTP workload where a large number of syntactically similar queries are executed. Examine the Instance Efficiency Percentages in the Automatic Workload Repository (AWR) report of the last hour: Which two inferences can be drawn from the report?

A. The optimizer is waiting for resources during parsing of the queries.

B. The CPU is spending more time on pinning cursors in the library cache.

C. The database buffer cache is undersized and is causing contention.

D. Cursors are not getting shared in the library cache.

Buy Now
Exam Code: 1Z0-064
Exam Name: Oracle Database 12c: Performance Management and Tuning
Last Update: Mar 21, 2024
Questions: 119
10%OFF Coupon Code: SAVE10

PDF (Q&A)

$45.99

VCE

$49.99

PDF + VCE

$59.99