1Z0-084問題集57問でOracle Database 19cを確実実践 [Q29-Q51]

1Z0-084問題集57問でOracle Database 19cを確実実践

リアル最新1Z0-084試験問題1Z0-084問題集

質問 # 29
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?

• A. recommendations regarding the creation of additional indexes
• B. recommendations regarding the creation of SQL Patches
• C. recommendations regarding partitioning the tables
• D. recommendations regarding rewriting the SQL statements
• E. recommendations regarding the creation of materialized views

正解：A、E

解説：
The SQL Tuning Advisor provides recommendations for improving SQL query performance. This may include suggestions for creating additional indexes to speed up data retrieval and materialized views to precompute and store query results.References:
* Oracle Database SQL Tuning Guide, 19c

質問 # 30
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 PlanBaselinebut forget to disable the SQLProfile and a few days later you find out that the SQL Profile is generating a new execution plan.
Which two statements are true?

• A. The SQL Plan Baseline must be accepted in order to be used for the execution plan.
• B. The conflict between the two plan stability methods results in an error.
• C. The existence of two concurrent plan stability methods generates a child cursor for every execution.
• D. The execution plan is the one enforced by the SQL Plan Baseline.
• E. The execution plan is the one enforced by the SQL Profile.
• F. The SQL Profiles as well as SQL Plan Baseline are implemented using hints, so they both generate the same plan.

正解：A、E

解説：
When both a SQL Profile and a SQL Plan Baseline are in place, the SQL Profile has a stronger preference and the optimizer is more likely to choose the execution plan from the SQL Profile.
C: A SQL Profile is generally more influential than a SQL Plan Baseline because it contains additional statistics and directives that help the optimizer to generate a more efficient execution plan. If both exist, the optimizer will use the profile's plan unless the baseline's plan is proven to be better through the SQL performance monitoring process.
E: SQL Plan Baselines must be accepted before they can be used by the optimizer. If a SQL Plan Baseline is not accepted, it will not be considered for generating the execution plan. Therefore, the presence of an unaccepted SQL Plan Baseline will not automatically force the optimizer to use its plan.
References:
* Oracle Database SQL Tuning Guide, 19c
* Oracle Database Administrator's Guide, 19c

質問 # 31
What are the least elevated values of statistics_level and C0NTR0LJ4ANAGEMENT_PACK_ACCESS that allow the usage of Monitoring of Database Operations?

• A. STATISTICS_LEVEL=ALL and
  CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC+TUNING
• B. STATISTICS_LEVEL=TYPICAL and
  CONTROL_MANAGEMENT_PACK_ACCESS-DIAGOSTIC*TUNING
• C. STATISTICS_LEVEL=BASIC and CONTROL_MANAGEMENT_PACK ACCESS=DIAGOSTIC
• D. STATISTICS_LEVEL=TYPICAL and CONTROL_MANAGEMENT_PACK_ACCESS=DIAGOSTIC

正解：A

解説：
Monitoring of Database Operations requires that theSTATISTICS_LEVELparameter be set toALLand CONTROL_MANAGEMENT_PACK_ACCESSbe set toDIAGNOSTIC+TUNING. These settings enable all the advisory features and automatic tuning features within the Oracle Database, including the Automatic Workload Repository (AWR), Automatic Database Diagnostic Monitor (ADDM), and the full functionality of the SQL Tuning Advisor and SQL Access Advisor, which are components of the Diagnostic and Tuning packs.
* STATISTICS_LEVEL=ALL:This setting enables the collection of all system statistics for problem detection and self-tuning purposes.
* CONTROL_MANAGEMENT_PACK_ACCESS=DIAGNOSTIC+TUNING:This grants access to both the Diagnostic Pack and the Tuning Pack, which are essential for detailed performance monitoring and tuning capabilities.
References:
* Oracle Database Reference:STATISTICS_LEVEL
* Oracle Database Licensing Information User Manual:Oracle Database Management Packs

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

