1Z0-054 - Oracle Database 11g: Performance Tuning
Go back to Oracle
Which File includes all the methods to insert, update, delete, and lock rows?
You identified poorly performing SQL by analyzing the V$SQL and V$SQLSTATS views. You want to investigate the plans for these SQL statements.Which two methods can you use to pull out the execution plan from the library cache for the already executed SQL? (Choose two.)
Query V$SQL_PLAN to view the execution plan.
Use the dbms_xplan.display_cursor function with the SQL ID and child number to generate the execution plan.
You have created a page template to use as the basis for the Web pages in your application however you have a handful of pages that require a slightly different layout from that of the template. What is the most efficient use of the framework to handle this situation?
Create one template for all pages and define template attributes to customize the template for those pages that are different.
Which statement is true regarding the setting of the PCTFREE value for an index?
It decides how much of a block to fill when the index is created.
Your database has the HR and Reporting applications. The HR application does most of the transactions on the database and the Reporting application does most of the batch processing operations.The Reporting application has more workload during nonpeak hours. Both the HR and Reporting applications run on the middle tier using connection pooling for connecting to database.You want to statistically measure the workload generated by both the applications and set priority for the workload generated by the applications. Which option would help you achieve this?
Creating services for the applications and setting the relative priority of services within an instance by binding the services directly to consumer groups
In your organization, there are two key databases. One of them is used primarily for an online transaction processing (OLTP) application and is directly accessed by the users in a client/server configuration. The application must process an average of 4,000 small transactions per day. The users enter frequent queries on whose results they depend for completing their transactions. One of the SLA requirements is that any query or transaction processing in the OLTP system should not exceed 15 seconds. The second database supports a decision support system (DSS) as well as batch report programs. It is accessed by client-side users, who submit ad hoc queries through SQL*Plus and by a browser-based,third party application that provides pooled connections for a large user base. The queries vary: some fetch only a few records, whereas others fetch and sort hundreds of records from several tables. As per the SLA specification the average turnaround time for a query on the DSS returning 10000 records should be 24 hours. For both the systems, the number of users is not consistent and may vary from time to time.The management wants to ensure maximum resource usage is allocated for the OLTP processing. Which two considerations should you give priority to when tuning these databases? (Choose two.)
Maximize throughput for batch processing.
Minimize the response time for OLTP users.
You work as a DBA and have the responsibility of managing a large online transaction processing (OLTP) system. You used the following query during the performance tuning activity: SQL> SELECT (1-((phy.value-phyd.value) / (cur.value + con.value))) * 100 2 "Cache Hit ratio" 3 FROM v$sysstat cur, v$sysstat con, v$sysstat phy, v$sysstat phyd 4 WHERE cur.name = 'db block gets' 5 AND con.name = 'consistent gets' 6 AND phy.name = 'physical reads' 7 AND phyd.name = 'physical reads direct'; Cache Hit Ratio --------------- 98.43 What can you conclude based on this ratio?
The database performance cannot be determined based only on this ratio.
Your database has the ACCTG service configured for an accounting application running on a middle tier. These service is used by the middle-tier applications to connect to the database by using connection pools. The application has three modules LEDGER, ACCOUNTS, and BILLING. You asked the developers to invoke the DBMS_APPLICATION_INFO.SET_MODULE procedure to set the module name and action in the code for the application. You enabled tracing at the service level by executing the following command: SQL> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('ACCTG'); You want to consolidate the trace output from the trace files created based on module. Which is the appropriate solution for this?
Using the trcsess utility to consolidate all trace files into a single output file,which can then be processed by the tkprof utility
Your database has the ACCTG service configured for an accounting application that runs on a middle tier. The application has three modules LEDGER, ACCOUNTS, and BILLING. You enabled tracing at the service level by executing the following command: SQL> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('ACCTG'); Which statement is true?
A trace file is created for each session that uses the service, regardless of the module and action.
In which case is the database buffer cache NOT used?
when the server process writes sort data to the temporary tablespace
Identify two correct statements about the Active Session History (ASH) data. (Choose two.)
A part of SGA memory is used to store ASH data as rolling buffer.
The ASH data can be analyzed between any two small time intervals.
You want information in the form of charts for throughput and services that shows baseline values and thresholds for the baseline. How would you achieve this?
By adding a baseline to the performance page in Oracle Enterprise Manager
There are 500 users of an online transaction processing (OLTP) application that resides in your database. The system had been maintaining an average response rate of 10 seconds as specified by the Service-Level Agreement (SLA).This morning you were informed that for the past four days the average response time has gone up to 15-20 seconds. No changes have been made to the hardware or networking setup.What is the first thing that you should do to diagnose the cause of the slow response time?
Determine whether the slow response is wait bound or CPU bound.
You notice that two wait events, Latch:cache buffer chains and Latch:cache buffer LRU chains, appear consistently in the Top 5 Timed Events for your database.Which two options describe what these wait events indicate? (Choose two.)
Latch:cache buffer LRU chains - indicates excessive block replacement
Latch:cache buffer chains - indicates inefficient SQL that accesses many blocks repeatedly
Examine the values for the following initialization parameters: STATISTICS_LEVEL = TYPICAL TIMED_STATISTICS = true You are managing an online transaction processing (OLTP) system. Application users notice that some queries have poor response time. You determine that queries from session ID 27, serial number 60, for user OE are heavy-resource consumers. To investigate further, you enabled tracing for the session by executing the following command: SQL> EXECUTE dbms_system.set_sql_trace_in_session (27, 60, true); Which statement is true?
The tkprof output file for the trace generated would not display bind variable values if bind variables are used in the queries executed in the session.
You have the automatic optimizer statistics gathering enabled for a 24x7 database as part of the automatic maintenance task. Because the automatic optimizer statistics collection runs during maintenance window(in the night), the statistics on tables that are significantly modified throughout the day becomes stale. This leads to poor query performance on these tables and you want to minimize the overhead in statistics collection. Identify a solution to this problem.
Gather statistics on the table when the tables have a representative number of rows, and then lock the statistics.
You are working on an online transaction processing (OLTP) system. By day most of the application users perform queries accessing the most recently added or modified rows. The applications have most of the queries based on multiple tables. But at night, some batch processing is also done. Which two actions would you recommend to choose a goal for the optimizer based on the needs of your application? (Choose two.)
setting the OPTIMIZER_MODE parameter to FIRST_ROWS_n at the instance level
asking the developer to add a hint /*ALL_ROWS*/ in the long-running batch processing queries
You are working on the design of Web pages for an application. One page requires that user can collapse or restore panels of data. When a panel collapsed the panel contents are hidden: when panel is restored, the contents are displayed. Which two layout components would enable you to achieve this design?
You have a page that shows employees, the department they work in, and their manager. When you assign an employee to a manager, you want to make sure the only managers you show are people who work in the same department as the employee. You can accomplish this task with a dependent list of values. In order to accomplish this task you need to create a view criteria on the employee's view. Which attribute from the employee's view should you use in view criteria?
You are managing an online transaction processing (OLTP) database with multiple applications. The applications support a large number of users and most of the applications execute groups of SQL that run together. Which option would you use to monitor the performance issues for users of these applications?
Monitor statistics and wait events collected for a MODULE and ACTION by performing service aggregation.
The Service-Level Agreement for the production database includes a clause that the database down time should not be more than 15 minutes.Which two details would you examine in the alert log to diagnose whether your database meets this requirement? (Choose two.)
instance recovery time
the FAST_START_MTTR_TARGET parameter setting
You are working on an online transaction processing (OLTP) system. You detected free buffer waits events for your database instance running in a machine that has multiple CPUs. You increased the database buffer cache size as the first step. After a few hours of work on the database, further investigation shows that the same event is being recorded. What would be your next step to avoid this event in future?
Increase the value of the DB_WRITER_PROCESSES parameter.
You are working on the Database using file system RAID level 0 striping. You want to migrate the database files to the Automatic Storage Management (ASM). The applications supported by the database primarily perform small, random I/Os in which each foreground process reads a data block into the buffer cache for updates and the changed blocks are written in batches by the DBWR process.Identify two outcomes of ASM striping in combination with RAID 0. (Choose two.)
It provides higher bandwidth.
It allows you to evenly distribute disks for your data.
What indicates that the rate of writing data blocks to the disk is very slow?
increasing number of free buffer waits
You work for a small manufacturing company as a DBA. The company has various applications to manage the details of its business and customer base. The company has only one sales outlet where the operator updates the SALES table in the sales application with the details when a product is sold. The company has many other applications for various other businesses that use the same database. The sales operator, while generating a report, noticed that there are some gaps generated in the serial number in the SLNO column during peak hours of business. On investigating, you find that the SLNO column in the SALES table uses the SLNSEQ sequence to generate the serial numbers when a record is inserted. During peak hours, you noticed the following: When the operator entered the sales details the last time, the sequence number was 1056300, but when you checked the sequence as follows, you found a gap of 100 numbers: SQL> SELECT sequence_name, last_number FROM user_sequences WHERE sequence_name='SLNSEQ'; SEQUENCE_NAME LAST_NUMBER ------------------------------ ----------- SLNSEQ 1056400 You observed this behavior many times and only during peak hours. What could be the problem and solution for this?
The sequence is aging out of the shared pool. Keep the sequence in the shared pool using the DBMS_SHARED_POOL.KEEP procedure.
The OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter is set to TRUE for a 24x7 database instance. The space consumption has significantly increased by the automatically captured plan baselines. You are suggested to run the following block: BEGIN dbms_spm.configure('SPACE_BUDGET_PERCENT', 30); END; After a few weeks of work, you observe warnings on exceeding space usage by SQL Management Base (SMB) in alert log file. Which two actions can be solutions for this? (Choose two.)
purging the older SQL management objects
increasing the size of the SYSAUX tablespace
Which two inferences are correct about the CPU time and the wait time that appear in the Automatic Workload Repository AWR and Statspack reports in the Top 5 Event section? (Choose two.)
Increase in wait time when the CPU time increases or remains the same indicates an increase in the load on the system.
Systems where the proportion of CPU time does not decrease significantly with increasing load would most likely benefit from adding CPUs.
You receive a recommendation for a SQL statement through the automatic SQL tuning process that suggests implementing a SQL profile. You implement the SQL profile. The tables associated with the SQL statement grow, and indexes are created and dropped on these tables. What would happen to the SQL profile that is created for the SQL statement?
The profile will be used for the changed plans of the SQL statement.
You observe that suboptimal execution plans for the queries are being generated on a table that previously used less resources. You have collected statistics on these tables two days ago. The optimizer statistics retention period is set to 31 days. You are able to find the timestamp information about statistics update from the DBA_TAB_STATS_HISTORY view. Because it is a frequently queried table, you would like the optimizer to generate better plans. Which action would enable you to use the previous set of statistics on the objects that may lead to better execution plans?
restoring statistics from statistics history up to the desired time
A batch workload that historically completed in the maintenance window between 10:00 PM and midnight is currently showing poor performance and completing at 2 AM. To help in the diagnosis of the performance degradation, the senior DBA in your organization asks you to execute the awrddrpt.sql script to produce a Compare Periods report. Which two statements are true about the report produced by this script? (Choose two.)
It compares details between any two selected time periods of the same duration.
It normalizes the statistics by the amount of time spent on the database for each time period.
A user in a session executed the following SQL statement to set the optimizer mode: ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS What impact would it have on the goal of the optimizer for that session? (Choose all that apply.)
Statement level OPTIMIZER_MODE hints take precedence over the session-level setting.
The optimizer uses a cost-based approach for all SQL statements in the session, regardless of the presence of statistics; it optimizes with a goal of best throughput.
You observed that some of the queries are performing poorly on the SALES_RECORDS table in your database. On further investigation, you find that at the end of each day the contents of the SALES_RECORDS table are transferred to the SALES table and deleted from the SALES_RECORDS table. The deleted operations cause the table to be sparsely populated. You decided to use the ALTER TABLE...SHRINK SPACE COMPACT command to shrink the table. Why would you choose this method? (Choose all that apply.)
because it can be used during peak hours
because it avoids unnecessary cursor invalidation
because you have long-running queries that might span the shrink operation
While reviewing the Top 5 Timed Events section in the Automatic Workload Repository report, you notice that the db file scattered read and db file sequential read events appear in the list. Which two reasons could have caused these wait events? (Choose two.)
SQL statements performing frequent full table scans
SQL statements performing frequent fast full index scans
In your organization the Oracle Database 11g installation includes the Oracle Diagnostic Pack.In your database, the STATISTICS_LEVEL and CONTROL_MANAGEMENT_PACK_ACCESS parameters, and the snapshot interval and retention period have been set to default values.Two users of your database have complained that a particular job or session is very slow for a transient interval of about 10 minutes during the day when the rest of the instance is performing as usual. Where would you begin the investigation of this performance issue?
As part of a proactive tuning activity, you have the Automatic Workload Repository (AWR) and ADDM enabled for your database with the default setting of snapshot retention as 8 days and snapshot interval as 60 minutes. Which performance issue does NOT appear in the ADDM and needs alternative tools for investigation?
short-lived or intermittent performance issues lasting for less than 5 minutes
Examine the following block that executes a SQL Performance Analyzer task: begin dbms_sqlpa.execute_analysis_task(task_name => 'SPA_SKD4', execution_type => 'TEST EXECUTE', execution_name => 'before'); end; Which two statements are true about the execution? (Choose two.)
It generates execution plans and execution statistics.
It executes only the query part of the DML statements.
You work as a DBA in ABC Corp. You are working on an online transaction processing (OLTP) database. The database size has grown to 100 GB. The database will support new applications and more users will be added. Presently, the database uses normal file system storage. Before the new applications are added, you want to move the storage to RAID. You are in consultation with the OS Administrator for finalizing the RAID option for the storage. You have the following requirements for the database: 1) Less mean time to recover (MTTR) 2) Striped for performance 3) Mirrored for safety Which RAID option would you recommend?
RAID level 0 over 1(1+0)
You work as a DBA and you have the responsibility of managing a large online transaction processing (OLTP) system. You used the following query during the performance tuning activity: SQL> SELECT (1-((phy.value-phyd.value) / (cur.value + con.value))) * 100 2 "Cache Hit ratio" FROM v$sysstat cur, v$sysstat con, v$sysstat phy, v$sysstat phyd 4 WHERE cur.name = 'db block gets' AND con.name = 'consistent gets' AND phy.name = 'physical reads' AND phyd.name = 'physical reads direct'; Cache Hit Ratio --------------- 98.43 Which understanding of this ratio is correct?
98.43% of times the requests have found the required data blocks in the buffer cache.
You have a business component model to support departments (EO, VO, AM), and want to access it as a Web service. What should be the steps you'd need to perform to create a JavaServer Faces page?
1. Create a service interface for an application module, 2: Select the entity objects and methods to include the service, 3: Deploy to WLS as BS service interface, 4: connect to server containing WSDL, 5: create data control from XSD, 6: select operations to include in data control, 7: Develop UI from the Data Control as Normal.
You are managing an online transaction processing (OLTP) application database. Some users on the system are reporting degradation in the performance of SQL statements. To diagnose the problem, you want to identify the SQL statements that are expensive on system resources. What would you recommend to accomplish the task? (Choose all that apply.)
querying the V$SQLSTATS and V$SQL_PLAN views to get the information
creating an Automatic Workload Repository (AWR) report for the problematic period
You want to capture the performance statistics during a system test that is scheduled for the weekend. Which action would help you to automatically capture the performance statistics?
Creating Automatic Workload repository (AWR) baseline templates
Examine the output of the following query: SQL> SELECT c.name,a.addr,a.gets,a.misses,a.sleeps, 2 a.immediate_gets,a.immediate_misses,b.pid 3 FROM v$latch a, v$latchholder b, v$latchname c 4 WHERE a.addr = b.laddr(+) and a.latch# = c.latch# 5 AND c.name LIKE '&latch_name%' ORDER BY a.latch#; LATCH NAME ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES ------------ -------- -------- ------- ------ -------------- ---------------- shared pool 20016544 8520540 14112 3137 0 0 Which two conclusions can you draw from this? (Choose two.)
The latch was requested in willing-to-wait mode.
The shared pool operations to allocate and free memory in it caused the misses.
What type of redundancy would you recommend for the Automatic Storage Management (ASM) of a file system based on RAID level 1 for the optimum performance of database using ASM storage?
Which three statements are true about performance analysis by SQL Performance Analyzer? (Choose three.)
It detects changes in SQL execution plans.
It produces results that can be used to create the SQL plan baseline.
It generates recommendations to run SQL Tuning Advisor to tune regressed SQL statements.
Your system has been upgraded from Oracle Database 10g to Oracle Database 11g. You imported SQL Tuning Sets (STS) from the previous version that contains important SQL statements. You are not sure about the elapsed time that the SQL statements will have in the new system due to the version change of the optimizer. Which tool provides a comparative report with respect to the elapsed time in both versions of the optimizer?
SQL Performance Analyzer (SPA)
Which managed property type supports a null value?
What file would you use if you wanted to modify the Authentication Security of an application?
Which three statements are true about Statspack usage? (Choose three.)
Both Statspack and AWR can be used on the same database only if the Database Diagnostics pack is present.
Statspack can be used to generate a report for a specific instance as well as a specific SQL statement in an instance.
For best performance when running the performance reports, optimizer statistics should be gathered on the Statspack schema.
Examine the output of the following query: SQL> SELECT c.name,a.addr,a.gets,a.misses,a.sleeps, 2 a.immediate_gets,a.immediate_misses,b.pid 3 FROM v$latch a, v$latchholder b, v$latchname c 4 WHERE a.addr = b.laddr(+) and a.latch# = c.latch# 5 AND c.name LIKE '&latch_name%' ORDER BY a.latch#; LATCH NAME ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES ------------ -------- -------- ------- ------ -------------- ---------------- shared pool 20016544 8520540 14112 3137 0 0 You calculated the Gets-to-Misses ratio, which is .99834, and this ratio is dropping over a period of time. Which two actions can improve this? (Choose two.)
reducing hard parses
increasing the size of the shared pool
You plan to use adaptive thresholds as part of the performance tuning activity. You decide to increase the window size of the default moving window baseline for all metric observations and comparisons in your database. The following error occurs when you try to increase the window size through Enterprise Manager: Failed to commit: ORA-13541: system moving window baseline size (1296000) greater than retention (1036800) ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 601 ORA- 06512: at line 2 Which action would allow you to perform the preceding task successfully?
increasing the Automatic Workload Repository (AWR) retention period