054
- 1.
After running SQL Performance Analyzer (SPA), you observe a few regressed SQL statements in the SPA output. Identify the two actions that you would suggest for these regressed SQL statements. (Choose two.)
- A.
Running SQL Access Advisor
- B.
Adding them to SQL Plan Baseline
- C.
Submitting them to SQL Tuning Advisor
- D.
Running Automatic Database Diagnostic Monitor (ADDM)
Correct Answer(s)
B. Adding them to SQL Plan Baseline
C. Submitting them to SQL Tuning Advisor -
- 2.
View 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 of OPTIMIZER_MODE is set to FIRST_ROWS? Exhibit 1: Exhibit 2:
- A.
The second plan, because it is a fixed plan
- B.
The first plan, because it is an accepted plan
- C.
The second plan, because it is the latest generated plan in FIRST_ROW mode
- D.
A new plan, because the second plan in FIRST_ROW mode is not an accepted plan
Correct Answer
B. The first plan, because it is an accepted plan -
- 3.
You work as a DBA for a company and you have the responsibility of managing one of its online transaction processing (OLTP) systems. The database encountered performance-related problems and you generated an Automatic Workload Repository (AWR) report to investigate it further. View the Exhibits and examine the AWR report. What could be the problem in this database? Exhibit 1: Exhibit 2: Exhibit 3: Exhibit 4:
- 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
C. The shared pool size is inadequate -
- 4.
You are working on an online transaction processing (OLTP) system. You detected free buffer waits events for your database instance running in a machine that has multiple CPUs. You increased the database buffer cache size as the first step. After a few hours of work on the database, further investigation shows that the same event is being recorded. What would be your next step to avoid this event in future?
- 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
Correct Answer
A. Decrease the value of the DBWR_IO_SLAVES parameter. -
- 5.
You are working on a development database that was upgraded to Oracle Database 11g from Oracle Database 9i. 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 tried to resize this by shrinking the database buffer cache but that caused inadequate buffer cache problems. The following are the related parameter settings: SQL> show parameter sga NAME TYPE VALUE ------------------------------ --------------- ----------------- lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 300M sga_target big integer 0 SQL> show parameter target NAME TYPE VALUE ------------------------------ --------------- ----------------- ..... fast_start_mttr_target integer 0 memory_max_target big integer 0 memory_target big integer 0 pga_aggregate_target big integer 100M sga_target big integer 0 You want to balance the memory between the System Global Area (SGA) components within SGA without affecting the size of the Program Global Area (PGA). Which action would solve this problem? Exhibit 1:
- 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
A. Set the SGA_TARGET parameter to 300M -
- 6.
You observed that some of the queries are performing poorly on the SALES_RECORDS table in your database. On further investigation, you find that at the end of each day the contents of the SALES_RECORDS table are transferred to the SALES table and deleted from the SALES_RECORDS table. The deleted operations cause the table to be sparsely populated. You decided to use the ALTER TABLE...SHRINK SPACE COMPACT command to shrink the table. Why would you choose this method? (Choose all that apply.)
- A.
Because it can be used during peak hours
- B.
Because it avoids unnecessary cursor invalidation
- C.
Because it adjusts the high-water mark (HWM) immediately
- D.
Because you have long-running queries that might span the shrink operation
- E.
Because it does not allow any data manipulation language (DML) operations, thereby making the shrink operation faster
Correct Answer(s)
A. Because it can be used during peak hours
B. Because it avoids unnecessary cursor invalidation
D. Because you have long-running queries that might span the shrink operation -
- 7.
View 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(s)
B. SQL execute elapsed time includes the time spent in performing fetches of query results.
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. -
- 8.
A batch workload that historically completed in the maintenance window between 10:00 PM and midnight is currently showing poor performance and completing at 2 AM. To help in the diagnosis of the performance degradation, the senior DBA in your organization asks you to execute the awrddrpt.sql script to produce a Compare Periods report. Which two statements are true about the report produced by this script? (Choose two.)
- A.
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.
Correct Answer(s)
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. -
- 9.
You plan to use adaptive thresholds as part of the performance tuning activity. You decide to increase the window size of the default moving window baseline for all metric observations and comparisons in your database. The following error occurs when you try to increase the window size through Enterprise Manager: Failed to commit: ORA-13541: system moving window baseline size (1296000) greater than retention (1036800) ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 601 ORA-06512: at line 2 Which action would allow you to perform the preceding task successfully?
- A.
Increasing the flashback retention period
- B.
Increasing the retention period for SQL Management Base
- C.
Increasing the undo retention period for the database instance
- D.
Increasing the Automatic Workload Repository (AWR) retention period
Correct Answer
D. Increasing the Automatic Workload Repository (AWR) retention period -
- 10.
Identify 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(s)
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. -
- 11.
A user in a session executed the following SQL statement to set the optimizer mode: ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS What impact would it have on the goal of the optimizer for that session? (Choose all that apply.)
- 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(s)
A. Statement level OPTIMIZER_MODE hints take precedence over the session-level setting.
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. -
- 12.
You are working on an online transaction processing (OLTP) system. By day most of the application users perform queries accessing the most recently added or modified rows. The applications have most of the queries based on multiple tables. But at night, some batch processing is also done. Which two actions would you recommend to choose a goal for the optimizer based on the needs of your application? (Choose two.)
- A.
Setting the OPTIMIZER_MODE parameter to ALL_ROWS at the instance level
- B.
Setting the OPTIMIZER_MODE parameter to FIRST_ROWS_n at the instance level
- C.
Asking the developer to add a hint /*ALL_ROWS*/ in the long-running batch processing queries
- D.
Asking the developer to add a hint /*FIRST_ROWS_n*/ in the long-running batch processing queries
Correct Answer(s)
B. Setting the OPTIMIZER_MODE parameter to FIRST_ROWS_n at the instance level
C. Asking the developer to add a hint /*ALL_ROWS*/ in the long-running batch processing queries -
- 13.
Which three factors influence the optimizer's behavior while choosing an optimization approach and goal for a SQL statement? (Choose three.)
- A.
Parsing of a SQL statement
- B.
Operating system (OS) statistics
- C.
Object statistics in the data dictionary
- D.
The OPTIMIZER_MODE initialization parameter
- E.
Optimizer SQL hints for changing the query optimizer goal
Correct Answer(s)
C. Object statistics in the data dictionary
D. The OPTIMIZER_MODE initialization parameter
E. Optimizer SQL hints for changing the query optimizer goal -
- 14.
Examine the initialization parameter values for the instance given below: NAME TYPE VALUE ------------------------------------ ----------- ------------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_dynamic_sampling integer 2 optimizer_features_enable string 11.1.0.6 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_mode string ALL_ROWS db_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 not using the index. Instead of an index scan, a full table scan is used. View the Exhibit and examine the autotrace output 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(s)
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. -
- 15.
Examine the initialization parameter values for the instance given below: NAME TYPE VALUE ------------------------------------ ----------- ------------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_dynamic_sampling integer 2 optimizer_features_enable string 11.1.0.6 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_mode string ALL_ROWS db_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. The cost 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.) Exhibit 1: Exhibit 2:
- 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(s)
B. The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to a high value.
D. The statistics for the table and all the indexes associated with the table are not current. -
- 16.
The columns CUST_CITY, CUST_STATE_PROVINCE, and COUNTRY_ID are frequently used together in the WHERE clause of the queries. The CUSTOMERS table is a big table with 20 GB of data. You notice that the selectivity 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 columns
- B.
Updating histogram statistics for these columns by using the DBMS_STATS.GATHER_TABLE_STATS procedure
- C.
Using the DBMS_STATS.CREATE_EXTENDED_STATS function to create a virtual column and create index on the virtual column
- D.
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
Correct Answer
D. 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 -
- 17.
View Exhibit1 to examine the description of the CUSTOMERS table. You observed that optimizer selectivity is not accurate when the CUST_STATE_PROVINCE and COUNTRY_ID columns are used together in the WHERE clause of a query. View Exhibit2 to examine the query 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 of rows returned from the table. What can you do to make the optimizer detect the actual number of rows? Exhibit 1: Exhibit: 2
- 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.
Correct Answer
C. Create extended statistics for the CUST_STATE_PROVINCE and COUNTRY_ID columns. -
- 18.
View 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 that estimated 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? Exhibit 1: Exhibit 2:
- A.
Setting the STATISTICS_LEVEL parameter to ALL
- B.
Setting the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE
- C.
Creating extended statistics for the CUST_LAST_NAME, CUST_ID, and CUST_TOTAL columns
- D.
Updating the statistics for the CUSTOMERS table by using the DBMS_STATS.GATHER_TABLE_STATS procedure
Correct Answer
D. Updating the statistics for the CUSTOMERS table by using the DBMS_STATS.GATHER_TABLE_STATS procedure -
- 19.
You are working on a decision support system (DSS). The index is available on the COUNTRY_ID column of the 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 updated
- B.
Because the index statistics for the index on the COUNTRY_ID column are not current
- C.
Because the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a higher value
- D.
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.
Correct Answer
D. 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. -
- 20.
View 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> EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO'); View Exhibit2 to examine a query plan. Even though the index is present on the COUNTRY_ID and CUST_GENDER columns, the query uses a full table scan. What could be the reason? Exhibit 1: Exhibit 2:
- A.
Because the histogram statistics for the COUNTRY_ID column are not updated
- B.
Because the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a high value
- C.
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 available
- D.
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.
Correct Answer
C. 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 available -
- 21.
You are working on an online transaction processing (OLTP) system. The CUST table in the SH schema was populated by direct load and after that it has gone through a lot of updates and deletions. The statistics for the CUST and SALES tables were updated recently. View the Exhibit and examine the query plan. The query is performing a lot of I/O for a query that fetches only 168 rows. To investigate further, you queried the ALL_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 2 FROM all_tables 3 WHERE table_name = 'CUST'; TABLE_NAME BLOCKS PCT_USED PCT_FREE NUM_ROWS ---------- ---------- ---------- ---------- ---------- CUST 13768 80 1 11060 What would you recommend to reduce the I/O? Exhibit:
- A.
Reorganizing the table to use fewer blocks
- B.
Rebuilding the index on the CUST_FIRST_NAME column
- C.
Increasing the value for the PCTFREE attribute for the CUST table
- D.
Increasing the value for the PGA_AGGREGATE_TARGET initialization parameter
Correct Answer
B. Rebuilding the index on the CUST_FIRST_NAME column -
- 22.
Your system has been upgraded from Oracle Database 10g to Oracle Database 11g. You imported SQL Tuning Sets (STS) from the previous version that contains important SQL statements. You are not sure about the elapsed time that the SQL statements will have in the new system due to the version change of the optimizer. Which tool provides a comparative report with respect to the elapsed time in both versions of the optimizer?
- A.
SQL Access Advisor
- B.
SQL Tuning Advisor (STA)
- C.
SQL Performance Analyzer (SPA)
- D.
Automatic Workload Repository (AWR) compare period report
Correct Answer
C. SQL Performance Analyzer (SPA) -
- 23.
Which 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(s)
A. It detects changes in SQL execution plans.
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. -
- 24.
The database application developers are planning to make some major schema changes such as creating new indexes and materialized views. They want to check the net impact of these changes on the workload performance. This activity has to be performed in the production database, so they want only the query part of the data manipulation language (DML) statements to be executed so that the side effects to the database or user data can be prevented. What should they use to achieve this?
- A.
Database Replay
- B.
SQL Tuning Advisor
- C.
SQL Access Advisor
- D.
SQL Performance Analyzer
Correct Answer
D. SQL Performance Analyzer -
- 25.
You upgraded database from the Oracle Database 10g to Oracle Database 11g. To test the performance SQL on the upgraded database. You want to build the before-change performance data by using SQL Performance Analyzer. Which method would allow the task to execute quickly?
- A.
The EXPLAIN PLAN method
- B.
The TEST EXECUTE method
- C.
The COMPARE PERFORMANCE method
- D.
The OPTIMIZER_USE_SQL_PLAN_BASELINE parameter set to TRUE
Correct Answer
B. The TEST EXECUTE method -
- 26.
Examine the following block that executes a SQL Performance Analyzer task: begin dbms_sqlpa.execute_analysis_task(task_name => 'SPA_SKD4', execution_type => 'TEST EXECUTE', execution_name => 'before'); end; Which two statements are true about the execution? (Choose two.)
- 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.
Correct Answer(s)
A. It generates execution plans and execution statistics.
B. It executes only the query part of the DML statements. -
- 27.
You plan to collect statistics for an important table in your database to help improved query performance. You are uncertain about the optimizer behavior after the statistics have been collected because currently there are no statistics for indexes or partitions on the table. You wish to test the performance effect of the newly collected statistics. How would you prevent the optimizer from using the newly collected statistics for other sessions immediately after 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
E. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE for the session. -
- 28.
You have the automatic optimizer statistics gathering enabled for a 24x7 database as part of the automatic maintenance 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 the database performance. What would you do to increase the interval of statistics collection for these tables so that the statistics collection jobs 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
Correct Answer
B. Increase the value of the STALE_PERCENT preference for these tables. -
- 29.
You have the automatic optimizer statistics gathering enabled for a 24x7 database as part of the automatic maintenance task. Because the automatic optimizer statistics collection runs during maintenance window (in the night), the statistics on tables that are significantly modified throughout the day becomes stale. This leads to poor query performance on these tables and you want to minimize the overhead in statistics collection. Identify a solution to this problem.
- 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.
Correct Answer
D. Gather statistics on the table when the tables have a representative number of rows, and then lock the statistics. -
- 30.
You observe that suboptimal execution plans for the queries are being generated on a table that previously used less resources. You have collected statistics on these tables two days ago. The optimizer statistics retention period is set to 31 days. You are able to find the timestamp information about statistics update from the DBA_TAB_STATS_HISTORY view. Because it is a frequently queried table, you would like the optimizer to generate better plans. Which action would enable you to use the previous set of statistics on the objects that may lead to better execution plans?
- A.
Restoring statistics from statistics history up to the desired time
- B.
Deleting all AWR snapshots collected after the time of desired statistics collection
- C.
Applying the flashback table technique until the time of desired statistics collection
- D.
Setting the OPTIMIZER_PENDING_STATISTICS parameter to TRUE to use the previous version of statistics
Correct Answer
A. Restoring statistics from statistics history up to the desired time -
- 31.
The SALES table in your database undergoes frequent changes. While observing the statistics for the table in the middle of the day, you find a change since the last observation. But the information about the statistics collection 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.
Correct Answer
C. Some of the statistics were collected using the ANALYZE command. -
- 32.
You have a range-partitioned table in your database. Each partition in the table contains the sales data for a quarter. The partition related to the current quarter is modified frequently, whereas other partitions undergo fewer data manipulations. The preferences for the table are set to their default values. You collect statistics for the 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.
Correct Answer
C. Set the INCREMENTAL preference to TRUE for the partition table. -
- 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
Correct Answer
B. It is auxiliary information collected by the optimizer for a SQL statement to eliminate estimation error. -
- 34.
You used SQL Tuning Advisor for a long-running SQL statement that suggested a SQL profile which can be used by the query subsequently for a better execution plan. After implementing the profile, you altered the profile 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 the query plan, you find that the query from the user session does not use the SQL profile. View the Exhibit and examine 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.
Correct Answer
C. The SQLTUNE_CATEGORY parameter is set to DEFAULT for the user session. -
- 35.
You used SQL Tuning Advisor for a long-running SQL statement that suggested a SQL profile which can be used by the query subsequently for better execution plan. View the Exhibit. But you want certain user sessions not 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.
Correct Answer
A. Alter the SQL profile to change the category of the SQL profile. -
- 36.
View the Exhibit and examine the result of Automatic SQL Tuning for a period. None of the recommended SQL profiles 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.
Correct Answer
D. Use the DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETERS procedure to set ACCEPT_SQL_PROFILES to TRUE. -
- 37.
You receive a recommendation for a SQL statement through the automatic SQL tuning process that suggests implementing a SQL profile. You implement the SQL profile. The tables associated with the SQL statement grow, and indexes are created and dropped on these tables. What would happen to the SQL profile that is created for the SQL statement?
- 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.
Correct Answer
C. The profile will be used for the changed plans of the SQL statement. -
- 38.
Identify the type of recommendation that can automatically be implemented by the Automatic Tuning Advisor as part of automatic SQL tuning task?
- A.
Recommendation for SQL profiles
- B.
Recommendation about effective indexing
- C.
Recommendation about stale or no statistics
- D.
Recommendation about the structure of SQL statements
Correct Answer
A. Recommendation for SQL profiles -
- 39.
A user session executes a query and the plan for the query is shown in the Exhibit as Plan-1. After verifying with the SQL Access advisor, an index is created on the table in the JOB_ID column and the query is executed again. A new plan is generated against the second query shown in the Exhibit as Plan-2. When explaining the plan for the second query, you observe that the optimizer uses the first plan instead of the second. The following parameters are set for the user session: OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE SQLTUNE_CATEGORY=DEFAULT Why does the optimizer not use the second plan? Exhibit:
- A.
Because the second plan is not verified
- B.
Because the second plan is not part of the SQL plan baseline
- C.
Because the SQLTUNE_CATEGORY parameter is set to DEFAULT
- D.
Because the explain plan option in the user session does not allow the new plan because SQLTUNE_CATEGORY parameter is set to DEFAULT
Correct Answer
A. Because the second plan is not verified -
- 40.
View the Exhibit and examine the plans in the SQL plan baseline. You observe that the last two SELECT statements (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 more efficient? (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.
Correct Answer(s)
B. Use SQL Tuning Advisor to tune the plan.
D. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to evolve the plan. -
- 41.
Identify the two direct sources from where SQL plans can be loaded into the SQL plan baselines. (Choose two.)
- A.
Cursor cache
- B.
Stored outline
- C.
SQL Tuning Set
- D.
Automatic Workload Repository (AWR) snapshots
Correct Answer(s)
A. Cursor cache
C. SQL Tuning Set -
- 42.
Your 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.
Correct Answer
A. Include the old plans in the SQL plan baseline. -
- 43.
The OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter is set to TRUE for a 24x7 database instance. The space consumption has significantly increased by the automatically captured plan baselines. You are suggested to run the following block: BEGIN dbms_spm.configure('SPACE_BUDGET_PERCENT', 30); END; After a few weeks of work, you observe warnings on exceeding space usage by SQL Management Base (SMB) in alert log file. Which two actions can be solutions for this? (Choose two.)
- A.
Increasing the AWR retention period
- B.
Purging the older SQL management objects
- C.
Increasing the size of the SYSTEM tablespace
- D.
Increasing the size of the SYSAUX tablespace
Correct Answer(s)
B. Purging the older SQL management objects
D. Increasing the size of the SYSAUX tablespace -
- 44.
Your company wants to migrate a single instance Oracle 11g Release 1 database to a RAC environment. The database 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 statements and 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 after performance. 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 after performance. Identify the correct steps required to perform the test.
- A.
2 and 3 only
- B.
6 and 7 only
- C.
1, 4, and 5 only
- D.
2, 3, and 5 only
- E.
2, 3, 4, and 5 only
Correct Answer
C. 1, 4, and 5 only -
- 45.
You are working on an online transaction processing (OLTP) system. The middle-tier applications use connection pooling to connect to the database. Presently, you have a single-node database. The company plans to migrate the database to a RAC environment. Before you move to a RAC environment, you want to test the performance of the SQL statements and the peak workload on the new environment. To accomplish the Database Capture for replay, you identified the peak workload period on the existing system and started the Database Capture. Which client requests to the database can be captured as part of the workload capture? (Choose all that apply.)
- A.
Flashback query
- B.
Distributed transactions
- C.
Logging in and logging out of sessions
- D.
All DDL statements having bind variables
- E.
Direct path load of data from external files
Correct Answer(s)
B. Distributed transactions
C. Logging in and logging out of sessions -
- 46.
You are a DBA in ABC Corp. You are working on a DSS system. The applications directly connect to the database to perform transactions. The application users also perform direct loads. The company wants to upgrade from Oracle Database 10g version 10.2.0.1 to Oracle Database 11g. You want to test the performance of 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 performance analysis
- B.
Capturing the workload on the production database, replaying it on the test machine, and comparing the performance
- C.
Creating a SQL Tuning Set by capturing the SQL workload on the production database and using SQL Performance 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
Correct Answer
C. Creating a SQL Tuning Set by capturing the SQL workload on the production database and using SQL Performance Analyzer for performance analysis -
- 47.
You are a DBA in ABC Corp. You are working on an online transaction processing (OLTP) system. The applications running on the database use connection pooling to connect to the database to perform transactions. The company wants to upgrade the CPU and the memory for the machine on which the database is running. To test the realistic workload on the new machine with increased CPU and memory, you identified 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 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 test machine? (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(s)
A. Preprocess the captured workload for the specified target database version.
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. -
- 48.
You are a DBA in ABC Corp. You are working on an online transaction processing (OLTP) system. The applications running on the database use connection pooling to connect to the database to perform transactions. The company wants to upgrade the CPU and the memory for the machine on which the production database is running. To test the realistic workload on the new machine with increased CPU and memory, you identified 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 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 performed 2) all the 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 = 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 and THINK_TIME_AUTO_CORRECT = TRUE
- D.
SYNCHRONIZATION = TRUE, THINK_TIME_SCALE = 100, CONNECT_TIME_SCALE = 100 and THINK_TIME_AUTO_CORRECT = FALSE
Correct Answer
A. SYNCHRONIZATION = FALSE, THINK_TIME_SCALE = 0, CONNECT_TIME_SCALE = 0 and THINK_TIME_AUTO_CORRECT = FALSE -
- 49.
View the Exhibit and examine the output for the WRC command-line utility that you executed to estimate the number 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
A. The replay database should be in the RESTRICTED mode. -
- 50.
Examine the output of the following query: SQL> SELECT c.name,a.addr,a.gets,a.misses,a.sleeps, 2 a.immediate_gets,a.immediate_misses,b.pid 3 FROM v$latch a, v$latchholder b, v$latchname c 4 WHERE a.addr = b.laddr(+) and a.latch# = c.latch# 5 AND c.name LIKE '%latch_name%' ORDER BY a.latch#; LATCH NAME ADDR GETS MISSES SLEEPS IMMEDIATE_GETSIMMEDIATE_MISSES ------------ -------- -------- ------- ------ -------------- ---------------- shared pool 200165448520540 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 parses
- B.
Increasing the size of the shared pool
- C.
Using only dedicated server connections
- D.
Setting the CURSOR_SHARING parameter to EXACT
- E.
Encouraging the use of more literal SQL statements
Correct Answer(s)
A. Reducing hard parses
B. Increasing the size of the shared pool -
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 20, 2022Quiz Edited by
ProProfs Editorial Team -
Sep 13, 2011Quiz Created by
Jswhitworth
- Binary Code Quizzes
- Computer Concept Quizzes
- Computer Essential Quizzes
- Computer Forensics Quizzes
- Computer Hardware Quizzes
- Computer Networking Quizzes
- Computer Parts Quizzes
- Computer Programming Quizzes
- Computer Science Quizzes
- Computer Security Quizzes
- Computer Shortcut Key Quizzes
- Computer Skills Quizzes
- Computer Virus Quizzes
- Cyber Security Quizzes
- Data Quizzes
- Data Security Quizzes
- Hacking Quizzes
- HCI Quizzes
- Internet Quizzes
- IT SecurITy Quizzes
- Malware Quizzes
- Microprocessor Quizzes
- Network Quizzes
- Operating System Quizzes
- Phishing Quizzes
- Programming Language Quizzes
- Server Quizzes
Back to top