Oracle Updated 1z0-084 Exam Questions and Answers by crystal

The SHRINK SPACE operation compacts the table, which can reduce fragmentation and thus improve performance for sequential reads of the table. This operation could improve full table scans, which are typically associated with db file sequential read wait events.

References:

• Oracle Database Administrator’s Guide, 19c

When you enable statistics gathering for a specific service and module usingDBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE, the output is aggregated and can be viewed using theV$SERV_MOD_ACT_STATSdynamic performance view. This view contains the cumulative statistics of database activity broken down by service and module, which is exactly what you collect when executing the provided command.

• B (Incorrect):While many types of trace files are located in the Diagnostic Destination directory ($ORACLE_BASE/diag), the aggregated statistics for services and modules are not written to trace files but are instead viewable through dynamic performance views.
• C (Incorrect):TheV$SERVICE_STATSview provides service-level statistics but does not provide the combined service/module-level breakdown.
• D (Incorrect):The output of the PL/SQL block is not written to a file in the current working directory; it is stored in the data dictionary and accessible via dynamic performance views.

References:

• Oracle Database PL/SQL Packages and Types Reference:DBMS_MONITOR
• Oracle Database Reference:V$SERV_MOD_ACT_STATS

To investigate the cause of the ORA-04036 error, which indicates that PGA memory usage exceeds thePGA_AGGREGATE_LIMIT, the appropriate views to consult areDBA_HIST_ACTIVE_SESS_HISTORYandDBA_HIST_PROCESS_MEM_SUMMARY.

• DBA_HIST_ACTIVE_SESS_HISTORY:This view provides historical information about active sessions in the database. It includes details about the SQL executed, the execution context, and the resources consumed by each session. By examining this view, you can identify the specific sessions and SQL queries that were active and potentially consuming excessive PGA memory around the time the ORA-04036 error occurred.
• DBA_HIST_PROCESS_MEM_SUMMARY:This view contains historical summaries of memory usage by processes. It can help in identifying the processes that were consuming a significant amount of PGA memory, leading to the ORA-04036 error. This view provides aggregated memory usage information over time,making it easier to pinpoint the processes responsible for high PGA memory consumption.

Together, these views offer a comprehensive overview of the memory usage patterns and specific queries or processes that might have contributed to exceeding thePGA_AGGREGATE_LIMIT, resulting in the ORA-04036 error.

References:

• Oracle Database Reference:DBA_HIST_ACTIVE_SESS_HISTORY
• Oracle Database Reference:DBA_HIST_PROCESS_MEM_SUMMARY
• Oracle Database Performance Tuning Guide:Managing Memory

To reduce the number of hard parses due to several almost identical SQL statements, you can take the following actions:

• C (Correct):Increasing the size of the library cache can help reduce hard parses by providing more memory to store more execution plans. This allows SQL statements to be shared more effectively.
• E (Correct):Setting theCURSOR_SHARINGparameter toFORCEwill cause Oracle to replace literals in SQL statements with bind variables, which can significantly reduce the number of hard parses by making it more likely that similar SQL statements will share the same execution plan.

The other options do not directly impact the number of hard parses:

• A (Incorrect):Creating the KEEP cache and caching tables accessed by the SQL statements can improve performance for those tables, but it does not directly reduce the number of hard parses.
• B (Incorrect):Creating the RECYCLE cache and caching tables accessed by the SQL statements can make it more likely that objects will be removed from the cache quickly, which does not help with hard parse issues.
• D (Incorrect):SettingOPTIMIZER_CAPTURE_SQL_PLAN_BASELINEStoTRUEcan help stabilize SQL execution plans but will not reduce the number of hard parses. This parameter is used to automatically capture SQL plan baselines for repeatable SQL statements, which can prevent performance regressions due to plan changes.

References:

• Oracle Database Performance Tuning Guide:Minimizing Hard Parses
• Oracle Database SQL Tuning Guide:CURSOR_SHARING