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
You are administering a database that supports an OLTP workload. CURSOR_SHARING is set to EXACT for the instance. An application is frequently executing almost identical queries that vary in literal values in the WHERE clause, causing a large number of hard parses to occur.
Which four statements would be true if you use bind variables for these queries? (Choose four.)
A. Mutex contention in the library cache will be reduced.
B. The optimizer will use one parent cursor and one child cursor for each SQL statement with different literal values.
C. Hard parses will be reduced for the queries.
D. The optimizer will use bind peeking and subsequent execution of the queries will always generate the same plans irrespective of the cardinality.
E. The optimizer will generate the same plan for all bind values if no histograms exist on the columns used in the WHERE clause of these queries.
F. The optimizer will use bind peeking and use the literal value to determine the execution plan for these queries.
You plan to upgrade your production database from Oracle Database 11g to 12c and also to introduce new objects to the database. You also want to upgrade the hardware. You have already created a test system with the upgrades to be made to the production database. As part of the testing, you want to:
analyze and compare the overall database workload with concurrency and transaction characteristics find SQL statements that might get regressed because of the upgrade analyze execution plans for SQL statements for which performance might get regressed analyze the impact of new schema objects on database performance
Which two tools would you recommend to achieve the objective? (Choose two.)
A. Database Replay
B. SQL Tuning Advisor
C. SQL Access Advisor
D. Automatic Database Diagnostic Monitor (ADDM) compare periods report
E. SQL Performance Analyzer
F. Automatic Workload Repository (AWR) compare periods report
Users complain about increased response time for queries in your production database that supports an OLTP workload. On investigation, you notice a large number of db file scattered read, latch: cache buffers lru chain, and latch: cache buffers chains wait events:
Identify three possible reasons for the increased response time. (Choose three.)
A. too many sort operations being performed
B. repeated simultaneous access to a block or small number of blocks
C. the shared pool is inadequately sized
D. queries not using indexes and performing full table scans
E. queries repeatedly fetching blocks that are not in the database buffer cache
F. cursors are closed explicitly after each execution
Examine this list of possible tasks:
1.
Ensure that STATISTICS_LEVEL is set to TYPICAL or ALL.
2.
Ensure that TIMED_STATISTICS is set to TRUE.
3.
Set MAX_DUMP_FILE_SIZE to UNLIMITED and DIAGNOSTIC_DEST to an appropriate destination.
4.
Ensure that SQL_TRACE is set to TRUE.
5.
Enable tracing at the database instance level by using the DBMS_MONITOR.DATABASE_TRACE_ENABLE procedure.
6.
Enable tracing in the required session by using the DBMS_SESSION.SET_SQL_TRACE procedure.
7.
Run TKPROF with the EXPLAIN parameter on the output trace file.
8.
Run the trcsess utility on the output trace files, and then run TKPROF on the output of the trcsess utility.
Select the minimum tasks to perform, in the correct order, to generate both a formatted trace file with timing information and an explain plan for each SQL statement for all sessions. (Choose the best answer.)
A. 1, 2, 5, 8
B. 1, 3, 6, 7
C. 2, 4, 5, 8
D. 1, 3, 4, 5, 6, 7
E. 1, 2, 4, 8
Examine the parameters set for your database instance: You are administering a database that supports an OLTP workload. Users complain about the degraded performance of some queries. While diagnosing, you notice a large number of hard parses occurring for several syntactically almost identical SQL statements that differ only in literal values in the WHERE clause.
Which two actions would you recommend to improve performance? (Choose two.)
A. Create the KEEP cache and cache the tables used in the queries.
B. Set the CURSOR_SHARING parameter to FORCE.
C. Use bind variables instead of literals.
D. Create SQL plan baselines for the almost identical SQL statements and load them into the cursor cache.
E. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE.
Which two statements are true about ADDM or Real-Time ADDM? (Choose two.)
A. ADDM can be run manually by selecting any range of AWR snapshots available within the AWR retention period, provided they do not cover a time period when the instances were restarted.
B. ADDM runs in Partial mode to analyze any hung database issues.
C. Real-Time ADDM can proactively detect and diagnose transient performance issues that last for a few seconds.
D. Real-Time ADDM is automatically invoked by ADDM at the end of every hour.
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.
Which two statements are true about server-generated alerts? (Choose two.)
A. They are always logged in the alert log.
B. They are written to a trace file if the TRACE_ENABLED initialization parameter is set to TRUE.
C. They are generated only when the STATISTICS_LEVEL initialization parameter is set to ALL.
D. They can be generated for user-defined metric thresholds.
E. They appear in the DBA_ALERT_HISTORY view whenever corrective action is taken for an alert.
Examine the Load Profile and partial Top 10 Foreground Events by Total Wait Time sections from an AWR report.
Which two areas should you examine next to identify possible bottlenecks?
A. the application code because of CPU-intensive activities
B. the application code because user calls are performing several queries that require sorting
C. the "SQL ordered by Gets" section of the AWR report to check for excessive logical I/O
D. the "SQL ordered by Reads" section of the AWR report to check for excessive physical reads
