1z1-084問題集で2025年最新のOracle 1z1-084試験問題
無料で使える1z1-084ブレーン問題集でダウンロード（1z1-084テスト問題集無料更新された)
質問 # 11
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
- C. STATISTICS_LEVEL=BASIC and CONTROL_MANAGEMENT_PACK ACCESS=DIAGOSTIC
- D. STATISTICS_LEVEL=TYPICAL and
CONTROL_MANAGEMENT_PACK_ACCESS-DIAGOSTIC*TUNING
正解：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
質問 # 12
Which two Oracle Database features use database services?
- A. Oracle SQL Tuning Advisor
- B. Database Resource Manager
- C. Oracle SQL Performance Management
- D. Oracle Scheduler
- E. Oracle Automatic Reoptimization
正解：B、D
解説：
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
質問 # 13
You are informed that the RMAN session that is performing the database duplication is much slower than usual. You want to know the approximate time when the rman operation will be completed.
Which view has this information?
- A. V$SESSION_LONGOPS
- B. V$SESSTAT
- C. V$SESSION
- D. V$RMAN_BACKUP_JOB_DETAILS
正解：A
解説：
In Oracle Database, theV$SESSION_LONGOPSview provides insights into various operations within the database that are expected to take more than six seconds to complete. These include operations related to RMAN (Recovery Manager), such as database duplication tasks. This view displays information about the progress of these long-running operations, including the start time, elapsed time, and estimated time to completion.
When an RMAN session is performing a database duplication and is observed to be slower than usual, checking theV$SESSION_LONGOPSview can give an approximation ofwhen the RMAN operation might complete. This view includes fields likeTIME_REMAININGandELAPSED_SECONDSthat help in estimating the completion time of the operation based on its current progress.
References:
* Oracle Database Reference:V$SESSION_LONGOPS
* Oracle Database Backup and Recovery User's Guide:Monitoring RMAN Jobs
質問 # 14
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 the SALES table using the columns in IX_SALES_TIME_ID as the hash partitioning key.
- B. Partition index IX_SALES_TIME_ID using hash partitioning.
- 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 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
質問 # 15
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. SQL trace file automatically generated by the error
- B. Alert log
- C. Session trace file SQL trace file automatically generated by the error
- D. ASH report
正解：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
質問 # 16
The CURS0R_SHARING and OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameters are set to default. The top five wait events in an awr report are due to a large number of hard parses because of several almost identical SQL statements.
Which two actions could reduce the number of hard parses?
- A. Create the RECYCLE cache and cache tables accessed by the SQL statements.
- B. Increase the size of the library cache.
- C. Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESto TRUE.
- D. Create the KEEP cache and cache tables accessed by the SQL statements.
- E. Set the CURSOR_SHARING parameter to FORCE.
正解：B、E
解説：
To reduce the number of hard parses due to several almost identical SQL statements, you can take the following actions:
* C (Correct):Increasing the size of the library cache can help reduce hard parses by providing more
* memory to store more execution plans. This allows SQL statements to be shared more effectively.
* E (Correct):Setting theCURSOR_SHARINGparameter toFORCEwill cause Oracle to replace literals in SQL statements with bind variables, which can significantly reduce the number of hard parses by making it more likely that similar SQL statements will share the same execution plan.
The other options do not directly impact the number of hard parses:
* A (Incorrect):Creating the KEEP cache and caching tables accessed by the SQL statements can improve performance for those tables, but it does not directly reduce the number of hard parses.
* B (Incorrect):Creating the RECYCLE cache and caching tables accessed by the SQL statements can make it more likely that objects will be removed from the cache quickly, which does not help with hard parse issues.
* D (Incorrect):SettingOPTIMIZER_CAPTURE_SQL_PLAN_BASELINEStoTRUEcan help stabilize SQL execution plans but will not reduce the number of hard parses. This parameter is used to automatically capture SQL plan baselines for repeatable SQL statements, which can prevent performance regressions due to plan changes.
References:
* Oracle Database Performance Tuning Guide:Minimizing Hard Parses
* Oracle Database SQL Tuning Guide:CURSOR_SHARING
質問 # 17
A database supporting a mixed workload is hosted on a server with 64 CPUs.
A large number of free buffer waits and buffer busy waits occur affecting performance.
The buffer cache size was then increased but after a few hours, the same wait events occur more often than before the change.
Examine these parameter settings:
Which two actions can help reduce the number of these waits7
- A. increasing the value of DBWRITERPROCESSES to 64,
- B. Increasing the size of MEMORYTARGET
- C. increasing the value of DB_FILE_MULTIBLOCK_READ_COUNT to 128
- D. reducing the values of DB_FILE_MULTILOCK_READ_COUNT to 64
- E. setting dbwr_io_slaves to 64
正解：A、E
解説：
Given a server with 64 CPUs, if the buffer cache size increase did not alleviate free buffer waits and buffer busy waits, one can look into optimizing I/O and the efficiency of the DB writer processes.
C: Setting theDBWR_IO_SLAVESparameter to a non-zero value, such as the number of CPUs, would initiate I/O slave processes to assist the DB writer process. This can help reduce I/O contention when writing from the buffer cache to disk, particularly for systems without asynchronous I/O capabilities.
D: Increasing the value ofDBWRITERPROCESSESenables multiple DB writer processes to be active simultaneously. In a system with many CPUs, such as 64, increasing this value can improve the write throughput to disk and potentially reduce buffer busy waits.
References:
* Oracle Database Reference, 19c
* Oracle Database Performance Tuning Guide, 19c
質問 # 18
Examine these statements and output:
What parameter change activates the generation and use of SQL Plan Directives7
- A. optimizer_adaptive_plans=TRUE
- B. optimizer_adaptive_statistics = TRUE
- C. optimizer_dynamic_sampling=11
- D. optimizer_capture_sql_plan_baselines_TRUE
- E. optimizer_features_enable=12.2.0.1
正解：B
解説：
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
質問 # 19
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
質問 # 20
Which application lifecycle phase could be managed reactively?
- A. Testing
- B. Design and development
- C. Deployment
- D. Upgrade or migration
- E. Production
正解：E
解説：
The production phase of the application lifecycle is often managed reactively. While proactive measures and performance tuning are essential, unforeseen issues can arise in production that require immediate attention and resolution. Reactive management involves monitoring performance and responding to issues as they occur, ensuring the application maintains acceptable performance levels for end-users.
References
* Oracle Database 19c Performance Tuning Guide - Reactive Tuning
質問 # 21
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 materialized views
- B. recommendations regarding the creation of SQL Patches
- C. recommendations regarding rewriting the SQL statements
- D. recommendations regarding partitioning the tables
- E. recommendations regarding the creation of additional indexes
正解：C、E
解説：
https://docs.oracle.com/en/database/oracle/oracle-database/21/tgsql/sql-tuning-advisor.html#GUID-
8E1A39CB-A491-4254-8B31-9B1DF7B52AA1
The goal is to reduce the db file scattered read waits, which are associated with full table scans. These are I
/O operations where Oracle retrieves data blocks scattered across the disk, typically when large amounts of data are read inefficiently. Running the SQL Tuning Advisor analyzes the workload and provides tuning recommendations. Let's evaluate the options.
Why A. Recommendations regarding the creation of additional indexes is correct:
* Full table scans (which cause db file scattered read) often occur because suitable indexes are missing.
* The SQL Tuning Advisor can identify queries that would benefit from indexes and recommend creating them. Indexes allow the database to access data more efficiently using row lookups, reducing the need for full table scans.
Why B. Recommendations regarding rewriting the SQL statements is correct:
* Sometimes, poorly written SQL statements cause inefficient execution plans that lead to db file scattered read.
* SQL Tuning Advisor can recommend SQL rewrites to make better use of indexes, avoid full table scans, or optimize joins. For example:
* Rewriting predicates to use indexed columns.
* Using hints to guide the optimizer.
Why Other Options Are Incorrect:
* C. Recommendations regarding the creation of materialized views:
* Materialized views are typically recommended to optimize complex queries involving aggregations or joins, not to address db file scattered read directly. They are less relevant for solving I/O issues caused by full table scans in this context.
* D. Recommendations regarding the creation of SQL Patches:
* SQL Patches are used to influence the execution plan for specific SQL statements. While SQL Patches can potentially fix performance issues, the SQL Tuning Advisor focuses on improving SQL and database design rather than patching queries.
* E. Recommendations regarding partitioning the tables:
* Partitioning tables can improve query performance, especially for very large datasets. However, this is a database design-level recommendation and is not typically provided by SQL Tuning Advisor. Partitioning would not directly target db file scattered read.
How SQL Tuning Advisor Helps:
The SQL Tuning Advisor provides actionable recommendations, such as:
* Creating indexes to reduce full table scans.
* Rewriting SQL to optimize the execution plan.
* Improving statistics to help the optimizer make better decisions.
References to Oracle Documentation:
* Oracle Database 19c Performance Tuning Guide:
* Section: Using SQL Tuning Advisor to Optimize Workloads.
* Explains recommendations for indexes and SQL rewrites to reduce I/O.
* Understanding Wait Events:
* Details about db file scattered read and how to address it.
質問 # 22
Buffer cache access is too frequent when querying the SALES table. Examine this command which executes successfully:
ALTER TABLE SALES SHRINK SPACE;
For which access method does query performance on sales improve?
- A. index range scan
- B. index full scan
- C. db file sequential read
- D. db file scattered read
正解：C
解説：
The SHRINK SPACE operation compacts the table, which can reduce fragmentation and thus improve performance for sequential reads of the table. This operation could improve full table scans, which are typically associated with db file sequential read wait events.
References:
* Oracle Database Administrator's Guide, 19c
質問 # 23
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. Create an index on SALES.TIME_ID to force the return of rows in the order specified by the ORDER BY clause.
- B. 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. Collect a new set of statistics on PRODUCT, CUSTOMERS, and SALES because the current stats are inaccurate.
- D. Enable Adaptive Plans so that Oracle can change the Join method as well as the Join order for this query.
- E. Replace the FETCH FIRST clause with ROWNUM to enable the use of an index on SALES.
正解：A
解説：
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
質問 # 24
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_SHARING = FORCE;
- B. ALTER SYSTEM SET CURSOR_INVALIDATION = DEFERRED;
- C. ALTER SYSTEM SET CURSOR_SPACE_FOR_TIME - TRUE;
- D. ALTER SYSTEM SET SESSION CACHED CURSORS = 500;
正解：B
解説：
Thecursor: pin S wait on Xwait 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 thecursor: pin S wait on Xwait events.
The other options are incorrect in addressing this issue:
* A (Incorrect):SettingCURSOR_SHARINGtoFORCEmakes the optimizer replace literal values with bind variables. It doesn't address the contention for cursor pins directly.
* B (Incorrect):CURSOR_SPACE_FOR_TIME=TRUEaims 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 Xwaits.
* D (Incorrect):IncreasingSESSION_CACHED_CURSORScaches more session cursors but doesn't necessarily prevent the contention indicated by thecursor: pin S wait on Xwait events.
References:
* Oracle Database Reference:CURSOR_INVALIDATION
* Oracle Database Performance Tuning Guide:Reducing Cursor Invalidation
質問 # 25
Which two statements are true about cursor sharing?
- A. Setting cursor_sharing to EXACT prevents Adaptive Cursor Sharing from being used.
- B. Setting optimizer_capture_sql_plan_baselines to TRUE loads all adaptive plans for the same statement into the cursor cache.
- C. 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.
- D. Adaptive Cursor Sharing guarantees that a suboptimal plan will never be used on any execution of a SQL statement.
- E. 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.
正解：A、C
解説：
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
質問 # 26
......
Oracle 1Z0-084試験は、60の複数選択の質問で構成されるコンピューターベースのテストです。候補者は試験を完了するために120分かかり、合格するには少なくとも63％を獲得する必要があります。試験料は245ドルで、候補者は世界中のピアソンビューテストセンターで試験を受けることができます。 Oracleは、候補者がこの試験を受ける前に、データベースのパフォーマンスとチューニング管理の少なくとも3年の経験があることを推奨しています。
Oracle 1Z0-084認定試験では、データベースの監視、パフォーマンス分析、パフォーマンスチューニング、トラブルシューティングなど、パフォーマンスとチューニング管理に関連する幅広いトピックをカバーしています。この試験では、リソース管理、データベースの統合、高可用性などの高度なトピックもカバーしています。試験に合格するには、候補者はこれらすべてのトピックを深く理解している必要があり、実際のシナリオに知識を適用できる必要があります。
Oracle 1Z0-084 試験は、105分以内に60問の多肢選択問題を解答する必要があります。本試験は、パフォーマンスモニタリング、チューニング、トラブルシューティング、メモリ管理など、さまざまなトピックをカバーしています。Oracleデータベースアーキテクチャーを理解し、パフォーマンスの問題を識別して解決する能力が必要です。
検証済みの1z1-084問題集と解答で合格保証で試験問題集テストエンジン：https://www.jpntest.com/shiken/1z1-084-mondaishu
1z1-084問題集は合格保証付き！合格させる1z1-084試験：https://drive.google.com/open?id=1_ZKFzV29ApbXZD7zKrS9TAy4uC-5tRRD