What will certainly you get right after purchase: One. A Oracle 1Z0-117 review guide A couple of. Printable Pdf files and Examination Engine software Several. Truly on-line Oracle 1Z0-117 test Four. Interactive space with other candidates 5. Produce and take notes on the 1Z0-117 practice questions
2016 Oct 1Z0-117 free draindumps
Q71. You ran a high load SQL statement that used an index through the SQL Tuning Advisor and accepted its recommendation for SQL profile creation. Subsequently you noticed that there has been a 2% growth in number of rows in the tables used by the SQL statement and database statistics have also been refreshed.
How does this impact the created SQL profile?
A. It becomes invalid and no longer used the optimizer.
B. It remains valid and ensures that the optimizer always use the execution plan that was created before the changes happened.
C. It remains and allows the optimizer to pick a different plan required.
D. It becomes invalid and a new SQL profile is created for the statement by the auto tuning task.
Q72. Examine the Following Query and execution plan:
Which query transformation technique is used by the optimizer?
A. Filter push down
B. Subquery factoring
C. Subquery unnesting
D. Predicate pushing
* In the execution plan BX, note the keyword 'VIEW PUSHED PREDICATE' indicates that the view has undergone the join predicate pushdown transformation.
Q73. How can you reduce fragmentation of an index without affecting the current transactions that are using the index?
A. Use the ANALYZE INDEX . . . command
B. Use the ALTER INDEX . . . VALIDATE STRUCTURE command
C. Us the ALTER INDEX . . . REBUILD ONLINE command
D. Use the ALTER INDEX . . . DEALLOCATE UNUSED command
Reference: Oracle Database SQL Language Reference 11g, alter index
Q74. You are administering a database that supports a DSS workload, where in an application a set of queries use the query rewrite on materialized views. You notice that these queries are performing poorly.
Which two actions can you make to improve the performance of theses queries?
A. Use DBMS_MVIEW.EXPLAIN_REWRITE to analyze whether the queries are rewritten.
B. USE DBMS_ADVISOR.QUICK_TUNE to analyze the query rewrite usage of materialized views for the entire workload.
C. Create an STS for all the queries and use SQL performance Analyzer to generate recommendations for determining the regressed SQL statements.
D. Create an STS for all the queries in the application and use the SQL Tuning Advisor to generate recommendations.
E. Create an STS for all the queries in the application and use the SQL Access Advisor to generate a recommendation for optimizing materialized views for maximum query rewrite usage and fast refresh.
Q75. 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.
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.
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)
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
Updated 1Z0-117 test:
Q76. When would bind peeking be done for queries that vary only in values used in the WHERE clause?
A. When the column used in the WHERE clause has evenly distributed data and histogram exists on that column.
B. When the column used in the WHERE clause has evenly distributed data and index exists on that column.
C. When the column used in the WHERE clause has non uniform distribution of data, uses a bind variable, and no histogram exists for the column.
D. When the column used in the WHERE clause has non uniform distribution of data and histogram exists for the column.
Q77. Which two tasks are performed during the optimization stage of a SQL statement?
A. Evaluating the expressions and conditions in the query
B. Checking the syntax and analyzing the semantics of the statement
C. Separating the clauses of the SQL statement into structures that can be processed
D. Inspecting the integrity constraints and optimizing the query based on this metadata
E. Gathering the statistics before creating the execution plan for the statement
* Oracle SQL is parsed before execution, and a hard parse includes these steps: . Loading into shared pool - The SQL source code is loaded into RAM for parsing. (the "hard" parse step) . Syntax parse - Oracle parses the syntax to check for misspelled SQL keywords. . Semantic parse - Oracle verifies all table & column names from the dictionary and checks to see if you are authorized to see the data. . Query Transformation - If enabled (query_rewrite=true), Oracle will transform complex SQL into simpler, equivalent forms and replace aggregations with materialized views, as appropriate.
. Optimization - Oracle then creates an execution plan, based on your schema statistics (or maybe with statistics from dynamic sampling in 10g). . Create executable - Oracle builds an executable file with native file calls to service the SQL query.
The parsing process performs two main functions:
o Syntax Check: is the statement a valid one. Does it make sense given the SQL grammar documented in the SQL Reference Manual. Does it follow all of the rules for SQL.
o Semantic Analysis: Going beyond the syntax ? is the statement valid in light of the objects in the database (do the tables and columns referenced exist). Do you have access to the objects ? are the proper privileges in place? Are there ambiguities in the statement ? for example if there are two tables T1 and T2 and both have a column X, the query ?select X from T1, T2 where ?? is ambiguous, we don?t know which table to get X from. And so on.
So, you can think of parsing as basically a two step process, that of a syntax check to check the validity of the statement and that of a semantic check ? to ensure the statement can execute properly.
Reference: Oracle hard-parse vs. soft parse
Q78. How can you analyze an existing trace file to list the almost resource-intensive statements, aggregation of statistics, and to either exclude recursive call details?
A. By using the DBMS_TRACE package
B. By using the EXPLAIN PLAN command
C. By enabling the SQL_TRACE parameter for the session
D. By using the TKPROF utility
E. By using the TRCSESS utility
Explanation: D: You can run the TKPROF program to format the contents of the trace file and place the output into a readable output file. TKPROF can also:
Create a SQL script that stores the statistics in the database
Determine the execution plans of SQL statements
TKPROF reports each statement executed with the resources it has consumed, the number of times it was called, and the number of rows which it processed. This information lets you easily locate those statements that are using the greatest resource. With experience or with baselines available, you can assess whether the resources used are reasonable given the work done.
A: DBMS_TRACE provides subprograms to start and stop PL/SQL tracing in a session. Oracle collects the trace data as the program executes and writes it to database tables.
A typical session involves:
Starting PL/SQL tracing in session (DBMS_TRACE.SET_PLSQL_TRACE).
Running an application to be traced.
Stopping PL/SQL tracing in session (DBMS_TRACE.CLEAR_PLSQL_TRACE).
E: The trcsess utility consolidates trace output from selected trace files based on several criteria:
Client Id Service name Action name Module name After trcsess merges the trace information into a single output file, the output file could be
processed by TKPROF Reference: Oracle Database Performance Tuning Guide, Understanding TKPROF
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.
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.
Q80. Examine the Exhibit.
Which two options are true about the execution plan and the set of statements?
A. The query uses a partial partition-wise join.
B. The degree of parallelism is limited to the number of partitions in the EMP_RANGE_DID table.
C. The DEPT table id dynamically distributed based on the partition keys of the EMP_RANGE_DID table.
D. The server process serially scans the entire DEPT table for each range partition on the EMP_RANGE_DID table.
E. The query uses a full partition-wise join.