1Z0-117 | All About Download 1Z0-117 examcollection


Q31. You want to run SQL Tuning Advisor statements that are not captured by ADDM, AWR, and are not in the library cache. 

What is the prerequisite? 

A. Enable SQL plan management 

B. Create a SQL plan baseline for each query 

C. Create a SQL Tuning Set (STS) containing the SQL statements 

D. Gather statistics for objects used in the application 

Answer:

Explanation: You can use an STS as input to SQL Tuning Advisor, which performs 

automatic tuning of the SQL statements based on other user-specified input parameters. 

Note: 

A SQL tuning set (STS) is a database object that includes one or more SQL statements 

along with their execution statistics and execution context, and could include a user priority 

ranking. You can load SQL statements into a SQL tuning set from different SQL sources, 

such as AWR, the shared SQL area, or customized SQL provided by the user. An STS 

includes: 

A set of SQL statements 

Associated execution context, such as user schema, application module name and action, list of bind values, and the cursor compilation environment Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk 

reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost, and the command type Associated execution plans and row source statistics for each SQL statement (optional). Reference: Oracle Database Performance Tuning Guide, Managing SQL Tuning Sets 

Q32. You are working on a database that supports an OLTP workload. You see a large number of hard parses occurring and several almost identical SQL statements in the library cache that vary only in the literal values in the WHERE clause conditions. 

Which two methods can you use to reduce hard parsing? 

A. Replace literals with bind variables and evolve a baseline for the statement. 

B. Use the RESULT_CACHE hint in the queries. 

C. Create baselines for the almost identical SQL statement by manually loading them from the cursor cache. 

D. Set the CURSOR_SHARING parameter to SIMILAR. 

Answer: A,D 

Explanation: A: We can reduce this Hard parsing by using bindvariables 

D: SIMILAR Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized. 

Note: A hard parse is when your SQL must be re-loaded into the shared pool. A hard parse is worse than a soft parse because of the overhead involved in shared pool RAM allocation and memory management. Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated. Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables. See the cursor_sharing parameter for a easy way to make SQL reentrant and remember that you should always use host variables in you SQL so that they can be reentrant. 

Reference: Oracle Database Reference, CURSOR_SHARING 

Q33. You are administering a database supporting an OLTP application. The application runs a series of extremely similar queries the MYSALES table where the value of CUST_ID changes. 

Examine Exhibit1 to view the query and its execution plan. 

Examine Exhibit 2 to view the structure and indexes for the MYSALES table. The MYSALES table has 4 million records. 

Data in the CUST_ID column is highly skewed. Examine the parameters set for the instance: 

Which action would you like to make the query use the best plan for the selectivity? 

A. Decrease the value of the OPTIMIZER_DYNAMIC_SAMPLING parameter to 0. 

B. Us the /*+ INDEX(CUST_ID_IDX) */ hint in the query. 

C. Drop the existing B* -tree index and re-create it as a bitmapped index on the CUST_ID column. 

D. Collect histogram statistics for the CUST_ID column and use a bind variable instead of literal values. 

Answer:

Explanation: Using Histograms 

In some cases, the distribution of values within a column of a table will affect the optimizer's decision to use an index vs. perform a full-table scan. This scenario occurs when the value with a where clause has a disproportional amount of values, making a full-table scan cheaper than index access. 

A column histogram should only be created when we have data skew exists or is suspected. 

Q34. You are administering a database supporting a DDS workload in which some tables are updated frequently but not queried often. You have SQL plan baseline for these tables and you do not want the automatic maintenance task to gather statistics for these tables regularly. 

Which task would you perform to achieve this? 

A. Set the INCREMENTAL statistic preference FALSE for these tables. 

B. Set the STALE_PERCENT static preference to a higher value for these tables. 

C. Set the GRANULARITY statistic preference to AUTO for these tables. 

D. Set the PUBLISH statistic preference to TRUE for these tables. 

Answer:

Explanation: With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects. 

STALE_PERCENT - This value determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered. The default value is 10%. 

Reference: Oracle Database PL/SQL Packages and Types Reference 

Q35. You are administering a database that supports an OLTP workload. Automatic optimizer statistics collection is scheduled in the night maintenance window. Some of the tables get updated frequently during day time and you notice a performance degradation for queries using those tables due to stale statistics. 

Which two options might help to avoid the performance degradation of the queries? 

A. Set the global statistics preference STALE_PERCENT to 0. 

B. Use dynamically sampling hint for the queries on frequently updated tables. 

C. Create histogram for the columns used frequently in the WHERE clause. 

