Fast2test 1z1-084問題集PDFで100%合格保証付き [Q32-Q56]

Fast2test 1z1-084問題集PDFで100%合格保証付き

1z1-084ブレーン問題集でリアル試験最新問題2025年07月25日には57問題

Oracle 1Z0-084試験は、Oracle Database 19cの経験があり、データベース管理の基本的な概念に精通している個人を対象としています。候補者は、SQL、データベースアーキテクチャ、パフォーマンスチューニング技術に強い理解を持っている必要があります。試験を受ける前に、候補者は少なくとも2年間Oracle Database 19cでの作業経験を持っていることが推奨されます。

 

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

• A. ROW STORE COMPRESS ADVANCED
• B. COLUMN STORE COMPRESS FOR QUERY HIGH
• C. MN STORE COMPRESS FOR QUERY LOW
• D. STORE COMPRESS

正解：B

解説：
The question asks to reduce database I/O impact without increasing the buffer cache size or modifying SQL statements. This indicates a need to reduce the physical I/O required to access the data. Let's analyze the scenario and the options.
Analysis of the AWR Report:
* Top Wait Events:
* The top foreground wait event is db file sequential read, which accounts for 40.4% of DB time.
This indicates significant physical I/O operations, primarily single-block reads, which are typically associated with index access.
* Reducing the physical I/O associated with db file sequential read can significantly improve performance.
* SQL Ordered by Reads:
* The SQL consuming the most reads involves high physical I/O. This confirms the need to reduce I
/O overhead by compressing data efficiently to minimize physical reads.
Compression Techniques and Their Suitability:
* A. COLUMN STORE COMPRESS FOR QUERY LOW:
* This option is a columnar compression method that optimizes for query performance but provides less compression compared to the HIGH option. While effective, it is not as suitable as FOR QUERY HIGH for reducing I/O.
* B. STORE COMPRESS:
* This is the basic compression option for tables and does not offer the advanced capabilities required for reducing significant physical I/O for queries.
* C. ROW STORE COMPRESS ADVANCED:
* This is a row-level compression that is suitable for OLTP workloads. While it reduces storage, it does not reduce query-related I/O as effectively as columnar compression.
* D. COLUMN STORE COMPRESS FOR QUERY HIGH (Correct Option):
* This is the most effective option for reducing query-related I/O. It:
* Uses columnar compression to reduce the size of data stored on disk.
* Reduces the number of physical reads by compressing data highly, meaning fewer blocks need to be read.
* Optimizes query performance for analytical workloads, which aligns with the scenario described in the AWR report.
Why COLUMN STORE COMPRESS FOR QUERY HIGH Is the Best Fit:
* It is designed to improve query performance by minimizing the amount of I/O required.
* Suitable for environments with heavy read operations (as indicated by the db file sequential read waits).
* Does not require changes to SQL or buffer cache size, adhering to the constraints in the question.
Reference to Oracle Documentation:
* Oracle Database 19c Performance Tuning Guide:
* Section: Using Compression to Reduce Storage and I/O Requirements.
* Discussion of columnar compression techniques for reducing I/O in query-intensive environments.
* Oracle Advanced Compression Documentation:
* Details on COLUMN STORE COMPRESS FOR QUERY HIGH and its benefits for analytical workloads.

質問 # 33
You need to transport performance data from a Standard Edition to an Enterprise Edition database. What is the recommended method to do this?

• A. 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.
• B. Export the data by using expdp from Statspack and import it by using $ORACLE_HOME/rdbms/admin
  /awrload into the AWR repository.
• C. 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.
• D. Export the data by using expdp from the ftatspack repository and import it by using impdp into the AWR repository.

正解：A

