0841

The CURSOR_SHARING and OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameters are set to default. The top five wait events in an AWR report are due to a large number of hard parses because of several almost identical SQL statements. Which two actions could reduce the number of hard parses? (Choose two.). Create the KEEP cache and cache tables accessed by the SQL statements. Create the RECYCLE cache and cache tables accessed by the SQL statements. Increase the size of the library cache. Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE. Set the CURSOR_SHARING parameter to FORCE.

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.

Examine this AWR report excerpt: You must reduce the impact of database I/O, without increasing buffer cache size and without modifying the SQL statements. Which compression option satisfies this requirement?. COLUMN STORE COMPRESS FOR QUERY LOW. ROW STORE COMPRESS. ROW STORE COMPRESS ADVANCED. COLUMN STORE COMPRESS FOR QUERY HIGH.

Examine this output of a query of V$PGA_TARGET_ADVICE: Which statements is true?. With a target of 700 MB or more, all multipass executions work areas would be eliminated. PGA_AGGREGATE_TARGET should be set to at least 800 MB. PGA_AGGREGATE_TARGET should be set to at least 700 MB. With a target of 800 MB or more, all one-pass execution work areas would be eliminated.

Examine this statement and its corresponding execution plan: Which phase introduces the CONCATENATION step?. SQL Semantic Check. SQL Execution. SQL Row Source Generation. SQL Transformation. SQL Adaptive Execution.

You execute the following: EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE; Which advisor remains enabled?. Automatic SQL Tuning. SQL Plan Management Evolve Advisor. Optimizer Statistics Advisor. Automatic Optimizer Statistics Collection. Automatic Segment Advisor.

You must write a statement that returns the ten most recent sales. Examine this statement: SELECT s.prod_id, p.prod_name, s.amount_sold, c.cust_name, c.cust_city FROM sales s, customers c, products p WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id ORDER BY s.time_id FETCH FIRST 10 ROWS ONLY; Users complain that the query executes too slowly. Examine the statement's current execution plan: What must you do to reduce the execution time and why?. Create an index on SALES.TIME_ID to force the return of rows in the order specified by the ORDER BY clause. Replace the FETCH FIRST clause with ROWNUM to enable the use of an index on SALES. Collect a new set of statistics on PRODUCT, CUSTOMERS, and SALES because the current stats are inaccurate. Enable Adaptive Plans so that Oracle can change the join method as well as the join order for this query. Create an index on SALES.CUST_ID to force an INDEX RANGE SCAN on this index followed by a NESTED LOOP join between CUSTOMERS and SALES.

Which two statements are true about Data Pump import for objects that used the In Memory (IM) column store in their source database? (Choose two.). It always gives preference to the IM column store clause defined at the tablespace level over table-level definitions. It must always transport existing INMEMORY attributes. Its INMEMORY_CLAUSE of the Data Pump Export allows modifications to IM column store clause of a table with existing INMEMORY setting. Its TRANSFORM clause can be used to add the INMEMORY clause to exported tables that lack them. It ignores the IM column store clause of the exporting objects. It can generate the INMEMORY clause that matches the table settings at export time.

For which two actions can SQL Performance Analyzer be used to assess the impact of changes to SQL performance? (Choose two.). storage, network, and interconnect changes. operating system upgrades. changes to database initialization parameters. database consolidation for pluggable databases (PDBs). operating system and hardware migrations.

You want to reduce the amount of db file scattered read that is generated in the database. You execute the SQL Tuning Advisor against the relevant workload. Which two can be part of the expected result? (Choose two.). 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.

Multiple sessions are inserting data concurrently into a table that has an LOB column. At some point in time, one of the sessions cannot find available space in the LOB segment and needs to allocate a new extent. Which wait event will be raised in the other sessions that need space in the LOB column?. enq: SQ - contention. enq: TM - contention. enq: HW - contention. enq: TX - allocate ITL entry.

You manage a 19c database with default optimizer settings. This statement is used extensively as subquery in the application queries: SELECT city_id FROM sh2.sales WHERE city_id=:B1 You notice the performance of these queries is often poor and, therefore, execute: SELECT city_id,COUNT(*) FROM sh2.sales GROUP BY city_id; Examine the results: CITY_ID COUNT(*) ------- -------- 1 8 2 1 3 12 4 121 5 1218921 6 118 There is no index on the CITY_ID column. Which two options improve the performance? (Choose two.). Generate frequency histograms on the CITY_ID column. Create an index on the CITY_ID column. Use a SQL Profile to enforce the appropriate plan. Force the subquery to use dynamic sampling. Activate the adaptive plans.