• A. DB Time accounts for all time used by background processes and user sessions.
• B. The proportion of WAIT TIME to CPU TIME always increases with increased system load.
• C. 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.
• D. When WAIT TIME is high, instance tuning may improve performance.
• E. Systems in which CPU time is dominant need more tuning that those in which WAIT TIME is dominant.
• F. Parse Time Elapsed accounts for successful soft and hard parse operations only.

正解：A、C、D

解説：
A: Statspack is a performance diagnostic tool that can help identify high CPU usage issues. High CPU time may indicate that SQL statements need to be tuned for better performance.
D: High wait times can often be reduced by instance tuning, such as adjusting database parameters or improving I/O performance.
F: DB Time is a cumulative time metric that includes the time spent by both user sessions and background processes executing database calls.References:
* Oracle Database Performance Tuning Guide, 19c
* Oracle Database Concepts, 19c

質問 # 33
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. A critical alert will be issued when CPU time exceeds 2 minutes for each user call.
• B. A warning alert will be issued only when CPU time exceeds 10000 microseconds for each user call.
• C. A warning alert will be issued when CPU time exceeds 1 minute for each user call.
• D. A critical alert will be issued when CPU time exceeds 10000 microseconds for each user call.

正解：D

解説：
In the provided code block, theDBMS_SERVER_ALERT.SET_THRESHOLDprocedure is used to set alert thresholds for the CPU time per call in Oracle Database. This procedure is a part of Oracle's Database Server Alert system, which monitors various metrics and generates alerts when certain thresholds are exceeded.
The parameters passed to theSET_THRESHOLDprocedure are as follows:
* The first parameterDBMS_SERVER_ALERT.CPU_TIME_PER_CALLspecifies the metric for which the threshold is being set, in this case, the CPU time consumed per database call.
* The second and third parametersDBMS_SERVER_ALERT.OPERATOR_GEand'8000'specify the warning threshold level and its value, respectively. However, these are not relevant to the answer as they are overridden by the critical threshold settings.
* The fourth and fifth parametersDBMS_SERVER_ALERT.OPERATOR_GEand'10000'set the critical threshold level and its value. This means that a critical alert will be generated when the CPU time per call exceeds 10000 microseconds.
* The remaining parameters specify the warning and critical alert intervals, the instance name, the object type, and the service name. These are not directly relevant to the behavior described in the options.
Thus, the correct answer is B, as the critical threshold for CPU time per call is set to 10000 microseconds, and the system is configured to issue a critical alert when this threshold is exceeded.
References:
* Oracle Database 19c documentation on theDBMS_SERVER_ALERT.SET_THRESHOLDprocedure, which details the parameters and usage of this procedure for setting alert thresholds within Oracle Database monitoring system.
* Oracle Database Performance Tuning Guide, which provides best practices and methodologies for monitoring and tuning Oracle Database performance, including the use of server alerts and thresholds.

質問 # 34
What is the right time to stop tuning an Oracle database?

• A. When the allocated budget for performance tuning has been exhausted
• B. When the I/O is less than 10% of the DB time
• C. When all the concurrency waits are eliminated from the Top 10
• D. When the buffer cache and library cache hit ratio is above 95%

正解：A

解説：
The right time to stop tuning an Oracle database is often determined by the point of diminishing returns - when the cost of further tuning (in terms of time, resources, or money) exceeds the performance benefits gained.
This is often related to the budget allocated for performance tuning.
* A (Correct):When the allocated budget for performance tuning has been exhausted, it may be time to stop tuning unless the benefits of further tuning justify requesting additional budget.
* B (Incorrect):Eliminating all concurrency waits from the Top 10 is an unrealistic goal since some waits are inevitable and can occur due to application design, which might not be possible to eliminate completely.
* C (Incorrect):The buffer cache and library cache hit ratio being above 95% does not necessarily indicate that the database is fully optimized. Hit ratios are not reliable indicators of database performance and should not be used as sole criteria to end tuning efforts.
* D (Incorrect):Having I/O less than 10% of DB time is not a definitive indicator to stop tuning. It is essential to consider the overall performance goals and whether they have been met rather than focusing solely on I/O metrics.
References:
* Oracle Database Performance Tuning Guide:Introduction to Performance Tuning
* Oracle Database 2 Day + Performance Tuning Guide:Understanding the Tuning Process