解説：
To transport performance data from an Oracle Database Standard Edition, which uses Statspack, to an Enterprise Edition database, which uses AWR, you must consider the compatibility of data structures and repository schemas between these tools. The recommended method is:
* D (Correct): Export the data using the exp utility with a parameter file appropriate for Statspack (like spuexp.par) from the Statspack repository and import it into a dedicated Statspack schema on the destination. Since Statspack and AWR use different schemas, it's not recommended to import Statspack data directly into the AWR repository.
The other options are incorrect because:
* A (Incorrect): expdp is not designed to export from Statspack, and awrload is intended for loading from an AWR export file, not a Statspack export.
* B (Incorrect): Although expdp and impdp are used for exporting and importing data, the AWR repository schema is different from the Statspack schema, so importing Statspack data directly into the AWR repository is not recommended.
* C (Incorrect): Using expdp to export from Statspack and then importing directly into the AWR repository is not the correct approach due to the schema differences between Statspack and AWR.
References:
* Oracle Database Performance Tuning Guide: Migrating from Statspack to AWR

質問 # 34
Which two statements are true about session wait information contained in v$session or v$session_wait?

• A. Rows for sessions displaying WAITED UNKNOWN TIME in the STATE column indicate that the session is still waiting.
• B. Rows for sessions that are currently waiting have their wait time incremented every microsecond.
• C. Rows for sessions that are not waiting might contain the actual wait time for the last event for which they waited.
• D. Rows for sessions that are currently waiting have a wait time of 0.
• E. Rows for sessions that are not waiting always contain the total wait time since the session started.

正解：C、D

解説：
In theV$SESSIONview, Oracle provides information about the session waits:
B: When theWAIT_TIMEcolumn has a value of 0, it signifies that the session is currently waiting for a resource. This column represents the duration of the current or last wait.
C: If the session is not actively waiting, theWAIT_TIMEcolumn shows the time the session spent waiting for the last wait event. If theSTATEcolumn is showing "WAITED KNOWN TIME", it means the session is not currently waiting, but it indicates the time for which it had waited.
References:
* Oracle Database Reference, 19c
* Oracle Database Performance Tuning Guide, 19c

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

Which are two actions either one of which will allow Big Table caching to occur?

• A. Increasing DB_BIG_TABLE_CACHE_PERCENT_TARGET to at least 25
• B. Increasing DB_BIG_TABLE_CACHE_PERCENT_TARGET to at least 50
• C. Setting PARALLEL_DEGREE_POLICYAUTO
• D. Setting PARALLEL_DEGREE_POLICYADAPTIVE
• E. Setting DB_KEEP_CACHE_SIZE to at least 50M
• F. Increasing DB_CACHESIZE to 1 G

正解：A、F

解説：
Big Table caching is a feature that allows frequently accessed large tables to be cached in memory to improve I/O performance. From the parameter settings provided, Big Table caching is not occurring because DB_BIG_TABLE_CACHE_PERCENT_TARGET is set to 10, which is the minimum threshold for enabling the feature, but the size of the cache is too small for the big tables to be effectively cached.
To enable Big Table caching, one of the following actions could be taken:
* C (Correct): Increasing DB_BIG_TABLE_CACHE_PERCENT_TARGET to at least 25. This action would allocate a larger percentage of the buffer cache for storing big tables, which could allow for caching large tables and thus improve I/O performance.
* D (Correct): Increasing DB_CACHE_SIZE to 1G. Since the size of the buffer cache is a determining factor for how much data can be cached, increasing this parameter would provide more memory space for big tables to be cached.
Options A, B, E, and F will not enable Big Table caching because:
* A: Increasing DB_BIG_TABLE_CACHE_PERCENT_TARGET to 50 without adjusting the overall size of the cache might still not be sufficient if the DB_CACHE_SIZE is not large enough to hold the big tables.
* B: Setting DB_KEEP_CACHE_SIZE to at least 50M only specifies a separate buffer pool for objects with the KEEP cache attribute and does not affect Big Table caching.
* E: and F: Changing the PARALLEL_DEGREE_POLICY to ADAPTIVE or AUTO influences the behavior of parallel execution but does not directly enable or influence Big Table caching.
References:
* Oracle Database Performance Tuning Guide: Big Table Caching
* Oracle Database Reference: DB_BIG_TABLE_CACHE_PERCENT_TARGET
* Oracle Database Reference: DB_CACHE_SIZE