D. Gather statistics with global statistics preference NO_VALIDATE to TRUE. 

E. Set the OPTIMZER_USE_PENDING_STATISTICS parameter to TRUE. 

Answer: B,C 

Explanation: B: Dynamic sampling first became available in Oracle9i Database Release 2. It is the ability of the cost-based optimizer (CBO) to sample the tables a query references during a hard parse, to determine better default statistics for unanalyzed segments, and to verify its “guesses.” This sampling takes place only at hard parse time and is used to dynamically generate better statistics for the optimizer to use, hence the name dynamic sampling. 

There are two ways to use dynamic sampling: 

The OPTIMIZER_DYNAMIC_SAMPLING parameter can be set at the database instance level and can also be overridden at the session level with the ALTER SESSION command. The DYNAMIC_SAMPLING query hint can be added to specific queries. 

C: A histogram is a collection of information about the distribution of values within a column.In some cases, the distribution of values within a column of a table will affect the optimizer's decision to use an index vs. perform a full-table scan. This scenario occurs when the value with a where clause has a disproportional amount of values, making a full-table scan cheaper than index access. Histograms are also important for determinine the optimal table join order. 

Incorrect: 

A: Too much statistics would be gathered. 

Note: STALE_PERCENT - This value determines the percentage of rows in a table that 

have to change before the statistics on that table are deemed stale and should be 

regathered. The default value is 10%. 

D: In Oracle PL/SQL, the VALIDATE keyword defines the state of a constraint on a column 

in a table. 

E: OPTIMZER_USE_PENDING_STATISTICS specifies whether or not the optimizer uses 

pending statistics when compiling SQL statements. 

Q36. A database instance is configured in the shared server mode and it supports multiple applications running on a middle tier. These applications connect to the database by using different services and tracing is enabled for the services. You want to view the detailed tracing setting for particular service. 

What would you use to view the tracing information? 

A. DBMS_SERVICE package 

B. DBMS_MONITOR package 

C. DBA_ENABLED_TRACES view 

D. Trcsess and tkprof 

Answer:

Explanation: DBA_ENABLED_TRACES displays information about enabled SQL traces. 

Incorrect: 

A: The DBMS_SERVICE package lets you create, delete, activate, and deactivate services for a single instance. 

B: The DBMS_MONITOR package let you use PL/SQL for controlling additional tracing and statistics gathering. 

Reference: Oracle Database Reference, DBA_ENABLED_TRACES 

Q37. You executed the following statements: 

Which two statements are true about the query execution? 

A. The execution plan is generated and fetched from the library cache. 

B. The query executes and displays the execution plan and statistics. 

C. The query executes and inserts the execution plan in PLAN_TABLE. 

D. The query executes and execution plan is stored in the library cache and can be viewed using v$SQL_PLAN. 

E. The query will always use the plan displayed by the AUTOTRACE output. 

Answer: B,D 

Explanation: B: set autotrace traceonly:Displays the execution plan and the statistics (as 

set autotrace on does), but doesn't print a query's result. 

Note: 

/ Autotrace 

Autotrace can be configured to run the SQL & gives a plan and statistics afterwards or just 

give you an explain plan without executing the query. To achieve this use the following: 

*

 Explain only set autotrace traceonly explain 

*

 Execute with stats and explain plan 

set autotrace on explain stat (with data returned by query) 

or 

autotrace traceo expl stat (without data returned by query) 

*

 To make the output from an autotrace more readable col plan_plus_exp format a100 

*

 Turn off autotrace set autotrace off 

/ V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache. http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3054.htm#REFRN302 

Q38. You instance has these parameter settings: 

Which three statements are true about these settings if no hints are used in a SQL statement? 

A. A statement estimated for more than 10 seconds always has its degree of parallelism computed automatically. 

B. A statement with a computed degree of parallelism greater than 8 will be queued for a maximum of 10 seconds. 

C. A statement that executes for more than 10 seconds always has its degree of parallelism computed automatically. 

D. A statement with a computed degree of parallelism greater than 8 will raise an error. 

E. A statement with any computed degree of parallelism will be queued if the number of busy parallel execution processes exceeds 64. 

F. A statement with a computed degree of parallelism of 20 will be queued if the number of available parallel execution processes is less 5. 

Answer: C,E,F 

Explanation: C (not A): PARALLEL_MIN_TIME_THRESHOLD specifies the minimum execution time a statement should have before the statement is considered for automatic degree of parallelism. By default, this is set to 30 seconds. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED. 

PARALLEL_DEGREE_LIMIT integer 

A numeric value for this parameter specifies the maximum degree of parallelism the optimizer can choose for a SQL statement when automatic degree of parallelism is active. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED. 

