oracle 1z0-054 exam questions & answers...2013/11/28 · the optimizer_mode parameter set at...

Oracle 1Z0-054 Exam Questions & Answers

Number: 1Z0-054Passing Score: 800Time Limit: 120 minFile Version: 14.5

http://www.gratisexam.com/

Oracle 1Z0-054 Exam Questions & Answers

Exam Name: Oracle Database 11g: Performance Tuning

For Full Set of Questions please visit: http://www.passguide.com/1Z0-054.html

Passguide

QUESTION 1After running SQL Performance Analyzer (SPA), you observe a few regressed SQL statements in the SPAoutput.

Identify the two actions that you would suggest for these regressed SQL statements. (Choose two.)

A. Running SQL Access AdvisorB. Adding them to SQL Plan BaselineC. Submitting them to SQL Tuning AdvisorD. Running Automatic Database Diagnostic Monitor (ADDM)

Correct Answer: BCSection: (none)Explanation

Explanation/Reference:

QUESTION 2View the Exhibit exhibit1 to examine the series of SQL commands and parameter settings.

View the Exhibit exhibit2 to examine the plans available in the SQL plan baseline.

The first plan (in red) is created when OPTIMIZER_MODE is set to ALL_ROWS and the second plan (in blue)is created when OPTIMIZER_MODE is set to FIRST_ROWS.

Which SQL plan baseline would be used if the SQL query in exhibit1 is executed again when the value ofOPTIMIZER_MODE is set to FIRST_ROWS?

Exhibit 1 (exhibit):


A. the second plan, because it is a fixed planB. the first plan, because it is an accepted planC. the second plan, because it is the latest generated plan in FIRST_ROW modeD. A new plan, because the second plan in FIRST_ROW mode is not an accepted plan

Correct Answer: BSection: (none)Explanation


QUESTION 3You work as a DBA for a company and you have the responsibility of managing one of its online transactionprocessing (OLTP) systems. The database encountered performance-related problems and you generated anAutomatic Workload Repository (AWR) report to investigate it further. View the Exhibits and examine the AWRreport.

What could be the problem in this database?



A. Java pool is not configured.B. The CPU in the system is slow.C. The shared pool size is inadequate.D. The database buffer cache is inadequate.E. The OPEN_CURSORS parameter is set to a small value.

Correct Answer: CSection: (none)Explanation


QUESTION 4You are working on an online transaction processing (OLTP) system. You detected free buffer waits events foryour database instance running in a machine that has multiple CPUs. You increased the database buffer cachesize as the first step. After a few hours of work on the database, further investigation shows that the sameevent is being recorded.

What would be your next step to avoid this event in future?

A. Decrease the value of the DBWR_IO_SLAVES parameter.B. Set the USE_INDIRECT_DATA_BUFFERS parameter to TRUE.C. Increase the value of the DB_WRITER_PROCESSES parameter.D. Increase the value of the DB_FILE_MULTIBLOCK_READ_COUNT parameter.



QUESTION 5You are working on a development database that was upgraded to Oracle Database 11g from Oracle Database9i. An ADDM finding in this database says that the shared pool is inadequately sized, as shown in the Exhibit.

You diagnosed that this is due to different kinds of workloads and this occurs only during peak hours. You triedto resize this by shrinking the database buffer cache but that caused inadequate buffer cache problems. Thefollowing are the related parameter settings:

SQL> show parameter sgaNAME TYPE VALUE------------------------------ --------------- ----------------- lock_sga boolean FALSEpre_page_sga boolean FALSEsga_max_size big integer 300Msga_target big integer 0SQL> show parameter targetNAME TYPE VALUE------------------------------ --------------- ----------------- .....fast_start_mttr_target integer 0memory_max_target big integer 0memory_target big integer 0pga_aggregate_target big integer 100Msga_target big integer 0

You want to balance the memory between the System Global Area (SGA) components within SGA withoutaffecting the size of the Program Global Area (PGA).

Which action would solve this problem?

Exhibit:

A. Set the SGA_TARGET parameter to 300M.B. Set the SGA_MAX_SIZE parameter to 400M.C. Set the MEMORY_TARGET parameter to 100M.D. Set the MEMORY_MAX_TARGET parameter to 300M.

Correct Answer: ASection: (none)Explanation


QUESTION 6You observed that some of the queries are performing poorly on the SALES_RECORDS table in yourdatabase.On further investigation, you find that at the end of each day the contents of the SALES_RECORDS table aretransferred to the SALES table and deleted from the SALES_RECORDS table. The deleted operations causethe table to be sparsely populated. You decided to use the ALTER TABLE...SHRINK SPACE COMPACTcommand to shrink the table.


Why would you choose this method? (Choose all that apply.)

A. because it can be used during peak hoursB. because it avoids unnecessary cursor invalidationC. because it adjusts the high-water mark (HWM) immediatelyD. because you have long-running queries that might span the shrink operationE. because it does not allow any data manipulation language (DML) operations, thereby making the shrink

operation faster

Correct Answer: ABDSection: (none)Explanation


QUESTION 7View the Exhibit and examine a portion of the output obtained from the following query:

SQL> SELECT * FROM v$sys_time_model;

Select three correct interpretations of the time model statistics. (Choose three.)

Exhibit:

A. DB time includes the wait time of all the nonidle and idle user sessions.B. SQL execute elapsed time includes the time spent in performing fetches of query results.C. DB CPU includes the CPU time spent on database user-level calls and background CPU time.D. SQL execute elapsed time includes components of the hard parse elapsed time like bind elapsed time.E. DB time includes the connection management call elapsed time excluding the background process time.

Correct Answer: BDESection: (none)Explanation


QUESTION 8A batch workload that historically completed in the maintenance window between 10:00 PM and midnight iscurrently showing poor performance and completing at 2 AM. To help in the diagnosis of the performancedegradation, the senior DBA in your organization asks you to execute the awrddrpt.sql script to produce aCompare Periods report.

Which two statements are true about the report produced by this script? (Choose two.)

A. It is refreshed automatically based on the moving window baseline.B. It compares details between any two selected time periods of the same duration.C. It normalizes the statistics by the amount of time spent on the database for each time period.D. It compares details between two consecutive time periods of the same or different durations and is

refreshed every 60 minutes.



QUESTION 9You 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 yourdatabase. 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?

A. increasing the flashback retention periodB. increasing the retention period for SQL Management BaseC. increasing the undo retention period for the database instanceD. increasing the Automatic Workload Repository (AWR) retention period

Correct Answer: DSection: (none)Explanation


QUESTION 10Identify two correct statements about the Active Session History (ASH) data. (Choose two.)

A. A part of SGA memory is used to store ASH data as rolling buffer.B. The ASH data can be analyzed between any two small time intervals.C. All ASH data in memory are flushed to disk by MMON in every 3 seconds.D. All ASH data in memory are flushed to disk by MMNL process whenever the buffer is full.

Correct Answer: ABSection: (none)Explanation


QUESTION 11A 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.)

A. Statement level OPTIMIZER_MODE hints take precedence over the session-level setting.B. The OPTIMIZER_MODE parameter set at instance level takes precedence over the session-level value.C. The optimizer uses a cost-based approach, regardless of the presence of statistics; it optimizes with a goal

of best response time.D. 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.

Correct Answer: ADSection: (none)Explanation


QUESTION 12You 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 thequeries 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 yourapplication? (Choose two.)

A. setting the OPTIMIZER_MODE parameter to ALL_ROWS at the instance levelB. setting the OPTIMIZER_MODE parameter to FIRST_ROWS_n at the instance levelC. asking the developer to add a hint /*ALL_ROWS*/ in the long-running batch processing queriesD. asking the developer to add a hint /*FIRST_ROWS_n*/ in the long-running batch processing queries



QUESTION 13Which three factors influence the optimizer's behavior while choosing an optimization approach and goal for aSQL statement? (Choose three.)

A. parsing of a SQL statementB. operating system (OS) statisticsC. object statistics in the data dictionaryD. the OPTIMIZER_MODE initialization parameterE. optimizer SQL hints for changing the query optimizer goal

Correct Answer: CDESection: (none)Explanation


QUESTION 14Examine the initialization parameter values for the instance given below:NAME TYPE VALUE------------------------------------ ----------- ------------------ optimizer_capture_sql_plan_baselines boolean FALSEoptimizer_dynamic_sampling integer 2optimizer_features_enable string 11.1.0.6optimizer_index_caching integer 0optimizer_index_cost_adj integer 100optimizer_mode string ALL_ROWSdb_file_multiblock_read_count integer 64

The index created on the column used in the WHERE clause of the query. You notice that the query is notusing the index. Instead of an index scan, a full table scan is used. View the Exhibit and examine the autotraceoutput for a query.

What could be the reason for it? (Choose all that apply.)

Exhibit:

A. The OPTIMIZER_INDEX_COST_ADJ initialization parameter has a low value.B. The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter has a low value.C. The statistics for the table and all the indexes associated with the table are not current.D. The table has less than DB_FILE_MULTIBLOCK_READ_COUNT blocks under the high-water mark.

Correct Answer: CDSection: (none)Explanation


QUESTION 15Examine the initialization parameter values for the instance given below:NAME TYPE VALUE------------------------------------ ----------- ------------------optimizer_capture_sql_plan_baselines boolean FALSEoptimizer_dynamic_sampling integer 2optimizer_features_enable string 11.1.0.6optimizer_index_caching integer 0optimizer_index_cost_adj integer 100optimizer_mode string ALL_ROWSdb_file_multiblock_read_count integer 64

You notice that the one of the queries is using a full table scan (view Exhibit1) instead of index unique scan(view Exhibit2). The index is present on the column that is accessed in the WHERE clause of the query. Thecost for a full table scan is more than that for an index unique scan.

Why would the optimizer choose full table scan over index unique scan? (Choose all that apply.)



A. The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to a low value.B. The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to a high value.C. The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a low value.D. The statistics for the table and all the indexes associated with the table are not current.

Correct Answer: BDSection: (none)Explanation


QUESTION 16The columns CUST_CITY, CUST_STATE_PROVINCE, and COUNTRY_ID are frequently used together in theWHERE clause of the queries. The CUSTOMERS table is a big table with 20 GB of data. You notice that theselectivity for these three columns varies from the selectivity that the optimizer calculates.

What would you recommend to influence the selectivity calculated by the optimizer?

A. creating function-based indexes by concatenating all the columnsB. updating histogram statistics for these columns by using the DBMS_STATS.GATHER_TABLE_STATS

procedureC. using the DBMS_STATS.CREATE_EXTENDED_STATS function to create a virtual column and create

index on the virtual columnD. using the DBMS_STATS.CREATE_EXTENDED_STATS function to create a virtual column and the

DBMS_STATS.GATHER_TABLE_STATS procedure to collect statistics on the virtual column



QUESTION 17View Exhibit1 to examine the description of the CUSTOMERS table.

You observed that optimizer selectivity is not accurate when the CUST_STATE_PROVINCE andCOUNTRY_ID columns are used together in the WHERE clause of a query. View Exhibit2 to examine thequery execution plan and the commands executed to gather the statistics.

The optimizer predicts that 20 rows will be processed rather than the 3,341 rows, which is the actual number ofrows returned from the table.

What can you do to make the optimizer detect the actual number of rows?



A. Set the STATISTICS_LEVEL parameter to ALL.B. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE.C. Create extended statistics for the CUST_STATE_PROVINCE and COUNTRY_ID columns.D. Increase the STALE_PERCENT value for the CUSTOMERS table by using the

DBMS_STATS.SET_TABLE_PREFS procedure.



QUESTION 18View Exhibit1 to examine the description of the CUSTOMERS table.

The CUSTOMERS table has been updated heavily today. In a frequently used SQL statement, you notice thatestimated rows and the actual number of rows fetched differ greatly. The COUNTRY_ID column has an index.View Exhibit2 and examine the query execution plan.

What would you recommend to improve the optimizer's estimation?



A. setting the STATISTICS_LEVEL parameter to ALLB. setting the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSEC. creating extended statistics for the CUST_LAST_NAME, CUST_ID, and CUST_TOTAL columnsD. updating the statistics for the CUSTOMERS table by using the DBMS_STATS.GATHER_TABLE_STATS

procedure



QUESTION 19You are working on a decision support system (DSS). The index is available on the COUNTRY_ID column ofthe CUSTOMERS table.View the Exhibit and examine the parameter settings and the query execution plan.

Why is the query using a full table scan instead of an index scan?

Exhibit:

A. because the histogram statistics for the COUNTRY_ID column are not updatedB. because the index statistics for the index on the COUNTRY_ID column are not currentC. because the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a higher valueD. because the optimizer predicts that most of the blocks in the table are accessed.

Therefore, it uses a full table scan, even though indexes are available.



QUESTION 20View Exhibit1 and examine the indexes on the CUSTOMERS table.

The statistics for the CUSTOMERS table have been updated recently by using the following command:SQL> EXECDBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',method_opt=>'FOR ALL INDEXED COLUMNSSIZE AUTO');

View Exhibit2 to examine a query plan. Even though the index is present on the COUNTRY_ID andCUST_GENDER columns, the query uses a full table scan.

What could be the reason?


Exibit 2 (exhibit):

A. because the histogram statistics for the COUNTRY_ID column are not updatedB. because the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a high valueC. because the optimizer calculates the cost of accessing blocks by using a full table scan to be less as

compared to index scans, even though indexes are availableD. because indexes on CUST_GENDER and COUNTRY_ID columns are of different types, the index on the

CUST_GENDER column is bitmap index, and on COUNTRY_ID columns is btree index.



QUESTION 21You are working on an online transaction processing (OLTP) system. The CUST table in the SH schema waspopulated by direct load and after that it has gone through a lot of updates and deletions. The statistics for theCUST and SALES tables were updated recently.View the Exhibit and examine the query plan.The query isperforming a lot of I/O for a query that fetches only 168 rows. To investigate further, you queried theALL_TABLES view to find out PCTUSED, PCTFREE, and the number of rows in the CUST table, as given

below:

SQL> SELECT table_name ,blocks, pct_used, pct_free, num_rows FROM all_tablesWHERE table_name = 'CUST';

TABLE_NAME BLOCKS PCT_USED PCT_FREE NUM_ROWS ---------- ---------- ---------- ---------- ---------- CUST 13768 80 111060

What would you recommend to reduce the I/O?

Exhibit:

A. reorganizing the table to use fewer blocksB. rebuilding the index on the CUST_FIRST_NAME columnC. increasing the value for the PCTFREE attribute for the CUST tableD. increasing the value for the PGA_AGGREGATE_TARGET initialization parameter



