[2025年03月23日] トップクラスの1z1-084練習試験問題 [Q23-Q40]

[2025年03月23日] トップクラスの1z1-084練習試験問題

実際問題を使って1z1-084無料問題集サンプル問題と練習テストエンジン

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

Which statements is true'

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

正解：D

解説：
The query output from V$PGA_TARGET_ADVICE provides tuning information for the PGA (Program Global Area). Let's break it down step by step:
Key Columns in the Output:
* TARGET_MB:
* Represents the hypothetical PGA_AGGREGATE_TARGET values (in megabytes) evaluated by Oracle.
* CACHE_HIT_PERC:
* The percentage of work areas that could execute in-memory (optimal execution) without requiring temporary disk writes.
* Higher percentages indicate fewer work areas requiring disk I/O.
* ESTD_OVERALLOC_COUNT:
* The estimated number of work areas that need to go to disk (multipass operations or overallocations).
Observations from the Data:
* At TARGET_MB = 700 MB:
* The CACHE_HIT_PERC is 68%.
* The ESTD_OVERALLOC_COUNT is 30. This indicates that some multipass work areas still exist.
* At TARGET_MB = 800 MB:
* The CACHE_HIT_PERC rises to 74%.
* The ESTD_OVERALLOC_COUNT drops to 0. This indicates that no work areas require multipass execution.
* At TARGET_MB = 900 MB and above:
* The CACHE_HIT_PERC increases slightly to 82%-84%.
* The ESTD_OVERALLOC_COUNT remains 0, meaning that all work areas are now either optimal or one-pass.
Why D is Correct:
* At 800 MB or more, the ESTD_OVERALLOC_COUNT is 0, indicating that all one-pass execution work areas are eliminated.
* A one-pass execution requires temporary disk I/O for intermediate results, but with sufficient PGA, these are no longer necessary.
Why Other Options Are Incorrect:
* Option A:
* It mentions all multipass executions work areas would be eliminated at 700 MB. This is incorrect because, at 700 MB, the ESTD_OVERALLOC_COUNT is still 30, indicating some multipass work areas still exist.
* Option B:
* Suggests setting the PGA_AGGREGATE_TARGET to at least 800 MB, which is partially correct but does not address the elimination of one-pass execution.
* Option C:
* Suggests setting the PGA_AGGREGATE_TARGET to at least 700 MB, which is not sufficient to eliminate all one-pass executions, as shown by the ESTD_OVERALLOC_COUNT of 30.

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

• A.
• B.
• C.
• D.
• E.
• F.
• G.

正解：F、G

解説：
When MEMORY_TARGET is set to a nonzero value, Oracle automatically manages the memory allocation between the System Global Area (SGA) and the Program Global Area (PGA). If MEMORY_MAX_TARGET is not explicitly set, Oracle will behave in the following manner:
* MEMORY_MAX_TARGET will default to the value of MEMORY_TARGET, assuming the platform allows for the value of MEMORY_TARGET to be increased dynamically. This means that MEMORY_TARGET represents both the initial allocation and the maximum limit for the dynamically managed memory unless MEMORY_MAX_TARGET is specified differently.
* If MEMORY_TARGET is set to a value that is less than the sum of the current values of SGA_TARGET and PGA_AGGREGATE_TARGET, Oracle will use the higher sum as the default value for MEMORY_MAX_TARGET to ensure that there is adequate memory for both areas. The database instance will not start if MEMORY_TARGET is not sufficient to accommodate the combined SGA and PGA requirements.
References
* Oracle Database Administrator's Guide 19c: Automatic Memory Management
* Oracle Database Performance Tuning Guide 19c: Using Automatic Memory Management

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

• A. enq: HW - contention
• B. enq: TX - allocate ITL entry
• C. enq: SQ - contention
• D. enq: TM - contention

正解：A

解説：
When sessions concurrently insert data into a table with an LOB column and one session needs to allocate a new extent because it cannot find available space, the wait event associated with this contention is "enq: HW - contention". The HW stands for High Water Mark which is related to space allocation in the database segment.
When asession needs to allocate a new extent, it may raise this wait event in other sessions that are also attempting to allocate space in the same LOB segment.
References
* Oracle Database 19c Reference Guide - enq: HW - contention

質問 # 26
Which two Oracle Database features use database services?

• A. Oracle Automatic Reoptimization
• B. Oracle SQL Performance Management
• C. Oracle SQL Tuning Advisor
• D. Oracle Scheduler
• E. Database Resource Manager

正解：D、E

解説：
Database services in Oracle are used to manage how resources are allocated and how workloads are managed within the database. The features that use database services are:
* B (Correct):Database Resource Manager (DBRM) uses services to control resource allocation to different workloads. It ensures that resources are assigned to the most critical tasks first, based on the service associated with the workload.
* E (Correct):Oracle Scheduler can also utilize database services. Jobs in Oracle Scheduler can be assigned to different services to control resource allocation and prioritization.
The other features mentioned are related to SQL performance but do not directly utilize database services in the way Resource Manager and Scheduler do:
* A:Oracle Automatic Reoptimization is a feature that allows the database to automatically improve the execution plan of a SQL statement after it is executed, based on the actual performance metrics, but it does not directly use database services.
* C:Oracle SQL Performance Management involves various components of SQL tuning and monitoring, but it does not use database services to operate.
* D:Oracle SQL Tuning Advisor provides advice on how to tune SQL queries for better performance.
While it can be used in conjunction with services for managing and analyzing workloads, it doesn't use services in its core functionality.
References:
* Oracle Database Administrator's Guide:Administering Services
* Oracle Database Administrator's Guide:Managing Resources with Oracle Database Resource Manager
* Oracle Database Scheduler Developer's Guide:Using the Scheduler

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