Database performance has degraded recently. INDEX RANGE SCAN operations on index IX_SALES_TIME_ID are slower due to an increase in buffer gets on SALES table blocks. Examine these attributes displayed by querying DBA_TABLES: OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN ----- ---------- -------- ------ ------------ --------- --------- ----------- SH2 SALES 3675372 18337 0 0 25 37 Now, examine these attributes displayed by querying DBA_INDEXES: OWNER TABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS ----- ---------- ---------------- ------ ----------- ------------- SH2 SALES IX_SALES_TIME_ID 3 353967 3541460 AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS ------------------------ ----------------- ------ -------- 9 2830691 VALID 3675372 Which action will reduce the excessive buffer gets?. Re-create the SALES table sorted in order of index IX_SALES_TIME_ID. Re-create index IX_SALES_TIME_ID using ADVANCED COMPRESSION. Re-create the SALES table using the columns in IX_SALES_TIME_ID as the hash partitioning key. Partition index IX_SALES_TIME_ID using hash partitioning.

You must produce a consolidated formatted trace file by combining all trace files generated by all clients for a single service. Which combination of utilities does this?. Trace Analyzer and Trcsess. Trcsess and TKPROF. Autotrace and TKPROF. TKPROF and Trace Analyzer.

SGA_TARGET and PGA_AGGREGATE_TARGET are configured to nonzero values. MEMORY_TARGET is then set to a nonzero value but MEMORY_MAX_TARGET is not set. Which two statements are true? (Choose two.). SGA_TARGET and PGA_AGGREGATE_TARGET will define lower size limits for the SGA and PGA, respectively. MEMORY_MAX_TARGET will default to either the sum of SGA_TARGET and PGA_AGGREGATE_TARGET or to MEMORY_TARGET, whichever is smaller. PGA_AGGREGATE_TARGET will be ignored but SGA_TARGET will define the lower size limit for SGA size. MEMORY_MAX_TARGET will default to zero. SGA_TARGET will be ignored but PGA_AGGREGATE_TARGET will define the lower size limit for PGA size. MEMORY_MAX_TARGET will default to either the sum of SGA_TARGET and PGA_AGGREGATE_TARGET or to MEMORY_TARGET, whichever is greater. MEMORY_MAX_TARGET will default to MEMORY_TARGET.

Which three statements are true about using the In Memory (IM) column store? (Choose three.). It does not improve performance for queries using cached results of function evaluations on columns from the same table. It does not improve performance for queries that use join groups on columns from different tables. It can improve OLTP workload performance by avoiding the use of indexes. It does not improve performance for queries using user-defined virtual column results. It does not require all database data to fit in memory to improve query performance. It improves performance for queries joining several tables using bloom filter joins.

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 X Which 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;.

Which two statements are true about cursor sharing? (Choose two.). 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 of a 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.

Examine this statement and output: Which three statements are true? (Choose three.). Both 9822 and 8779 sessions are waiting for operating system resources. Session 8779 may be waiting due to a network problem. Session 9857 waited 1354 seconds for another process, which was also waiting for a transaction to end. Session 9857 is not waiting. Session 8779 may be waiting for a user or application response. Session 9822 will always stop waiting if the session that owns the TX enqueue issues a COMMIT statement as session 9822 is the first session in the transaction queue.

Accessing the SALES table causes excessive db file sequential read wait events. Examine this AWR excerpt: Segments by Physical Reads Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total ----- --------------- ----------------- -------------- --------- -------------- ------ SH2 SH2_DATA SALES TABLE 4,854,865 69.31 SH2 SH2_DATA IX_SALES_CUST_ID INDEX 683,225 11.53 Now, examine these attributes displayed by querying DBA_TABLES: OWNER TABLE_NAME NUM_ROWS BLOCKS PCT_FREE PCT_USED INI_TRANS CHAIN_CNT AVG_ROW_LEN ----- ---------- --------- ------- -------- -------- --------- --------- ----------- SH2 SALES 33675372 118337 20 50 1 7196335 125 Finally, examine these parameter settings: NAME VALUE -------------------------------- ----- db_block_size 8192 db_file_multiblock_read_count 128 sga_target 132G sga_max_size 132G Which two must both be used to reduce these excessive waits? (Choose two.). Partition the SALES table. Increase PCTFREE for the SALES table. Re-create the SALES table. Compress the SALES table. Coalesce all SALES table indexes.