QUESTION 22Your 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 aboutthe elapsed time that the SQL statements will have in the new system due to the version change of theoptimizer.

Which tool provides a comparative report with respect to the elapsed time in both versions of the optimizer?

A. SQL Access AdvisorB. SQL Tuning Advisor (STA)C. SQL Performance Analyzer (SPA)D. Automatic Workload Repository (AWR) compare period report



QUESTION 23Which three statements are true about performance analysis by SQL Performance Analyzer? (Choose three.)

A. It detects changes in SQL execution plans.B. It shows only the overall impact on workload.C. It produces results that can be used to create the SQL plan baseline.D. It generates recommendations to run SQL Tuning Advisor to tune regressed SQL statements.E. The importance of SQL statements to be analyzed is based on the size of the objects accessed.

Correct Answer: ACDSection: (none)Explanation


QUESTION 24The database application developers are planning to make some major schema changes such as creating newindexes and materialized views. They want to check the net impact of these changes on the workloadperformance. This activity has to be performed in the production database, so they want only the query part ofthe data manipulation language (DML) statements to be executed so that the side effects to the database oruser data can be prevented.

What should they use to achieve this?

A. Database ReplayB. SQL Tuning AdvisorC. SQL Access AdvisorD. SQL Performance Analyzer



QUESTION 25

You upgraded database from the Oracle Database 10g to Oracle Database 11g. To test the performance SQLon the upgraded database. You want to build the before-change performance data by using SQL PerformanceAnalyzer.

Which method would allow the task to execute quickly?

A. the EXPLAIN PLAN methodB. the TEST EXECUTE methodC. the COMPARE PERFORMANCE methodD. the OPTIMIZER_USE_SQL_PLAN_BASELINE parameter set to TRUE



QUESTION 26Examine 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.)

A. It generates execution plans and execution statistics.B. It executes only the query part of the DML statements.C. The test plans are generated and added to plan baseline.D. The test plans are executed, but not generated if they are available in the plan baseline.



QUESTION 27You plan to collect statistics for an important table in your database to help improved query performance. Youare uncertain about the optimizer behavior after the statistics have been collected because currently there areno statistics for indexes or partitions on the table. You wish to test the performance effect of the newly collectedstatistics.

How would you prevent the optimizer from using the newly collected statistics for other sessions immediatelyafter statistics collection?

A. Set the PUBLISH preference to FALSE for the table.B. Set the NO_VALIDATE preference to TRUE for the table.C. Set the OPTIMIZER_DYNAMIC_SAMPLING parameter to 0.D. Set the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter to FALSE for the session.E. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE for the session.

Correct Answer: A

Section: (none)Explanation


QUESTION 28You have the automatic optimizer statistics gathering enabled for a 24x7 database as part of the automaticmaintenance task. You observed that a few tables are being updated regularly, but not being queried often.However statistics are being gathered for these tables automatically and regularly, thereby affecting thedatabase performance.

What would you do to increase the interval of statistics collection for these tables so that the statistics collectionjobs spend less time during the automatic maintenance task?

A. Set the OPTIMIZER_USE_PENDING parameter statistics to TRUE.B. Increase the value of the STALE_PERCENT preference for these tables.C. Mark those associated indexes that are less frequently used as invisible.D. Increase the value of the ESTIMATE_PERCENT preference for these tables



QUESTION 29You have the automatic optimizer statistics gathering enabled for a 24x7 database as part of the automaticmaintenance task. Because the automatic optimizer statistics collection runs during maintenance window(in thenight), the statistics on tables that are significantly modified throughout the day becomes stale. This leads topoor query performance on these tables and you want to minimize the overhead in statistics collection.

Identify a solution to this problem.

A. Change the STALE_PERCENT preference to zero for the tables.B. Set the OPTIMIZER_USE_PENDING parameter statistics to TRUE.C. Set the OPTIMIZER_DYNAMIC_SAMPLING parameter to zero during day time.D. Gather statistics on the table when the tables have a representative number of rows, and then lock the

statistics.



QUESTION 30You observe that suboptimal execution plans for the queries are being generated on a table that previouslyused less resources. You have collected statistics on these tables two days ago. The optimizer statisticsretention period is set to 31 days. You are able to find the timestamp information about statistics update fromthe DBA_TAB_STATS_HISTORY view. Because it is a frequently queried table, you would like the optimizer togenerate better plans.

Which action would enable you to use the previous set of statistics on the objects that may lead to betterexecution plans?

A. restoring statistics from statistics history up to the desired timeB. deleting all AWR snapshots collected after the time of desired statistics collectionC. applying the flashback table technique until the time of desired statistics collectionD. setting the OPTIMIZER_PENDING_STATISTICS parameter to TRUE to use the previous version of

statistics



QUESTION 31The SALES table in your database undergoes frequent changes. While observing the statistics for the table inthe middle of the day, you find a change since the last observation. But the information about the statisticscollection is not recorded in the DBA_TAB_STATS_HISTORY.

Identify the reason for this.

A. The AWR retention time was set to zero.B. The STATISTICS_LEVEL parameter was set to BASIC.C. Some of the statistics were collected using the ANALYZE command.D. There were user-defined statistics that were set by using the DBMS_STATS.SET_TABLE_STATS

procedure.



QUESTION 32You have a range-partitioned table in your database. Each partition in the table contains the sales data for aquarter. The partition related to the current quarter is modified frequently, whereas other partitions undergofewer data manipulations. The preferences for the table are set to their default values. You collect statistics forthe table using the following command at regular intervals:SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',GRANULARITY=>'AUTO');

You need statistics to be collected more quickly. How do you achieve this?

A. Set the PUBLISH preference to TRUE for the partition table.B. Set the NO_VALIDATE preference to TRUE for the partition table.C. Set the INCREMENTAL preference to TRUE for the partition table.D. Increase the value of the STALE_PERCENT preference for the partition table.



QUESTION 33

Which is the correct description of SQL profiling while using SQL Tuning Advisor?

A. It is a set of recommendations by the optimizer to create new indexes.B. It is auxiliary information collected by the optimizer for a SQL statement to eliminate estimation error.C. It is a set of recommendations by the optimizer to refresh stale statistics to avoid bad execution plan.D. It is a set of recommendations by the optimizer to restructure a SQL statement to avoid bad execution plan.



QUESTION 34You used SQL Tuning Advisor for a long-running SQL statement that suggested a SQL profile which can beused by the query subsequently for a better execution plan. After implementing the profile, you altered theprofile to include it in a category as follows:

SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ( name=> 'SYS_SQLPROF_0146ced728214000',attribute_name => 'CATEGORY', value => 'DEV2').

But the performance has not improved for the user session. The profile is enabled and on investigating thequery plan, you find that the query from the user session does not use the SQL profile. View the Exhibit andexamine the parameters for the user session.

What could be the reason for this?

Exhibit:

A. The CONTROL_MANAGEMENT_PACK_ACCESS parameter is not set to DEV2.B. The OPTIMIZER_MODE parameter is set to ALL_ROWS for the user session.C. The SQLTUNE_CATEGORY parameter is set to DEFAULT for the user session.D. The OPTIMIZER_USE_PLAN_BASELINES parameter is set to TRUE for the user session.



QUESTION 35You used SQL Tuning Advisor for a long-running SQL statement that suggested a SQL profile which can beused by the query subsequently for better execution plan. View the Exhibit. But you want certain user sessionsnot to use this SQL profile for their queries.

How would you implement this?

Exhibit:

A. Alter the SQL profile to change the category of the SQL profile.B. Set the OPTIMIZER_USE_PENDING_STATISTICS to TRUE the desired sessions.C. Use database resource manager to prevent the use of the SQL profile by these user sessions.D. Use database resource manager to preUse the resource management feature in profiles of these users to

prevent the use of the SQL profile.



QUESTION 36View the Exhibit and examine the result of Automatic SQL Tuning for a period. None of the recommended SQLprofiles are implemented.

What would you do to allow the Automatic SQL Tuning implement the SQL profiles automatically?

Exhibit:

A. Set the OPTIMIZER_MODE parameter to ALL_ROWS for the database instance.B. Set the SQLTUNE_CATEGORY parameter to DEFAULT for the database instance.C. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE for the database instance.D. Use the DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETERS procedure to set

ACCEPT_SQL_PROFILES to TRUE.



QUESTION 37You receive a recommendation for a SQL statement through the automatic SQL tuning process that suggestsimplementing a SQL profile. You implement the SQL profile. The tables associated with the SQL statementgrow, and indexes are created and dropped on these tables.

What would happen to the SQL profile that is created for the SQL statement?

A. It becomes outdated immediately after the tables change.B. It is maintained automatically along with the changes in the tables.C. The profile will be used for the changed plans of the SQL statement.D. The plan for the SQL statement is frozen until it is regenerated by the SQL Tuning Advisor.



QUESTION 38Identify the type of recommendation that can automatically be implemented by the Automatic Tuning Advisor aspart of automatic SQL tuning task?

A. recommendation for SQL profilesB. recommendation about effective indexingC. recommendation about stale or no statisticsD. recommendation about the structure of SQL statements



QUESTION 39A user session executes a query and the plan for the query is shown in the Exhibit as Plan-1. After verifyingwith the SQL Access advisor, an index is created on the table in the JOB_ID column and the query is executedagain. A new plan is generated against the second query shown in the Exhibit as Plan-2. When explaining theplan for the second query, you observe that the optimizer uses the first plan instead of the second. Thefollowing parameters are set for the user session:

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUEOPTIMIZER_USE_SQL_PLAN_BASELINES=TRUESQLTUNE_CATEGORY=DEFAULT

Why does the optimizer not use the second plan?

Exhibit:

A. because the second plan is not verifiedB. because the second plan is not part of the SQL plan baselineC. because the SQLTUNE_CATEGORY parameter is set to DEFAULTD. because the explain plan option in the user session does not allow the new plan because

SQLTUNE_CATEGORY parameter is set to DEFAULT



QUESTION 40View the Exhibit and examine the plans in the SQL plan baseline.You observe that the last two SELECTstatements (outlined in red and blue) are identical, but have two different plans. Also, the second plan (in blue)is not being used by the optimizer.

Which two steps do you perform to ensure that the optimizer uses the second plan (in blue) if the plan is moreefficient? (Choose two.)

Exhibit:

A. Mark the second plan (in blue) as fixed.B. Use SQL Tuning Advisor to tune the plan.C. Use SQL Access Advisor to tune the plan.D. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to evolve the plan.



QUESTION 41Identify the two direct sources from where SQL plans can be loaded into the SQL plan baselines. (Choose two.)

A. Cursor cacheB. Stored outlineC. SQL Tuning SetD. Automatic Workload Repository (AWR) snapshots

Correct Answer: ACSection: (none)Explanation


QUESTION 42Your system is upgraded from Oracle Database 10g to Oracle Database 11g. You imported SQL Tuning Sets(STS) from the previous version. After running the SQL Performance Analyzer for optimizer version change,you observe performance regression for a few SQL statements.

What would you suggest to avoid performance regression for these SQL statements?

A. Include the old plans in the SQL plan baseline.B. Use DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE to evolve plans.C. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to TRUE.D. Increase the value for the OPTIMIZER_DYNAMIC_SAMPLING parameter.



QUESTION 43The OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter is set to TRUE for a 24x7 databaseinstance. The space consumption has significantly increased by the automatically captured plan baselines. Youare 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.)

A. increasing the AWR retention periodB. purging the older SQL management objectsC. increasing the size of the SYSTEM tablespaceD. increasing the size of the SYSAUX tablespace



QUESTION 44Your company wants to migrate a single instance Oracle 11g Release 1 database to a RAC environment. Thedatabase supports middle-tier applications using connection pooling for connecting to database.Being a part of the performance management team, you want to test the performance of the SQL statementsand the peak workload of the applications in the new environment before upgrading the production system.Given below are some of the steps in random order to accomplish the task:

1) Capture the real workload on the existing system.2) Create a SQL Tuning Set (STS) for the statements executed by the application.3) Transport STS on the test environment and use SQL Tuning Advisor to compare the before and afterperformance.4) Replay the workload on the test environment.5) Analyze the divergence after replay.6) Use Automatic Workload Repository (AWR) to capture the information for top SQL on the existing system.7) Transport STS on the test environment and use SQL Performance Analyzer to compare the before and afterperformance.

Identify the correct steps required to perform the test.

A. 2 and 3 onlyB. 6 and 7 onlyC. 1, 4, and 5 onlyD. 2, 3, and 5 onlyE. 2, 3, 4, and 5 only



QUESTION 45You are working on an online transaction processing (OLTP) system. The middle-tier applications useconnection pooling to connect to the database. Presently, you have a single-node database. The companyplans to migrate the database to a RAC environment. Before you move to a RAC environment, you want to testthe performance of the SQL statements and the peak workload on the new environment. To accomplish theDatabase Capture for replay, you identified the peak workload period on the existing system and started theDatabase Capture.

Which client requests to the database can be captured as part of the workload capture? (Choose all that apply.)

A. flashback queryB. distributed transactionsC. logging in and logging out of sessionsD. all DDL statements having bind variablesE. direct path load of data from external files



QUESTION 46You are a DBA in ABC Corp. You are working on a DSS system.. The applications directly connect to thedatabase to perform transactions. The application users also perform direct loads. The company wants toupgrade from Oracle Database 10g version 10.2.0.1 to Oracle Database 11g. You want to test the performanceof the SQL statements in the new environment before upgrading the production database. To accomplish this,you set up the test database with Oracle Database 11g, with the same data and schemas as in production.

What would you recommend to accomplish the task?

A. gathering all the SQL statements and using SQL Tuning Advisor on the test database for performanceanalysis

B. capturing the workload on the production database, replaying it on the test machine, and comparing theperformance

C. creating a SQL Tuning Set by capturing the SQL workload on the production database and using SQLPerformance Analyzer for performance analysis

D. configuring AWR to capture the maximum number of SQL statements, transporting it to the test system,and using SQL Access Advisor for performance analysis



QUESTION 47You are a DBA in ABC Corp. You are working on an online transaction processing (OLTP) system.Theapplications running on the database use connection pooling to connect to the database to performtransactions. The company wants to upgrade the CPU and the memory for the machine on which the databaseis running. To test the realistic workload on the new machine with increased CPU and memory, you identifiedthe peak workload time for the database and captured the workload on the production system.You set up the test machine with the upgraded CPU and memory and installed the database with the same

version as production.

Which three actions would you perform to accomplish the realistic replay of the peak workload on the testmachine? (Choose three.)

A. Preprocess the captured workload for the specified target database version.B. Set up as many replay clients as there are actual clients to the production system.C. Set up all the replay clients on the same machine on which the test database is running.D. Resolve all external references such as database links, external tables, and directory objects.E. Restore the replay database to match the capture database at the start of the workload capture.