質問 # 35
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=:Bl
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:

There is no index on the CITY_ID column.
Which two options improve the performance?

• A. Activate the adaptive plans.
• B. Create an index on the CITY IP column.
• C. Force the subquery to use dynamic sampling.
• D. Use a SQL Profile to enforce the appropriate plan.
• E. Generate frequency histograms on the CITY__ID column.

正解：B、E

解説：
In this scenario, creating an index and generating frequency histograms are two methods that can potentially improve performance:
* A (Correct):Generating frequency histograms on theCITY_IDcolumn can help the optimizer make better decisions regarding the execution plan, especially if the data distribution is skewed. Histograms provide the optimizer with more detailed information about the data distribution in a column, which is particularly useful for columns with non-uniform distributions.
* B (Correct):Creating an index on theCITY_IDcolumn would speed up queries that filter on this column, especially if it's used frequently in the WHERE clause as a filter. An index would allow for an index range scan instead of a full table scan, reducing the I/O and time needed to execute such queries.
* C (Incorrect):While SQL profiles can be used to improve the performance of specific SQL statements, they are usually not the first choice for such a problem, and creating a profile does not replace the need for proper indexing or statistics.
* D (Incorrect):Forcing the subquery to use dynamic sampling might not provide a consistent performance benefit, especially if the table statistics are not representative or are outdated. However, dynamic sampling is not as effective as having accurate statistics and a well-chosen index.
* E (Incorrect):Adaptive plans can adjust the execution strategy based on the conditions at runtime.
While they can be useful in certain scenarios, in this case, creating an index and ensuring accurate statistics would likely provide a more significant performance improvement.
References:
* Oracle Database SQL Tuning Guide:Managing Optimizer Statistics
* Oracle Database SQL Tuning Guide:Using Indexes and Clusters

質問 # 36
Examine this statement and output:

Which three statements are true?

• A. Both 9822 and 8779 sessions are waiting for operating system resources.
• B. Session 8779 may be waiting due to a network problem.
• C. Session 9857 waited 1354 seconds for another process, which was also waiting for a transaction to end.
• D. Session 9857 is not waiting.
• E. 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.
• F. Session 8779 may be waiting for a user or application response.

正解：B、E、F

解説：
For this SQL statement and output, we can analyze theEVENTcolumn to understand the type of wait:
B: The event "SQL*Net message from client" typically indicates that the session is waiting for a response from the client. This can be due to a network issue, user response, or an application processing delay.
E: The event "SQL*Net message from client" also implies that the session is idle waiting for the client (a user or an application) to send a request to the server. This event usually indicates that the session is not actively working but is instead waiting for the next command.
F: The wait event "enq: TX - row lock contention" suggests that session 9822 is waiting for a row-level lock held by another session. If the holding session issues a COMMIT or ROLLBACK, the lock will be released, and session 9822 will stop waiting. Since this session is experiencing row lock contention, it implies it's waiting for a specific transaction to complete.
References:
* Oracle Database Reference, 19c
* Oracle Wait Events Documentation

質問 # 37
You must configure and enable Database Smart Flash Cache for a database.
You configure these flash devices:

Examine these parameter settings:

What must be configured so that the database uses these devices for the Database Smart Flash Cache?

• A. Disable Automatic Memory Management and set SGA_TARGET to 256G.
• B. Set DB_FLASH_CACHE_SIZE to 192G and MEMORY_TARGET to 256G.
• C. Set DB_FLASH_CACHE_SIZE parameter to 128G, 64G.
• D. Set DB_FLASH_CACHE_SIZE to 256G and change device /dev/sdk to 128G.
• E. Set DB_FLASH_CACHE_SIZE parameter to 192G.

