Oracle 1Z0-064 Braindumps 2021

NEW QUESTION 1
You are administering a database that supports a DSS workload. Automatic Shared Memory Management is enabled for the database instance. Users issue queries to perform large 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 the PGA_AGGREGATE_TARGET parameter.
• C. Create a temporary tablespace group and assign it to users.
• D. Increase the value of the PGA_AGGREGATE_LIMIT parameter.
• E. Create another temporary tablespace and assign it to users.
• F. Enable temporary undo.

Answer: CD

NEW QUESTION 2
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.

Answer: BE

NEW QUESTION 3
Your database supports an OLTP workload during the day and batch processing at night. You want to monitor performance metrics to detect any degradation of performance in both types of workloads over a time period of 30 days.
Examine this list of possible steps:
1. Create a fixed baseline.
2. Create a baseline template.
3. Create a new moving window baseline.
4. Increase the retention period default value to 30 days.
5. Increase the size of the existing moving window baseline to 30 days.
6. Create warning and critical alerts for the relevant metrics.
7. Enable adaptive thresholds to detect the workload patterns and specify a high- significance-level threshold type.
8. Enable adaptive thresholds to detect the workload patterns and set different threshold values as a percentage of the maximum value.
Which option represents the required steps in the correct order? (Choose the best answer.)

• A. 5, 7
• B. 2, 4, 3
• C. 3, 4, 8
• D. 4, 5, 7
• E. 5, 1, 6, 8

Answer: E

NEW QUESTION 4
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.

Answer: AB

NEW QUESTION 5
To investigate the slow response time of queries on the TRANS table, you gathered the table statistics and executed the query:

The table is stored in a tablespace that has Automatic Segment Space Management (ASSM) enabled. The tablespace is created with a standard block size of 8192 bytes.
Which three can be reasons for the slow response time of the queries? (Choose three.)

• A. Row size is too large to fit into a single block during insert operations.
• B. Row moves from one data block to another data block because the row grows too large to fit in the original block.
• C. The table is subject to frequent insert, update, and delete DML activity leading to sparsely populated blocks.
• D. The value of PCTUSED is set to a value lower than the default, causing row changing.
• E. The value of PCTFREE is set to a value lower than the default, causing row chaining.

Answer: ABC

NEW QUESTION 6
You are administering a database that supports a mixed workload. Many applications are running on the middle tier that use connection pools to connect to the database instance. Application users perform OLTP operations during the day and another application performs batch job operations at night. You want to measure and prioritize the two workloads.
Which action would you take to achieve this? (Choose the best answer.)

• A. Create database services for the applications, assign individual sessions created by the applications to consumer groups, and then set a priority.
• B. Assign profiles to users running the batch operations and make sure that a priority is set for resource limits in profiles.
• C. Create database services for the applications and assign different profiles to the sessions to set a relative priority for resource usage.
• D. Create database services for the applications, create a job class associated with the service, batch the jobs, and then create jobs by using the job class.

Answer: C

NEW QUESTION 7
Which two statements are true about Active Session History (ASH)? (Choose two.)

• A. The Data Sample size available in an ASH report is dynamic and, at any given moment, is directly related to the amount of work being performed.
• B. ASH contains sampled data from all sessions that are connected to a database instance at any given moment.
• C. ASH samples data from V$SESSION every second.
• D. An ASH report can be used to identify the service that may be the cause of a transient performance problem.

Answer: AD

NEW QUESTION 8
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.

Answer: CD

NEW QUESTION 9
You are administering a database that supports an OLTP workload. An application regularly creates global temporary tables and a large number of transactions are performed on them. You notice that performance is degraded because of excessive generation of undo due to a large number of transactions on the global temporary tables.
What is the recommended action to improve performance? (Choose the best answer.)

• A. Increase the size of the undo tablespace and enable undo retention guarantee.
• B. Increase the size of the database buffer cache.
• C. Enable temporary undo.
• D. Increase the size of the temporary tablespace or make it autoextensible.
• E. Enable Automatic Segment Space Management (ASSM) for the undo tablespace.

Answer: C

Explanation: Reference: https://docs.oracle.com/cd/B13789_01/server.101/b10739/undo.htm