You are informed that the RMAN session that is performing the database duplication is much slower than usual. You want to know the approximate time when the rman operation will be completed. Which view has this information?. V$SESSION. V$SESSTAT. V$RMAN_BACKUP_JOB_DETAILS. V$SESSION_LONGOPS.

Which two statements are true about space usage in temporary tablespaces? (Choose two.). When a global temporary table instantiation is too large to fit in memory, space is allocated in a temporary tablespace. Temporary tablespaces setting includes quotas to limit temporary space used by a session for that Temporary tablespace. A sort will fail if a sort to disk requires more disk space and no additional extent can be found/allocated in/for the sort segment. When a session consumes all temporary tablespace storage, then the session would hang until the temporary space used by that session is cleared. Lack of temporary tablespace space for sort operations can be prevented by using temporary tablespace groups.

Examine this command: BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE ( day_of_week => 'monday', hour_in_day => 17, duration => 3, expiration => 30, start_time => '2021-04-02 17:00:00 PST', end_time => '2022-04-04 17:00:00 PST', baseline_name_prefix => 'baseline_2021_', template_name => 'template_2021', dbid => 3310949047 ); END; / What is the maximum number of baselines generated by this command that you can have at any given time?. 1. 3. 52. 5.

Which two statements are true about session wait information contained in v$session or v$session_wait? (Choose two.). Rows for sessions displaying WAITED UNKNOWN TIME in the STATE column indicate that the session is still waiting. Rows for sessions that are currently waiting have a wait time of 0. Rows for sessions that are not waiting might contain the actual wait time for the last event for which they waited. Rows for sessions that are currently waiting have their wait time incremented every microsecond. Rows for sessions that are not waiting always contain the total wait time since the session started.

Which three statements are true about tuning dimensions and details of v$sys_time_model and DB time? (Choose three.). 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 statements are true about the use and monitoring of Buffer Cache Hit ratios and their value in tuning Database I/O performance? (Choose two.). The performance of workloads that primarily generate full table scans and fast full index scans are always affected by the cache hit ratio. A 99% cache hit ratio can be observed for database instances which have very poor I/O performance. The buffer cache advisory view v$db_cache_advice provides advice on cache hit ratios appropriate for the instance workload. Both the RECYCLE and KEEP buffer caches should always have a very high cache hit ratio. A 60% cache hit ratio can be observed for database instances which have very good I/O performance.

What are the least elevated values of STATISTICS_LEVEL and CONTROL_MANAGEMENT_PACK_ACCESS that allow the usage of Monitoring of Database Operations?. STATISTICS_LEVEL=ALL and CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC+TUNING. STATISTICS_LEVEL=BASIC and CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC. STATISTICS_LEVEL=TYPICAL and CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC+TUNING. STATISTICS_LEVEL=TYPICAL and CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC.

You need to transport performance data from a Standard Edition to an Enterprise Edition database. What is the recommended method to do this?. Export the data by using expdp from Statspack and import it by using $ORACLE_HOME/rdbms/admin/awrload into the AWR repository. Export the data by using expdp from the Statspack repository and import it by using impdp into the AWR repository. Export the data by using the expdp utility and parameter file spuexp.par from the Statspack repository and import it by using impdp into Export the data by using expdp from the Statspack repository and import it by using impdp into the AWR repository. Export the data by using the exp utility and parameter file spuexp.par from the Statspack repository and import it by using imp into a dedicated Statspack schema on the destination.

A database supporting a mixed workload is hosted on a server with 64 CPUs. A large number of free buffer waits and buffer busy waits occur affecting performance. The buffer cache size was then increased but after a few hours, the same wait events occur more often than before the change. NAME TYPE VALUE ---------------------------- ----------- ----- dbwr_io_slaves integer 0 db_file_multiblock_read_count integer 100 db_writer_processes integer 1 memory_target big integer 1G Which two actions can help reduce the number of these waits? (Choose two.). increasing the value of DB_FILE_MULTIBLOCK_READ_COUNT to 128. increasing the size of MEMORY_TARGET. setting dbwr_io_slaves to 64. increasing the value of DB_WRITER_PROCESSES to 64. reducing the value of DB_FILE_MULTIBLOCK_READ_COUNT to 64.

Which two types of performance problems are reported by ADDM for PDBs? (Choose two.). I/O capacity limits. Excessive checkpoint writes. SGA sizing issues. Top SQL statements. User I/O waits.

Which Optimizer component helps decide whether to use a nested loop join or a hash join in an adaptive execution plan?. Statistics Feedback. SQL Plan Directives. Statistics Collector. Automatic Reoptimization. Dynamic Statistics.