正解：C

解説：
To configure and enable Database Smart Flash Cache, you must set the DB_FLASH_CACHE_SIZE parameter to reflect the combined size of the flash devices youintend to use for the cache. In this scenario, two flash devices are configured: /dev/sdj with 128G and /dev/sdk with 64G.
* Determine the combined size of the flash devices intended for the Database Smart Flash Cache. In this case, it's 128G + 64G = 192G.
* However, Oracle documentation suggests setting DB_FLASH_CACHE_SIZE to the exact sizes of the individual devices, separated by a comma when multiple devices are used.
* Modify the parameter in the database initialization file (init.ora or spfile.ora) or using an ALTER SYSTEM command. Here's the command for altering the system setting:
ALTER SYSTEM SET DB_FLASH_CACHE_SIZE='128G,64G' SCOPE=SPFILE;
* Since this is a static parameter, a database restart is required for the changes to take effect.
* Upon database startup, it will allocate the Database Smart Flash Cache using the provided sizes for the specified devices.
It is important to note that MEMORY_TARGET and MEMORY_MAX_TARGET parameters should be configured independently of DB_FLASH_CACHE_SIZE. They control the Oracle memory management for the SGA and PGA, and do not directly correlate with the flash cache configuration.
References
* Oracle Database 19c Documentation on Database Smart Flash Cache
* Oracle Support Articles and Community Discussions on DB_FLASH_CACHE_SIZE Configuration

質問 # 38
Which three types of statistics are captured by statspack with snap level 6?

• A. Segment-level statistics
• B. Enqueue statistics
• C. Optimizer execution plans
• D. Parent and child latches
• E. Plan usage data
• F. Parent and child latches

正解：A、B、D

解説：
Statspack is a performance diagnostic tool provided by Oracle prior to the introduction of the Automatic Workload Repository (AWR). At snap level 6, Statspack captures the following types of statistics:
* A (Correct):Parent and child latches are captured. Latch statistics provide information about contention for latches, which are low-level serialization mechanisms used by Oracle.
* E (Correct):Enqueue statistics, which provide information on the waits for locks that manage the concurrency between users.
* F (Correct):Segment-level statistics, which provide detailed information on database segments such as tables, indexes, etc., to identify I/O and contention issues.
* C (Incorrect):While optimizer execution plans are an essential aspect of performance tuning, detailed execution plan capture is not part of the Statspack report at level 6.
* D (Incorrect):Plan usage data refers to how frequently a plan is being used, which is more associated with AWR and not typically captured in Statspack reports.
References:
* Oracle Database Performance Tuning Guide:Using Statspack

質問 # 39
Examine this statement and output:

Which two situations can trigger this error?

• A. The user lacks the required privileges to execute the DBMS WORKLOAD CAPTURE package or the directory.
• B. The instance is unable to access the capture directory.
• C. There is a file in the capture directory.
• D. The syntax is incomplete.
• E. The capture directory is part of the root file system.

正解：A、B

解説：
The ORA-15505 error indicates that the instance encountered errors while trying to access the specified directory. This could be due to:
A: Insufficient privileges: The user attempting to start the workload capture might not have the required permissions to execute the DBMS_WORKLOAD_CAPTURE package or to read/write to the directory specified.
E: Accessibility: The database instance may not be able to access the directory due to issues such as incorrect directory path, directory does not exist, permission issues at the OS level, or the directory being on a file system that's not accessible to the database instance.
References:
* Oracle Database Error Messages, 19c
* Oracle Database Administrator's Guide, 19c

質問 # 40
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?

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

正解：B

解説：
The Oracle Optimizer statistics advisor, which is part of the automated tasks framework, can be disabled using the DBMS_AUTO_TASK_ADMIN package. This will prevent it fromrunning and thus reduce space usage in the SYSAUX tablespace.References:
* Oracle Database PL/SQL Packages and Types Reference, 19c

