Page: 1
/ 6
Total 84 questions
Examine the structure of the EMPLOYEES table.
EMPLOYEE_ID is the primary key. No other indexes exist on this table.
View the Exhibit to examine the commands and their output.
Which two actions should you perform to make the cursor bind aware? (Choose two.)
- A. Create a histogram on the DEPARTMENT_ID column.
- B. Change the default CURSOR_SHARING value to FORCE.
- C. Execute the query with the same DEPARTMENT_ID value multiple times.
- D. Create an index on the DEPARTMENT_ID column.
- E. Gather statistics for the index.
- F. Regather statistics on the table.
Answer : C,D
Which two actions can reduce library cache latch contention for an OLTP application that repeatedly executes queries containing a mix of literals and bind variables? (Choose two.)
- A. setting the OPEN_CURSORS parameter to hold a sufficient number of concurrently open cursors
- B. coding the application such that a frequently executed statement is parsed only once and executed repeatedly as required
- C. setting the CURSOR_SHARING parameter to EXACT
- D. avoiding the granting of privileges on objects associated with cursors during peak load
- E. enabling Automatic Memory Management and allocating at least 50% of the available memory for SHARED_POOL_SIZE
- F. configuring shared server connections
Answer : B,E
Reference: http://docs.oracle.com/cd/B28359_01/server.111/b28274/memory.htm
You want to capture the performance of your database during the last ten days of the first quarter of the current financial year, so that you can compare this performance against the remaining quarter ends of the current financial year.
Which method should you use? (Choose the best answer.)
- A. Create a static baseline that can be used with AWR compare reports.
- B. Create a new moving window baseline and enable adaptive thresholds for relevant metrics.
- C. Use a repeating baseline template to create and drop baselines based on a repeating time schedule and set adaptive thresholds at a high significance level.
- D. Use fixed baseline templates to create a new moving window baseline and set relevant warning alerts that are computed as a percentage multiple of the maximum value observed for the data in the moving window baseline.
Answer : D
You are administering a database that supports a mixed workload. The
CURSOR_SHARING parameter is set to the default value. While analyzing the latest
Automatic Workload Repository (AWR) report, you find a large number of cursor: pin S wait on X, cursor: pin X wait on S, and library cache mutex waits in the Top 10 foreground events section. Examine the Instance Efficiency Percentages section in the AWR report:
Which three statements are true in this scenario? (Choose three.)
- A. Sessions are waiting for mutexes in share mode on cursors but other sessions are holding the mutexes in exclusive mode.
- B. The CPU is spending more time in finding cursors in the library cache.
- C. Cursors are not getting shared, resulting in a large number of hard parses.
- D. Sessions are waiting for mutexes in exclusive mode on cursors but other sessions are holding the mutexes in share mode.
- E. The buffers required by queries are not found in the buffer cache, thereby increasing expensive disk I/O.
Answer : B,D,E
Examine the command to change a parameter value from the default to 50:
SQL> ALTER SYSTEM SET OPTIMIZER_INDEX_COST_ADJ = 50;
What is the effect of changing the value of the parameter? (Choose the best answer.)
- A. It influences the optimizer to use full table scans instead of index scans as the estimated cost of full table scan is reduced.
- B. It influences the optimizer to use bitmap indexes as the estimated cost of conversion from bitmap to rowid is reduced.
- C. It influences the optimizer to always use fast full index scans as the estimated cost of using an index is reduced.
- D. It influences the optimizer to use indexes instead of full table scans as the estimated cost of using an index is reduced.
Answer : A
Reference: http://www.dba-oracle.com/oracle_tips_cost_adj.htm
Your database supports an online transaction processing (OLTP) workload. The database uses ASM storage. One of the ASM disks goes offline because of hardware failure. When the disk is replaced and then added back to the diskgroup, database performance is affected by rebalance operations.
Which two actions would you recommend to lower the impact of rebalance operations on the performance of the database? (Choose two.)
- A. Increase the number of ASMB processes.
- B. Decrease the value of the ASM_POWER_LIMIT parameter.
- C. Set the DISK_REPAIR_TIME disk attribute to a lower value.
- D. Specify the POWER clause with a lower value in an ALTER DISKGROUP statement.
- E. Set the DISK_REPAIR_TIME disk attribute to a higher value.
Answer : B,D
Which two statements are true about ADDM? (Choose two.)
- A. It analyzes the performance of a database instance based on the time period covered by the most recent AWR snapshot, and generates recommendations based on hard-coded criteria.
- B. It can analyze performance issues that occurred in past events provided they fall within the AWR retention period.
- C. ADDM resource utilization and cost of analysis depends on the actual load on the database and the number of performance problems analyzed.
- D. It first identifies the performance symptoms, and then refines them to reach the root cause with the singular aim of reducing the DB CPU metric.
- E. It documents only those components and wait classes that are significantly impacting the performance of the database.
Answer : A,B
Which two statements are true about Active Session History (ASH)? (Choose two.)
- A. The Data Sample size available in an ASH report is dynamic and, at any given moment, is directly related to the amount of work being performed.
- B. ASH contains sampled data from all sessions that are connected to a database instance at any given moment.
- C. ASH samples data from V$SESSION every second.
- D. An ASH report can be used to identify the service that may be the cause of a transient performance problem.
Answer : A,D
Which two statements are true about gathering statistics? (Choose two.)
- A. If an application has only SQL statements with bind variables, it is better to drop existing histograms, disable creation of histograms, and allow the optimizer to select the best execution plans.
- B. If end users query newly inserted data, it is possible to get a suboptimal execution plan even if the automatic statistics gathering job is enabled.
- C. If concurrent statistics gathering is done by using parallel execution, the Resource Manager should be used for efficient resource management.
- D. For each session that is accessing a global temporary table, the optimizer uses only the shared statistics.
Answer : A,B
You are administering a database that supports an OLTP workload. The
CURSOR_SHARING parameter is set to EXACT for the instance. The performance of queries issued by one of the modules has degraded. The queries executed by the module are almost identical in syntax. To investigate, you analyze the latest AWR report and find a large number of latch:shared pool wait events and also a high percentage of the hard parse elapsed time.
Which two can be reasons for this? (Choose two.)
- A. The I/O performance is slow.
- B. Bind variables are not used for similar queries, causing hard parses.
- C. Repeated access to a small number of blocks.
- D. Excessive time is spent on finding cached cursors in the library cache.
- E. The CURSOR_SHARING parameter is set to EXACT, which does not allow similar queries to share a cursor.
Answer : B,C
Examine the parameters set for your database instance:
You are asked by a developer to create a table for an application with these requirements:
-> The table will be used for a DSS application.
-> High volume bulk loads will be performed.
-> The table will be used to store archival data on which large full-table scans (FTS) will be performed.
Which attributes are the best for the tablespace in which this table should be created?
(Choose the best answer.)
- A. Create it in a locally managed tablespace with ASSM enabled and assign a high value for the PCTFREE attribute.
- B. Create it in a locally managed tablespace with manual segment space management.
- C. Create it in a locally managed tablespace with a bigger nonstandard block size and ASSM enabled.
- D. Create it in locally managed tablespace with ASSM enabled and an additional freelist.
Answer : C
You want to generate statistics for new objects added to an OLTP application without affecting old statistics and the sessions that currently use them.
Which two tasks would you perform to test the new statistics? (Choose two.)
- A. Set the OPTIMIZER_USE_PENDING_STATISTICS initialization parameter to TRUE for the session.
- B. Set the STALE_PERCENT statistics preference to zero and then gather statistics.
- C. Set the PUBLISH statistics preference to FALSE and then gather statistics.
- D. Use the DBMS_STATS.PUBLISH_PENDING_STATS procedure to make pending statistics the current statistics.
- E. Set the NO_INVALIDATE statistics preference to FALSE and gather statistics without affecting old statistics.
Answer : A,B
Your database supports an OLTP workload during the day and batch processing at night.
You want to monitor performance metrics to detect any degradation of performance in both types of workloads over a time period of 30 days.
Examine this list of possible steps:
1. Create a fixed baseline.
2. Create a baseline template.
3. Create a new moving window baseline.
4. Increase the retention period default value to 30 days.
5. Increase the size of the existing moving window baseline to 30 days.
6. Create warning and critical alerts for the relevant metrics.
7. Enable adaptive thresholds to detect the workload patterns and specify a high- significance-level threshold type.
8. Enable adaptive thresholds to detect the workload patterns and set different threshold values as a percentage of the maximum value.
Which option represents the required steps in the correct order? (Choose the best answer.)
- A. 5, 7
- B. 2, 4, 3
- C. 3, 4, 8
- D. 4, 5, 7
- E. 5, 1, 6, 8
Answer : E
Your database supports a mixed workload. The ERP application creates short sessions and performs small, random I/Os; the REPORTING application executes long-running DSS queries.
You want to set a priority for the workload generated by the ERP application and optimize resource usage for them.
Which three objectives can be achieved by the Resource Manager? (Choose three.)
- A. limiting the amount of time that a session is idle and blocking other sessions of the ERP application
- B. limiting the amount of undo generated by operations performed by sessions created by the ERP application
- C. creating two resource plans with resource limits defined for the workload generated by the applications and automatically changing resource plans based on the workload
- D. allocating a lower percentage of CPU to sessions used by the REPORTING application than to those used by the ERP application
- E. limiting the physical I/O performed by the sessions or users of the ERP application that are connected to the database
Answer : B,D,E
In the CUSTOMERS table, the values in the CUST_STATE column are dependent on the values in the COUNTRY_ID column. You want to make the optimizer aware of this dependency when these columns are used together in WHERE clause predicates that contain equalities or in-lists.
Which two methods achieve this? (Choose two.)
- A. gathering statistics on the CUSTOMERS table and its dependent objects, and then locking the statistics
- B. using SQL plan directives to generate an optimal plan
- C. setting the dynamic statistics level to 4 and setting the OPTIMIZER_USE_PENDING_STATISTICS initialization parameter to true
- D. creating column group statistics, regathering statistics, and ensuring that histograms exist on both these columns
Answer : A,D
30 days access 