質問 # 36
Examine this output of a query of VSPGA_TAPGET_ADVICE:

Which statements is true'

• A. With a target of 800 MB or more, all one-pass execution work areas would be eliminated.
• B. With a target of 700 MB or more, all multipass executions work areas would be eliminated.
• C. GGREGATE_TARGET should be set to at least 700 MB.
• D. PGAA_AGGREGATE should be set to at least 800 MB.

正解：B

解説：
The V$PGA_TARGET_ADVICE view provides advice on potential performance improvements by adjusting the PGA_AGGREGATE_TARGET parameter. The column ESTD_OVERALLOC_COUNT indicates the estimated number of work areas that would perform multiple passes if the PGA_AGGREGATE_TARGET were set to the size in the TARGET_MB column.
A: According to the output, at the target of 700 MB, the ESTD_OVERALLOC_COUNT is 30. This suggests that if PGA_AGGREGATE_TARGET is set to 700 MB, 30 multipass execution work areas would be required. If we look further down, at the target of 800 MB, the ESTD_OVERALLOC_COUNT is 0, indicating that increasing PGA_AGGREGATE_TARGET to 800 MB or more would eliminate the need for multipass executions, not at 700 MB as initially suggested by the option. Hence, the verified answer derived from the data is slightly nuanced; it should be 800 MB to eliminate all multipass executions.
References:
* Oracle Database Performance Tuning Guide, 19c
* Oracle Database Reference, 19c

質問 # 37
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, the DBMS_SERVER_ALERT.SET_THRESHOLD procedure 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 the SET_THRESHOLD procedure are as follows:
* The first parameter DBMS_SERVER_ALERT.CPU_TIME_PER_CALL specifies the metric for which the threshold is being set, in this case, the CPU time consumed per database call.
* The second and third parameters DBMS_SERVER_ALERT.OPERATOR_GE and '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 parameters DBMS_SERVER_ALERT.OPERATOR_GE and '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 the DBMS_SERVER_ALERT.SET_THRESHOLD procedure, 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.

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

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

正解：C、E、F

解説：
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

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

• A. DBA_HIST_SQLTEXT
• B. DBA_HIST_PGASTAT
• C. DBA_HIST_SQLSTAT
• D. DBA_HIST_ACTIVE_SESS_HISTORY
• E. DBA_HIST_PROCESS_MEM_SUMMARY

正解：D、E

解説：
To investigate the cause of the ORA-04036 error, which indicates that PGA memory usage exceeds the PGA_AGGREGATE_LIMIT, the appropriate views to consult are DBA_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

質問 # 40
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. Re-create index IX_SALES_TIME_ID using ADVANCED COMPRESSION.
• B. Partition index IX_SALES_TIME_ID using hash partitioning.
• C. Re-create the SALES table using the columns in IX_SALES_TIME_ID as the hash partitioning key.
• D. Re-create the SALES table sorted in order of index IX_SALES_TIME_ID.

正解：A

解説：
Given that index range scan operations on IX_SALES_TIME_ID are 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 using ADVANCED COMPRESSION can 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 the SALES table 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 in IX_SALES_TIME_ID as a hash partitioning key for the SALES 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

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

正解：A、E

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

• A. Database Replay
• B. SQL Performance Analyzer
• C. AWR Compare Periods report
• D. Statspack report

正解：D

解説：
For a Standard Edition production database, the Statspack tool is available to diagnose performance problems.
The Automatic Workload Repository (AWR) and its related tools like AWR Compare Periods report and SQL Performance Analyzer are features of the Oracle Database Enterprise Edition and are not available in Standard Edition. Database Replay is also a feature of the Enterprise Edition. Statspack is a performance diagnostic tool provided for earlier versions and Standard Editions of the Oracle Database to collect, store, and analyze performance data.
References
* Oracle Database 19c Administrator's Guide - Using Statspack to Diagnose Database Performance Issues

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