質問 # 41
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:

Now, examine these attributes displayed by querying DBA_INDEXES:

Which action will reduce the excessive buffer gets?

• A. Partition index IX_SALES_TIME_ID using hash partitioning.
• B. Re-create the SALES table using the columns in IX_SALES_TIME_ID as the hash partitioning key.
• C. Re-create index IX_SALES_TIME_ID using ADVANCED COMPRESSION.
• D. Re-create the SALES table sorted in order of index IX_SALES_TIME_ID.

正解：C

解説：
Given that index range scan operations onIX_SALES_TIME_IDare slower due to an increase in buffer gets, the aim is to improve the efficiency of the index access. In this scenario:
* B (Correct):Re-creating the index usingADVANCED COMPRESSIONcan reduce the size of the index, which can lead to fewer physical reads (reduced I/O) and buffer gets when the index is accessed, as more of the index can fit into memory.
The other options would not be appropriate because:
* A (Incorrect):Re-creating theSALEStable sorted in order of the index might not address the issue of excessive buffer gets. Sorting the table would not improve the efficiency of the index itself.
* C (Incorrect):Using the columns inIX_SALES_TIME_IDas a hash partitioning key for theSALES table is more relevant to data distribution and does not necessarily improve index scan performance.
* D (Incorrect):Hash partitioning the index is generally used to improve the scan performance in a parallel query environment, but it may not reduce the number of buffer gets in a single-threaded query environment.
References:
* Oracle Database SQL Tuning Guide:Managing Indexes
* Oracle Database SQL Tuning Guide:Index Compression

質問 # 42
Which two statements are true about the use and monitoring of Buffer Cache Hit ratios and their value in tuning Database I/O performance?

• A. The buffer cache advisory view v$db_cache_advice provides advice on cache hit ratios appropriate for the instance workload.
• B. Both the RECYCLE and KEEP buffer caches should always have a very high cache hit ratio.
• C. A 99% cache hit ratio can be observed for database instances which have very poor I/O performance.
• D. A 60% cache hit ratio can be observed for database instances which have very good I/O performance.
• E. The performance of workloads that primarily generate full table scans and fast full index scans are always affected by the cache hit ratio.

正解：A、C

解説：
A high buffer cache hit ratio typically indicates that the database is effectively using the buffer cache and does not often need to read data from disk. However, this metric alone is not a reliable indicator of the I/O performance of the database for several reasons:
* Full table scans and fast full index scans (A) can bypass the buffer cache by design if the blocks are not
* deemed reusable shortly, which can impact the cache hit ratio.
* A high cache hit ratio (B) can be misleading if the database performance is poor due to other factors, such as inefficient queries or contention issues.
* The buffer cache advisory (C) is a more valuable tool for understanding the potential impact of different cache sizes on the database's I/O performance. It simulates scenarios with different cache sizes and provides a more targeted recommendation.
* The RECYCLE and KEEP buffer caches (D) are specialized caches designed for certain scenarios.
While high hit ratios can be beneficial, they are not universally required; some workloads might not be significantly impacted by lower hit ratios in these caches.
* A lower cache hit ratio (E) does not necessarily mean poor I/O performance. In some cases, a system with a well-designed storage subsystem and efficient queries might perform well even with a lower cache hit ratio.
References
* Oracle Database 19c Performance Tuning Guide - Buffer Cache Hit Ratio
* Oracle Database 19c Performance Tuning Guide - v$db_cache_advice

質問 # 43
You need to collect and aggregate statistics for the ACCTG service and PAYROLL module, and execute:

Where do you find the output of this command?

• A. By viewing V$SERV_MOD_ACT_STATS
• B. By viewing V$SERVICE_STATS
• C. In $ORACLE_BASE/diag/rdbms/<db unique name>/<instance name>/trace
• D. In the current working directory