NEW QUESTION 10
You want to capture the performance of your database during the last ten days of the first quarter of the current financial year, so that you can compare this performance against the remaining quarter ends of the current financial year.
Which method should you use? (Choose the best answer.)

• A. Create a static baseline that can be used with AWR compare reports.
• B. Create a new moving window baseline and enable adaptive thresholds for relevant metrics.
• C. Use a repeating baseline template to create and drop baselines based on a repeating time schedule and set adaptive thresholds at a high significance level.
• D. Use fixed baseline templates to create a new moving window baseline and set relevant warning alerts that are computed as a percentage multiple of the maximum value observed for the data in the moving window baseline.

Answer: D

NEW QUESTION 11
Examine the parameters set for your database instance:

You are asked by a developer to create a table for an application with these requirements:
✑ The table will be used for a DSS application.
✑ High volume bulk loads will be performed.
✑ The table will be used to store archival data on which large full-table scans (FTS) will be performed.
Which attributes are the best for the tablespace in which this table should be created? (Choose the best answer.)

• A. Create it in a locally managed tablespace with ASSM enabled and assign a high value for the PCTFREE attribute.
• B. Create it in a locally managed tablespace with manual segment space management.
• C. Create it in a locally managed tablespace with a bigger nonstandard block size and ASSM enabled.
• D. Create it in locally managed tablespace with ASSM enabled and an additional freelist.

Answer: C

NEW QUESTION 12
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.

Answer: D

NEW QUESTION 13
You have been asked to use table compression for two large tables. Given are the details of the tables:
The TRANS_DET table:
✑ The table is used by an OLTP application.
✑ High volume insert and update operations are performed on the table.
✑ The table is frequently queried using index range scans.
The TRANS_HISTORY table:
✑ The table is used by a DSS application.
✑ High volume bulk loads are performed on the table.
✑ The table is used to store archival data on which large table full-table scans (FTS) are performed.
Which row store compression would you recommend for these tables with minimal overhead on performance? (Choose the best answer.)

• A. basic table compression for both the tables
• B. advanced row compression for both the tables
• C. basic table compression for the TRANS_HISTORY table and advanced row compression for the TRANS_DET table
• D. basic table compression for the TRANS_DET table and advanced row compression for the TRANS_HISTORY table
• E. warehouse compression for the TRANS_DET table and archive compression for the TRANS_HISTORY table

Answer: A

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

• A. DB time cannot exceed the total elapsed time (walk clock time) since the database instance started.
• B. DB time cannot exceed the maximum number of concurrent sessions multiplied by the actual elapsed time for each session.
• C. DB time includes the time spent on client processes and background processes.
• D. Reducing DB time allows a database instance to support more user requests by using the same resources.
• E. DB time is always greater than or equal to the DB CPU time.

Answer: DE

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.

Answer: BC

NEW QUESTION 16
Which three statements are true about the interpretation of an execution plan? (Choose three.)

• A. The cost of the entire plan is indicated by the line with Id 0 and always includes both I/O and CPU resources.
• B. The ROWS column indicates the cardinality of each operation and is always calculated by dividing the total number of rows in the table by the number of distinct values in the column used in the WHERE clause predicate.
• C. A TABLE ACCESS FULL in the Operation column for a query with a WHERE clause occurs only if no index exists for the filter column.
• D. An INDEX UNIQUE SCAN in the Operation column always implies that only one row will be returned.
• E. A HASH JOIN in the Operation column always implies that two tables are joined by using an equijoin.

Answer: BDE

NEW QUESTION 17
Users complain about increased response time for queries in your production database that supports an OLTP workload. On investigation, you notice a large number of db file scattered read, latch: cache buffers lru chain, and latch: cache buffers chains wait events:
Identify three possible reasons for the increased response time. (Choose three.)

• A. too many sort operations being performed
• B. repeated simultaneous access to a block or small number of blocks
• C. the shared pool is inadequately sized
• D. queries not using indexes and performing full table scans
• E. queries repeatedly fetching blocks that are not in the database buffer cache
• F. cursors are closed explicitly after each execution

Answer: BDE