Refined 1Z0-064 Dumps Questions 2021

NEW QUESTION 1
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 2
Examine the partial PLAN_TABLE output:

Which is the correct sequence of execution? (Choose the best answer.)

• A. 3, 2, 1, 4, 0
• B. 0, 1, 2, 3, 4, 1
• C. 0, 4, 1, 3, 2, 1
• D. 3, 2, 4, 1, 0
• E. 3, 2, 4, 1, 0, 2

Answer: A

NEW QUESTION 3
Your database supports multiple applications. The applications run on the middle tier and use connection pooling for connecting to the database.
You notice that the sessions created by the applications are competing for resources. You want to statistically measure the workload and set priorities.
What action must you perform to achieve this? (Choose the best answer.)

• A. Create services for the applications and set a relative priority by assigning them to application users and using the DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE procedure to trace the services.
• B. Create services for the applications and set a relative priority by assigning them to application users and using the DBMS_MONITOR.SESSION_TRACE_ENABLE procedure to trace the services.
• C. Create services for the applications and set the relative priority of services within an instance by mapping the services directly to consumer groups.
• D. Create services for the applications and set a relative priority by assigning them to application users.

Answer: A

NEW QUESTION 4
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 5
You are administering a database that supports a mixed workload. The CURSOR_SHARING parameter is set to the default value. While analyzing the latest Automatic Workload Repository (AWR) report, you find a large number of cursor: pin S wait on X, cursor: pin X wait on S, and library cache mutex waits in the Top 10 foreground events section. Examine the Instance Efficiency Percentages section in the AWR report:

Which three statements are true in this scenario? (Choose three.)

• A. Sessions are waiting for mutexes in share mode on cursors but other sessions areholding the mutexes in exclusive mode.
• B. The CPU is spending more time in finding cursors in the library cache.
• C. Cursors are not getting shared, resulting in a large number of hard parses.
• D. Sessions are waiting for mutexes in exclusive mode on cursors but other sessions are holding the mutexes in share mode.
• E. The buffers required by queries are not found in the buffer cache, thereby increasing expensive disk I/O.

Answer: BDE

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

• A. executing SQL statements in an SQL Tuning Set (STS) on a test database by using database links to the production database.
• B. generating only execution plans on a test database without actually running SQL statements.
• C. generating an execution plan and statistics for selective SQL statements captured in an STS
• D. loading performance data and execution plans from an STS.
• E. generating both execution plans and statistics for each SQL statement in an STS by actually running the SQL statements on a test database.

Answer: BDE

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

NEW QUESTION 7
Examine the parameters set for a database instance:

The database supports a mixed workload. Users complain about the increased response time of a few DSS queries. During investigation, you execute the query:

Based on the output, which two are possible ways to improve the performance of the queries? (Choose two.)

• A. Enable temporary undo.
• B. Enable Automatic Memory Management.
• C. Increase the number of DBWn processes.
• D. Enable Automatic Shared Memory Management.
• E. Increase the value of the SGA_TARGET parameter.
• F. Increase the value of the PGA_AGGREGATE_TARGET parameter.

Answer: CE

NEW QUESTION 8
You observe that queries are performing poorly on the SALES_RECORDS table in your database. On investigation, you find that at the end of each day the contents of the SALES_RECORDS table are moved to the SALES_HISTORY table. The delete operations cause the table to be sparsely populated.
The SALES_RECORDS table is created in a tablespace using Automatic Segment Space Management (ASSM) and row movement is enabled. The table must be accessible 24x7.
Which two tasks would you recommend to improve the performance? (Choose two.)

• A. Perform EXPORT, DROP, and IMPORT operations on the SALES_RECORDS table.
• B. Shrink the SALES_RECORDS table by using the ALTER TABLE…SHRINK SPACE command.
• C. Move the SALES_RECORDS table to a different location by using the ALTER TABLE…MOVE command.
• D. Deallocate the space in the SALES_RECORDS table by using the ALTER TABLE…DEALLOCATE UNUSED command.
• E. Move the SALES_RECORDS table to a tablespace by using manual segment space management.
• F. Reorganize the SALES_RECORDS table online by using the DBMS_REDEFINITION package.