正解：A

解説：
When you enable statistics gathering for a specific service and module using DBMS_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

質問 # 44
Which two actions can cause invalidation or loss of one or more results in the SQL Query Result Cache?

• A. Decreasing the value set for RESULT_CACHE_REMOTE_EXPIRATION.
• B. 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.
• C. One or more results were aged out of the result cache.
• D. A request was made by the RCBG background of a non-RAC database.
• E. result_cache_max_size is set dynamically to 0.

正解：C、E

解説：
The SQL Query Result Cache stores the results of queries and PL/SQL function calls for reuse. However, entries in the result cache can be invalidated or lost under certain conditions:
A: Results can be aged out of the cache when the cache becomes full and new results need to be stored. This process ensures that the cache does not exceed its allocated memory and that it contains the most recently used entries.
B: Setting theRESULT_CACHE_MAX_SIZEparameter to 0 will effectively disable the result cache and all cached results will be lost, as Oracle will no longer allocate any memory to the result cache.
References:
* Oracle Database Performance Tuning Guide, 19c

質問 # 45
Which three statements are true about using the in Memory (IM) column store?

• A. It does not improve performance for queries that use join groups on columns from different tables.
• B. It does not require all database data to fit in memory to improve query performance.
• C. It improves performance for queries joining several tables using bloom filter joins.
• D. It can improve OLTP workload performance by avoiding the use of indexes.
• E. It does not improve performance for queries using user-defined virtual column results.
• F. It does not improve performance for queries using cached results of function evaluations on columns from the same table.

正解：B、C、D

解説：
The Oracle In-Memory (IM) column store feature enhances the performance of databases by providing a fast columnar storage format for analytical workloads while also potentially benefiting OLTP workloads.
* C (True):It can improve OLTP workload performance by providing a faster access path for full table scans and reducing the need for indexes in certain scenarios, as the In-Memory store allows for efficient in-memory scans.
* E (True):The In-Memory column store does not require all database data to fit in memory. It can be used selectively for performance-critical tables or partitions, and Oracle Database will manage the population and eviction of data as needed.
* F (True):In-Memory column store can significantly improve performance for queries joining several tables, especially when bloom filters are used, as they are highly efficient with the columnar format for large scans and join processing.
The other options provided are not correct in the context of the In-Memory column store:
* A (False):While In-Memory column store is designed for analytical queries rather than caching results of function evaluations, it does not specifically avoid improving performance for queries using cached results of function evaluations.
* B (False):In-Memory column store can improve the performance of queries that use join groups, which can be used to optimize joins on columns from different tables.
* D (False):In-Memory column store can improve the performance of queries using expressions, including user-defined virtual columns, because it supports expression statistics which help in
* optimizing such queries.
References:
* Oracle Database In-Memory Guide:In-Memory Column Store in Oracle Database
* Oracle Database In-Memory Guide:In-Memory Joins
* Oracle Database In-Memory Guide:In-Memory Aggregation

質問 # 46
Which two statements are true about cursor sharing?

• A. Setting cursor_sharing to EXACT prevents Adaptive Cursor Sharing from being used.
• B. 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.
• C. Adaptive Cursor Sharing guarantees that a suboptimal plan will never be used on any execution of a SQL statement.
• D. 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.
• E. Setting optimizer_capture_sql_plan_baselines to TRUE loads all adaptive plans for the same statement into the cursor cache.

正解：A、D

解説：
A: WhenCursor_sharingis set toFORCE, Oracle tries to avoid hard parses by replacing literals in SQL statements with bind variables, even if the original statement didn't include bind variables. This can lead to the use of a single execution plan for multiple executions of a statement with different literal values, which might not be optimal for all executions.
D: Settingcursor_sharingtoEXACTensures that SQL statements must match exactly for them to share a cursor. This setting prevents the use of Adaptive Cursor Sharing (ACS) since ACS relies on the ability to share cursors among similar statements that differ only in their literal values. WithEXACT, there's no cursor sharing for statements with different literals, hence no opportunity for ACS to operate.
References:
* Oracle Database SQL Tuning Guide, 19c
* Oracle Database Reference, 19c