Correct Answer: ADESection: (none)Explanation


QUESTION 48You are a DBA in ABC Corp. You are working on an online transaction processing (OLTP) system.Theapplications running on the database use connection pooling to connect to the database to performtransactions. The company wants to upgrade the CPU and the memory for the machine on which theproduction database is running. To test the realistic workload on the new machine with increased CPU andmemory, you identified the peak workload time for the database and captured the workload on the productionsystem. You set up the test machine with the upgraded CPU and memory, and performed the followingoperations for replay:

1) You preprocessed the captured workload.2) You restored the replay database to match the capture database at the start of the workload capture.3) You resolved all external references.4) You set up replay clients.

You have the following replay considerations:1) load testing to be performed2) all the sessions to connect immediately3) user waits between issuing calls to be kept to a minimum

What replay options would you choose to accomplish the replay?

A. SYNCHRONIZATION = FALSE, THINK_TIME_SCALE = 0,CONNECT_TIME_SCALE = 0 and THINK_TIME_AUTO_CORRECT = FALSE

B. SYNCHRONIZATION = TRUE, THINK_TIME_SCALE = 100,CONNECT_TIME_SCALE = 0 and THINK_TIME_AUTO_CORRECT = FALSE

C. SYNCHRONIZATION = FALSE, THINK_TIME_SCALE = 100, CONNECT_TIME_SCALE = 100 andTHINK_TIME_AUTO_CORRECT = TRUE

D. SYNCHRONIZATION = TRUE, THINK_TIME_SCALE = 100,CONNECT_TIME_SCALE = 100 andTHINK_TIME_AUTO_CORRECT = FALSE



QUESTION 49

You are a DBA in ABC Corp. You are working on an online transaction processing (OLTP) system.Theapplications running on the database use connection pooling to connect to the database to performtransactions. The company wants to upgrade the CPU and the memory for machine on which the productiondatabase is running. To test the realistic workload on the new machine with increased CPU and memory, youidentified the peak workload time for the database and captured the workload on the production system.You set up the test machine with the upgraded CPU and memory, and performed the following operations forreplay:

1) You preprocessed the captured workload.2) You restored the replay database to match the capture database at the start of the workload capture.3) You resolved all external references.4) You set up replay clients.

You have the following replay considerations:

1) minimal data divergence to be ensured2) all sessions to connect immediately.3) user waits between issuing calls to be kept to a minimum

What replay options would you choose to accomplish the replay?

A. SYNCHRONIZATION = TRUE, THINK_TIME_SCALE = 0,CONNECT_TIME_SCALE = 0 andTHINK_TIME_AUTO_CORRECT = TRUE

B. SYNCHRONIZATION = TRUE, THINK_TIME_SCALE = 0,CONNECT_TIME_SCALE = 100 andTHINK_TIME_AUTO_CORRECT = TRUE

C. SYNCHRONIZATION = TRUE, THINK_TIME_SCALE = 100,CONNECT_TIME_SCALE = 0 andTHINK_TIME_AUTO_CORRECT = FALSE

D. SYNCHRONIZATION = TRUE, THINK_TIME_SCALE = 100,CONNECT_TIME_SCALE = 100 andTHINK_TIME_AUTO_CORRECT = TRUE



QUESTION 50View the Exhibit and examine the output for the WRC command-line utility that you executed to estimate thenumber of replay clients.

Identify three prerequisites for completing the replay. (Choose three.)

Exhibit:

A. The replay database should be in the RESTRICTED mode.B. You need to start at least 21 replay clients on six CPUs to start the replay.C. The replay directory must have all the replay files that have been preprocessed.D. The user should be able to use the workload replay package and should have the DBA role.E. The replay options for think_time_scale and connect_time_scale must be set to 100 each.

Correct Answer: BCDSection: (none)Explanation


QUESTION 51Examine 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.pid3 FROM v$latch a, v$latchholder b, v$latchname c4 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.)

A. reducing hard parsesB. increasing the size of the shared poolC. using only dedicated server connectionsD. setting the CURSOR_SHARING parameter to EXACT

E. encouraging the use of more literal SQL statements




SQL>; SELECT c.name,a.addr,a.gets,a.misses,a.sleeps,2 a.immediate_gets,a.immediate_misses,b.pid3 FROM v$latch a, v$latchholder b, v$latchname c4 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.)

A. The latch was requested in no wait mode.B. The latch was requested in willing-to-wait mode.C. The shared pool operations to allocate and free memory in it caused the misses.D. Automatic Memory Management is not enabled because the IMMEDIATE_GETS and

IMMEDIATE_MISSES columns have zero in them.



QUESTION 53Examine the output of the query given below:

SQL> SELECT mutex_type, location, sum(gets), sum(sleeps)FROM v$mutex_sleep_historyGROUP BY mutex_type, location;

MUTEX_TYPE LOCATION SUM(GETS) SUM(SLEEPS)-------------- ------------------------------------- ---------- -----------Library Cache kglhdgn1 62 8669586 4538Library Cache kglget2 2 2016618 24Cursor Stat kkocsStoreBindAwareStats [KKSSTALOC8] 2975 1Cursor Pin kkslce [KKSCHLPIN2] 666831 678Library Cache kgllkdl1 85 3369224 110Library Cache kglpnal1 90 224199 13Library Cache kglic1 49 42068 10Library Cache kglpin1 4 9620087 374Library Cache kglpndl1 95 2065089 799 rows selected.

Which statement is true?

A. Each row in the output represents a SQL statement that had to wait for mutexes.B. The Cursor Stat and Cursor Pin SLEEPS indicate that the CURSOR_SHARING parameter is set to EXACT.C. The GETS column shows the number of times a mutex/location was requested by the requesting session

while being held by the blocking session.D. The sum of numbers in the GETS and SLEEPS columns indicates the number of times a mutex/location

was requested by the requesting session while being held by the blocking session.



QUESTION 54Examine the query output shown below:

SQL> SELECT sum(pins), sum(reloads), sum(reloads)/sum(pins)FROM v$librarycache;

SUM(PINS) SUM(RELOADS) SUM(RELOADS)/SUM(PINS)---------- ------------ ----------------------16479 16000 0.9709327

Which statement describes the correct interpretation of the reloads-to-pin ratio and the action to be taken?

A. This ratio is reasonable. However, decrease the shared pool size for better performance.B. This ratio is very high and the shared pool size should be increased immediately to reduce this ratio.C. This ratio is reasonable. However, this needs monitoring. Increase the shared pool size only if the ratio

crosses 1.D. This ratio is reasonable. However, this needs monitoring. Increase the shared pool size only if the ratio falls

below 0.1.



QUESTION 55You work as a DBA for a company and manage an online transaction processing (OLTP) system. You receivedcomplaints about the performance degradation of SQL statements executed by the application that uses thisdatabase.View the Exhibit and examine the output of the queries executed to investigate the performanceissues.

Which two actions can improve performance? (Choose two.)

Exhibit:

A. increasing the size of the shared poolB. setting the CURSOR_SHARING parameter to EXACTC. decreasing the value set for the OPEN_CURSORS parameterD. replacing literals with bind variables in SQL statements with the PLAN_HASH_VALUE 1337874392E. replacing literals with bind variables in SQL statements with the PLAN_HASH_VALUE 1445457117

Correct Answer: AESection: (none)Explanation


QUESTION 56You work as a consultant DBA for various clients. A performance issue in one of the online transactionprocessing (OLTP) systems is reported to you and you received the Automatic Workload Repository (AWR)report generated in the database. The main sections of the AWR report are shown in the Exhibits.View the Exhibit named DBTIME and note % DB Time.

View the Exhibit named TIMEMODEL and note what has contributed to % DB Time.

View the Exhibit named EFFICIENCY and examine the various percentages shown.

Identify the option that has the correct answers for the questions given below:1) Which factor indicates the main problem?2) What is the main problem?3) What solution would you recommend?




A. 1) The Exhibit DBTIME shows that the DB CPU event consumed very high % DB Time.2) The CPU is very slow.3) Increase the number of processors.

B. 1) hard parse elapsed time has the majority time in parse time elapsed, which is shown in the TIMEMODEL

Exhibit.2) This is due to inadequate Database Buffer Cache.3) Increase the size of database buffer cache.

C. 1) sql execute elapsed time and parse time elapsed are consuming very high % DB Time in theTIMEMODEL Exhibit.2) There are too many soft parses.3) Investigate the HOLD_CURSOR parameter setting in the application and set it appropriately.

D. 1) % Non-Parse CPU is low and Soft Parse % is also low in the EFFICIENCY Exhibit. This shows that veryfew statements are found in the cache.2) There are too many hard parses.3) Investigate the CURSOR_SHARING parameter setting and set it appropriately.



QUESTION 57You work as a consultant DBA for various clients. A performance issue in one of the online transactionprocessing (OLTP) systems is reported to you and you received the Automatic Workload Repository (AWR)report generated in the database. The main sections of the AWR report is shown in the Exhibits.View the Exhibit named DBTIME and note % DB Time.

View the Exhibit named TIMEMODEL and note what has contributed to % DB Time.

View the Exhibit named EFFICIENCY and examine the various percentages shown.

Which option describes the correct understanding of the main problem?




A. The Exhibit DBTIME shows that the DB CPU event consumed very high % DB Time, which indicates thatthe CPU is very slow.

B. sql execute elapsed time and parse time elapsed are consuming very high % DB Time in the TIMEMODELExhibit. This indicates too many soft parses.

C. hard parse elapsed time has the majority time in parse time elapsed, which is shown in the TIMEMODELExhibit. This indicates inadequate database buffer cache.

D. % Non-Parse CPU: and Soft Parse %: are low in the EFFICIENCY Exhibit, which shows that very fewstatements are found in the cache. This indicates that there were too many hard parses.



QUESTION 58You work as a DBA for a company and you have the responsibility of managing one of its online transactionprocessing (OLTP) systems. The database encountered performance-related problems and you generated anAutomatic Workload Repository (AWR) report to investigate it further.

View the Exhibits and examine the AWR report.

Which is the appropriate solution to the problem in this database?




A. increasing the size of the shared poolB. adding one more CPU to the systemC. setting the CURSOR_SHARING parameter to EXACTD. configuring Java pool because it is not configured



QUESTION 59You work on an online transaction processing (OLTP) database in which the SALES table has 10,000 rows butonly four distinct products are sold. View the Exhibit named HIST to check the distribution of values in the tableand the histograms on the table.

View the Exhibits named QUERY-1 and QUERY-2 that show details in the V$SQL view for the queriesexecuted on the SALES table.

You check the plan table and notice that both the queries that executed on the SALES table used index rangescan. The second query retrieved most of the rows in the table but used index range scan.

Why would the second query use the same plan?




A. because the plan was bind awareB. because the bind peeking never happenedC. because the OPEN_CURSORS parameter is set to a very low valueD. because the optimizer did not consider selectivity due to the use of bind variables



QUESTION 60You work on an online transaction processing (OLTP) database in which the SALES table has 10,000 rows butonly four distinct products are sold. View the Exhibit named HIST to check data distribution in the table and thehistograms on the table.

View the Exhibit named QUERY-1 that shows details in the V$SQL view for the query executed on the SALEStable having product id 1.

Further, you query the SALES table thrice more in the following order:- query rows having product ID 4

- query rows having product ID 2- query rows having product ID 4

View the Exhibit named QUERY-n to see the details about these queries.

What do you infer from this?




A. The second plan was created because the first plan was aged out.B. The CURSOR_SHARING parameter value was changed to EXACT after the second query was executed.C. The third plan was created because the first plan was aged out and the second plan had different selectivity.D. The first two executions used the same plan because at parse time the optimizer did not consider selectivity

as the cursor was not yet considered bind-aware.



QUESTION 61You are working for a company that uses huge applications to manage its customer details. During your regularperformance checks, you executed the following query:

SQL> show parameter shared_pool_reserved_sizeNAME TYPE VALUE------------------------------ --------------------- -----------------shared_pool_reserved_size big integer 6920601

SQL> SELECT free_space, used_space, request_misses, request_failures fromV$SHARED_POOL_RESERVED;

FREE_SPACE USED_SPACE REQUEST_MISSES REQUEST_FAILURES ---------- ---------- -------------- ---------------- 6469776 555528 0 113

You observed that REQUEST_FAILURES is 113 but not increasing, and also that REQUEST_MISSES is notincreasing.

What would you interpret from this?

A. The reserved pool is very large. Consider reducing the size of the shared pool.B. The reserved pool is large and has free space to satisfy requests. Consider reducing the size of the

reserved pool.C. The reserved pool is very small and is not finding enough memory to satisfy requests.

Increase the size of the shared pool.D. The reserved pool is very small and is not finding enough memory to satisfy requests.

Increase the size of the reserved pool.



QUESTION 62You work for a company as a DBA. The company has an application to manage the details of its business andcustomer base. However, application users complain that updating the SALES record takes more time than itused to earlier. On investigation, you notice that the application performance degrades when a call is made tothe UPDATE_INV procedure. Further, you execute the following query to investigate:

SQL> SELECT name, type, sharable_mem, kept2 FROM v$db_object_cache3 WHERE sharable_mem > 40004 AND EXECUTIONS > 55 AND (type='FUNCTION' OR type='PROCEDURE')6 /

NAME TYPE SHARABLE_MEM KEPT------------------- -------------- --------------- -----------GETEMKEY FUNCTION 13695 YESUPDATE_INV PROCEDURE 14766 NOSETEMUSERCONTEXT PROCEDURE 13703 YESDECRYPT FUNCTION 17790 YESUPDATE_DEPT PROCEDURE 18765 NO

What would you do to improve the performance?

A. Enable the result cache if not already enabled.B. Flush the shared pool to make space for this procedure.C. Keep the UPDATE_INV procedure by using the DBMS_SHARED_POOL.KEEP procedure.D. Increase the size of the keep buffer pool to accommodate the UPDATE_INV procedure.E. Ask the developers to modify and use literals instead of bind variables in the UPDATE_INV procedure.



QUESTION 63You work for a small manufacturing company as a DBA. The company has various applications to manage thedetails of its business and customer base. The company has only one sales outlet where the operator updatesthe SALES table in the sales application with the details when a product is sold. The company has many otherapplications for various other businesses that use the same database. The sales operator, while generating areport, noticed that there are some gaps generated in the serial number in the SLNO column during peak hoursof business. On investigating, you find that the SLNO column in the SALES table uses the SLNSEQ sequenceto 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 youchecked 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?

