1Z0-054 - Oracle Database 11g: Performance Tuning

Go back to Oracle

Exam FAQ

How many questions are there on the 1Z0-054 exam? How long is the 1Z0-054 exam? How much does the 1Z0-054 exam cost? What is the best way to study for the 1Z0-054 exam?

Example Questions

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.) 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? Which statement is true regarding the setting of the PCTFREE value for an index? 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? 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.) 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? 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? 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? In which case is the database buffer cache NOT used? Identify two correct statements about the Active Session History (ASH) data. (Choose two.) 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? 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? 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.) 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? 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. 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.) 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? 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.) 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? 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.) What indicates that the rate of writing data blocks to the disk is very slow? 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 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.) 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.) 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? 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? 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.) 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.) 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.) 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.) 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? 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.) 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? 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? 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? 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.) 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? 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.) 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.) 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? 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.) 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.) 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?