You are administering a database, where an application frequently executes identical SQL statements with the same syntax.
How will you optimize the query results without retrieving data blocks from the storage?
A. By setting the CURSOR_SHARING parameter to FORCE.
B. By using the bind variables and setting the CURSOR_SHARING parameter to EXACT.
C. By using the CACHE hint to pin the queries in the library cache
D. By ensuring that RESULT_CACHE_MODE parameter is set to MANUAL and using the RESULT_CACHE hint in the queries.
E. By creating a SQL plan baseline for the identical statements.
Correct Answer: D
As its name suggests, the query result cache is used to store the results of SQL queries for re-use in subsequent executions. By caching the results of queries,
Oracle can avoid having to repeat the potentially time-consuming and intensive operations that generated the resultset in the first place (for example, sorting/
aggregation, physical I/O, joins etc). The cache results themselves are available across the instance (i.e. for use by sessions other than the one that first executed
the query) and are maintained by Oracle in a dedicated area of memory. Unlike our homegrown solutions using associative arrays or global temporary tables, the
query result cache is completely transparent to our applications. It is also maintained for consistency automatically, unlike our own caching programs.
Note:
RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query's execution plan.
Values:
MANUAL
The ResultCache operator is added only when the query is annotated (that is, hints).
FORCE
The ResultCache operator is added to the root of all SELECT statements (provided that it is valid to do so).
For the FORCE setting, if the statement contains a NO_RESULT_CACHE hint, then the hint takes precedence over the parameter setting.
Incorrect:
A, B: CURSOR_SHARING determines what kind of SQL statements can share the same cursors.
Values:
FORCE
Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
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.
EXACT
Only allows statements with identical text to share the same cursor.
C: The Oracle library cache is a component of the System Global Area (SGA) shared pool. Similarly to other Oracle cache structures, the point of the library cache is to reduce work and therefore to improve performance by caching the result of parsing and optimizing SQL or PL/SQL so that subsequent executions of the same SQL or PL/SQL require fewer preparatory steps to deliver a query result.
Question 12:
Identify two situations in which full table scans will be faster than index range scans.
A. A query with a highly selective filter fetching less than 5 percent of the rows from a table.
B. A highly selective query on a table having high clustering factor for an index.
C. A query fetching less number of blocks than value specified by DB_FILE_MULTIBLOCK_READ_COUNT.
D. A query executing in parallel on a partitioned table with partitioned indexes.
E. A query on a table with sparsely populated table blocks.
Correct Answer: CD
D: DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.
Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.
Note:
* See 6) and 7) below.
The oracle optimizer choose the best plan and execute the query according the plan. It is common to hear that my table has indexes but why oracle does not use
indexes rather it is using full table scan.
There are several reasons behind choosing optimizer full table scans.
1)The table has no indexes within it.
2)Table has indexes but they are not appropriate to queries. For example in the table there is normal B- tree indexes but in the query the column used in the
WHERE clause contains function.
3)Query access large amount of data. The table has indexes but query against it select almost all of the rows. In that case optimizer might choose to full access of
table.
4)Index creation order may not appropriate. You have composite indexes on a table but in the where clause the leading column inside indexes are not used rather
trailing columns are used.
5)The table is skewed. For example column gender contains value 'M' 10,000 times but value 'F' only 10 times.
6)The table is small. If a table can read in a single I/O call, then a full table scan might be cheaper than an index range scan. Single I/O call is defined by
DB_FILE_MULTIBLOCK_READ_COUNT parameter and value defined by blocks.
7)High degree of parallelism. High degree of parallelism skews the optimizer toward full table scans.
8)In the query if there is no filtering then full table scan is the choice.
If an index has poor cardinality (ie. more than 4% rows with the same index key) *
then it will perform poorly. It will usually be faster to perform a full table scan. eg. Table SALES has an index on the column PAYMENT_METHOD which can
contain values such as COD, CREDIT, CHEQUE, CASH. The statement
SELECT *
FROM sales
WHERE payment_method = 'CASH'
will probably perform so badly that you are better off without the index.
* Oracle uses the full table scan as it assumes that it will have to read a certain part of the table.
You identified some DSS queries that perform expensive join and aggregation operations.
The queries access historical data from noncurrent partition of the fact tables.
What three actions could you perform to improve the response time of the queries without modifying the SQL statements?
A. Set the QUERY_REWRITE_ENABLED to TRUE at the session level.
B. Create an STS for the statements, run SQL Tuning Advisor for the STS, and implement any generated recommendations for materialized views.
C. Set QUERY_REWRITE_ENABLED to TRUE at the instance level.
D. Create an STS for the statements, run SQL Access Advisor for the STS, and implement any generated recommendations for materialized views.
E. Set QUERY_REWRITE_INTEGRITY to ENFORCED at the instance level.
Correct Answer: BCD
A: * QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally for the database. Values:
false
Oracle does not use rewrite.
true
Oracle costs the query with rewrite and without rewrite and chooses the method with the lower cost.
force
Oracle always uses rewrite and does not evaluate the cost before doing so. Use force when you know that the query will always benefit from rewrite and when
reduction in compile time is important.
To take advantage of query rewrite for a particular materialized view, you must enable query rewrite for that materialized view, and you must enable cost-based
optimization.
C: You can use SQL Tuning Advisor to tune one or more SQL statements
D: Using the SQL Access Advisor Wizard or API, you can do the following:
Recommend materialized views and indexes based on collected or hypothetical workload information.
Manage workloads.
Mark, update, and remove recommendations.
Note:
*
STS SQL tuning set.
*
A SQL Tuning Set is a database object that includes one or more SQL statements and their execution statistics and execution context. You can use the set as an input source for various advisors, such as SQL Tuning Advisor, SQL Access Advisor, and SQL Performance Analyzer.
Incorrect:
E: QUERY_REWRITE_INTEGRITY determines the degree to which Oracle must enforce query rewriting. At the safest level, Oracle does not use query rewrite transformations that rely on unenforced relationships.
Values:
enforced
Oracle enforces and guarantees consistency and integrity.
trusted
Oracle allows rewrites using relationships that have been declared, but that are not enforced by Oracle.
stale_tolerated
Oracle allows rewrites using unenforced relationships. Materialized views are eligible for rewrite even if they are known to be inconsistent with the underlying detail
data
Question 14:
Examine the following command:
Which query transformation technique is used by the optimizer in this case?
A. View merging
B. Filter push-down
C. Predicate pushing
D. Predicate move-around
Correct Answer: C
In predicate pushing, the optimizer "pushes" the relevant predicates from the containing query block into the view query block. For views that are not merged, this technique improves the subplan of the unmerged view because the database can use the pushed-in predicates to access indexes or to use as filters.
For example, suppose you create a view that references two employee tables. The view is defined with a compound query that uses the UNION set operator, as follows:
CREATE VIEW all_employees_vw AS
( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees )
UNION
( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers );
You then query the view as follows:
SELECT last_name
FROM all_employees_vw
WHERE department_id = 50;
Because the view is a compound query, the optimizer cannot merge the view's query into the accessing query block. Instead, the optimizer can transform the
accessing statement by pushing its predicate, the WHERE clause condition department_id=50, into the view's compound query.
The equivalent transformed query is as follows:
SELECT last_name
FROM ( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees
WHERE department_id=50
UNION
SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers
Examine the parallelism parameters for your instance:
Examine the Exhibit to view the query and its explain plan output.
All sessions use default parallelism settings.
What two steps could you take to make the query execute in parallel?
A. Add a parallel hint.
B. Decrease the value of PARALLEL_MIN_TIMETHRESHOLD.
C. Increase the value of PARALLEL_MIN_SERVERS.
D. Increase the value of PARALLEL_MAX_SERVERS.
E. Decrease the value of PARALLEL_MIN_PERCENT.
Correct Answer: AB
A: You can rely on hints to set the degree of parallelism, but these can be hard to set correctly.
B:
Decision tree for query parallelization
Question 16:
See the code fragment:
You receive the following error message:
ORA-12827: insufficient parallel query slaves available
Which three parameter settings could you change to avoid this error?
A. Decrease the value of PARALLEL_MIN_PERCENT
B. Increase the value of PARALLEL_MAX_SERVERS
C. Increase the value of PARALLEL_MIN_SERVERS
D. Reduce the value of PARALLEL_MIN_TIME_THRESHOLF
E. Increase the value of PARALLEL_DEGREE_LIMIT
F. Set the PARALLEL_DEGREE_POLICY = AUTO
G. Set the PARALLEL_DEGREE_POLICY = LIMITED
Correct Answer: ABG
A: ORA-12827: insufficient parallel query slaves available Cause: PARALLEL_MIN_PERCENT parameter was specified and fewer than minimum slaves were acquired Action: either re-execute query with lower PARALLEL_MIN_PERCENT or wait until some running queries are completed, thus freeing up slaves
B: Your query doesn't run because you've told Oracle not to run it unless at least 5% of the parallel execution processes are available for your query.
Set PARALLEL_MIN_PERCENT=0 or increase the number of parallel execution processes by increasing the PARALLEL_MAX_SERVERS parameter.
G: PARALLEL_DEGREE_POLICY
PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism, statement queuing, and in-memory parallel execution will be enabled.
LIMITED
Enables automatic degree of parallelism for some statements but statement queuing and in- memory Parallel Execution are disabled. Automatic degree of parallelism is only applied to those statements that access tables or indexes decorated explicitly with the PARALLEL clause. Tables and indexes that have a degree of parallelism specified will use that degree of parallelism.
Note: 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.
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.
You can use this parameter in conjunction with PARALLEL_ADAPTIVE_MULTI_USER. In a multi-user environment, an individual user or application can set PARALLEL_MIN_PERCENT to a minimum value until sufficient resources are available on the system and an acceptable degree of parallelism is returned.
Question 17:
Examine the Exhibit.
Given two sets of parallel execution processes, SS1 and SS2, which is true?
A. Each process SS1 reads some of the rows from the CUSTOMERS table and sends all the rows it reads to each process in SS2.
B. Each process in SS1 reads all the rows from the CUSTOMERS table and distributes the rows evenly among the processes in SS2.
C. Each process in SS1 reads some of the rows from the SALES table and sends all the rows it reads to each process in SS2.
D. Each process in SS1 reads all the rows from the SALES table and distributes the rows evenly among the processes in SS2.
E. Each process in SS1 reads some of the rows from the SALES table and distributes the rows evenly among the processes in SS2.
F. Each process in the SS1 reads some of the rows from the CUSTOMERS table and distributes the rows evenly among the processes in SS2.
Correct Answer: D
Note:
*
The execution starts with line 16 (accessing the SALES table), followed by line 15.
*
PX BLOCKITERATOR The PX BLOCK ITERATOR row source represents the splitting up of the table EMP2 into pieces so as to divide the scan workload between the parallel scan slaves. The PX SEND and PX RECEIVE row sources represent the pipe that connects the two slave sets as rows flow up from the parallel scan, get repartitioned through the HASHtable queue, and then read by and aggregated on the top slave set.
Question 18:
Your database supports a workload consisting of three categories of SQL statements:
Statements that should execute in less than one minute
Statement that may execute for up to 15 minutes
Statements that may be executed for more than 15 minutes
You set PARALLEL_DEGREE_POLICY to Auto.
You plan to prioritize queued statements by using the Database Resource manager.
Which two are true about parallelism prioritization by a consumer group?
A. PARALLEL_TARGET_PERCENTAGE is used to prioritize a consumer group's use of the overall PARALLEL_SERVER_TARGET.
B. Queuing is done for a consumer group exceeding its percentage, even if the number of busy PX servers in the instance has not reached PARALLEL_SERVERS_TARGET.
C. PARALLEL_TARGET_PERCENTAGE us used to prioritize a consumer group's use of the overall SPAN CLASS = `OracleCode'> PARALLEL_MAX_SERVERS.
D. Having separate queues for consumer groups requires the use of management attributes (MGMT_P1, MGMT_P2 etc. . . )
E. Separate queue timeout using PARALLEL_QUEUE_TIMEOUT require the use of management attributes (MGMT_P1, MGMT_P2 etc . . . groups)
Correct Answer: AD
A: Parallel Target Percentage
It is possible for a single consumer group to launch enough parallel statements to use all the available parallel servers. If this happens, when a high-priority parallel statement from a different consumer group is run, no parallel servers are available to allocate to this group. You can avoid such a scenario by limiting the number of parallel servers that can be used by a particular consumer group.
Use the PARALLEL_TARGET_PERCENTAGE directive attribute to specify the maximum percentage of the parallel server pool that a particular consumer group can use. The number of parallel servers used by a particular consumer group is counted as the sum of the parallel servers used by all sessions in that consumer group.
Incorrect:
Not B: 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. Not C: Would be true if we replace PARALLEL_MAX_SERVERS with PARALLEL_SERVER_TARGET. Not E: The PARALLEL_QUEUE_TIMEOUT directive attribute enables you to specify the maximum time, in seconds, that a parallel statement can wait in the parallel statement queue before it is timed out. The PARALLEL_QUEUE_TIMEOUT attribute can be set for each consumer group. This attribute is applicable even if you do not specify other management attributes (mgmt_p1, mgmt_p2, and so on) in your resource plan. Note:
*
PARALLEL_DEGREE_POLICY AUTO Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.
*
The PARALLEL_TARGET_PERCENTAGE attribute enables you to specify when parallel statements from a consumer group can be queued. Oracle Database maintains a separate parallel statement queue for each consumer group.
*
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.
By default, PARALLEL_SERVER_TARGET is set lower than the maximum number of parallel server processes allowed on the system (PARALLEL_MAX_SERVERS) to ensure each parallel statement will get all of the parallel server resources required and to prevent overloading the system with parallel server processes.
Note that all serial (non-parallel) statements will execute immediately even if statement queuing has been activated.
* Oracle Database Resource Manager (the Resource Manager) enables you to optimize resource allocation among the many concurrent database sessions.
The elements of the Resource Manager are:
/ Resource consumer group A group of sessions that are grouped together based on resource requirements. The Resource Manager allocates resources to resource consumer groups, not to individual sessions.
/ Resource plan A container for directives that specify how resources are allocated to resource consumer groups. You specify how the database allocates resources by activating a specific resource plan.
/ Resource plan directive Associates a resource consumer group with a particular plan and specifies how resources are to be allocated to that resource consumer group.
Question 19:
Which two statements are true about the use of the DYNAMIC_SAMPLING hint in a query?
A. It estimates selectivity better for the filters.
B. It is always used for flashback queries that contain the AS OF clause.
C. It cannot be used if there is a single-table predicate in the WHERE clause.
D. It cannot be used for processing SQL statements in parallel.
E. It can compensate for the lack of extended statistics to get accurate cardinality estimates for complex predicate expressions.
Correct Answer: DE
D: For parallel statements, the optimizer automatically decides whether to use dynamic sampling and which level to use. The decision depends on the size of the tables and the complexity of the predicates. The optimizer expects parallel statements to be resource-intensive, so the additional overhead at compile time is worth it to ensure the best plan. The database ignores the For serially processed SQL statements, the dynamic sampling level depends on the value of the OPTIMIZER_DYNAMIC_SAMPLING parameter and is not triggered automatically by the optimizer. Serial statements are typically short-running, so that any overhead at compile time could have a huge impact on their performance. the value is honored.
Reference: Oracle Database Administrator's Guide, About Oracle Database Resource Manager
Question 20:
Examine the Exhibit to view the structure of and indexes for the EMPLOYEES and DEPARTMENTS tables:
EXAMINE the SQL statement and its execution plan:
Which two statements are correct regarding the execution plan?
A. Step 2 is performing nested operation on JOB_ID column of the JOBS table, which is the driven table and the EMPLOYEES table is the driven table.
B. In step 2 for every row returned by the JOBS table matching rows from the EMPLOYEES table are accessed.
C. Step 1 is performing nested loop operation on the DEPARTMENT_ID column of the DEPARTMENTS table, which is the driven table and results returned by step 2 in the driving resultset.
D. The performance of the query can be improved by creating bitmap index on the JOB_ID column of the EMPLOYEES table.
E. The performance of the query can be improved by creating bitmapped index on the DEPARTMENT_ID column of the EMPLOYEES table.
Correct Answer: BE
As per exhibit:
B, not A, Not C: First is line 5 executed, followed by line 4, followed by line 3.
Step 2 is line 4.
E: The Department_ID column has lower cardinality compared to the JOB_ID column, so it is better suited for a bitmapped index.
Note:
*
Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two-dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index. This two-dimensional array represents each value within the index multiplied by the number of rows in the table.
At row retrieval time, Oracle decompresses the bitmap into the RAM data buffers so it can be rapidly scanned for matching values. These matching values are delivered to Oracle in the form of a Row-ID list, and these Row-ID values may directly access the required information.
*
The real benefit of bitmapped indexing occurs when one table includes multiple bitmapped indexes. Each individual column may have low cardinality. The creation of multiple bitmapped indexes provides a very powerful method for rapidly answering difficult SQL queries.
*
Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two-dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index. This two-dimensional array represents each value within the index multiplied by the number of rows in the table.
At row retrieval time, Oracle decompresses the bitmap into the RAM data buffers so it can be rapidly scanned for matching values. These matching values are delivered to Oracle in the form of a Row-ID list, and these Row-ID values may directly access the required information.
Nowadays, the certification exams become more and more important and required by more and more enterprises when applying for a job. But how to prepare for the exam effectively? How to prepare for the exam in a short time with less efforts? How to get a ideal result and how to find the most reliable resources? Here on Vcedump.com, you will find all the answers. Vcedump.com provide not only Oracle exam questions, answers and explanations but also complete assistance on your exam preparation and certification application. If you are confused on your 1Z0-117 exam preparations and Oracle certification application, do not hesitate to visit our Vcedump.com to find your solutions here.