A. The reserved pool is not configured. Configure the reserved pool.B. The keep buffer pool is not configured. Configure the keep buffer pool.C. The sequence was created with a CACHE of 100 numbers. Re-create the sequence with a higher CACHE

number.D. The sequence is aging out of the shared pool. Keep the sequence in the shared pool using the

DBMS_SHARED_POOL.KEEP procedure.



QUESTION 64You work as a DBA for a company and as a performance improvement measure, you implemented the resultcache in your database. Many users in the company say that performance has improved on the queries theyuse, but some users complain that they have not got any performance benefit on the queries they use. Youchecked all the queries they use and the following is one of them:

SQL> SELECT /*+ RESULT_CACHE */ slnoq.currval as "SLNO", prod_id, pdname, cust_nameFROM sales WHERE sl_date < sysdate;

View the Exhibit and examine the testing performed to check this.

Why is the result cache not used? (Choose all that apply.)

Exhibit:

A. because the query uses SYSDATEB. because the query uses an alias for a columnC. because the query uses the SLNOQ.CURRVAL sequenceD. because the table might have an index on the SL_DATE column



QUESTION 65You work as a DBA for a company and as a performance improvement measure, you implemented the resultcache in your database.View the Exhibit named SETTING and note the result cache settings.

Many users in the company state that performance has improved on the queries they use but some userscomplain that they have not got any performance benefit on the queries they use.You checked all the queries they use and the following is one of them:

SQL> SELECT slnoq.currval as "SLNO", prod_id, pdname,2 cust_name FROM sales WHERE sl_date < sysdate;

View the Exhibit named TEST and examine the testing performed to check this.

Why is the result cache not used? (Choose all that apply.)



A. because the query uses SYSDATEB. because the query uses an alias for a columnC. because the query uses the SLNOQ.CURRVAL sequenceD. because the CLIENT_RESULT_CACHE_SIZE parameter is set to 0



QUESTION 66In which case is the database buffer cache NOT used?

A. when physical I/Os are performed to read from data filesB. when the database writer process writes to the SYSTEM tablespaceC. when the server process writes sort data to the temporary tablespaceD. when the database writer process writes undo data to the undo tablespace



QUESTION 67Which two statements are true about the database buffer cache? (Choose two.)

A. A buffer can be written to by many processes at the same time.B. A buffer containing a block that is identical to the block on disk is called free buffer.C. A buffer containing a block that is being accessed by a process is called dirty buffer.D. A buffer available to be overwritten with a different database block at any time is called a dirty buffer.E. A buffer can contain a different version of a block that is available in a different buffer of the same cache.

Correct Answer: BESection: (none)Explanation


QUESTION 68You 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"3 FROM v$sysstat cur, v$sysstat con, v$sysstat phy, v$sysstat phyd4 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

Which understanding of this ratio is correct?

A. 98.43% of times the requests have found free buffers.B. 98.43% of the total number of requests used the buffer cache.C. 98.43% of times the requests were satisfied by performing physical I/Os.D. 98.43% of times the requests have found the required data blocks in the buffer cache.



QUESTION 69You 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 phyd4 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?

A. The database performance is very good because of reduced logical I/Os.B. The database performance cannot be determined based only on this ratio.C. Good database performance is guaranteed because very few physical I/Os are performed.D. The database performance is very good because most of the requested data blocks are found in the buffer

cache.



QUESTION 70You work as a DBA and have the responsibility of managing a large online transaction processing (OLTP)system. You used three queries to check the database performance as shown in the Exhibit.View the Exhibitand analyze the output.

What conclusion can you draw from this?

Exhibit:

A. There are many physical I/Os happening.B. There are many full table scans happening.C. The data blocks are aging out of the buffer cache very fast.D. There are many cursors trying to access the same data blocks.E. The DBWn processes are not freeing sufficient buffers to meet the demand.



QUESTION 71Examine the following information from the AWR report:

Top 5 Timed Events~~~~~~~~~~~~~~~~~~ % TotalEvent Waits Time (s) Call Time-------------------------------------- ------------ ----------- ---------CPU time 559 88.80log file parallel write 2.181 28 4.42SQL*Net more data from client 516.611 27 4.24db file parallel write 13.383 13 2.04db file sequential read 563 2 .27

Which could cause waits on the db file sequential read event?

A. too many full table scans

B. too large a database buffer cacheC. too many waits occurring while reading multiple blocks synchronouslyD. too many index range scans occurring which refers to blocks not in the database buffer cache



QUESTION 72Which two symptoms related to the database buffer cache together indicate that there are many full table scanshappening? (Choose two.)

A. too many buffer busy waitsB. very low buffer cache-hit ratioC. very high buffer cache-hit ratioD. many waits on the db file scattered read event



QUESTION 73What indicates that the rate of writing data blocks to the disk is very slow?

A. very high cache-hit ratioB. increasing number of free buffer waitsC. increasing number of buffer busy waitsD. waits on latch: cache buffers chains event



QUESTION 74You work as a database administrator to manage an online transaction processing (OLTP) system.Theapplication code was recently tuned for optimum performance and users acknowledged the performanceimprovement. However, the users are now again complaining that the application response time has gonedown considerably. You used a few queries to investigate the problem and a couple of important queries areshown in the Exhibit. View the Exhibit and analyze the output shown.

Which actions would improve the performance? (Choose all that apply.)

Exhibit:

A. increasing the number database writersB. decreasing the number of database writersC. increasing the size of the database buffer cacheD. decreasing the size of the database buffer cacheE. decreasing the value set to the FAST_START_MTTR_TARGET parameter



QUESTION 75You are working on an online transaction processing (OLTP) system. You are investigating the reasons forperformance degradation in the database. View the Exhibit named TOP5EVENTS and note the top five events.

View the Exhibit named TOPSQL and note the problems related to these statements.

View the Exhibit named INSTACT and note the table scans.

Which is the most appropriate solution?




A. Create indexes on short tables.B. Use the CACHE hint to access long tables.

C. Keep the long tables in the keep buffer pool.D. Keep the short tables in the keep buffer pool.E. Keep the short tables in the recycle buffer pool.



QUESTION 76You are working on an online transaction processing (OLTP) system. You are investigating the reasons forperformance degradation in the database. View the Exhibit named TOP5EVENTS and note the top five events.

View the Exhibit named TOPSQL and note the problems related to these statements.

View the Exhibit named INSTACT and note the table scans.

Which two are appropriate solutions to improve performance? (Choose two.)




A. Enable the large pool.B. Keep the long tables in the keep buffer pool.C. Keep the short tables in the keep buffer pool.D. Set the CACHE property on the table for the long tables.E. Set the CACHE property on the table for the short tables.

Correct Answer: CESection: (none)Explanation


QUESTION 77You observed very low cache-hit ratio in your database as shown below:

SQL> SELECT (1-((phy.value-phyd.value) / (cur.value + con.value))) * 1002 "Cache Hit ratio"3 FROM v$sysstat cur, v$sysstat con, v$sysstat phy, v$sysstat phyd4 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---------------68.43

After further investigation, you decided to increase the database buffer cache size. You are using theBuffer Cache Advisor to check the appropriate size for the buffer cache. View the Exhibit and examine thegraph shown by the advisory.

What can you infer from the graph?

Exhibit:

A. The buffer cache size can be set to 12 MB for optimal performance.B. The buffer cache size can be set to more than 16 MB to get more benefit.C. The buffer cache size can be set to more than 16 MB but there would be no benefit from it.D. The buffer cache size cannot be set to more than 16 MB because the SGA_MAX_SIZE value does not

allow that.



QUESTION 78You are working in an online transaction processing (OLTP) environment. You received many complaints from

users about degraded performance. Your senior DBA asked you to execute the following command to improvethe performance:

SQL> ALTER TABLE subscribe_log STORAGE(BUFFER_POOL recycle);

You checked the data in the SUBSCRIBE_LOG table and found that it is a large table having one million rows.Which factor could be a reason for this recommendation?

A. The keep pool is not configured.B. The automatic Program Global Area (PGA) is not configured.C. The CURSOR_SPACE_FOR_TIME initialization parameter is set to FALSE.D. The most of the rows in SUBSCRIBE_LOG table are accessed once a week.E. All the queries on the SUBSCRIBE_LOG table are rewritten using a materialized view.



QUESTION 79View the Exhibit and analyze the content of V$BUFFER_POOL_STATISTICS.

Why does the query on the EMP table not access buffers from the keep pool?

Exhibit:

A. because the query used SCOTT.EMP instead of EMPB. because the query with * always uses the default poolC. because the query with * always forces physical I/Os that bypass the keep poolD. because the blocks of the EMP table are already available in one of the other buffer pools

Correct Answer: D

Section: (none)Explanation


QUESTION 80You queried V$SYSTEM_EVENT in your online transaction processing (OLTP) environment to check thefollowing wait events:

Event Total Seconds Total Average WaitName Waits Waiting Timeouts (in secs)------------------------------ ------------ -------- ------------ ---------direct path read temp 18.274 11.312 16.282 .619direct path write temp 3.251 416 25 .127

Which step would be appropriate to improve the performance?

A. Increase the number of I/O slaves.B. Increase the size of the Database Buffer Cache.C. Increase the size of the Program Global Area (PGA).D. Increase the number of database writer (DBWn) processes.





What do these wait events indicate?

A. The DBWn processes are not creating free buffers fast enough.B. The buffer cache is too small and block replacement is excessive.C. The aggressive checkpointing policy is causing too many writes to disk.D. The work areas are too large to fit in memory and are, therefore, written to disk.



QUESTION 82The users in your online transaction processing (OLTP) environment complain that the query response timehas increased considerably. On investigation, you find that the Program Global Area (PGA) is undersized and

using temporary segments for sort operations. You plan to increase the size of the PGA and there is ampleamount of memory available to you. You used the PGA advisor to check the correct size for PGA. View theExhibit and examine the content of the PGA advisor.

What size of PGA would give the maximum benefit?

Exhibit:

A. 125 MBB. 3009 MBC. 8024 MBD. 1003 MBE. 1204 MBF. 1404 MB



QUESTION 83View the Exhibit and examine the content of the V$PGA_TARGET_ADVICE_HISTOGRAM view.

What would you infer from this?

Exhibit:

A. If the PGA size is set to 4 MB, then all the work areas would execute in optimal mode.B. If the PGA size is doubled, then all the work areas that are less than 2 MB would execute in optimal mode.C. If the PGA size is set to 32 MB, then the number of work areas would reduce. So it is the appropriate size

for PGA.D. If the size of the Program Global Area (PGA) is set to more than 64 MB, then all the work areas would

execute in optimal mode. So it is the appropriate size for PGA.



QUESTION 84The users in your online transaction processing (OLTP) environment complain that the query response timehas increased considerably. You checked the Program Global Area (PGA)- related information in the AWRreport to investigate it further.View the Exhibit named PGAHIT and note the PGA cache-hit percentage.

View the Exhibit named PGAADV and analyze the content.

If you want to increase the size of PGA, you have sufficient memory available in the system. What would be thecorrect decision?



A. Double the size of PGA.B. Increase the size of PGA to 120 MB.C. Increase the size of PGA to 160 MB or more.D. Leave the PGA at its current size and investigate other possible causes.



QUESTION 85The users in your online transaction processing (OLTP) environment complain that the response time of theapplication has increased dramatically.To investigate the problem, view the Exhibit and examine the queriesyou executed.


What do you infer?

Exhibit:

A. Cursor sharing is not enabled.B. The large pool is not configured.C. The temporary tablespace is full.D. The value set for the SHARED_POOL_SIZE initialization parameter is too small.E. The value set for the PGA_AGGREGATE_TARGET initialization parameter is too small.

Correct Answer: ESection: (none)Explanation


QUESTION 86You work in an online transaction processing (OLTP) environment and users are experiencing slow responsetime on their queries. These users connect as FINANCE to run their application and run hundreds of sessions.Your investigation shows that there is lot of I/O contention happening on only one temporary tablespace andthere is no load on other temporary tablespaces. View the Exhibit and examine the details.After some time you observed that sort operations are running out of temporary space.

What would be a long-term solution?

Exhibit:

A. Change the temporary tablespace assigned to this user ID.B. Create and assign temporary tablespace group to this user ID.C. Increase the size of the temporary tablespace assigned to this user ID.D. Make the bigger temporary tablespace the default temporary tablespace ID.



QUESTION 87You work in an online transaction processing (OLTP) environment and users are experiencing slow responsetime on their queries. These users connect as FINANCE to run their application and run hundreds of sessions.Your investigation shows that there is lot of I/O contention happening on only one temporary tablespace andthere is no load on other temporary tablespaces. View the Exhibit and examine the details. After some time youobserved that the sort operations are running out of temporary space.

What would be a long-term solution?

Exhibit:

A. Configure the large pool if it is not done already.B. Change the temporary tablespace assigned to this user ID.C. Create and assign temporary tablespace group to this user ID.D. Increase the number of database writer (DBWn) processes ID.E. Make the bigger temporary tablespace the default temporary tablespace ID.



QUESTION 88View the Exhibit and examine the content. ALLOCATED_SPACE and FREE_SPACE are not matching with theTABLESPACE_SIZE value.

What would you conclude from this?

Exhibit:

A. A new temporary file is added to the tablespace.B. The temporary space was not sufficient for sort operations.C. The temporary space allocated to large sort operations is not deallocated.D. TEMP_L is a default temporary tablespace because the numbers show some overhead on this tablespace.



QUESTION 89View the Exhibit and examine the content.

ALLOCATED_SPACE and FREE_SPACE indicate that a large amount of space is allocated to the temporarytablespace that is being used.

Which action would reclaim the space and refresh these values?

Exhibit:

A. shrinking the tablespaceB. closing all the current sessionsC. restarting the database instanceD. forcing a checkpoint or waiting for a checkpoint to occur




SQL> SELECT tablespace_name, tablespace_size, allocated_space, free_space2 FROM DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE------------------------------ --------------- --------------- ----------TEMP_L 157286400 144048128 157220864TEMP 105906176 105906176 0

After some time you executed the same command and observed a different output, as shown below:

SQL> SELECT tablespace_name, tablespace_size, allocated_space, free_space2 FROM DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE------------------------------ --------------- --------------- ----------TEMP_L 131072 65536 65536TEMP 105906176 105906176 0

Why has the output changed?

A. because checkpoint occurredB. because the TEMP_L tablespace was shrunkC. because the database instance was restarted

D. because no sort operations are running currently



QUESTION 91You are working on a database that was upgraded to Oracle Database 11g from Oracle Database 9i. An ADDMfinding in this database says that the shared pool is inadequately sized, as shown in the Exhibit.

