Oracle 1Z0-064 Free Practice Questions

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
You are administering a database that supports an OLTP workload. RESULT_CACHE_MODE is set to the default value and a result cache is configured for the instance. Multiple sessions execute syntactically similar queries without dblinks, containing functions and expressions, on tables with no DML activity. Some users complain about poor performance of these queries.
You investigate and find that the queries are frequently performing physical I/O, even though the results fetched by the queries are similar.
Which two actions do you recommend to overcome the problem affecting these queries? (Choose two.)

• A. Set the RESULT_CAHCE_MODE parameter to FORCE for the instance.
• B. Use the result cache hint in the queries.
• C. Use bind variables for similar queries instead of literals.
• D. Set the RESULT_CACHE_REMOTE_EXPIRATION parameter to a nonzero value.
• E. Configure the KEEP pool and cache the queried tables used in the KEEP pool.

Answer: AB

NEW QUESTION 3
Your database supports an online transaction processing (OLTP) workload. The database uses ASM storage. One of the ASM disks goes offline because of hardware failure. When the disk is replaced and then added back to the diskgroup, database performance is affected by rebalance operations.
Which two actions would you recommend to lower the impact of rebalance operations on the performance of the database? (Choose two.)

• A. Increase the number of ASMB processes.
• B. Decrease the value of the ASM_POWER_LIMIT parameter.
• C. Set the DISK_REPAIR_TIME disk attribute to a lower value.
• D. Specify the POWER clause with a lower value in an ALTER DISKGROUP statement.
• E. Set the DISK_REPAIR_TIME disk attribute to a higher value.

Answer: BD

NEW QUESTION 4
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 5
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 6
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 7
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 8
Which two statements are true about Compare Period ADDM? (Choose two.)

• A. It is automatically invoked whenever the AWR Compare Period report is invoked.
• B. It is automatically invoked whenever ADDM is run by default.
• C. It verifies if there is any change in the workload or average resource consumption by the SQL executed during the two specified time periods, to ensure 100% accuracy.
• D. It can be used to create a comparison report between the Database Replay workload capture report and the replay report.

Answer: CD

NEW QUESTION 9
Which three statements are true about using Real-Time Database Operations? (Choose three.)

• A. The STATISTICS_LEVEL initialization parameter must be set to ALL to enable automatic SQL monitoring for all long-running queries.
• B. The CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter must be set to DIAGNOSTIC+TUNUNG to use Real-Time Database Operations.
• C. The STATISTICS_LEVEL initialization parameter can be set to TYPICAL or ALL to enable Real-Time Database Operations.
• D. Real-Time Database Operations can be enabled only at the system level.
• E. Real-Time Database Operations can be created by using the DBMS_MONITOR or DBMS_SESSION packages.
• F. Database operation monitoring starts automatically when a database operation consumes at least five seconds of the CPU or I/O time in a single execution.

Answer: BCF

NEW QUESTION 10
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 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
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 13
Examine the parameters set for your database instance:

You are administering a database that supports an OLTP workload. Users complain about the degraded performance of some queries. While diagnosing, you notice a large number of hard parses occurring for several syntactically almost identical SQL statements that differ only in literal values in the WHERE clause.
Which two actions would you recommend to improve performance? (Choose two.)

• A. Create the KEEP cache and cache the tables used in the queries.
• B. Set the CURSOR_SHARING parameter to FORCE.
• C. Use bind variables instead of literals.
• D. Create SQL plan baselines for the almost identical SQL statements and load them into the cursor cache.
• E. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE.

Answer: BE

NEW QUESTION 14
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 15
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 16
In which three situations does DB time always increase? (Choose three.)

• A. when the host is CPU bound for foreground processes
• B. when I/O wait time increases for foreground processes
• C. when more connections are made to a database instance
• D. when CPU consumption by background processes increases
• E. when wait time for data to be sent over a network increases

Answer: ABC

Explanation:
Reference: http://www.oracle.com/technetwork/oem/db-mgmt/s317294-db-perf-tuning-with- db-time-181631.pdf (page 21)

NEW QUESTION 17
Examine the initialization parameters set for a database instance:

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

• A. Run the I/O calibration tool.
• B. Configure the database instance to make asynchronous I/O available to DBWR.
• C. Spread the data files over multiple disks, controllers, and I/O buses to ensure that there are no hotspots in the I/O subsystem.
• D. Configure dedicated server connections for the applications.

Answer: B

NEW QUESTION 18
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 19
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 20
Which two actions should you take to monitor the throughput generated by the modules of an application? (Choose two.)

• A. Use the Resource Manager.
• B. Enable SQL Trace at the session level.
• C. Create a service.
• D. Use a dedicated server configuration.
• E. Use the DBMS_APPLICATION_INFO package to define the current module and action so that they appear in V$SESSION.

Answer: BE

NEW QUESTION 21
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 22
Examine the partial TOP 10 Foreground Events by Total Wait Time section of an AWR report:

What should you examine to diagnose the cause of the top three wait events? (Choose the best answer.)

• A. the V$ACTIVE_SESSION_HISTORY view
• B. the Time Model Statistics section of the AWR report
• C. the SQL statements based on elapsed time from the AWR report
• D. the Latch Activity section
• E. the Segment Statistics section of the AWR report

Answer: B

NEW QUESTION 23
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 24
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 25
......