1 08 2014

QUESTION 1
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: CEF

QUESTION 2
 
Examine the following SQL statement:
 
Examine the exhibit to view the execution plan. Which statement is true about the execution plan?

A.    The EXPLAIN PLAN generates the execution plan and stores it in c$SQL_PLAN after executing the query. Subsequent executions will use the same plan.
B.    The EXPLAIN PLAN generates the execution plan and stores it in PLAN_TABLE without executing the query. Subsequent executions will always use the same plan.
C.    The row with the ID 3 is the first step executed in the execution plan.
D.    The row with the ID 0 is the first step executed in the execution plan.
E.    The rows with the ID 3 and 4 are executed simultaneously.

Answer: E
Explanation:
Note the other_tag parallel in the execution plan.
Within the Oracle plan_table, we see that Oracle keeps the parallelism in a column called other_tag. The other_tag column will tell you the type of parallel operation that is being performed within your query.
For parallel queries, it is important to display the contents of the other_tag in the execution.

QUESTION 3
Which two types of SQL statements will benefit from dynamic sampling?

A.    SQL statements that are executed parallel
B.    SQL statement that use a complex predicate expression when extended statistics are not available.
C.    SQL statements that are resource-intensive and have the current statistics
D.    SQL statements with highly selective filters on column that has missing index statistics
E.    Short-running SQL statements

Answer: BD
Explanation:
B: One scenario where DS is used is when the statement contains a complex predicate expression and extended statistics are not available. Extended statistics were introduced in Oracle Database 11g Release 1 with the goal to help the optimizer get good quality cardinality estimates for complex predicate expressions.
D: DS It is typically used to compensate for missing or insufficient statistics that would otherwise lead to a very bad plan.
Note:
* Dynamic sampling (DS) was introduced in Oracle Database 9i Release 2 to improve the optimizer’s ability to generate good execution plans.
* During the compilation of a SQL statement, the optimizer decides whether to use DS or not by considering whether the available statistics are sufficient to generate a good execution plan. If the available statistics are not enough, dynamic sampling will be used. It is typically used to compensate for missing or insufficient statistics that would otherwise lead to a very bad plan. It is typically used to compensate for missing or insufficient statistics that would otherwise lead to a very bad plan. For the case where one or more of the tables in the query does not have statistics, DS is used by the optimizer to gather basic statistics on these tables before optimizing the statement. The statistics gathered in this case are not as high a quality or as complete as the statistics gathered using the DBMS_STATS package. This trade off is made to limit the impact on the compile time of the statement.

QUESTION 4
Examine the following query and execution plan:
 
Which query transformation technique is used in this scenario?

A.    Join predicate push-down
B.    Subquery factoring
C.    Subquery unnesting
D.    Join conversion

Answer: A
Explanation:
* Normally, a view cannot be joined with an index-based nested loop (i.e., index access) join, since a view, in contrast with a base table, does not have an index defined on it. A view can only be joined with other tables using three methods: hash, nested loop, and sort-merge joins.
* The following shows the types of views on which join predicate pushdown is currently supported.
UNION ALL/UNION view
Outer-joined view
Anti-joined view
Semi-joined view
DISTINCT view
GROUP-BY view
QUESTION 5
You enabled auto degree of parallelism (DOP) for your instance. Examine the query:
 
Which two are true about the execution of this query?

A.    Dictionary DOP will be used, if present, on the tables referred in the query.
B.    DOP is calculated if the calculated DOP is 1.
C.    DOP is calculated automatically.
D.    Calculated DOP will always by 2 or more.
E.    The statement will execute with auto DOP only when PARALLEL_DEGREE_POLICY is set to AUTO.

Answer: CD
Explanation:
C: * You can use the PARALLEL hint to force parallelism. It takes an optional parameter: the DOP at which the statement should run. In addition, theNO_PARALLEL hint overrides a PARALLEL parameter in the DDL that created or altered the table.
The following example illustrates computing the DOP the statement should use:
 
Not A: to override the dictionary dop, we could use hints at object level Not E: statement hints override the PARALLEL_DEGREE_POLICY.
Note:
* Automatic Parallel Degree Policy
When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database automatically decides if a statement should execute in parallel or not and what DOP it should use. Oracle Database also determines if the statement can be executed immediately or if it is queued until more system resources are available. Finally, Oracle Database decides if the statement can take advantage of the aggregated cluster memory or not.
The following is a summary of parallel statement processing when parallel degree policy is set to automatic.
1. A SQL statement is issued.
2. The statement is parsed and the optimizer determines the execution plan.
3. The threshold limit specified by the PARALLEL_MIN_TIME_THRESHOLD initialization parameter is checked.
a. If the execution time is less than the threshold limit, the SQL statement is run serially.
b. If the execution time is greater than the threshold limit, the statement is run in parallel based on the DOP that the optimizer calculates.

QUESTION 6
View Exhibit1 and examine the structure and indexes for the MYSALES table.
 
The application uses the MYSALES table to insert sales record. But this table is also extensively used for generating sales reports. The PROD_ID and CUST_ID columns are frequently used in the WHERE clause of the queries. These columns have few distinct values relative to the total number of rows in the table. The MYSALES table has 4.5 million rows. View exhibit 2 and examine one of the queries and its autotrace output.
 