You diagnosed that this is due to different kinds of workloads and this occurs only during peak hours. Thefollowing are the related parameter settings:

SQL> show parameter sganame TYPE VALUE------------------------------ --------------- -----------------lock_sga boolean FALSEpre_page_sga boolean FALSEsga_max_size big integer 300Msga_target big integer 0SQL> show parameter targetname TYPE VALUE------------------------------ --------------- -----------------.....fast_start_mttr_target integer 0memory_max_target big integer 0memory_target big integer 0pga_aggregate_target big integer 100Msga_target big integer 0

You want to balance the memory between the System Global Area (SGA) components within the SGAdepending on the workloads.

Which two options would solve this problem? (Choose two.)

Exhibit:

A. Set the SGA_TARGET parameter to 300M.B. Set the SGA_MAX_SIZE parameter to 400M.C. Set the MEMORY_MAX_TARGET and MEMORY_TARGET parameters to 400M.D. Set the MEMORY_MAX_TARGET and MEMORY_TARGET parameters to 100M.E. Set the PGA_AGGREGATE_TARGET parameter to 200M and the SGA_MAX_SIZE parameter to 400M.



QUESTION 92View the Exhibit and examine the content of the V$SGA_RESIZE_OPS view.

The Shared Pool was shrunk when the Default Buffer Cache required more memory. Why did the Java Pool notshrink when the Default Buffer Cache grew?

Exhibit:

A. because it is not an auto-tuned componentB. because it uses memory available outside the total SGA sizeC. because it always requires three granules and cannot be reducedD. because it has less free memory than the Database Buffer CacheE. because it cannot be shrunk below the value set to the JAVA_POOL_SIZE parameter



QUESTION 93View the Exhibit and examine the content of the V$SGA_RESIZE_OPS view.

You executed the following command to increase the size of the Large Pool:SQL> ALTER SYSTEM SET large_pool_size=8M;System altered.

Which component would shrink to increase the size of the Large Pool?

Exhibit:

A. the Java PoolB. the Shared PoolC. KEEP Buffer CacheD. Default Buffer Cache



QUESTION 94You are hired by ABC Pvt Ltd., and assigned the task of managing one of its development databases. Thecompany has recently upgraded this database to Oracle Database 11g from Oracle Database 10g.You noticed that SGA is undersized as shown in Exhibit 1.

To investigate further, you checked the related parameters as shown in Exhibit 2.

You executed the following command to solve the problem:ALTER SYSTEM SET MEMORY_TARGET=800M;

What would happen?



A. The implementation of AMM fails because the MEMORY_MAX_TARGET parameter is not set.B. It implements AMM by setting MEMORY_TARGET and

MEMORY_MAX_TARGET parameters to 800M.C. It implements Automatic Memory Management (AMM) by setting MEMORY_TARGET to 768M and the

MEMORY_MAX_TARGET parameter to 800M.D. The implementation of AMM fails because the MEMORY_TARGET parameter cannot be set to a value less

than the sum of the current PGA_AGGRAGATE_TARGET and SGA_MAX_SIZE values.



QUESTION 95You work for ABC Pvt Ltd. The company has recently upgraded one of its development databases to OracleDatabase 11g from Oracle Database 10g. You noticed that the System Global Area (SGA) is undersized asshown in the Exhibit. To investigate further, you checked the related parameters as shown below:

SQL> show parameter sga_max_sizeNAME TYPE VALUE------------------------------ --------------- -----------------sga_max_size big integer 500M

SQL> show parameter targetNAME TYPE VALUE------------------------------ --------------- ------------------archive_lag_target integer 0db_flashback_retention_target integer 1440fast_start_io_target integer 0fast_start_mttr_target integer 0memory_max_target big integer 0memory_target big integer 0pga_aggregate_target big integer 384M

sga_target big integer 384M

Which is the best solution that you would recommend?

Exhibit:

A. Increase the value of the SGA_MAX_SIZE parameter.B. Set MEMORY_MAX_TARGET and MEMORY_TARGET parameters.C. Reduce the value of the PGA_AGGREGATE_TARGET parameter.D. Increase the value of both SGA_TARGET and PGA_AGGREGATE_TARGET parameters.E. Diagnose further to identify which component in SGA is undersized and then resize it.



QUESTION 96View the Exhibit and analyze the output of a query executed on the V$MEMORY_RESIZE_OPS view.

Why do INITIAL_SIZE, TARGET_SIZE, and FINAL_SIZE columns have the value zero for some of thecomponents?

Exhibit:

A. because they are the only components that are auto-tunedB. because they are the only components that are not auto-tunedC. because they are the only components that have not undergone any manual resizingD. because they are the only components that do not have a default size or their respective parameters are not

set in the initialization parameter file



QUESTION 97You are asked to implement Automatic Memory Management (AMM) and you added two initializationparameters to do this. The following are the some of the parameter settings:

MEMORY_MAX_TARGET=600MSGA_MAX_SIZE=500MMEMORY_TARGET=600MOPEN_CURSORS=300SGA_TARGET=300MPROCESSES=150STATASTICS_LEVEL=BASICPGA_AGGREGATE_TARGET=0

When you tried to start the database instance with these parameter settings, you received the following error:

SQL> startupORA-00824: cannot set SGA_TARGET or MEMORY_TARGET due to existing internal settings, see alert logfor more informationYou modified the parameter settings as shown below and the database instance started successfully.

MEMORY_MAX_TARGET=600MSGA_MAX_SIZE=400MMEMORY_TARGET=550MOPEN_CURSORS=400SGA_TARGET=300MPROCESSES=200STATASTICS_LEVEL=TYPICALPGA_AGGREGATE_TARGET=100M

Analyze the parameter settings and identify why the instance failed to start the first time?

A. because the STATISTICS_LEVEL parameter was set to BASICB. because the PGA_AGGREGATE_TARGET parameter was set to zeroC. because both the SGA_TARGET and MEMORY_TARGET parameters were setD. because MEMORY_TARGET and MEMORY_MAX_TARGET parameters were equalE. because the PROCESSES parameter was set to a value less than the minimum required



QUESTION 98View the Exhibit and examine the content of the V$MEMORY_RESIZE_OPS view.Which two conclusions canyou draw? (Choose two.)

Exhibit:

A. Automatic Memory Management (AMM) is enabled.B. Automatic Shared Memory Management (ASMM) is enabled but not AMM.C. The Shared Pool grew in IMMEDIATE mode to avoid an out-of-memory error.D. The Default Buffer Cache grew in DEFERRED mode to avoid an out-of-memory error.E. The memory is transferred in IMMEDIATE mode only if there are entirely empty granules available in other

components.



QUESTION 99View the Exhibit that shows your investigation of performance degradation in your database. You also know thatthere are a few tablespaces using automatic segment-space management in the database.

What could be the problem?

Exhibit:

A. There are excessive disk sorts.B. There are excessive memory sorts.C. There is a contention due to free list management.D. The tablespace that contains the segments on which waits are occurring is full.E. The server process is processing buffers at a rate that is faster than the rate at which the I/O system can

return the buffers.



QUESTION 100View the Exhibit that shows your investigation on degraded performance.What would you do? (Choose all thatapply.)

Exhibit:

A. Shrink the temporary segment.B. Keep the large tables in the KEEP pool.C. Use free list groups wherever applicable.D. Increase the value set to the PGA_AGGREGATE_TARGET parameter.E. Switch the segments to Automatic Segment Space Management (ASSM).



QUESTION 101Which statement is true regarding the setting of the PCTFREE value for an index?

A. It decides how much of a block to fill when the index is created.B. It has no role to play when segment space management is automatic.C. It keeps space reserved in a block for possible updates to existing rows in that block.D. It is the minimum percentage of a block that can be used for row data and overhead before new rows are

added to the block.



QUESTION 102Identify the reason why chained or migrated rows in a table cause performance degradation when you accessthem.

A. because the ROWID changes for these rowsB. because these rows make existing indexes invalidC. because it is not possible to use indexes to retrieve these rowsD. because retrieving these rows requires more than one data block to be accessedE. because the rows force queries to use temporary segments while retrieving them



QUESTION 103View the Exhibit and analyze the output of the query.What does the high value for the table fetch continuedrows statistics indicate?

Exhibit:

A. chained rows fetched onlyB. migrated rows fetched onlyC. rows fetched from external tablesD. both chained and migrated rows fetchedE. rows fetched from Index Organized Tables (IOT)



QUESTION 104You observed in your database that the queries formerly executed in 5 seconds now take longer time.View theExhibit and analyze the output of the query that you executed to find the problem.

Which is the primary concern?

Exhibit:

A. the number of disk sortsB. the number of full table scansC. the number of chained and migrated rowsD. the number of rows fetched based on rowid



QUESTION 105You received complaints from users about slow response time in some parts of the application. Oninvestigation, you find that this happens whenever the application runs any query against the PROD_DET tablethat contains detailed information about all the products. You gathered the table statistics and executed thefollowing statements to find more information about this table:

SQL> SELECT chain_cnt,2 round(chain_cnt/num_rows*100,2) pct_chained,3 avg_row_len, pct_free, pct_used4 FROM user_tables5 WHERE table_name = 'PROD_DET';

CHAIN_CNT PCT_CHAINED AVG_ROW_LEN PCT_FREE PCT_USED---------- ----------- ----------- ---------- ----------4789 100 3691 10 40

Which methods can you use to improve the performance? (Choose all that apply.)

A. online table redefinitionB. export and import the tableC. decrease the PCTFREE valueD. move the table using the ALTER TABLE command



QUESTION 106You observed that some of the queries are performing poorly on the SALES_RECORDS table in yourdatabase.On further investigation, you find that at the end of each day the contents of the SALES_RECORDS table aretransferred to the SALES table and deleted from the SALES_RECORDS table. The deleted operations causethe table to be sparsely populated.The SALES_RECORDS table has Automatic Segment Space Management (ASSM) and row movementenabled. The table is accessible in 24x7 mode.

What is the most efficient method to improve the performance?

A. Perform EXPORT, DROP, and IMPORT operations on the SALES_RECORDS table sequentially.B. Shrink the SALES_RECORDS table by using the ALTER TABLE...SHRINK SPACE command.C. Move the SALES_RECORDS table to a different location by using the ALTER TABLE...MOVE command.D. Deallocate the space in the SALES_RECORDS table by using the ALTER TABLE...DEALLOCATE

UNUSED command.



QUESTION 107You are working as a DBA for a DSS system, on which applications primarily perform large sequential I/Os. Thecompany wants to upgrade the storage used by the database to faster hard disks.

What other factors would you consider before giving the go-ahead for the new hard disks? (Choose all thatapply.)

A. the sizing of PGA to meet the faster data accessB. the sizing of buffer cache to meet the faster data accessC. the time it takes for a submitted I/O request to be serviced by the storageD. the capacity of the I/O channel that connects the server to the storage array



QUESTION 108You are working as a DBA on an online transaction processing (OLTP) system. This OLTP systems runs on amachine with a single CPU, on which applications primarily perform small random I/Os where each foregroundprocess reads a data block into the buffer cache for updates and the changed blocks are written in batches bythe DBWR process. You noticed the increase in I/O requests queued up against a disk, and an increase in thewait time in queue.

What would you recommend to improve the latency of I/O requests?

A. Stripe data across multiple disks.B. Increase the size of buffer cache.C. Decrease the value of the DBWR_IO_SLAVES parameter.D. Increase the value of the DB_WRITER_PROCESSES parameter.



QUESTION 109The OS administrator in your company informed you about the upgrade of storage to new disk controllers andhard disks. You want to test if the new I/O devices are capable of handling the I/O load. The database is usingAutomatic Storage Management(ASM) for storage. To test the throughput and other I/O metrics, you performedIO calibration from the Enterprise Manager but you notice that the IO calibration job failed. To see the details ofthe error, view the Exhibit.

How you can resolve the error?

Exhibit:

A. Set FILESYSTEMIO_OPTIONS to NONE and DISK_ASYNCH_IO to FALSE.B. Set FILESYSTEMIO_OPTIONS to SYNCH and DISK_ASYNCH_IO to FALSE.C. Set FILESYSTEMIO_OPTIONS to ASYNCH and DISK_ASYNCH_IO to TRUE.D. Set FILESYSTEMIO_OPTIONS to DIRECTIO and DISK_ASYNCH_IO



QUESTION 110You are working on a single node database. The storage for the database was recently upgraded to new, fasterhard disks and new disk controllers. You ran the I/O calibration procedure to check the I/O metrics for the

upgraded storage. The details for the execution of the I/O calibration procedure are given below:

SQL>DECLARE2 l_latency INTEGER;3 l_iops INTEGER;4 l_mbps INTEGER;5 BEGIN6 dbms_resource_manager.calibrate_io (1,10,l_iops,l_mbps,l_latency);7 END;/

You queried the DBA_RSRC_IO_CALIBRATE data dictionary view to check the results of the I/O calibration.The output for the query is given as follows:

SQL> SELECT max_iops, max_mbps, max_pmbps, latency FROM DBA_RSRC_IO_CALIBRATE;

MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY---------- ---------- ---------- ----------137 12 6 64

What do you infer from the output?

A. The actual latency is 10 milliseconds for one disk.B. The maximum sustainable I/O for a single process is 12 MB per second.C. The system can sustain a maximum of 137 I/O operations per second with a throughput of 6 MB per

second.D. The system can sustain a maximum of 137 I/O operations per second with a throughput of 12 MB per

second.



QUESTION 111View the Exhibit and examine the output for the query on V$IOSTAT_FUNCTION.Which two statements aretrue about the interpretation of the output? (Choose two.)

Exhibit:

A. The I/O statistics are only for the single block read and write operations.B. The I/O statistics are for single block or multi-block read and write operations.C. If there is a conflict of I/O functions, the I/O is placed in the bucket with the lower FUNCTION_ID.D. If there is a conflict of I/O functions, the I/O is placed in the bucket with the higher FUNCTION_ID.



QUESTION 112You work as a DBA in ABC Corp. You are working on an online transaction processing (OLTP) database. Thedatabase 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 tomove the storage to RAID. You are in consultation with the OS Administrator for finalizing the RAID option forthe storage.

You have the following requirements for the database:

1) Less mean time to recover (MTTR)2) Striped for performance3) Mirrored for safety

Which RAID option would you recommend?

A. RAID level 0B. RAID level 1C. RAID level 0 over 1(1+0)D. RAID level 1 over 0(0+1)



QUESTION 113You are working 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 userswill be added. Presently, the database uses normal file system storage. However, before the new applicationsare added, you want to move the storage to RAID. You are in consultation with the OS Administrator forfinalizing the RAID option.Performance is the primary goal of the database because applications aredemanding in terms of I/O operations and it should have lower mean recovery time (MTTR) than the currentMTTR.