Answer: BD

NEW QUESTION 9
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 10
In your database, the locally managed tablespace, USERS, has the default space usage alert set to 85% for the warning level and 97% for the critical level.
Which two statements are true? (Choose two.)

• A. Alerts are recorded in both Oracle Enterprise Manager Cloud Control and DBA_OUTSTANDING_ALERTS only when the critical threshold is exceeded.
• B. Alert settings for the warning and critical levels must be disabled before taking the USERS tablespace offline.
• C. Alerts that are triggered are automatically recorded in DBA_ALERT_HISTORY after they are cleared.
• D. Alerts are triggered when the space usage reaches the warning level, again when it reaches the critical level, and yet again when the space usage falls below the critical level.

Answer: BC

NEW QUESTION 11
You plan to upgrade your production database from Oracle Database 11g to 12c and also to introduce new objects to the database. You also want to upgrade the hardware. You have already created a test system with the upgrades to be made to the production database. As part of the testing, you want to:
✑ analyze and compare the overall database workload with concurrency and transaction characteristics
✑ find SQL statements that might get regressed because of the upgrade
✑ analyze execution plans for SQL statements for which performance might get regressed
✑ analyze the impact of new schema objects on database performance
Which two tools would you recommend to achieve the objective? (Choose two.)

• A. Database Replay
• B. SQL Tuning Advisor
• C. SQL Access Advisor
• D. Automatic Database Diagnostic Monitor (ADDM) compare periods report
• E. SQL Performance Analyzer
• F. Automatic Workload Repository (AWR) compare periods report

Answer: BE

NEW QUESTION 12
Examine the Load Profile section of an AWR report:

Which two inferences can you derive from the details in this section? (Choose two.)

• A. The values for Redo size and Block changes imply that only updates were performed by transactions.
• B. The values for Parses (SQL) and Hard parses (SQL) imply that cursor sharing occurred quite often.
• C. The values for DB Time and DB CPU imply that the database had a high proportion of idle time during the specified snapshot interval.
• D. The values for SQL Work Area and User calls imply that only sort-based operations were performed.
• E. The values for Logical reads and Physical reads imply that the number of disk reads per second was less than the total number of DB block reads and consistent gets per second.

Answer: BD

NEW QUESTION 13
You want to capture AWR data to monitor performance variation every Monday between 9:00 AM and 12:00 PM for three months and automatically remove the older AWR data every fortnight.
How would you achieve this? (Choose the best answer.)

• A. Create AWR baselines.
• B. Create SQL plan baselines.
• C. Create repeating baseline templates.
• D. Create database services and make sure that user connections use them to connect to the database instance.
• E. Create a single baseline template.

Answer: D

NEW QUESTION 14
In the CUSTOMERS table, the values in the CUST_STATE column are dependent on the values in the COUNTRY_ID column. You want to make the optimizer aware of this dependency when these columns are used together in WHERE clause predicates that contain equalities or in-lists.
Which two methods achieve this? (Choose two.)

• A. gathering statistics on the CUSTOMERS table and its dependent objects, and then locking the statistics
• B. using SQL plan directives to generate an optimal plan
• C. setting the dynamic statistics level to 4 and setting the OPTIMIZER_USE_PENDING_STATISTICS initialization parameter to true
• D. creating column group statistics, regathering statistics, and ensuring that histograms exist on both these columns

Answer: AD

NEW QUESTION 15
Examine the partial AWR report taken for a time period of 60 minutes:

Which two inferences can you draw from this report? (Choose two.)

• A. The database user calls are issuing frequent explicit commits.
• B. The CPUs are busy executing server processes and background processes for a considerable amount of CPU time.
• C. The database user calls are spending most of their time in I/O for single block reads.
• D. The database user calls are spending most of their time waiting for sessions that are in more important consumer groups.

Answer: BC

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