Which two methods can examine one of the queries and its autotrace output?

A.    Drop the current standard balanced B* Tree indexes on the CUST_ID and PROD_ID columns and re-create as bitmapped indexes.
B.    Use the INDEX_COMBINE hint in the query.
C.    Create a composite index involving the CUST_ID and PROD_ID columns.
D.    Rebuild the index to rearrange the index blocks to have more rows per block by decreasing the value for PCTFRE attribute.
E.    Collect histogram statistics for the CUST_ID and PROD_ID columns.

Answer: BC
Explanation:
B: The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B-tree, bitmap, and bitmap join indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for the combination of multiple indexes, because it is a more versatile hint.
C: Combining the CUST_ID and PROD_ID columns into an composite index would improve performance.

QUESTION 7
Examine the Exhibit to view the structure of an indexes for the SALES table.
 
The SALES table has 4594215 rows. The CUST_ID column has 2079 distinct values. What would you do to influence the optimizer for better selectivity?

A.    Drop bitmap index and create balanced B*Tree index on the CUST_ID column.
B.    Create a height-balanced histogram for the CUST_ID column.
C.    Gather statistics for the indexes on the SALES table.
D.    Use the ALL_ROWS hint in the query.

Answer: D
Explanation:
OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.
Values:
FIRST_ROWS_N – The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000). FIRST_ROWS – The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
ALL_ROWS – The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

QUESTION 8
Which three are tasks performed in the hard parse stage of a SQL statement executions?

A.    Semantics of the SQL statement are checked.
B.    The library cache is checked to find whether an existing statement has the same hash value.
C.    The syntax of the SQL statement is checked.
D.    Information about location, size, and data type is defined, which is required to store fetched values in variables.
E.    Locks are acquired on the required objects.

Answer: BDE
Explanation:
Parse operations fall into the following categories, depending on the type of statement submitted and the result of the hash check:
A) Hard parse
If Oracle Database cannot reuse existing code, then it must build a new executable version of the application code. This operation is known as a hard parse, or a library cache miss. The database always perform a hard parse of DDL.
During the hard parse, the database accesses the library cache and data dictionary cache numerous times to check the data dictionary. When the database accesses these areas, it uses a serialization device called a latch on required objects so that their definition does not change (see "Latches"). Latch contention increases statement execution time and decreases concurrency.
B) Soft parse
A soft parse is any parse that is not a hard parse. If the submitted statement is the same as a reusable SQL statement in the shared pool, then Oracle Database reuses the existing code. This reuse of code is also called a library cache hit.
Soft parses can vary in the amount of work they perform. For example, configuring the session cursor cache can sometimes reduce the amount of latching in the soft parses, making them "softer." In general, a soft parse is preferable to a hard parse because the database skips the optimization and row source generation steps, proceeding straight to execution.
Incorrect:
A, C: During the parse call, the database performs the following checks:
Syntax Check
Semantic Check
Shared Pool Check
The hard parse is within Shared Pool check.
Reference: Oracle Database Concepts 11g, SQL Parsing

QUESTION 9
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: D
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.

QUESTION 10
Which two are the fastest methods for fetching a single row from a table based on an equality predicate?

A.    Fast full index scan on an index created for a column with unique key
B.    Index unique scan on an created for a column with unique key
C.    Row fetch from a single table hash cluster
D.    Index range scan on an index created from a column with primary key
E.    Row fetch from a table using rowid

Answer: CE
Explanation:
A scan is slower than a row fetch (from hash value or rowid).

QUESTION 11
An application user complains about statement execution taking longer than usual. You find that the query uses a bind variable in the WHERE clause as follows:
 
You want to view the execution plan of the query that takes into account the value in the bind variable PCAT. Which two methods can you use to view the required execution plan?

A.    Use the DBMS_XPLAN.DISPLAY function to view the execution plan.
B.    Identify the SQL_ID for the statementsand use DBMS_XPLAN.DISPLAY_CURSOR for that SQL_ID to view the execution plan.
C.    Identify the SQL_ID for the statement and fetch the execution plan PLAN_TABLE.
D.    View the execution plan for the statement from V$SQL_PLAN.
E.    Execute the statement with different bind values and set AUTOTRACE enabled for session.

Answer: BD
Explanation:
D: V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.
B: The DBMS_XPLAN package supplies five table functions:
DISPLAY_SQL_PLAN_BASELINE – to display one or more execution plans for the SQL statement identified by SQL handle
DISPLAY – to format and display the contents of a plan table. DISPLAY_AWR – to format and display the contents of the execution plan of a stored SQL statement in the AWR.
DISPLAY_CURSOR – to format and display the contents of the execution plan of any loaded cursor. DISPLAY_SQLSET – to format and display the contents of the execution plan of statements stored in a SQL tuning set.
Passing your Oracle 1Z0-117 Exam by using the latest 1Z0-117 Exam Demo Full Version: http://www.braindump2go.com/1z0-117.html

1Z0-117 Exam Dumps, 1Z0-117 Exam Questions, 1Z0-117 Practice Exam, 1Z0-117 Practice Test, Oracle 1Z0-117 Exam

2014 Latest Oracle 1Z0-108 Exam Dump Free Download! 2014 Latest Oracle 1Z0-140 Exam Dump Free Download!