Which RAID option would you recommend?

A. RAID level 0B. RAID level 1C. RAID level 5D. RAID level 1 over 0(0+1)



QUESTION 114In which two scenarios would you recommend using RAID level 5 as an option for database storage?(Choosetwo.)

A. For a database with lower recovery (MTTR) overheadB. For a database in which the applications primarily perform read operations and predominantly sequential I/O

is performedC. For a database in which performance is the primary goal of the database because applications are

demanding in terms of I/O operationsD. For a database in which the database size grows rapidly, the cost of storage is critical and data accessibility

is more important than performance



QUESTION 115You are working on an online transaction processing (OLTP) database that runs on a machine having multipleCPUs. The database uses normal file system storage. Examine the following initialization parameters and theirrespective values for the instance:

NAME TYPE VALUE------------------------------------ ---------- ----------------------dbwr_io_slaves integer 0db_writer_processes integer 1filesystemio_options string NONEdisk_asynch_io boolean TRUE

The applications perform small, random I/Os in which each foreground process reads a data block into thebuffer cache for updates and the changed blocks are written in batches by the DBWR process. You noticedfree buffer waits events for your database. To solve the problem, you increased the size of the buffer cache.But after some time, you notice the same event again.

Which three possible actions would you recommend? (Choose three.)

A. configuring multiple DBWR processesB. decreasing the size of the buffer cacheC. configuring I/O slaves for the DBWR processD. configuring instances to make asynchronous I/O available to DBWRE. spread data files over multiple disks, controllers, and I/O buses to ensure that there are no hotspots in the I/

O subsystem

Correct Answer: ADESection: (none)Explanation


QUESTION 116For which two systems would you recommend configuring DBWR I/O slaves instead of multiple database writerprocesses? (Choose two.)

A. for systems where you have a single CPUB. for systems where asynchronous I/O is not availableC. for systems with multiple CPUs or multiple processor groupsD. for systems where asynchronous I/O is available and configuredE. for systems where throughput is an important performance consideration



QUESTION 117You are working as a DBA in ABC Corp. You are working on online transaction processing (OLTP) system. Thedatabase uses ASM storage. One of the ASM disk goes offline because of hardware failure. When the disk isadded back again after the fix, because the database is active, rebalance operations impact the I/O subsystem.

Which two recommendations would you give to lower the impact on the I/O subsystem? (Choose two.)

A. Increase the number of ASMB processes.B. Increase the number of DBWR_IO_SLAVES.C. Decrease the value for the ASM_POWER_LIMIT parameter.D. Increase the value for the ASM_POWER_LIMIT parameter.E. Set the DISK_REPAIR_TIME disk attribute to a lower value.F. Specify the POWER clause with a lower value in an ALTER DISKGROUP statement.

Correct Answer: CFSection: (none)Explanation


QUESTION 118You are working on the Database using file system RAID level 0 striping. You want to migrate the database filesto the Automatic Storage Management (ASM). The applications supported by the database primarily performsmall, random I/Os in which each foreground process reads a data block into the buffer cache for updates andthe changed blocks are written in batches by the DBWR process.

Identify two outcomes of ASM striping in combination with RAID 0. (Choose two.)

A. It provides higher bandwidth.B. It negatively impacts the write-intensive workloads.C. It allows you to evenly distribute disks for your data.D. It causes contention on Data and Flash Recovery Area (FRA).

Correct Answer: ACSection: (none)

Explanation


QUESTION 119What type of redundancy would you recommend for the Automatic Storage Management (ASM) of a file systembased on RAID level 1 for the optimum performance of database using ASM storage?

A. high redundancyB. normal redundancyC. external redundancyD. use both ASM mirroring and hardware mirroring



QUESTION 120You look at the Top 5 Timed Events section of the Automatic Workload Repository (AWR) report and find thatthe wait event buffer busy waits is consuming a significant portion of DB time.

What does this wait event indicate?

A. buffer cache too smallB. insufficient DBWn processesC. excessive block replacementD. a large number of locally-managed tablespaces in the databaseE. block contention on segment headers, data blocks, undo header, or undo blocks



QUESTION 121You find that in the Top 5 Timed Events section of the Automatic Workload Repository (AWR) report, the waitevent buffer busy waits shows significantly high values.The database uses only non-ASSM locally managedtablespaces.On further investigation, you find that the contention is on data blocks.

Which option would you consider first to decrease the wait event values on a long-term basis?

A. decreasing PCTUSEDB. decreasing PCTFREEC. increasing the number of DBWn processesD. using automatic segment space management (ASSM)E. increasing db_buffer_cache based on the V$DB_CACHE_ADVICE recommendation



QUESTION 122While reviewing the Top 5 Timed Events section in the Automatic Workload Repository report, you notice thatthe 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.)

A. SQL statements performing frequent full table scansB. tablespaces using manual segment space managementC. SQL statements performing frequent fast full index scansD. excessive writes due to an aggressive checkpointing policy



QUESTION 123Which two inferences are correct about the CPU time and the wait time that appear in the Automatic WorkloadRepository AWR and Statspack reports in the Top 5 Event section? (Choose two.)

A. Adding CPUs to systems showing increased wait time due to contention will improve response time.B. Systems where the CPU time is dominant in comparison to the wait time have optimal response time.C. Increase in wait time when the CPU time increases or remains the same indicates an increase in the load

on the system.D. Systems where the proportion of CPU time does not decrease significantly with increasing load would most

likely benefit from adding CPUs.E. The appearance of CPU time together with wait time in the Top 5 Event section of the AWR report of a

system always indicates high contention.



QUESTION 124You notice that two wait events, Latch:cache buffer chains and Latch:cache buffer LRU chains, appearconsistently in the Top 5 Timed Events for your database.

Which two options describe what these wait events indicate? (Choose two.)

A. Latch:cache buffer LRU chains - indicates excessive block replacementB. Latch:cache buffer LRU chains - indicates insufficient size of application cursor cacheC. Latch:cache buffer chains - indicates inefficient SQL that accesses many blocks repeatedlyD. Latch:cache buffer chains - indicates that there are several sessions waiting for a buffer that is presently

being read into the buffer cache by another session

Correct Answer: ACSection: (none)

Explanation


QUESTION 125View the Exhibit and examine the details of the Top 5 Timed Events from an Automatic Workload Repository(AWR) report.What can be the three possible causes for the latch-related wait events? (Choose three.)

Exhibit:

A. Shared pool size is too small.B. Cursors are not being shared.C. There are frequent logons/logoffs.D. A large number of COMMITs are being performed.E. A large number of data definition language (DDL) and query statements are being executed simultaneously

by different sessions.

Correct Answer: ABCSection: (none)Explanation


QUESTION 126You query the V$SYSSTAT view and notice a significantly high value for the redo log space requests statistic.

Which three components would you consider for further investigation based on this information? (Choosethree.)

A. LGWRB. checkpointsC. archiver activityD. size of the log bufferE. size of the redo log files

Correct Answer: BCESection: (none)Explanation


QUESTION 127As a DBA configuring a new instance, you want to run a representative workload on the instance and examinethe relevant statistics to see whether the cache is underconfigured or overconfigured.Given below is a list of theinitialization parameters that would affect the statistics collection and advisories.

1. STATISTICS_LEVEL set to BASIC2. STATISTICS_LEVEL set to TYPICAL3. DB_CACHE_ADVICE set to ON4.TIMED_STATISTICS set to TRUE

Which of the above initialization parameters' values must be set explicitly to get the relevant statistics withminimal impact on the overall database performance?

A. only 2B. 2 and 3C. 1, 3, and 4D. 2, 3, and 4



QUESTION 128You want to review a summary of all the wait events in an instance since it started over a month ago.You issued the following query:

SQL>SELECT event, total_waits, time_waited, average_waitFROM V$SYSTEM_EVENTORDER BY time_waited DESC;

View the Exhibit and examine a portion of the output from the above query.

Which events would you consider from the output for further investigation? (Choose all that apply.)

Exhibit:

A. log file syncB. rdbms ipc message

C. SQL*Net message from clientD. log file switch (checkpoint incomplete)



QUESTION 129View the Exhibit and examine the partial output obtained when you issued the following query:

SQL> SELECT name,class,value FROM v$sysstat ORDER BY class;

Which two interpretations are correct about the information obtained from this view? (Choose two.)

Exhibit:

A. db block changes indicates the number of data blocks that have been changed.B. buffer is pinned count indicates the number of buffers that are currently pinned in the database instance.C. Usage of CACHE and NOCACHE hints affects statistics for table scans (long tables) and table scans (short

tables).D. redo log space requests indicates the number of times a there were waits for disk space to be allocated for

the redo log entries.



QUESTION 130As a DBA, you notice a change in the performance of your database.

Which two pieces of tuning-related information can you examine in the alert log to identify possible causes?(Choose two.)

A. the number of block corruption errorsB. SQL statements that are consuming maximum resourcesC. the top five sessions that are consuming maximum resourcesD. the instance parameter values that have been changed using ALTER SYSTEM since the last baseline



QUESTION 131As a DBA, you notice that the response time of your database has degraded.


A. frequency of log switchesB. frequency of block corruption errorsC. concurrent parsing and buffer cache latch contentionD. the amount of CPU time spent on database user-level calls



QUESTION 132The Service-Level Agreement for the production database includes a clause that the database down timeshould not be more than 15 minutes.

Which two details would you examine in the alert log to diagnose whether your database meets thisrequirement? (Choose two.)

A. log switch frequencyB. data file recovery timeC. instance recovery timeD. deadlock and timeout errorsE. the FAST_START_MTTR_TARGET parameter setting



QUESTION 133What is the purpose of running the statspack.snap script during the installation of the Statspack package?

A. It creates the initial baseline.

B. It automates the collection of statistics.C. It stores the current performance statistics in the Statspack tables.D. It creates the PERFSTAT user, the Statspack tables, and constraints



QUESTION 134While configuring the snapshot data capture for your database, you have specified the snapshot level setting as6.

Which statistics would be captured by the Statspack report? (Choose all that apply.)

A. resource limitB. memory statisticsC. parent and child latchesD. segment-level statisticsE. optimizer execution plans

Correct Answer: ABESection: (none)Explanation


QUESTION 135Which three statements are true about Statspack usage? (Choose three.)

A. If both Statspack and AWR are used, they cannot be scheduled to capture snapshots at the same time.B. Time Model data cannot be captured by Statspack unless the STATISTICS_LEVEL parameter is set to ALL.C. Both Statspack and AWR can be used on the same database only if the Database Diagnostics pack is

present.D. Statspack can be used to generate a report for a specific instance as well as a specific SQL statement in an

instance.E. For best performance when running the performance reports, optimizer statistics should be gathered on the

Statspack schema.

Correct Answer: CDESection: (none)Explanation


QUESTION 136Your organization has purchased licenses for the Diagnostic Pack along with the Oracle Database 11gsoftware.The application workload on your database is known to be stable between 10:00 PM and midnightevery night, but the performance on a particular Thursday was poor between 10:00 PM and 11:00 PM.

Which tool would you consider using first to diagnose the cause of the performance degradation?

A. Database ReplayB. STATSPACK ReportC. AWR Compare ReportD. SQL Performance Analyzer



QUESTION 137View the Exhibit and examine the Instance Efficiency Percentages that you got on generating an AutomaticWorkload Repository (AWR) report for your online transaction processing (OLTP) database.

What can you deduce from this part of the AWR report? (Choose all that apply.)

Exhibit:

A. A low Library Hit % indicates that cursors are not being reused.B. A low Execute to Parse % indicates that most SQL statements are reusing the existing execution plans.C. A high In-memory Sort % indicates that most sorts are being done in memory, thereby causing the

performance to degrade.D. A high Buffer Hit % indicates that required data blocks were found in the Buffer Cache memory, thereby

avoiding expensive disk I/O.



QUESTION 138You generate an AWR Compare Periods Report for two consecutive time periods as a routine activity tomonitor your database performance.View the Exhibit and examine the partial details of the Time ModelStatistics generated in the report.

Which two statements give a correct inference based on the DB Time and DB CPU in the Time ModelStatistics? (Choose two.)

Exhibit:

A. More CPU time was used for background processes in the first time period.B. A change to the database configuration reduced the % DB time for transactions.C. More of DB Time was consumed by background processes in the first time period.D. A reduction in Hard Parse activity resulted in a reduction of DB CPU Time (in seconds).E. More transactions were processed in the first time period based on the DB Time values.



QUESTION 139As part of a proactive tuning activity, you have the Automatic Workload Repository (AWR) and ADDM enabledfor 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?

A. suboptimal parameter settingsB. application-level block contentionC. performance issues related to the Resource ManagerD. short-lived or intermittent performance issues lasting for less than 5 minutes



QUESTION 140In your organization the Oracle Database 11g installation includes the Oracle Diagnostic Pack.In your database,the STATISTICS_LEVEL andCONTROL_MANAGEMENT_PACK_ACCESS parameters, and the snapshot interval and retention period havebeen set to default values.Two users of your database have complained that a particular job or session is veryslow for a transient interval of about 10 minutes during the day when the rest of the instance is performing asusual.

Where would you begin the investigation of this performance issue?

A. ASHB. ADDMC. Alert logD. Load Profile in an AWR Compare Period report



QUESTION 141There are 500 users of an online transaction processing (OLTP) application that resides in your database. Thesystem had been maintaining an average response rate of 10 seconds as specified by the Service-LevelAgreement (SLA).This morning you were informed that for the past four days the average response time hasgone 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?

A. Determine whether it is a memory configuration issue.B. Determine whether the slow response is wait bound or CPU bound.C. Determine whether the application is performing excessive I/O due to poor access path choice.D. Determine whether new tablespaces have been created on the disks that are being used by the existing

tablespaces.



QUESTION 142In your organization, there are two key databases. One of them is used primarily for an online transactionprocessing (OLTP) application and is directly accessed by the users in a client/server configuration. Theapplication must process an average of 4,000 small transactions per day. The users enter frequent queries onwhose results they depend for completing their transactions. One of the SLA requirements is that any query ortransaction processing in the OLTP system should not exceed 15 seconds. The second database supports adecision support system (DSS) as well as batch report programs. It is accessed by client-side users, whosubmit ad hoc queries through SQL*Plus and by a browser-based,third party application that provides pooledconnections for a large user base. The queries vary: some fetch only a few records, whereas others fetch andsort hundreds of records from several tables. As per the SLA specification the average turnaround time for aquery 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 managementwants 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.)

