Oracle 1Z0-064 Study Guides 2021

NEW QUESTION 1
Examine the partial TKPROF output for an SQL statement:

Which two inferences can definitely be made from this output? (Choose two.)

• A. Array fetch operations were not performed for this query.
• B. No hard parse was performed for this query.
• C. The number of logical I/Os is almost equal to the number of physical I/Os.
• D. Another transaction held a shared lock on the table, thereby causing a significant delay.

Answer: BD

NEW QUESTION 2
Your database supports a DSS workload. In an application, a few complex queries that contain multiple functions and expressions are using materialized views. You notice that some queries are performing poorly because they are not benefiting from query rewrites.
Which three actions would you take to improve the performance of queries? (Choose three.)

• A. Create an SQL Tuning Set (STS) and submit as input to the SQL Access Advisor to generate recommendations about query rewrite and fast refresh for materialized views.
• B. Use the DBMS_MVIEW.EXPLAIN_REWRITE procedure to analyze why a query failed to rewrite.
• C. Create an STS and submit as input to the SQL Performance Analyzer to get recommendations about improving the performance of queries.
• D. Use the DBMS_ADVISOR.TUNE_MVIEW procedure to get recommendations about rewriting materialized views.
• E. Use the DBMS_ADVISOR.QUICK_TUNE procedure to analyze queries based on the usage of query rewrite with materialized views.

Answer: ACE

NEW QUESTION 3
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 4
Examine the command to change a parameter value from the default to 50: SQL> ALTER SYSTEM SET OPTIMIZER_INDEX_COST_ADJ = 50;
What is the effect of changing the value of the parameter? (Choose the best answer.)

• A. It influences the optimizer to use full table scans instead of index scans as the estimated cost of full table scan is reduced.
• B. It influences the optimizer to use bitmap indexes as the estimated cost of conversion from bitmap to rowid is reduced.
• C. It influences the optimizer to always use fast full index scans as the estimated cost of using an index is reduced.
• D. It influences the optimizer to use indexes instead of full table scans as the estimated cost of using an index is reduced.

Answer: A

Explanation: Reference: http://www.dba-oracle.com/oracle_tips_cost_adj.htm

NEW QUESTION 5
You execute this query twice in a session:

Then you query V$SQL_SHARED_CURSOR for details about child cursors as shown.

Which two statements are true? (Choose two.)

• A. No statistics were collected during the first execution of the query.
• B. A subsequent execution of the query in this session is likely to undergo a soft parse.
• C. The second execution of the query was hard parsed because the estimated cardinality was inaccurate.
• D. A subsequent execution of the query in this session will undergo a hard parse.
• E. The second execution of the query was hard parsed because extended statistics were collected after the first execution of the query.

Answer: BC

NEW QUESTION 6
You define the warning threshold for the tablespace usage metric for the USERS tablespace to be 60% and the critical threshold to be 80%.
Which two sources should you check for the alert information when either the warning or the critical threshold is exceeded? (Choose two.)

• A. the alert log
• B. Oracle Enterprise Manager Cloud Control
• C. DBA_ALERT_HISTORY
• D. DBA_OUTSTANDING_ALERTS
• E. DBA_ACTIVE_SESSION_HISTORY
• F. DBA_THRESHOLDS

Answer: AF

NEW QUESTION 7
Examine this list of possible tasks:
1. Ensure that STATISTICS_LEVEL is set to TYPICAL or ALL.
2. Ensure that TIMED_STATISTICS is set to TRUE.
3. Set MAX_DUMP_FILE_SIZE to UNLIMITED and DIAGNOSTIC_DEST to an appropriate destination.
4. Ensure that SQL_TRACE is set to TRUE.
5. Enable tracing at the database instance level by using the
DBMS_MONITOR.DATABASE_TRACE_ENABLE procedure.
6. Enable tracing in the required session by using the DBMS_SESSION.SET_SQL_TRACE procedure.
7. Run TKPROF with the EXPLAIN parameter on the output trace file.
8. Run the trcsess utility on the output trace files, and then run TKPROF on the output of the trcsess utility.
Select the minimum tasks to perform, in the correct order, to generate both a formatted trace file with timing information and an explain plan for each SQL statement for all sessions. (Choose the best answer.)

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

Answer: C

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

Answer: CF

NEW QUESTION 9
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 10
You want to generate statistics for new objects added to an OLTP application without affecting old statistics and the sessions that currently use them.
Which two tasks would you perform to test the new statistics? (Choose two.)

• A. Set the OPTIMIZER_USE_PENDING_STATISTICS initialization parameter to TRUE for the session.
• B. Set the STALE_PERCENT statistics preference to zero and then gather statistics.
• C. Set the PUBLISH statistics preference to FALSE and then gather statistics.
• D. Use the DBMS_STATS.PUBLISH_PENDING_STATS procedure to make pending statistics the current statistics.
• E. Set the NO_INVALIDATE statistics preference to FALSE and gather statistics without affecting old statistics.

Answer: AB

NEW QUESTION 11
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 12
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 13
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 14
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:

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

• A. Gather statistics for the objects used in the queries.
• B. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to evolve new plans and fix the plans for the statements.
• C. Create an SQL Tuning Set (STS) and run it through the SQL Access Advisor to generate recommendations.
• D. Create an STS and run it through the SQL Tuning Advisor to generate recommendations.
• E. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE.
• F. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function to alter the acceptedplans as fixed plans.

Answer: ABC

NEW QUESTION 15
Identify two effects of the DB_FILE_MULTIBLOCK_READ_COUNT parameter on the optimizer. (Choose two.)

• A. Decreasing the value of DB_FILE_MULTIBLOCK_READ_COUNT from the default increases the cost of index probes for DSS workloads.
• B. A full table scan can become cheaper than index scans if the database instance has a high enough DB_FILE MULTIBLOCK_READ_COUNT for both OLTP and DSS workloads.
• C. Increasing the value of DB_FILE_MULTIBLOCK_READ_COUNT within OS limits lowers the costing of an index probe that is done in conjunction with a nested loop for OLTP workloads.
• D. In DSS workloads where full table scans may run in parallel and bypass the buffer cache, decreasing the value of DB_FILE_MULTIBLOCK_READ_COUNT from the default increases the cost of full table scans.
• E. Increasing the value of DB_FILE_MULTIBLOCK_READ_COUNT within OS limits lowers the cost of full table scans and can result in the optimizer choosing a full table scan over an index scan for both OLTP and DSS workloads.

Answer: BE

NEW QUESTION 16
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