E: PARALLEL_SERVERS_TARGET specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used. When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that require parallel execution, if the necessary parallel server processes are not available. Statement queuing will begin once the number of parallel server processes active on the system is equal to or greater than PARALLEL_SERVER_TARGET. 

F: PARALELL_MIN_MINPERCENT PARALLEL_MIN_PERCENT operates in conjunction with PARALLEL_MAX_SERVERS and PARALLEL_MIN_SERVERS. It lets you specify the minimum percentage of parallel execution processes (of the value of PARALLEL_MAX_SERVERS) required for parallel execution. Setting this parameter ensures that parallel operations will not execute sequentially unless adequate resources are available. The default value of 0 means that no minimum percentage of processes has been set. Consider the following settings: PARALLEL_MIN_PERCENT = 50 PARALLEL_MIN_SERVERS = 5 PARALLEL_MAX_SERVERS = 10 

If 8 of the 10 parallel execution processes are busy, only 2 processes are available. If you then request a query with a degree of parallelism of 8, the minimum 50% will not be met. 

Note: With automatic degree of parallelism, Oracle automatically decides whether or not a statement should execute in parallel and what degree of parallelism the statement should use. The optimizer automatically determines the degree of parallelism for a statement based on the resource requirements of the statement. However, the optimizer will limit the degree of parallelism used to ensure parallel server processes do not flood the system. This limit is enforced by PARALLEL_DEGREE_LIMIT. 

Values: 

CPU 

IO 

integer 

A numeric value for this parameter specifies the maximum degree of parallelism the optimizer can choose for a SQL statement when automatic degree of parallelism is active. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED. 

Reference: PARALLEL_MIN_TIME_THRESHOLD PARALLEL_DEGREE_LIMIT PARALELL_MIN_MINPERCENT PARALELL_SERVERS_TARGET 

Q39. View the exhibit and examine the query and its execution plan from the PLAN_TABLE. 

Which statement is true about the execution? 

A. The row with the ID column having the value 0 is the first step execution plan. 

B. Rows are fetched from the indexes on the PRODUCTS table and from the SALES table using full table scan simultaneously, and then hashed into memory. 

C. Rows are fetched from the SALES table, and then a hash join operator joins with rows fetched from indexes on the PRODUCTS table. 

D. All the partitions of the SALES table are read in parallel. 

Answer:

Q40. Which two types of column filtering may benefit from partition pruning? 

A. Equally operates on range-partitioned tables. 

B. In-list operators on system-partitioned tables 

C. Equality operators on system-partitioned tables 

D. Operators on range-partitioned tables E. Greater than operators on hash-partitioned tables 

Answer: A,D 

Explanation: The query optimizer can perform pruning whenever a WHERE condition can be reduced to either one of the following two cases: 

partition_column = constant 

partition_column IN (constant1, constant2, ..., constantN) 

In the first case, the optimizer simply evaluates the partitioning expression for the value given, determines which partition contains that value, and scans only this partition. In many cases, the equal sign can be replaced with another arithmetic comparison, including <, >, <=, >=, and <>. Some queries using BETWEEN in the WHERE clause can also take advantage of partition pruning. 

Note: 

*

 The core concept behind partition pruning is relatively simple, and can be described as “Do not scan partitions where there can be no matching values”. 

When the optimizer can make use of partition pruning in performing a query, execution of the query can be an order of magnitude faster than the same query against a nonpartitioned table containing the same column definitions and data. 

*

 Example: 

Suppose that you have a partitioned table t1 defined by this statement: 

CREATE TABLE t1 ( 

fname VARCHAR(50) NOT NULL, 

lname VARCHAR(50) NOT NULL, 

region_code TINYINT UNSIGNED NOT NULL, 

dob DATE NOT NULL 

PARTITION BY RANGE( region_code ) ( 

PARTITION p0 VALUES LESS THAN (64), 

PARTITION p1 VALUES LESS THAN (128), 

PARTITION p2 VALUES LESS THAN (192), 

PARTITION p3 VALUES LESS THAN MAXVALUE 

); 

Consider the case where you wish to obtain results from a query such as this one: 

SELECT fname, lname, region_code, dob 

FROM t1 WHERE region_code > 125 AND region_code < 130; It is easy to see that none of the rows which ought to be returned will be in either of the partitions p0 or p3; that is, we need to search only in partitions p1 and p2 to find matching rows. By doing so, it is possible to expend much less time and effort in finding matching rows than would be required to scan all partitions in the table. This“cutting away” of unneeded partitions is known as pruning.