A. Minimize parallelism for the OLTP systems.B. Maximize throughput for batch processing.C. Minimize the response time for OLTP users.D. Maximize parallelism for the decision support systems.E. Minimize response time for the decision support systems.



QUESTION 143You find that the OS utilization is over 40% in your database server. Examine the following set of performancemonitoring-related activities:

1. Investigate the operating system for network transfers, paging, and swapping.2. Check the SQL and the transactions submitting the SQL for optimal execution.3. Investigate the top SQL by CPU utilization.4. Consider rescheduling some work to off-peak hours.5. Check whether there are any nondatabase jobs consuming CPU time on the machine.

What is the correct sequence in which the above activities should be performed to diagnose and reducepotential performance issues?

A. 1,5,3,2,4B. 1,2,3,4,5C. 2,4,5,3,1D. 4,5,3,1,2



QUESTION 144Which two activities can be identified as common unscalable causes of suboptimal database performance?(Choose two.)

A. defining multiple buffer poolsB. applications generating dynamic SQLC. large amounts of recursive SQL executed by SYSD. recursive SQL executed by a user other than SYSE. using cursors with bind variables that open the cursor and execute it many times



QUESTION 145View the Exhibit and examine the Load Profile from the AWR report of a test database.The application mustprocess an average of 10,000 very small transactions per day. The bulk of the processing activity is for DMLoperations with minimal to nil query processing.

Which two statements are correct about the values of the Physical reads events? (Choose two.)

Exhibit:

A. The per second value indicates the total number of data blocks read from disk to only the buffer cache.B. The per transaction value includes both the db block gets and the consistent gets that occurred during the

last sample period.C. The per transaction value indicates that write or update transactions have been committed or rolled back

during the last sample period.D. The per second value indicates the total number of data blocks read from disk to the buffer cache including

reads bypassing the buffer cache, and reads into process private buffers.



QUESTION 146Examine the following query and its output:

SQL>SELECT namespace, gets, gethits, gethitratio, pins, pinhits, reloads,invalidationsFROM v$librarycacheWHERE namespace = 'SQL AREA';

NAMESPACE GETS GETHITS GETHITRATIO PINS PINHITS RELOADS INVALIDATIONS--------- ----- ------- ----------- ----- ------- -------- -------------SQL AREA 11942 2832 .237146207 65900 62486 28 118

Which two observations are correct? (Choose two.)

A. About 24% of the parse calls were able to find a cursor to share.B. DML locks were requested 65,900 times for objects in the SQL AREA.C. Library cache misses happened 118 times because invalidations occurred due to DDL on the dependant

objects.D. Reparsing of SQL statements before they had finished executing happened 28 times due to modification of

a dependent object.



QUESTION 147On examining the Automatic Workload Repository (AWR) report, you find that log file sync appears among thetop 5 wait events.On further investigation, you notice that the average time waited for this event is low, but thenumber of waits are high.

What does this imply?

A. DBWR is slow.B. LGWR is slow due to slow I/O.C. Redo log buffer size is insufficient.D. The application might be committing frequently.



QUESTION 148During a proactive database performance monitoring routine, on examining the AWR report you find that log filesync appears among the top 5 wait events.

What does this event indicate?

A. Frequent logfile switches are occurring.B. Redo is generated faster than LGWR can write it out.C. Frequent commits or rollbacks are taking place in the application.D. Frequent incremental checkpoints are taking place in the database.



QUESTION 149View the Exhibit and examine the partial output from the following query in an online transaction processing(OLTP) database:

SQL>SELECT intsize_csec,metric_name,value, metric_unit FROM v$sysmetric;

Which two conclusions can you draw about the database from these metrics? (Choose two.)

Exhibit:

A. The buffer cache is inadequately sized for the workload.B. Very few executions of SQL statements resulted in reparsing.C. There were no multipass executions in the Program Global Area (PGA).D. The database spends more time in wait events in comparison to statement processing.



QUESTION 150View the Exhibit and examine the query based on the DBA_THRESHOLDS view and its partial output :

Which observation about the output is correct?

Exhibit:

A. Logons Per Sec triggers an alert when the number of users logging on in an interval of one second is equalto or greater than 100.

B. Blocked User Session Count triggers an alert based on the ELAPSED_TIME_PER_CALL andDB_TIME_WAITING metrics collection per minute.

C. Database Time Spent Waiting(%) triggers an alert when more than 30% of the DB time per second for threeconsecutive seconds is spent on lock waits.

D. Average Users Waiting Counts triggers an alert when the count is more than 30 for 3 consecutive 1 minuteintervals and is related to the log file sync wait event.



QUESTION 151

A user, SCOTT, complains that his database session, SID 249, is extremely slow in processing transactions.You check the ADDM report and do not find any overall database performance issues.You decide to check the wait events, specifically in SCOTT's session and issue the following command:

SQL>SELECT wait_class_id, wait_class, total_waits, time_waited FROM v$session_wait_classWHERE sid = 249;

View Exhibit1 and examine the output.

You note that the APPLICATION wait class is the second most expensive wait class after the IDLE wait class.You issue the following query to identify individual waits in the APPLICATION wait class:

SQL>select event, total_waits, time_waitedfrom v$system_event e, v$event_name nwhere n.event_id = e.event_idand wait_class_id = 4217450380;

View Exhibit2 and examine the output.

Which view would you examine next to pinpoint the problem that is causing this performance issue?



A. DBA_HIST_SESSMETRIC_HISTORY - to find the highest value metrics in the sessionB. V$SQLAREA statistics - to find the SQL statements with the highest number of executionsC. V$SESS_TIME_MODEL - to identify whether the SQL statements in the session were spending more of

execution time in parsing or waiting for the CPUD. V$EVENT_HISTOGRAM - to determine whether the TIME_WAITED is evenly distributed across the

occurrences of the wait or whether some waits for the events were very long whilst others were shortindicating more erratic response times.



QUESTION 152View the Exhibit and examine the partial output from the query on the v$file_histogram view:

Which statement correctly interprets the output displayed in the Exhibit?

Exhibit:

A. The database waited 58 times in a time interval of two milliseconds for FILE# 1.B. The database experienced 58 waits of two milliseconds duration each for FILE# 1.C. The database performed 58 synchronous single block reads in a time interval of two milliseconds for FILE#

1.D. The database experienced 58 waits of duration less than two milliseconds each that are not included in any

smaller bucket.



QUESTION 153Which statement is correct about server-generated alerts?

A. All server-generated alerts are based on threshold levels.B. Alerts are not issued for locally managed tablespaces in read-only mode.C. The STATISTIC_LEVEL initialization parameter must be set to ALL to enable server-generated alerts.D. Alerts for both locally managed tablespaces and dictionary-managed tablespaces are server generated.



QUESTION 154View the Exhibit. Examine the query on the v$servicemetric view and the output.You wish to define a service-level threshold based on wall-clock time to monitor the performance of the service.

Which metric should be used to set the appropriate threshold?

Exhibit:

A. DELTAB. CPUPERCALLC. CALLSPERSECD. DBTIMEPERSECE. DBTIMEPERCALL



QUESTION 155You want information in the form of charts for throughput and services that shows baseline values andthresholds for the baseline. How would you achieve this?

A. By quickly configuring the metric thresholdB. By computing statistics for the desired baselineC. By adding a baseline to the performance page in Oracle Enterprise ManagerD. By adding a baseline template to the performance page in Oracle Enterprise Manager



QUESTION 156Examine the following block:

BEGINDBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline',cascade => FALSE, dbid => 3310949047);END;

Why would you use the FALSE value for the CASCADE parameter while dropping the baseline?

A. because it does not remove snapshots associated with the baseline

B. because it does not drop the template that is used to create the baselineC. because it does not remove the baseline from the performance page if it appears thereD. because it does not remove the Automatic Database Diagnostic Management (ADDM) results from the

Automatic Workload Repository (AWR) that were generated using the baseline



QUESTION 157You want to capture the performance statistics during a system test that is scheduled for the weekend. Whichaction would help you to automatically capture the performance statistics?

A. Creating SQL tuning setsB. Setting up SQL plan baselinesC. Configuring new services for the databaseD. Creating Automatic Workload repository (AWR) baseline templates



QUESTION 158Identify the situation in which you would use the Automatic Workload Repository (AWR) baseline template.

A. when creating AWR snapshots based on a repeating time scheduleB. when creating and dropping AWR baselines based on a repeating time scheduleC. when computing statistics for all moving window baselines based on a repeating time schedule in the futureD. when resetting the threshold values for the SYSTEM_MOVING_WINDOW baseline based on a repeating

time schedule in the future



QUESTION 159In a database, the values for transactions per second are higher during working hours and lower duringnonworking hours. You want to receive alerts in future time periods if the daytime performance variescompared to the captured daytime performance or the nighttime performance varies compared to the capturednighttime performance, over a repeating time period. This would permit the DBA to perform additionalperformance tuning operations.

Which feature would you use to record this variation in performance statistics?

A. SQL tuning setB. SQL plan baselinesC. the Automatic Workload Repository (AWR) baseline

D. the Automatic Workload Repository (AWR) baseline template



QUESTION 160View the Exhibit.You want to set the threshold for the number of transactions (per second)metric with thethreshold type as significance level.

Which baseline would you choose to make the threshold adaptive?

Exhibit:

A. LAST_NIGHTB. THU_SECTIONC. SYSTEM_MOVING_WINDOWD. MONDAY_1_2008-07-15.02:00



QUESTION 161View the Exhibit.Which statement is true about applying the threshold setting in the Exhibit.

Exhibit:

A. The threshold does not depend on any baseline.B. The threshold is decided by comparing the warning and threshold values with the LAST_NIGHT baseline.C. The threshold is decided by comparing the warning and threshold values with the

SYSTEM_MOVING_WINDOW baseline.D. The threshold is decided by comparing the warning and critical values with the baseline set on the

performance page.



QUESTION 162View the Exhibit.You decide to quickly configure the adaptive threshold.

What would happen to the existing metric thresholds shown in the Exhibit?

Exhibit:

A. It would be overwritten.B. It would remain in effect.C. Only the thresholds set with the SYSTEM_MOVING_WINDOW baseline will remain in effect.D. Only the thresholds set with baselines other than the SYSTEM_MOVING_WINDOW baseline will remain in

effect.



QUESTION 163Identify the correct statements about the SYSTEM_MOVING_WINDOW baseline. (Choose two.)

A. It is used for adaptive threshold setting.B. The statistics computation cannot be scheduled explicitly for it.C. Only significance-level thresholds can be set for metrics under it.D. It is the only baseline that can be used on the performance page.



QUESTION 164View the Exhibit and note the workload type selected to quickly configure the metric threshold.

Which statement describes the result after finishing this quick configuration process?

Exhibit:

A. It sets thresholds for all basic metrics.B. All thresholds set are of the Significance Level type.C. It uses only the SYSTEM_MOVING_WINDOW baseline for thresholds.D. The thresholds set by the quick configuration process are of the Fixed Value type.



QUESTION 165View the Exhibit.You decide to select the setting option in the Exhibit to choose a baseline to be displayed in theperformance page.

What would you achieve if the baseline is selected in the performance page?

Exhibit:

A. A line appears in the chart that displays the 99th percentile level of the baseline.B. The performance page displays session-wise statistics compared with the baseline.C. The performance page displays the metric names and corresponding threshold values set to them.D. An extra chart appears that shows the performance at the time when the base line was last computed.



QUESTION 166You analyze an Automatic Database Diagnostic Monitor (ADDM) report to identify the component thatcontributes most to the database time.

Which is the correct description of database time?

A. It is the sum of the CPU time of all nonidle sessions.B. It is the sum of wait time and the CPU time of all nonidle sessions.C. It is the time spent in processing the input/output (I/O) request by users.D. It is the difference between the wait time and the CPU time of all nonidle sessions.



QUESTION 167View the Exhibit and examine the Automatic Database Diagnostic Monitor (ADDM) performance analysis.

Identify the correct description of the Impact(%) column in the Exhibit.

Exhibit:

A. It is the time consumed by the corresponding issue compared with the database time for the analysisperiod.

B. It is the time consumed by the corresponding issue compared with the response time for the analysisperiod.

C. It is the workload volume by the corresponding issue compared with the SYSTEM_MOVING_WINDOWbaseline for the analysis period.

D. It is the resource consumed by the corresponding issue compared with the SYSTEM_MOVING_WINDOWbaseline for the analysis period.



QUESTION 168After an Automatic Database Diagnostic Monitor (ADDM) run, you receive a recommendation for buffer busywait issue. View the Exhibit.

What is the significance of the Benefit(%) column in the Exhibit?

Exhibit:

A. It produces better query plans if the recommendation is implemented.B. It gives the maximum reduction in the number of input/output (I/O) operations.C. It gives the maximum reduction in resource usage if the recommendation is implemented.D. It gives the maximum reduction in database elapsed time if the recommendation is implemented.



QUESTION 169While diagnosing performance problems for a database, you find Buffer Busy wait is the reason from V$ views.But the Automatic Database Diagnostic Monitor (ADDM) report does not reflect the Buffer Busy findings.

Why does the Buffer Busy finding not appear in the ADDM report?

A. because an ADDM filter has been setB. because the he USE_INDIRECT_DATA_BUFFERS parameter has been set to TRUEC. because the DBIO_EXPECTED default task parameter for ADDM has been set to zeroD. because the CONTROL_MANAGEMENT_PACK_ACCESS parameter has been set to DIAGNOSTIC



QUESTION 170View the Exhibit and examine the sample Automatic Database Diagnostic Monitor (ADDM) report.

Which statement is true about the report?

Exhibit:

A. The sum of impacts under recommendations in Finding 2 cannot exceed 100%.B. It is possible that the sum of impacts under recommendations in Finding 2 can exceed 100%.

C. The recommendations regarding "buffer busy" wait cannot appear more than once in the report.D. The sum of impacts of recommendation 2 in Finding 1 and recommendation 2 in Finding 2 cannot exceed

100%.



QUESTION 171View the Exhibit and analyze a portion of the Active Session History (ASH) report marked with a red rectangle.

Which two can be inferred from the output? (Choose two.)

Exhibit:

A. increase in the number of active sessionsB. unable to grow the database buffer cache as per demandC. increase in number of COMMITS and ROLLBACKS per secondD. increase in the number of processes waiting for the buffer busy wait event



QUESTION 172View Exhibit1 and examine a portion of Active Session History(ASH) report.View Exhibit2 to examine the tablespaces.

Which action would you suggest after analyzing the output?



A. Increase the log buffer size.B. Rebuild indexes on SPCT table.

C. Decreasing the number of free lists in segment SPCT.D. Move SPCT table to a different tablespace that is locally managed with automatic segment space