質問 # 47
Which two options are part of a Soft Parse operation?

• A. SQL Row Source Generation
• B. SQL Optimization
• C. Shared Pool Memory Allocation
• D. Syntax Check
• E. Semantic Check

正解：E

質問 # 48
Examine these statements and output:

What parameter change activates the generation and use of SQL Plan Directives7

• A. optimizer_adaptive_statistics = TRUE
• B. optimizer_features_enable=12.2.0.1
• C. optimizer_dynamic_sampling=11
• D. optimizer_adaptive_plans=TRUE
• E. optimizer_capture_sql_plan_baselines_TRUE

正解：A

解説：
The optimizer_adaptive_statistics parameter, when set to TRUE, enables the optimizer to use adaptive statistics, such as SQL Plan Directives, to help improve plans by automatically adjusting them based on the actual execution statistics.
References:
* Oracle Database SQL Tuning Guide, 19c

質問 # 49
Examine this command:

What is the maximum number of baselines generated by this command that you can have at any given time?

• A. 0
• B. 1
• C. 2
• D. 3

正解：B

解説：
TheDBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATEprocedure is used to create a repeating baseline template in the Automatic Workload Repository (AWR). This template will generate baselines for a specified duration of time on a repeating schedule. Theparameters of the CREATE_BASELINE_TEMPLATEprocedure include the start and end times, as well as the day of the week and hour in the day when the baseline should be captured.
Given that the command specifies a repeating baseline every Monday at 5 PM with a duration of 3 hours and it expires after 30 days, the number of baselines generated by this command that you can have at any given time depends on how many Mondays fall within the most recent 30-day period.
Since the maximum number of Mondays that can occur within any 30-day period is 5 (four to five weeks), but considering the baseline has a duration of 3 hours and starts every Monday at 5 PM, only one baseline for each Monday can exist at a time. However, since baselines are preserved for 30 days, you could have multiple instances of Monday baselines preserved at a time.
* A (Incorrect):There can be more than one baseline at a time because the template will generate a baseline for every Monday during the 30-day expiration period.
* B (Incorrect):There will be more than three baselines because the template creates a baseline for every Monday within the 30-day expiration period.
* C (Correct):Over a 30-day period, considering the duration of the baselines and their frequency, you could have up to a maximum of 52 baselines if you consider the entire year.
* D (Incorrect):There is no option that restricts the number of baselines to 5 specifically, the answer relies on the calculation of how many baselines can exist over a period of time considering their expiration.
References:
* Oracle Database PL/SQL Packages and Types Reference:DBMS_WORKLOAD_REPOSITORY

質問 # 50
For which two actions can SQL Performance Analyzer be used to assess the impact of changes to SQL performance?

• A. changes to database initialization parameters
• B. storage, network, and interconnect changes
• C. operating system and hardware migrations
• D. operating system upgrades
• E. database consolidation for pluggable databases (PDBs)

正解：A、E

解説：
SQL Performance Analyzer (SPA) can be used to assess the impact of different types of changes on SQL performance. These changes can include database initialization parameters, which can significantly affect how SQL statements are executed and therefore their performance. SPA allows you to capture a workload before and after the change and compare the performance of each SQL statement.
Database consolidation, including moving to pluggable databases (PDBs), can also affect SQL performance.
SPA can analyze the SQL workload to see how consolidation impacts performance, by comparing metrics such as elapsed time and CPU time before and after the consolidation.
References:
* Oracle Database SQL Tuning Guide, 19c
* Oracle Database Performance Tuning Guide, 19c

質問 # 51
......

1Z0-084別格な問題集で最上級の成績にさせる1Z0-084問題：https://jp.fast2test.com/1Z0-084-premium-file.html