Which application lifecycle phase could be managed reactively?. Design and development. Upgrade or migration. Testing. Production. Deployment.

A database instance is suffering poor I/O performance on two frequently accessed large tables. No Big Table caching occurs in the database. Examine these parameter settings: PARALLEL_DEGREE_POLICY=LIMITED DB_BIG_TABLE_CACHE_PERCENT_TARGET=10 DB_CACHE_SIZE=500M DB_KEEP_CACHE_SIZE=0M Which are two actions either one of which will allow Big Table caching to occur?. increasing DB_BIG_TABLE_CACHE_PERCENT_TARGET to at least 50. setting DB_KEEP_CACHE_SIZE to at least 50M. increasing DB_BIG_TABLE_CACHE_PERCENT_TARGET to at least 25. increasing DB_CACHE_SIZE to 1G. setting PARALLEL_DEGREE_POLICY=ADAPTIVE. setting PARALLEL_DEGREE_POLICY=AUTO.

What is the right time to stop tuning an Oracle database?. When the allocated budget for performance tuning has been exhausted. When all the concurrency waits are eliminated from the Top 10. When the buffer cache and library cache hit ratio is above 95%. When the I/O is less than 10% of the DB time. When the tuning goal has been met.

You use SQL Tuning Advisor to tune a given SQL statement. The analysis eventually results in the implementation of a SQL Profile. You then generate the new SQL Profile plan and enforce it using a SQL Plan Baseline but forget to disable the SQL Profile and a few days later you find out that the SQL Profile is generating a new execution plan. Which two statements are true? (Choose two.). The SQL Profiles as well as SQL Plan Baseline are implemented using hints, so they both generate the same plan. The execution plan is the one enforced by the SQL Profile. The execution plan is the one enforced by the SQL Plan Baseline. The SQL Plan Baseline must be accepted in order to be used for the execution plan. The conflict between the two plan stability methods results in an error. The existence of two concurrent plan stability methods generates a child cursor for every execution.

You must configure and enable Database Smart Flash Cache for a database. You configure these flash devices: /dev/sdj with size 128G /dev/sdk with size 64G Examine these parameter settings: NAME TYPE VALUE -------------------- ----------- ---------------------- db_flash_cache_file string /dev/sdj, /dev/sdk db_flash_cache_size big integer 0 memory_max_target big integer 64G memory_target big integer 64G sga_target big integer 0 What must be configured so that the database uses these devices for the Database Smart Flash Cache?. Set DB_FLASH_CACHE_SIZE to 192G and MEMORY_TARGET to 256G. Set DB_FLASH_CACHE_SIZE parameter to 192G. Disable Automatic Memory Management and set SGA_TARGET to 256G. Set DB_FLASH_CACHE_SIZE to 256G and change device /dev/sdk to 128G. Set DB_FLASH_CACHE_SIZE parameter to 128G, 64G.

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? (Choose two.). DBA_HIST_ACTIVE_SESS_HISTORY. DBA_HIST_SQLSTAT. DBA_HIST_SQLTEXT. DBA_HIST_PGASTAT. DBA_HIST_PROCESS_MEM_SUMMARY.

Database performance degraded between 23:15 and 23:30 for the last three nights. The AWR snapshot interval is one hour. The ADDM 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 two actions can cause invalidation or loss of one or more results in the SQL Query Result Cache? (Choose two.). One or more results were aged out of the result cache. result_cache_max_size is set dynamically to 0. A request was made by the RCBG background of a non-RAC database. A request was made by the RCBG background process in a physical standby database that is opened read only and whose primary has a result cache. Decreasing the value set for RESULT_CACHE_REMOTE_EXPIRATION.

Examine these statements and output: sqlplus / as sysdba Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.9.0.0.0 SQL> show parameter optimizer NAME VALUE ---------------------------------- ---------------- optimizer_adaptive_plans FALSE optimizer_adaptive_statistics FALSE optimizer_dynamic_sampling 2 optimizer_features_enable 12.1.0.2 optimizer_mode ALL_ROWS optimizer_secure_view_merging TRUE optimizer_capture_sql_plan_baselines FALSE optimizer_use_sql_plan_baselines FALSE What parameter change activates the generation and use of SQL Plan Directives?. optimizer_features_enable = 12.2.0.1. optimizer_capture_sql_plan_baselines = TRUE. optimizer_dynamic_sampling =11. optimizer_adaptive_plans = TRUE. optimizer_adaptive_statistics = TRUE.