• A. DBMS_STATS.GATHER_FIXED_ OBJECTS_STATS
• B. DBMS_STATS.GATHER_DATABASE_STATS
• C. DBMS_STATS.GATHER_DICTIONARY_STATS
• D. DBMS_STATS.GATHER_SYSTEM_STATS

正解：B

解説：
TheDBMS_STATS.GATHER_DATABASE_STATSprocedure is used to gather statistics for all schema objects in the database that do not have up-to-date statistics. These statistics are essential for the optimizer to make informed decisions about the most efficient way to execute a query. The procedure collects statistics such as table and column statistics, index statistics, and system statistics, which are all used in the execution plan generation.
References:
* Oracle Database PL/SQL Packages and Types Reference, 19c
* Oracle Database Performance Tuning Guide, 19c

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

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

正解：D

解説：
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

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

正解：B、C

解説：
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

質問 # 46
Accessing the SALES tables causes excessive db file sequential read wait events.
Examine this AWR except:

Now, examine these attributes displayed by querying dba_tables:

Finally, examine these parameter settings:

Which two must both be used to reduce these excessive waits?

• A. Compress the SALES table.
• B. Re-create the SALES table.
• C. Partition the SALES table.
• D. Coalesce all sales table indexes.
• E. Increase PCTFREE for the SALES table.

正解：A、C

解説：
The AWR excerpt points to excessive physical reads on the SALES table and index, suggesting the need for optimizing table storage and access.
Partitioning the SALES table (A) can reduce 'db file sequential read' waits by breaking down the large SALES table into smaller, more manageable pieces. This can localize the data and reduce the I/O necessary for query operations.
Compressing the SALES table (D) can also help reduce I/O by minimizing the amount of data that needs to be read from disk. This can also improve cache utilization and reduce the 'db file sequential read' waits.
References:
* Oracle Database VLDB and Partitioning Guide, 19c
* Oracle Database Administrator's Guide, 19c
These changes are recommended based on Oracle's best practices for managing large tables and reducing I/O waits, ensuring better performance and efficiency.

質問 # 47
You must write a statement that returns the ten most recent sales. Examine this statement:

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?

• A. Enable Adaptive Plans so that Oracle can change the Join method as well as the Join order for this query.
• B. Replace the FETCH FIRST clause with ROWNUM to enable the use of an index on SALES.
• C. Create an index on SALES.TIME_ID to force the return of rows in the order specified by the ORDER BY clause.
• D. Collect a new set of statistics on PRODUCT, CUSTOMERS, and SALES because the current stats are inaccurate.
• E. 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.

正解：C

解説：
The execution plan shows a full table access for the SALES table. To reduce the execution time, creating an index on SALES.TIME_ID would be beneficial as it would allow the database to quickly sort and retrieve the most recent sales without the need to perform a full table scan, which is I/O intensive and slower. By indexing TIME_ID, which is used in the ORDER BY clause, the optimizer can take advantage of the index to efficiently sort and limit the result set to the ten most recent sales.
* B (Incorrect): Replacing FETCH FIRST with ROWNUM would not necessarily improve the performance unless there is an appropriate index that the optimizer can use to avoid sorting the entire result set.
* C (Incorrect): There is no indication that the current statistics are inaccurate; hence, collecting new statistics may not lead to performance improvement.
* D (Incorrect): While adaptive plans can provide performance benefits by allowing the optimizer to adapt the execution strategy, the main issue here is the lack of an index on the ORDER BY column.
* E (Incorrect): Creating an index on SALES.CUST_ID could improve join performance but would not address the performance issue caused by the lack of an index on the ORDER BY column.
References:
* Oracle Database SQL Tuning Guide: Managing Indexes
* Oracle Database SQL Tuning Guide: Using Indexes and Clusters