management.



QUESTION 173View the Exhibit and examine portions of two ASH reports generated for the same time period. The secondreport was generated after the ASH data is flushed to disk.

Why is the sample count reduced in the second report?

Exhibit:

A. because the AWR is not able to store all the samples.B. because the samples have been filtered from ASH buffer.C. because a portion of the ASH rolling buffer has been overwritten.D. because the shared pool size is not enough to hold ASH information.

Correct Answer: BSection: (none)

Explanation


QUESTION 174The Automatic Workload Repository (AWR) snapshot interval is set to 1 hour. A user of your database notifiesyou that the system was slow between 10:00 PM and 10:10 PM, but the Automatic Database DiagnosticManagement (ADDM) analysis for the time period between 10:00 PM and 11:00 PM does not show thisperformance problem.

What would you suggest to analyze this performance problem for the user?

A. SQL Tuning AdvisorB. SQL Performance AnalyzerC. Active Session History reportD. AWR Compare Periods report



QUESTION 175You are managing an online transaction processing (OLTP) database with multiple applications. Theapplications support a large number of users and most of the applications execute groups of SQL that runtogether.

Which option would you use to monitor the performance issues for users of these applications?

A. Monitor statistics for individual sessions in an application by querying V$SESSION.B. Specify service-level thresholds and priorities for users of applications and monitor the alerts.C. Monitor statistics and wait events collected for a MODULE and ACTION by performing service aggregation.D. Configure application users as part of the resource consumer group and monitor the resource usage

statistics for the resource consumer group.



QUESTION 176You are managing an online transaction processing (OLTP) system. The database is supporting a largenumber of applications using connection pools to connect to the database.

Which method would you use for performance management?

A. Service aggregation for action within application moduleB. Monitoring individual sessions by querying V$SESSION_LONGOPSC. Monitoring individual sessions in an application by querying V$SESSIOND. Mapping the sessions for an application to one resource consumer group



QUESTION 177Identify situations in which collecting statistics at the service level helps diagnosing performance problems.(Choose all that apply.)

A. in a database where you want to monitor and diagnose individual sessionsB. in a database where you want to statistically measure the workload of jobsC. in a database that supports an application where the client directly creates a sessionD. in multitier environments where a request from an end client is routed to different database sessions



QUESTION 178Your database has the HR and Reporting applications. The HR application does most of the transactions on thedatabase and the Reporting application does most of the batch processing operations.The Reportingapplication has more workload during nonpeak hours. Both the HR and Reporting applications run on themiddle tier using connection pooling for connecting to database.You want to statistically measure the workloadgenerated by both the applications and set priority for the workload generated by the applications.

Which option would help you achieve this?

A. Assigning individual sessions created by the applications to consumer groups and setting relative priorityB. Assigning profiles to individual sessions created by the applications to set the relative priority for the

resource usageC. Creating services for the applications and setting the relative priority of services within an instance by

binding the services directly to consumer groupsD. Creating services for the applications and assigning different profiles to the sessions created by the

sessions to set the relative priority for the resource usage



QUESTION 179Your database supports many applications running on the middle tier. Many applications users create jobs forwhich you want to statistically measure workload as a part of performance management.

What would you do to accomplish the task?

A. Assign resource consumer group to jobs while creating the jobs.B. Create services for the applications and create jobs by using the DBMS_JOBS PL/SQL package.C. Query v$SESSION to gather statistics of the individual sessions for the workload created by the jobs.

D. Create services for the applications, create job class associated with the service, and then create jobs byusing the job classes.



QUESTION 180You created two services HR and FINANCE on a single instance database. These services are used by themiddle-tier applications to connect to the database by using connection pools. The applications have variousmodules and you asked the developers to invoke the DBMS_APPLICATION_INFO.SET_MODULE procedureto set the module name and action in the code for the application. As part of performance management, youwant to gather statistics for these modules and see the amount of workload created by these modules.

What would you recommend to accomplish the task?

A. Using the DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE procedure to collect statistics for a moduleand querying the V$SERVICE_EVENT view

B. Using the DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE procedure to collect statistics for a moduleand querying the V$SERVICE_STATS view

C. Using the DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE procedure to collect statistics for a moduleand querying the V$SERV_MOD_ACT_STATS view

D. Querying the V$SERVICE_STATS view because the statistics for the modules are collected automaticallywhen you invoke theDBMS_APPLICATION_INFO.SET_MODULE procedure



QUESTION 181Your database has the ACCTG service configured for an accounting application that runs on a middle tier. Theapplication has three modules LEDGER, ACCOUNTS, and BILLING. You enabled tracing at the service levelby executing the following command:

SQL> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('ACCTG');


A. A single trace file would be created for all the sessions for the service.B. Three trace files would be created for the three modules in the application.C. The trace file would not be created because the module name is not specified.D. A trace file is created for each session that uses the service, regardless of the module and action.



QUESTION 182Your database has the ACCTG service configured for an accounting application running on a middle tier. Theseservice is used by the middle-tier applications to connect to the database by using connection pools. Theapplication has three modules LEDGER, ACCOUNTS, and BILLING. You asked the developers to invoke theDBMS_APPLICATION_INFO.SET_MODULE procedure to set the module name and action in the code for theapplication. 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?

A. Using trace files as input for the tkprof utility to consolidate the tracing of sessions for a moduleB. Using the trace file as input for the trcsess utility to consolidate and process the output for a moduleC. Using the trcsess utility to consolidate all trace files into a single output file, which can then be processed by

the tkprof utilityD. Using the trace file as input for the trcsess utility and creating output files that can directly be used by the

users for performance or debugging purposes



QUESTION 183You noticed a high number of SQL statements with the same PLAN_HASH_VALUES.View Exhibit1 to see the details.

View Exhibit2 to see the details of the SQL text for the statements.

What do you infer from the results?



A. The statements are using the same cursor.B. The statements are not using the same cursor.C. Removing the bind variable would improve performance.D. The cursor for the statement is aging out of the shared pool very quickly.



QUESTION 184You are managing an online transaction processing (OLTP) application database. Some users on the systemare reporting degradation in the performance of SQL statements. To diagnose the problem, you want to identifythe SQL statements that are expensive on system resources.

What would you recommend to accomplish the task? (Choose all that apply.)

A. running SQL Access AdvisorB. running SQL Performance AnalyzerC. querying the V$SQLSTATS and V$SQL_PLAN views to get the informationD. creating an Automatic Workload Repository (AWR) report for the problematic period



QUESTION 185View the Exhibit and examine the statistics of a SQL statement.

Which two conclusions can you draw from the statistics? (Choose two.)

Exhibit:

A. The SQL statement has high logical I/O.B. The SQL statement has high CPU usage.C. The SQL statement has high physical I/O.D. The SQL statement has long sort operations.



QUESTION 186You are managing an online transaction processing (OLTP) system. Many users complain about the slowresponse time. You investigated and found that in the Top 5 events, direct path read accounts for 73.2% of thetotal wait time.

Which two are the possible reasons for the high direct path read time? (Choose two.)

A. The I/O subsystem is slow.B. The SQL statements are using high CPU.C. The SQL statement has a long parsing time.D. The SQL statements are performing a large number of physical and logical reads.



QUESTION 187You identified poorly performing SQL by analyzing the V$SQL and V$SQLSTATS views. You want toinvestigate 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 executedSQL? (Choose two.)

A. Query V$SQL_PLAN to view the execution plan.B. Query DBA_HIST_SQL_PLAN to view the execution plan.C. Copy and paste the SQL text from the V$SQL view and use EXPLAIN PLAN to generate the execution plan.D. Use the dbms_xplan.display_cursor function with the SQL ID and child number to generate the execution

plan.



QUESTION 188You identified a SQL statement with SQL ID 9g485acn2n30m, which is expensive on the resources. To viewthe execution plan for the statement, you executed the following command:

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('9g485acn2n30m',1));

What would this command accomplish?

A. It formats and displays the contents of the execution plan for the cursor with the SQL_ID '9g485acn2n30m'from the library cache.

B. It formats and displays the contents of the execution plan but does not guarantee accuracy if the SQLstatement has bind variables.

C. It formats and displays the contents of the execution plan but does not guarantee that the same executionplan would be executed.

D. It formats and displays the contents of the execution plan for the cursor with the SQL_ID '9g485acn2n30m'from the DBA_HIST_SQL_PLAN view.



QUESTION 189You identified poorly performing SQL statements that use bind variables. You want to examine the executionplans for the most recently executed statements for further diagnosis.

Which method would you recommend to view the execution plans?

A. SQL*Plus AutotraceB. the dbms.xplan packageC. EXPLAIN PLAN for SQL statementsD. the SQL trace utility to generate trace files and read the formatted output using the tkprof utility



QUESTION 190

Examine the SQL statement given below:

SQL> EXPLAIN PLAN forSELECT department_name,count(*)FROM hr.employees e, hr.departments dWHERE e.department_id=d.department_idGROUP BY d.department_name;

To view the execution plan for the statement, you queried plan_table. View the Exhibit and examine theexecution plan.

Which two statements are true about the execution plan? (Choose two.)

Exhibit:

A. The execution plan is displayed for the executed SQL statement.B. The EXPLAIN PLAN command does not actually execute the statement.C. The row with the id column having a value of 4 is the first step executed in the execution plan.D. The row with the id column having a value of 0 is the first step executed in the execution plan.



QUESTION 191You enabled tracing for a session by executing the following command:

SQL>SET AUTOTRACE TRACEONLY;

You issued a SQL query in the same session. Which two statements are true? (Choose two.)

A. It generates only a report on the execution path used by the SQL optimizer.B. Trace for the statement would be created without displaying the query output.C. It generates only the statement execution statistics without executing the query.D. It generates a report on the execution path used by the SQL optimizer and the statement execution

statistics.



QUESTION 192Examine the values for the following initialization parameters:

STATISTICS_LEVEL = TYPICALTIMED_STATISTICS = true

You are managing an online transaction processing (OLTP) system. Application users notice that some querieshave poor response time. You determine that queries from session ID 27, serial number 60, for user OE areheavy-resource consumers. To investigate further, you enabled tracing for the session by executing thefollowing command:

SQL> EXECUTE dbms_system.set_sql_trace_in_session (27, 60, true);


A. The tkprof output file for the trace generated would display only the timed statistics for the SQL statements.B. The tkprof output file for the trace generated would display statistics for all the sessions created by the user

OE.C. The tkprof output file for the trace generated would display bind variable values if bind variables are used in

the queries executed in the session.D. 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.



QUESTION 193You observed that some of the queries are performing poorly on the SALES_RECORDS table in yourdatabase.On further investigation, you find that at the end of each day the contents of the SALES_RECORDS table aretransferred to the SALES table and deleted from the SALES_RECORDS table. The deleted operations causethe table to be sparsely populated. You decided to use the ALTER TABLE...SHRINK SPACE COMPACTcommand to shrink the table.

Why would you choose this method? (Choose all that apply.)

A. because it can be used during peak hoursB. because it avoids unnecessary cursor invalidationC. because it adjusts the high-water mark (HWM) immediatelyD. because you have long-running queries that might span the shrink operationE. because it does not allow any data manipulation language (DML) operations, thereby making the shrink

operation faster





What do these wait events indicate?

A. The DBWn processes are not creating free buffers fast enough.B. The buffer cache is too small and block replacement is excessive.C. The aggressive checkpointing policy is causing too many writes to disk.D. The work areas are too large to fit in memory and are, therefore, written to disk.



QUESTION 195You notice that two wait events, Latch:cache buffer chains and Latch:cache buffer LRU chains, appearconsistently in the Top 5 Timed Events for your database.

Which two options describe what these wait events indicate? (Choose two.)

A. Latch:cache buffer LRU chains - indicates excessive block replacementB. Latch:cache buffer LRU chains - indicates insufficient size of application cursor cacheC. Latch:cache buffer chains - indicates inefficient SQL that accesses many blocks repeatedlyD. Latch:cache buffer chains - indicates that there are several sessions waiting for a buffer that is presently

being read into the buffer cache by another session



QUESTION 196You identified poorly performing SQL by analyzing the V$SQL and V$SQLSTATS views. You want toinvestigate 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 executedSQL? (Choose two.)

A. Query V$SQL_PLAN to view the execution plan.B. Query DBA_HIST_SQL_PLAN to view the execution plan.C. Copy and paste the SQL text from the V$SQL view and use EXPLAIN PLAN to generate the execution plan.D. Use the dbms_xplan.display_cursor function with the SQL ID and child number to generate the execution

plan.



QUESTION 197View the Exhibit exhibit1 to examine the series of SQL commands and parameter settings.

View the Exhibit exhibit2 to examine the plans available in the SQL plan baseline.

The first plan (in red) is created when OPTIMIZER_MODE is set to ALL_ROWS and the second plan (in blue)is created when OPTIMIZER_MODE is set to FIRST_ROWS.

Which SQL plan baseline would be used if the SQL query in exhibit1 is executed again when the value ofOPTIMIZER_MODE is set to FIRST_ROWS?



A. the second plan, because it is a fixed planB. the first plan, because it is an accepted planC. the second plan, because it is the latest generated plan in FIRST_ROW modeD. A new plan, because the second plan in FIRST_ROW mode is not an accepted plan



QUESTION 198You find that in the Top 5 Timed Events section of the Automatic Workload Repository (AWR) report, the waitevent buffer busy waits shows significantly high values.The database uses only non-ASSM locally managedtablespaces.On further investigation, you find that the contention is on data blocks.

Which option would you consider first to decrease the wait event values on a long-term basis?

A. decreasing PCTUSEDB. decreasing PCTFREEC. increasing the number of DBWn processesD. using automatic segment space management (ASSM)E. increasing db_buffer_cache based on the V$DB_CACHE_ADVICE recommendation



QUESTION 199As a DBA, you notice a change in the performance of your database.


A. the number of block corruption errorsB. SQL statements that are consuming maximum resourcesC. the top five sessions that are consuming maximum resourcesD. the instance parameter values that have been changed using ALTER SYSTEM since the last baseline



QUESTION 200View the Exhibit and examine the partial output from the following query in an online transaction processing(OLTP) database:

SQL>SELECT intsize_csec,metric_name,value, metric_unit FROM v$sysmetric;

Which two conclusions can you draw about the database from these metrics? (Choose two.)

Exhibit:

A. The buffer cache is inadequately sized for the workload.B. Very few executions of SQL statements resulted in reparsing.C. There were no multipass executions in the Program Global Area (PGA).D. The database spends more time in wait events in comparison to statement processing.




oracle 1z0-054 exam questions & answers...2013/11/28 · the optimizer_mode parameter set at...

Documents