Examine this code block, which executes successfully: DBMS_SERVER_ALERT.SET_THRESHOLD( DBMS_SERVER_ALERT.CPU_TIME_PER_CALL, DBMS_SERVER_ALERT.OPERATOR_GE, '8000', DBMS_SERVER_ALERT.OPERATOR_GE, '10000', 1, 2, 'inst1', DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE, 'main.regress.rdbms.dev.us.example.com' ); What will happen?. A warning alert will be issued when CPU time exceeds 1 minute for each user call. A critical alert will be issued when CPU time exceeds 10000 microseconds for each user call. A warning alert will be issued only when CPU time exceeds 10000 microseconds for each user call. A critical alert will be issued when CPU time exceeds 2 minutes for each user call.

Examine this statement and output: SQL> BEGIN dbms_workload_capture.start_capture('CAPTURE-ORACLE_TEST', 'TEST', NULL, 'INCLUDE', FALSE); END; ERROR at line 1: ORA-15505: cannot start workload capture because instance 1 encountered errors while accessing directory "/TEST" ORA-06512: at "SYS.DBMS_WORKLOAD_CAPTURE", line 799 ORA-06512: at line 1 Which two situations can trigger this error? (Choose two.). The user lacks the required privileges to execute the DBMS_WORKLOAD_CAPTURE package or the directory. There is a file in the capture directory. The syntax is incomplete. The capture directory is part of the root file system. The instance is unable to access the capture directory.

You need to collect and aggregate statistics for the ACCTG service and PAYROLL module, and execute: BEGIN DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE( service_name => 'ACCTG', module_name => 'PAYROLL' ); END; Where do you find the output of this command?. By viewing V$SERV_MOD_ACT_STATS. In $ORACLE_BASE/diag/rdbms/<db unique name>/<instance name>/trace. By viewing V$SERVICE_STATS. In the current working directory.

Buffer cache access is too frequent when querying the SALES table. Examine this command which executes successfully: ALTER TABLE SALES SHRINK SPACE; For which access method does query performance on SALES improve?. db file scattered read. db file sequential read. index full scan. index range scan.

Which two statements are true about disabling Automatic Shared Memory Management (ASMM)? (Choose two.). All auto-tuned SGA components are reset to their original user-defined values. All SGA components excluding fixed SGA and other internal allocations are readjusted immediately after disabling ASMM. Both SGA_TARGET and SGA_MAX_SIZE must be set to zero. All SGA components retain their current sizes at the time of disabling. The SGA size remains unaffected after disabling ASMM. It requires a database instance restart to take effect.

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.

Examine these commands, which execute successfully: SQL> ALTER SYSTEM SET AWR_PDB_AUTOFLUSH_ENABLED=TRUE; SQL> EXEC dbms_workload_repository.modify_snapshot_settings(interval=>60); Which statement is true?. ADDM is enabled for all pluggable databases. ADDM, AWR, and ASH reports can be purged automatically. AWR snapshots in all pluggable databases will be purged automatically after every 60 mins. AWR snapshots can be purged manually in pluggable databases.

During which application lifecycle phase do you take baselines?. Testing. Migration or upgrade. Design and development. Deployment. Production.

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.

Users complain about slowness and session interruptions. Additional checks reveal the following error in the application log: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource Which file has additional information about this error?. Alert log. ASH report. Session trace file SQL trace file automatically generated by the error. SQL trace file automatically generated by the error.

Which three statements are true about server-generated alerts? (Choose three.). They are notifications from the Oracle Database Server of an existing or impending problem. They provide notifications but never any suggestions for correcting the identified problems. They are logged in the alert log. They can be viewed only from the Cloud Control Database home page. Their threshold settings can be modified by using DBMS_SERVER_ALERT. They may contain suggestions for correcting the identified problems.

Which two Oracle Database features use database services? (Choose two.). Oracle Automatic Reoptimization. Database Resource Manager. Oracle SQL Performance Management. Oracle SQL Tuning Advisor. Oracle Scheduler.

Which two options are part of a Soft Parse operation? (Choose two.). Syntax Check. SQL Row Source Generation. SQL Optimization. Shared Pool Memory Allocation. Semantic Check.

A Standard Edition production database has performance problems for two hours on the same day each week. Which tool must you use to diagnose the problem?. SQL Performance Analyzer. AWR Compare Periods report. Database Replay. Statspack report.

Which statement is true about DB time in V$SYS_TIME_MODEL?. DB time is organized as a simple list of statistics and any time period is attributable to only one statistic. DB time can be many times greater than the elapsed time since the database instance started. DB time excludes the time spent waiting for a CPU in the operating system run queue. DB time includes the time spent executing the RMAN backup and restore command.