Refined of 1Z0-117 book materials and testing material for Oracle certification for IT learners, Real Success Guaranteed with Updated 1Z0-117 pdf dumps vce Materials. 100% PASS Oracle Database 11g Release 2: SQL Tuning Exam exam Today!
2021 Sep 1Z0-117 testing engine
Q1. 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.
Explanation: * QUERY_REWRITE_ENABLED allows you to enable or disable query
rewriting globally for the database.
Oracle does not use rewrite.
Oracle costs the query with rewrite and without rewrite and chooses the method with the
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
. Manage workloads.
. Mark, update, and remove recommendations.
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.
Q2. Examine the initialization parameters for a database an OLTP overload.
What is the effect of changing the value of the parameter?
A. It influences the optimizer to always use the value of the parameter?
B. It influences the optimizer to use indexes instead of full table scans as the estimated cost of the using index is reduced.
C. It influences the optimizer to use full table scans instead of index scans as the estimated cost of full table scan is reduced.
D. It influenced the optimizer to use bitmap indexes as the estimated cost conversion from bimap is rowed us reduced.
Explanation: OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan. The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.
Q3. 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
Explanation: 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.
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.
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.
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.
Q4. 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.
Explanation: * PARALLEL (AUTO): The database computes the degree of parallelism (C), which can be 1 or greater (not D). If the computed degree of parallelism is 1, then the statement runs serially.
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:
SELECT /*+ parallel(auto) */ ename, dname FROM emp e, dept d
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.
Q5. Examine the execution plan:
Which two are true regarding the execution plan?
A. The CUSTOMERS table is hash partitioned.
B. The SALES table is hash partitioned.
C. The CUSTOMERS table is scanned first and selected partitions from the SALES table are scanned based on the BLOOM Filter created during the scan of the CUSTOMERS table.
D. The SALES table is scanned first and selected partitions from the CUSTOMERS table are scanned based on the Bloom Filter created during the scan of the SALES table.
E. Both the CUSTOMERS and SALES tables are scanned simultaneously and rows from the CUSTOMERS table are joined to row of the SALES table.
F. The CUSTOMERS table is range partitioned.
Explanation: B: As per line 14 and 15.
C: As per exhibit line 13 is execute before line 15.
Not E: As per line 13 and 15 – they are not executed simultaneously.
Renew 1Z0-117 test engine:
Q6. You notice some performance degradation for a high-load SQL statement in your database. After investigations, you run the SQL Tuning Advisor, which recommends a SQL Profile. You accept the profile recommendation resulting in a new, tuned execution plan for the statement.
Your database uses SQL plan management and a SQL plan baseline exists for this SQL statement.
Which statement is true?
A. The database adds the tuned plan to the SQL plan baseline as a nonfixed plan.
B. The database adds the tuned plan to the SQL plan baseline as a fixed plan.
C. The optimizer uses the new tuned plan only when a reproducible fixed plan is present.
D. The created SQL profile will continuously adapt to all changes made to the database, the object, and to the system statistics over an extended length of time.
When the SQL Tuning Advisor recommends that a SQL Profile be used, you should accept the SQL Profile that is recommended. In cases where the SQL Tuning Advisor recommends that an index and a SQL Profile be used, both should be used. You can use the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to accept a SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a SQL Profile in the database.
When tuning SQL statements with the SQL Tuning Advisor, if the advisor finds a tuned plan and verifies its performance to be better than a plan chosen from the corresponding SQL plan baseline, it makes a recommendation to accept a SQL profile. When the SQL profile is accepted, the tuned plan is added to the corresponding SQL plan baseline.
If SQL plan management is used and there is already an existing plan baseline for the SQL statement, a new plan baseline will be added when a SQL profile is created.
SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.
SQL plan baseline is fixed if it contains at least one enabled plan whose FIXED attribute is set to YES.
ACCEPT_SQL_PROFILE Procedure and Function
This procedure creates a SQL Profile recommended by the SQL Tuning Advisor. The SQL text is normalized for matching purposes though it is stored in the data dictionary in de-normalized form for readability.
Q7. Which two statements are true about index full scans?
A. An index fast full scan multi block I/O to read the index structure in its entirely.
B. Index nodes are not retrieved in the index order, and there fore the nodes are not in sequence.
C. An index fast full scan reads the index block by block.
D. An index fast full scan reads the whole index from the lowest value to the higher value.
Explanation: A: To speed table and index block access, Oracle uses the db_file_multiblock_read_count parameter (which defaults to 8) to aid in getting full-table scan and full-index scan data blocks into the data buffer cache as fast as possible.
B: The index nodes are not retrieved in index order, the rows will not be sequenced.
Note: there are some requirements for Oracle to invoke the fast full-index scan. . All of the columns required must be specified in the index. That is, all columns in the select and where clauses must exist in the index. . The query returns more than 10 percent of the rows within the index. This 10 percent figure depends on the degree of multi-block reads and the degree of parallelism. . You are counting the number of rows in a table that meet a specific criterion. The fast full-index scan is almost always used for count(*) operations.
Reference: index fast full scan tips
Q8. An application supplied by a new vendor is being deployed and the SQL statements have plan baselines provided by the supplier. The plans have been loaded from a SQL tuning set. You require the optimizer to use these baselines, but allow better plans to used, should any be created.
Which two tasks would you perform to achieve this?
A. Set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE.
B. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE.
C. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function to fix the plans.
D. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to fix the new plans.
E. Use the DBMS_SPM.ALTER_SQL_BASELINE function to accept new plans.
Explanation: OPTIMIZER_USE_SQL_PLAN_BASELINES enables or disables the use of SQL plan baselines stored in SQL Management Base. When enabled, the optimizer looks for a SQL plan baseline for the SQL statement being compiled. If one is found in SQL Management B ase, then the optimizer will cost each of the baseline plans and pick one with the lowest cost.
Q9. 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.
Q10. 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