質問 # 48
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 the V$SERV_MOD_ACT_STATS dynamic 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): The V$SERVICE_STATS view 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

質問 # 49
You need to transport performance data from a Standard Edition to an Enterprise Edition database. What is the recommended method to do this?

• A. 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.
• B. Export the data by using expdp from Statspack and import it by using
  $ORACLE_HOME/rdbms/admin/awrload into the AWRrepository.
• C. 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.
• D. Export the data by using expdp from the ftatspack repository and import it by using impdp into the AWR repository.

正解：A

解説：
To transport performance data from an Oracle Database Standard Edition, which uses Statspack, to an Enterprise Edition database, which uses AWR, you must consider the compatibility of data structures and repository schemas between these tools. The recommended method is:
* D (Correct):Export the data using theexputility with a parameter file appropriate for Statspack (like spuexp.par) from the Statspack repository and import it into a dedicated Statspack schema on the destination. Since Statspack and AWR use different schemas, it's not recommended to import Statspack data directly into the AWR repository.
The other options are incorrect because:
* A (Incorrect):expdpis not designed to export from Statspack, andawrloadis intended for loading from an AWR export file, not a Statspack export.
* B (Incorrect):Althoughexpdpandimpdpare used for exporting and importing data, the AWR repository schema is different from the Statspack schema, so importing Statspack data directly into the AWR repository is not recommended.
* C (Incorrect):Usingexpdpto export from Statspack and then importing directly into the AWR repository is not the correct approach due to the schema differences between Statspack and AWR.
References:
* Oracle Database Performance Tuning Guide:Migrating from Statspack to AWR

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

• A. ALTER SYSTEM SET CURSOR_INVALIDATION = DEFERRED;
• B. ALTER SYSTEM SET CURSOR_SPACE_FOR_TIME - TRUE;
• C. ALTER SYSTEM SET SESSION CACHED CURSORS = 500;
• D. ALTER SYSTEM SET CURSOR_SHARING = FORCE;

正解：A

解説：
The cursor: pin S wait on X wait event suggests contention for a cursor pin, which is associated with mutexes (a type of locking mechanism) that protect the library cache to prevent concurrent modifications.
This issue can often be alleviated by deferring the invalidation of cursors until the end of the call to reduce contention. The correct command to use would be:
* C (Correct): ALTER SYSTEM SET CURSOR_INVALIDATION=DEFERRED; This setting defers the invalidation of dependent cursors until the end of the PL/SQL call, which can reduce the cursor: pin S wait on X wait events.
The other options are incorrect in addressing this issue:
* A (Incorrect): Setting CURSOR_SHARING to FORCE makes the optimizer replace literal values with bind variables. It doesn't address the contention for cursor pins directly.
* B (Incorrect): CURSOR_SPACE_FOR_TIME=TRUE aims to reduce the parsing effort by keeping cursors for prepared statements open. It may increase memory usage but does not directly resolve cursor: pin S wait on X waits.
* D (Incorrect): Increasing SESSION_CACHED_CURSORS caches more session cursors but doesn't necessarily prevent the contention indicated by the cursor: pin S wait on X wait events.
References:
* Oracle Database Reference: CURSOR_INVALIDATION
* Oracle Database Performance Tuning Guide: Reducing Cursor Invalidation

質問 # 51
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. Create an index on the CITY IP column.
• B. Use a SQL Profile to enforce the appropriate plan.
• C. Activate the adaptive plans.
• D. Generate frequency histograms on the CITY__ID column.
• E. Force the subquery to use dynamic sampling.

正解：A、D

解説：
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

質問 # 52
Examine this statement and its corresponding execution plan:

Which phase introduces the CONCATENATION step?

• A. SQL Transformation
• B. SQL Execution
• C. SQL Adaptive Execution
• D. SQL Row Source Generation
• E. SQL Semantic Check

