Online Access Free 1Z0-117 Exam Questions

An application accessing your database got the following error in response to SQL query: ORA-12827: insufficient parallel query slaves available
View the parallel parameters for your instance:

No hints are used and the session use default parallel settings.
What four changes could you make to help avoid the error and ensure that the query executes in parallel?

• A.Decrease PARELLEL_MIN_TIME_THRESHOLD.
• B.Increase PARELLEL__MIN_TIME_THRESHOLD.
• C.Increase PARELLEL_SERVERS_TARGET.
• D.Set PARELLEL_DEGREE_POLICY to AUTO.
• E.Decrease PARELLEL_MIN_PERCENT.
• F.Increase PARELLEL_MIN_SERVERS.
• G.Increase the value of PARELLEL_MAX_SERVERS.

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.All the partitions of the SALES table are read in parallel.
• 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.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.

Which statement is true about the usage of the STAR_TRANSFORMATION hint in a query?

• A.The optimizer always uses a plan in which the transformation is used.
• B.The optimizer always generates subqueries to transform a query.
• C.The optimizer always uses bitmap indexes on the primary key column for any dimension table to transform a query.
• D.The optimizer uses transformation only if the cost is less than a query executing without transformation.

An application frequently executed similar types of queries that vary only in the usage of literals in the WHERE clause. You plan to use bind variable in place of literal values.
The CURSOR_SHARING parameter to set to EXACT.
Which two statements are true about the usage of bind variables?

• A.Bind peeking will happen and subsequent execution of queries will have the same parent cursor but different child cursors.
• B.Bind peeking will take place and subsequent execution of queries can have different plans based on the cardinality of the bind value in the column.
• C.The number of latch gets in the library cache will be reduced.
• D.Bind peeking will take place and subsequent execution of queries can have different plans only when the histograms exists on a column used in WHERE clause.
• E.Bind peeking will not happen and the optimizer will use the same plan for all bind values if no histograms exist on a column use in the WHERE clause.

You are administering database that supports an OLTP workloads. Most of the queries use an index range scan or index unique scan as access methods.
Which three scenarios can prevent the index access being used by the queries?

• A.When the histogram statistics are not collected for the columns used in where clause.
• B.When the statistics for the table are not current.
• C.When a highly selective filter is applied on the indexed column and the index has very low value for clustering factor.
• D.When the rows are filtered with an IS NULL operator on the column with a unique key defined
• E.When highly selective filters is applied on an indexed column of a table with sparsely populated blocks.