• A. DB time includes the time spent executing the RMAN backup and restore command.
• B. DB time can be many times greater than the elapsed time since the database instance started.
• C. DB tine excludes the time spent waiting for a CPU in the operating system run queue.
• 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

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

Which statements is true'

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

正解：D

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

質問 # 29
Which two types of performance problems are reported by ADDM for PDBS?

• A. Top SQL statements
• B. Excessive checkpoint writes
• C. I/O capacity limits
• D. User I/O waits
• E. SGA sizing issues

正解：C、D

解説：
The Automatic Database Diagnostic Monitor (ADDM) analyzes and reports on various types of performance problems. For Pluggable Databases (PDBs), it can identify issues such as I/O capacity limits which may hinder the overall performance by causing bottlenecks. Additionally, ADDM can report on user I/O waits, which can indicate performance issues related to the time it takes for user queries to read data from the disk.
References:
* Oracle Multitenant Administrator's Guide, 19c
* Oracle Database Performance Tuning Guide, 19c

質問 # 30
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. Coalesce all sales table indexes.
• C. Partition the SALES table.
• D. Re-create the SALES table.
• 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.

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

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

正解：B、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

質問 # 32
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_ACTIVE_SESS_HISTORY
• C. DBA_HIST_PGASTAT
• D. DBA_HIST_PROCESS_MEM_SUMMARY
• E. DBA_HIST_SQLSTAT

正解：B、D

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

質問 # 33
Users complain about slowness and session interruptions. Additional checks reveal the following error in the application log:

Which file has additional information about this error?

• A. ASH report
• B. Alert log
• C. Session trace file SQL trace file automatically generated by the error
• D. SQL trace file automatically generated by the error

正解：B

解説：
When an ORA-00060 deadlock error occurs, detailed information about the error and the deadlock graph are dumped into the alert log. This log contains a trace file name that you can use to find additional detailed information about the sessions involved in the deadlock and the SQL statements they were executing.
References:
* Oracle Database Administrator's Guide, 19c
* Oracle Database Error Messages, 19c

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

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

正解：C

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

質問 # 35
Examine these commands, which execute successfully:

Which statement is true?

• A. ADDM, AWR, and ASH reports can be purged automatically.
• B. AD DM is enabled for all pluggable databases.
• C. AWR- snapshots in all pluggable databases will be purged automatically after every 60 mins.
• D. AWR snapshots can be purged manually in pluggable databases.

正解：D

解説：
The DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure allows
setting attributes related to AWR snapshots. While the interval setting controls the frequency of snapshot generation, purging them is a separate process that can be managed either automatically (with retention settings) or manually.References:
* Oracle Database PL/SQL Packages and Types Reference, 19c
* Oracle Multitenant Administrator's Guide, 19c

質問 # 36
Examine this AWRreport 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. MN STORE COMPRESS FOR QUERY LOW
• B. STORE COMPRESS
• C. COLUMN STORE COMPRESS FOR QUERY HIGH
• D. ROW STORE COMPRESS ADVANCED

正解：D

解説：
To reduce the impact of database I/O without increasing the size of the buffer cache and without modifying SQL statements, you can use table compression. Among the given options,ROW STORE COMPRESS ADVANCEDis the most suitable form of table compression to satisfy this requirement.
Advanced row compression (ROW STORE COMPRESS ADVANCED) is designed to work well with all supported types of data, whether it's OLTP or data warehouse environments. It offers a higher level of compression than basic table compression (ROW STORE COMPRESS BASIC)without significant overhead during DML operations. This feature can help reduce the amount of I/O required to retrieve data by storing it more efficiently on disk.
* A, B, D:WhileCOLUMN STORE COMPRESS FOR QUERY HIGHandROW STORE
COMPRESSare both valid compression types,COLUMN STORE COMPRESS FOR QUERY
* HIGHapplies to the In-Memory column store and is not available in all versions and editions, andROW STORE COMPRESSis less advanced thanROW STORE COMPRESS ADVANCED.
References:
* Oracle Database Concepts Guide:Table Compression
* Oracle Database Performance Tuning Guide:Row Compression

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

• A. Systems in which CPU time is dominant need more tuning that those in which WAIT TIME is dominant.
• 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. DB Time accounts for all time used by background processes and user sessions.
• E. The proportion of WAIT TIME to CPU TIME always increases with increased system load.
• F. 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.

正解：C、D、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

質問 # 38
Which two statements are true about disabling Automatic Shared Memory Management (ASMM)?

• A. All SGA components excluding fixed SGA and other internal allocations are readjusted immediately after disabling ASMM.
• B. All SGA components retain their current sizes at the time of disabling.
• C. Both SGA_TARGET and SGA_MAX_SIZE must be set to zero.
• D. The SGA size remains unaffected after disabling ASMM.
• E. All auto-tuned SGA components are reset to their original user-defined values.
• F. It requires a database instance restart to take effect.

正解：B、D

解説：
When ASMM is disabled, the sizes of the automatically managed SGA components remain at their current values. ASMM is controlled by theSGA_TARGETparameter. IfSGA_TARGETis set to a non-zero value, ASMM is enabled and Oracle will automatically manage the sizes of the various SGA components. When ASMM is disabled, by settingSGA_TARGETto zero, the SGA components that were automatically sized will retain their current sizes rather than being reset to their original user-defined values. The overall size of the SGA remains the same unless manually changed by modifying individual component sizes or SGA_MAX_SIZE.
References:
* Oracle Database Administration Guide, 19c
* Oracle Database Performance Tuning Guide, 19c

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

正解：A

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

質問 # 40
......

合格させるOracle 1z1-084試験問題でテスト復刻エンジンとPDF：https://jp.fast2test.com/1z1-084-premium-file.html