Questions And Answers PDF
Oracle 1Z0-117 Oracle Database 11g Release 2: SQL Tuning
Version: DEMO
http://www.TestWarrior.com/1Z0-117-practice-exam.html
FOR 1Z0-117 Candidates: We offer Two Products: 1st - We have Practice Tests Software with Actual Exam Questions 2nd - Questions and Answers in PDF Format. Try a Free DEMO of these Exam Products via below Link:
http://www.TestWarrior.com/1Z0-117-practice-exam.html
TestWarrior.com
1
Questions And Answers PDF
Question 1 Examine the query and its executon plan:
Which statement is true regarding the executon plan? A. This query frst fetches rows from the CUSTOMERS table that satsfy the conditonss and then the join return NULL from the CUSTOMER_ID column when it does not fnd any corresponding rows in the ORDERS table. B. The query fetches rows from CUSTOMERS and ORDERS table simultaneouslys and flters the rows that satsfy the conditons from the resultset. C. The query frst fetches rows from the ORDERS table that satsfy the conditonss and then the join returns NULL form CUSTOMER_ID column when it does not fnd any corresponding rows in the CUSTOMERS table. D. The query frst joins rows from the CUSTOMERS and ORDERS tables and returns NULL for the ORDERS table columns when it does not fnd any corresponding rows in the ORDERS tables and then fetches the rows that satsfy the conditons from the result set.
Aoswern A Question 2 Which three statements are true about histograms? A. They capture the distributon of diferent values in an index for beter selectvity estmates. B. They can be used only with indexed columns. C. They provide metadata about distributon of and occurrences of values in a table column. D. They provide improved selectvity estmates in the presence of data skews resultng in executon plans with uniform distributon. E. They help the optmiier in deciding whether to use an index or a full table scan. F. They help the optmiier to determine the fastest table join order.
TestWarrior.com
2
Questions And Answers PDF
Aoswern C, E, F Explanaton: C: A histogram is a frequency distributon (metadata) that describes the distributon of data values within a table. E: It's well established that histograms are very useful for helping the optmiier choose between a full-scan and and index-scan. F: Histograms may help the Oracle optmiier in deciding whether to use an index vs. a full-table scan (where index values are skewed) or help the optmiier determine the fastest table join order. For determining the best table join orders the WHERE clause of the query can be inspected along with the executon plan for the original query. If the cardinality of the table is too-highs then histograms on the most selectve column in the WHERE clause will tp-of the optmiier and change the table join order. Note: * The Oracle Query Optmiier uses histograms to predict beter query plans. The ANALYZE command or DBMS_STATS package can be used to compute these histograms. Incorrect: B: Histograms are NOT just for indexed columns. – Adding a histogram to an un-indexed column that is used in a where clause can improve performance. D: Histograms Opportunites Any column used in a where clause with skewed data Columns that are not queried all the tme Reduced overhead for inserts updates delete
Question 3
View the exhibit and examine the query and its executon plan from the PLAN_TABLEs Which
TestWarrior.com
3
Questions And Answers PDF
statement is true about the executon? A. The row with the ID column having the value 0 is the frst step executon plan. B. Rows are fetched from the indexes on the PRODUCTS table and from the SALES table using full table scan simultaneouslys and then hashed into memory. C. Rows are fetched from the SALES tables and then a hash join operator joins with rows fetched from indexes on the PRODUCTS table. D. All the parttons of the SALES table are read in parallel.
Aoswern C Question 4 Which four statements are correct about communicaton between parallel executon process? A. The number of logical pathways between parallel executon producers and consumers depends on the degree parallelism. B. The shared pool can be used for parallel executon messages bufers. C. The large pool can be used for parallel executon messages bufers. D. The bufer cache can be used for parallel executon message bufers. E. Communicaton between parallel executon processes is never required if a query uses full partton-wise joins. F. Each parallel executon process has an additonal connecton to the parallel executon coordinator.
Aoswern A, B, E, F Explanaton: A: Note that the degree of parallelism applies directly only to intra-operaton parallelism. If interoperaton parallelism is possibles the total number of parallel executon servers for a statement can be twice the specifed degree of parallelism. No more than two sets of parallel executon servers can run simultaneously. Each set of parallel executon servers may process multple operatons. Only two sets of parallel executon servers need to be actve to guarantee optmal inter-operaton parallelism. B: By defaults Oracle allocates parallel executon bufers from the shared pool. F: When executng a parallel operatons the parallel executon coordinator obtains parallel executon servers from the pool and assigns them to the operaton. If necessarys Oracle can create additonal parallel executon servers for the operaton. These parallel executon servers remain with the operaton throughout job executons then become available for other operatons. Afer the statement has been processed completelys the parallel executon servers return to the pool. Reference: Oracle Database Data Warehousing Guides Using Parallel Executon
Question 5 You have enabled DML by issuing: ALTER session ENABLE PARALLEL DML; The PARELLEL_DEGREE_POLICY initaliiaton parameter is set to AUTO. Which two optons true about DML statements for which parallel executon is requested? A. Statements for which PDML is requested will execute serially estmated tme is less than the tme specifed by the PARALLEL_MIN_THRESHOLD parameter. B. Statements for which PDML is requested will be queued if the number of busy parallel executon servers greater than PARALLEL_MIN_SERVERS parameter.
TestWarrior.com
4
Questions And Answers PDF
C. Statements for which PDML is requested will always execute in parallel if estmated executon in parallel if estmated executon tme is greater than the tme specifed by the PARELLEL_MIN_TIME_THRESHOLD parameter. D. Statements for which PDML is requested will be queued if the number of busy parallel executon servers is greater than PARELLEL_SERVERS_TARGET parameter. E. Statement for which PDML is requested will be queued if the number of busy parallel executon servers is greater than PARELLEL_DEGREE_LIMIT parameter.
Aoswern C, D Explanaton: C: PARALLEL_MIN_TIME_THRESHOLD specifes the minimum executon tme a statement should have before the statement is considered for automatc degree of parallelism. By defaults this is set to 30 seconds. Automatc degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED. D: PARALLEL_SERVERS_TARGET specifes 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 AUTOs Oracle will queue SQL statements that require parallel executons if the necessary parallel server processes are not available. Statement queuing will begin once the number of parallel server processes actve on the system is equal to or greater than PARALLEL_SERVER_TARGET. Note: * PARALLEL_DEGREE_POLICY specifes whether or not automatc degree of Parallelisms statement queuings and in-memory parallel executon will be enabled. AUTO Enables automatc degree of parallelisms statement queuings and in-memory parallel executon. * PARALLEL_MIN_SERVERS specifes the minimum number of parallel executon processes for the instance. This value is the number of parallel executon processes Oracle creates when the instance is started. Reference: Oracle Database Reference; PARALLEL_SERVERS_TARGET
Question 6 Examine Exhibit1 to view the query and its AUTOTRACE output.
TestWarrior.com
5
Questions And Answers PDF
Which two statements are true about tracing? A. The displayed plan will be stored in PLAN_TABLE. B. Subsequent executon of this statement will use the displayed plan that is stored in v$SQL. C. The displayed plan may not necessarily be used by the optmiier. D. The query will not fetch any rows; it will display only the executon plan and statstcs. E. The executon plan generated can be viewed from v$SQLAREA.
Aoswern A, C Explanaton: A: The PLAN_TABLE is automatcally created as a public synonym to a global temporary table. This temporary table holds the output of EXPLAIN PLAN statements for all users. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing executon plans Incorrect: B: V$SQL lists statstcs on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statstcs displayed in V$SQL are normally updated at the end of query executon. Howevers for long running queriess they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are stll in progress. D: autotrace traceonly – Displays executon plan and statstcs without displaying the returned rows. This opton should be used when a large result set is expected. E: V$SQLAREA lists statstcs on shared SQL area and contains one row per SQL string. It provides statstcs on SQL statements that are in memorys parseds and ready for executon.
TestWarrior.com
6
Questions And Answers PDF
Note: * The autotrace provides instantaneous feedback including the returned rowss executon plans and statstcs. The user doesn’t need to be concerned about trace fle locatons and formatng since the output is displayed instantly on the screen. This is very important data that can be used to tune the SQL statement. * SET AUTOTRACE ON The AUTOTRACE report includes both the optmiier executon path and the SQL statement executon statstcs. SET AUTOTRACE TRACEONLY Similar to SET AUTOTRACE ONs but suppresses the printng of the user's query outputs if any. If STATISTICS is enableds query data is stll fetcheds but not printed.
Question 7 Which two types of column fltering may beneft from partton pruning? A. Equally operates on range-parttoned tables. B. In-list operators on system-parttoned tables C. Equality operators on system-parttoned tables D. Operators on range-parttoned tables E. Greater than operators on hash-parttoned tables
Aoswern A, D Explanaton: The query optmiier can perform pruning whenever a WHERE conditon can be reduced to either one of the following two cases: partton_column = constant partton_column IN (constant1s constant2s ...s constantN) In the frst cases the optmiier simply evaluates the parttoning expression for the value givens determines which partton contains that values and scans only this partton. In many casess the equal sign can be replaced with another arithmetc comparisons including <s >s <=s >=s and <>. Some queries using BETWEEN in the WHERE clause can also take advantage of partton pruning. Note: * The core concept behind partton pruning is relatvely simples and can be described as “Do not scan parttons where there can be no matching values”. When the optmiier can make use of partton pruning in performing a querys executon of the query can be an order of magnitude faster than the same query against a nonparttoned table containing the same column defnitons and data. * Example: Suppose that you have a parttoned table t1 defned by this statement:
TestWarrior.com
7
Questions And Answers PDF
Question 8 Which two statements about In-Memory Parallel Executon are true? A. It can be confgured using the Database Resource Manager. B. It increases the number of duplicate block images in the global bufer cache. C. It requires setng PARALLEL_DEGREE_POLICY to LIMITED. D. Objects selected for In-Memory Parallel Executon have blocks mapped to specifc RAC instances. E. It requires setng PARALLEL_DEGREE_POLICY to AUTO F. Objects selected for In-Memory Parallel Executon must be parttoned tables or indexes.
Aoswern D, E Explanaton: Ds E: In-Memory Parallel Executon When the parameter PARALLEL_DEGREE_POLICY is set to AUTOs Oracle Database decides if an object that is accessed using parallel executon would beneft from being cached in the SGA (also called the bufer cache). The decision to cache an object is based on a well-defned set of heuristcs including the siie of the object and frequency on which it is accessed. In an Oracle RAC environments Oracle Database maps pieces of the object into each of the bufer caches on the actve instances. By creatng this mappings Oracle Database automatcally knows which bufer cache to access to fnd diferent parts or pieces of the object. Using this informatons Oracle Database prevents multple instances from reading the same informaton from disk over and over agains thus maximiiing the amount of memory that can cache objects. If the siie of the object is larger than the siie of the bufer cache (single instance) or the siie of the bufer cache multplied by the number of actve instances in an Oracle RAC clusters then the object is read using direct-path reads.
TestWarrior.com
8
Questions And Answers PDF
E: PARALLEL_DEGREE_POLICY specifes whether or not automatc degree of Parallelisms statement queuings and in-memory parallel executon will be enabled. AUTO Enables automatc degree of parallelisms statement queuings and in-memory parallel executon. Incorrect: C: LIMITED Enables automatc degree of parallelism for some statements but statement queuing and in-memory Parallel Executon are disabled. Automatc 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 specifed will use that degree of parallelism. Reference: Oracle Database VLDB and Parttoning Guide 11gs How Parallel Executon Works
Question 9 Which two are benefts of In-Memory Parallel Executon? A. Reducton in the duplicaton of block images across multple bufer caches B. Reducton in CPU utliiaton C. Reducton in the number of blocks accessed D. Reducton in physical I/O for parallel queries E. Ability to exploit parallel executon servers on remote instance
Aoswern A, C Explanaton: Note: In-Memory Parallel Executon When the parameter PARALLEL_DEGREE_POLICY is set to AUTOs Oracle Database decides if an object that is accessed using parallel executon would beneft from being cached in the SGA (also called the bufer cache). The decision to cache an object is based on a well-defned set of heuristcs including the siie of the object and frequency on which it is accessed. In an Oracle RAC environments Oracle Database maps pieces of the object into each of the bufer caches on the actve instances. By creatng this mappings Oracle Database automatcally knows which bufer cache to access to fnd diferent parts or pieces of the object. Using this informatons Oracle Database prevents multple instances from reading the same informaton from disk over and over agains thus maximiiing the amount of memory that can cache objects. If the siie of the object is larger than the siie of the bufer cache (single instance) or the siie of the bufer cache multplied by the number of actve instances in an Oracle RAC clusters then the object is read using direct-path reads. Reference: Oracle Database VLDB and Parttoning Guide 11gs How Parallel Executon Works
Question 10 You plan to bulk load data INSERT INTO . . . SELECT FROM statements. Which two situatons beneft from parallel INSERT operatons on tables that have no materialiied views defned on them? A. Direct path insert of a million rows into a parttoneds index-organiied table containing one million rows and a conventonal B*tree secondary index. B. Direct path insert of a million rows into a parttoneds index-organiied table containing 10 rows and a bitmapped secondary index. C. Direct path insert of 10 rows into a parttoneds index-organiied table containing one million rows
TestWarrior.com
9
Questions And Answers PDF
and conventonal B* tree secondary index. D. Direct path insert of 10 rows into a parttoneds index-organiied table containing 10 rows and a bitmapped secondary index E. Conventonal path insert of a million rows into a nonparttoneds heap-organiied containing 10 rows and having a conventonal B* tree index. F. Conventonal path insert of 10 rows into a nonparttoneds heap-organiied table one million rows and a bitmapped index.
Aoswern A, B Explanaton: Note: * A materialiied view is a database object that contains the results of a query. * You can use the INSERT statement to insert data into a tables parttons or view in two ways: conventonal INSERTand direct-path INSERT. * With direct-path INSERTs the database appends the inserted data afer existng data in the table. Data is writen directly into datafless bypassing the bufer cache. Free space in the existng data is not reused. This alternatve enhances performance during insert operatons and is similar to the functonality of the Oracle direct-path loader utlitys SQL*Loader. When you insert into a table that has been created in parallel modes direct-path INSERT is the default. * Direct-path INSERT is not supported for an index-organiied table (IOT) if it is not parttoneds if it has a mapping tables or if it is reference by a materialiied view. * When you issue a conventonal INSERT statements Oracle Database reuses free space in the table into which you are insertng and maintains referental integrity constraints * Conventonal INSERT always generates maximal redo and undo for changes to both data and metadatas regardless of the logging setng of the table and the archivelog and force logging setngs of the database
Question 11 Which are the two prerequisites for enabling star transformaton on queries? A. The STAR_TRANSFORMATION_ENABLED parameter should be set to TRUE or TEMP_DISABLE. B. A B-tree index should be built on each of the foreign key columns of the fact table(s)s C. A bitmap index should be built on each of the primary key columns of the fact table(s). D. A bitmap index should be built on each of the foreign key columns of the fact table(s). E. A bitmap index must exist on all the columns that are used in the flter predicates of the query.
Aoswern A, E Explanaton: A: Enabling the transformaton E: Star transformaton is essentally about adding subquery predicates corresponding to the constraint dimensions. These subquery predicates are referred to as bitmap semi-join predicates. The transformaton is performed when there are indexes on the fact join columns (s.tmeids s.custd...). By driving bitmap AND and OR operatons (bitmaps can be from bitmap indexes or generated from regular B-Tree indexes) of the key values supplied by the subqueriess only the relevant rows from the fact table need to be retrieved. If the flters on the dimension tables flter out a lot of datas this can be much more efcient than a full table scan on the fact table. Afer the relevant rows have been retrieved from the fact tables they may need to be joined back to the
TestWarrior.com
10
Questions And Answers PDF
dimension tabless using the original predicates. In some casess the join back can be eliminated. Star transformaton is controlled by the star_transformaton_enabled parameter. The parameter takes 3 values. TRUE - The Oracle optmiier performs transformaton by identfying fact and constraint dimension tables automatcally. This is done in a cost-based manners i.e. the transformaton is performed only if the cost of the transformed plan is lower than the non-transformed plan. Also the optmiier will atempt temporary table transformaton automatcally whenever materialiiaton improves performance. FALSE - The transformaton is not tried. TEMP_DISABLE - This value has similar behavior as TRUE except that temporary table transformaton is not tried. The default value of the parameter is FALSE. You have to change the parameter value and create indexes on the joining columns of the fact table to take advantage of this transformaton. Reference: Optmiier Transformatons: Star Transformaton
Question 12 An applicaton accessing your database got the following error in response to SQL query: ORA-12827: insufcient parallel query slaves available View the parallel parameters for your instance:
No hints are used and the session use default parallel setngs. What four changes could you make to help avoid the error and ensure that the query executes in parallel? A. Set PARELLEL_DEGREE_POLICY to AUTO. B. Increase the value of PARELLEL_MAX_SERVERS. C. Increase PARELLEL_SERVERS_TARGET. D. Decrease PARELLEL_MIN_PERCENT. E. Increase PARELLEL_MIN_SERVERS. F. Decrease PARELLEL_MIN_TIME_THRESHOLD. G. Increase PARELLEL__MIN_TIME_THRESHOLD.
Aoswern A, C, D, G Explanaton:
TestWarrior.com
11
Questions And Answers PDF
C: PARALLEL_SERVERS_TARGET specifes 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 AUTOs Oracle will queue SQL statements that require parallel executons if the necessary parallel server processes are not available. Statement queuing will begin once the number of parallel server processes actve on the system is equal to or greater than PARALLEL_SERVER_TARGET. By defaults 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. D: Note: ORA-12827: insufcient parallel query slaves available Cause: PARALLEL_MIN_PERCENT parameter was specifed and fewer than minimum slaves were acquired Acton: either re-execute query with lower PARALLEL_MIN_PERCENT or wait untl some running queries are completeds thus freeing up slaves As G: PARALLEL_MIN_TIME_THRESHOLD specifes the minimum executon tme a statement should have before the statement is considered for automatc degree of parallelism. By defaults this is set to 30 seconds. Automatc degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.
TestWarrior.com
12
Questions And Answers PDF
Thank You for Trying Our Product Visit Our Site to Purchase the Full Set of Actual 1Z0-117 Exam Questions With Answers.
http://www.TestWarrior.com/1Z0-117-practice-exam.html We Also Provide Practice Exam Software That Simulates Real Exam Environment And Has Many Self-Assessment Features. Download Free Product Demo From:
Download Free Product Demo from: http://www.TestWarrior.com/1Z0-117-practice-exam.html
Check Out Our Customer Testimonials
TestWarrior.com
13