正解：A

解説：
The CONCATENATION step in an execution plan is introduced during the SQL Transformation phase. This phase is part of the optimizer's query transformations which can include various techniques to rewrite the query for more efficient execution. The CONCATENATION operation is used to combine the results of two separate SQL operations, typically when there is an OR condition in the WHERE clause, as seen in the provided query.
References:
* Oracle Database SQL Tuning Guide, 19c
* Oracle Database Concepts, 19c

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

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

正解：D

解説：
The objective of performance tuning in Oracle Database is to meet specific performance goals. These goals may vary based on the requirements of the system and business objectives. Let's evaluate each option in detail to understand why E is correct and others are not.
Option Analysis:
* A. When the allocated budget for performance tuning has been exhausted
* Why it's incorrect:
* Tuning should not stop simply because the budget is exhausted. If performance goals are not met, the database might still experience performance issues, impacting the end-users or business-critical processes. Budget is a constraint, but it shouldn't define when tuning stops.
* B. When all the concurrency waits are eliminated from the Top 10
* Why it's incorrect:
* Concurrency waits (such as locks or latches) are just one aspect of database performance tuning. Eliminating these waits does not necessarily mean the system meets its performance goals. Other factors like query optimization, I/O performance, and CPU usage might still need attention.
* C. When the buffer cache and library cache hit ratio is above 95%
* Why it's incorrect:
* Cache hit ratios are often overemphasized as a performance metric. While a high hit ratio indicates efficient memory usage, it doesn't guarantee optimal performance. A high ratio could still mask inefficient SQL queries, suboptimal execution plans, or other bottlenecks.
* D. When the I/O is less than 10% of the DB time
* Why it's incorrect:
* While reducing I/O is beneficial, it is not always a sufficient indicator that tuning can stop.
Certain workloads may inherently have high or low I/O percentages. The real question is whether the database is meeting its required service levels, not just reducing I/O.
* E. When the tuning goal has been met
* Why it's correct:
* The purpose of performance tuning is to meet the specific performance goals set by the business or the database administrators. Once the database meets these goals (e.g., query response times, throughput requirements, or SLA commitments), tuning can stop. This ensures effort is focused on achieving measurable outcomes, rather than chasing arbitrary metrics.
The Importance of Defining a Tuning Goal
Performance tuning should be driven by clear goals such as:
* Reducing response time for specific critical queries.
* Meeting SLAs for application performance.
* Supporting a target number of concurrent users.
* Reducing resource contention for improved scalability.
Tuning should stop once these goals are achieved because continuous tuning without purpose can lead to unnecessary complexity and resource usage.
Reference to Oracle Documentation:
* Oracle Database 19c Performance Tuning Guide:
* Section: Establishing Performance Goals and Metrics.
* Discussion on focusing tuning efforts on business requirements and goals.
* Oracle Database Concepts Guide:
* Best practices for balancing performance improvements with system complexity.

質問 # 54
Which statement is true about DB time in V$$YS_TIME_MODEL?

• A. DB tine excludes the time spent waiting for a CPU in the operating system run queue.
• B. DB time can be many times greater than the elapsed time since the database instance started.
• C. DB time includes the time spent executing the RMAN backup and restore command.
• D. DB time is organized as a simple list of statistics and any time period is attributable to only one statistic.

正解：B

解説：
DB time includes the time spent on user and background processes. It can be greater than the elapsed time because it accumulates the active time of all the processes. For example, if two sessions are each active for 2 seconds at the same time, DB time would accumulate 4 seconds, while the elapsed time would be only 2 seconds.References:
* Oracle Database Performance Tuning Guide, 19c
* Oracle Database Reference, 19c

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

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

正解：A、B

解説：
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

質問 # 56
......

1z1-084問題集には100%厳密検証された問題と解答で合格保証付きもしくは全額返金：https://jp.fast2test.com/1z1-084-premium-file.html