Free Oracle Database 19c: Performance Management and Tuning Exam 1Z0-084 Exam Practice Test

Which three types of statistics are captured by statspack with snap level 6?

• Parent and child latches
• Parent and child latches
• Optimizer execution plans
• Plan usage data
• Enqueue statistics
• Segment-level statistics

18. The application provider has given full indications regarding the procedure to collect statistics.To reduce the space used in the SYSAUX tablespace, you want to prevent the optimizer statistics Advisor from running.Which method will allow you to do this?

• Set the parameter OPTIMIZER_ADAPTIVE_STATISTICS to FALSE.
• Use DBMS_AUTO_TASK_ADMIN. DISABLE to disable the AUTO_STATS_ADVISOR_TASK task.
• Set the AUTO_STATS_ADVISOR_TASK global statistics preference to FALSE.
• Use DBMS STATS.DROP ADVISOR TASK to drop the AUTO_STATS_ADVISOR_TASK task.

Database performance degraded between 23:15 and 23:30 for the last three nights. The awr snapshot interval is one hour. The AODM report contains nothing about this performance problem.With which tool can you further analyze this problem?

• SQL Performance Analyzer
• AWR Compare Periods report
• SQL Tuning Advisor
• Active Session History report

Which three statements are true about tuning dimensions and details of v$sys_time_model and DB time?

• Statspack cannot account for high CPU time when CPU TIME is a Top 10 event in DB time. When CPU time is high, SQL tuning may improve performance.
• Systems in which CPU time is dominant need more tuning that those in which WAIT TIME is dominant.
• The proportion of WAIT TIME to CPU TIME always increases with increased system load.
• When WAIT TIME is high, instance tuning may improve performance.
• Parse Time Elapsed accounts for successful soft and hard parse operations only.
• DB Time accounts for all time used by background processes and user sessions.

Which two types of performance problems are reported by ADDM for PDBS?

• I/O capacity limits
• Excessive checkpoint writes
• SGA sizing issues
• Top SQL statements
• User I/O waits

An Oracle 19c database uses default values for all optimizer initialization parameters. After a table undergoes partition maintenance, a large number of wait events occur for: cursor: pin S wait on XWhich command reduces the number of these wait events?

• ALTER SYSTEM SET CURSOR_SHARING = FORCE;
• ALTER SYSTEM SET CURSOR_SPACE_FOR_TIME - TRUE;
• ALTER SYSTEM SET CURSOR_INVALIDATION = DEFERRED;
• ALTER SYSTEM SET SESSION CACHED CURSORS = 500;

You want to reduce the amount of db file scattered read that is generated in the database. Youexecute the SQL Tuning Advisor against the relevant workload. Which two can be part of the expected result?

• recommendations regarding the creation of additional indexes
• recommendations regarding rewriting the SQL statements
• recommendations regarding the creation of materialized views
• recommendations regarding the creation of SQL Patches
• recommendations regarding partitioning the tables

This error occurred more than four hours ago in the database:ORA-04036 PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT You want to know which process and query were at fault.Which two views should you use for this purpose?

• DBA_HIST_ACTIVE_SESS_HISTORY
• DBA_HIST_SQLSTAT
• DBA_HIST_SQLTEXT
• DBA_HIST_PGASTAT
• DBA_HIST_PROCESS_MEM_SUMMARY

Which procedure gathers statistics that are always used in the generation of any execution plan?

• DBMS_STATS.GATHER_DICTIONARY_STATS
• DBMS_STATS.GATHER_FIXED_ OBJECTS_STATS
• DBMS_STATS.GATHER_DATABASE_STATS
• DBMS_STATS.GATHER_SYSTEM_STATS

Which two statements are true about cursor sharing?

• Setting Cursor_sharing to FORCE can result in a plan that is suboptimal for the majority of values bound to a bind variable when executing a cursor with one or more bind variables.
• Adaptive Cursor Sharing guarantees that a suboptimal plan will never be used on any execution ofa SQL statement.
• Setting optimizer_capture_sql_plan_baselines to TRUE loads all adaptive plans for the same statement into the cursor cache.
• Setting cursor_sharing to EXACT prevents Adaptive Cursor Sharing from being used.
• Adaptive Cursor Sharing requires histograms on filtered columns, used in equality predicates, to allow different execution plans to be generated for statements whose bound values would normally generate different plans at hard parse time.