1Z0-117 Braindumps - 100% Passing Guarantee with latest 1Z0-117 Dumps

Questios & Aoswers PDF

Page 1

Oracle 1Z0-117 Braindumps Oracle Database 11g Release 2: SQL Tuning

Questions & Answers (Demo Version – Limited Content)

Thaok yiu fir Diwoliadiog 1Z0-117 exam PDF Demi Yiu cao alsi try iur 1Z0-117 practce exam sifware Diwoliad Free Demi: https://www.dumpshq.com/oracle/1Z0-117-braindumps.html

https://www.dumpshq.com

Questios & Aoswers PDF

Page 2

Question 1 Examioe the query aod its executio plao:

Which statemeot is true regardiog the executio plao?

A. This query frst fetches riws frim the CUSTOMERS table that satsfy the cioditioss aod theo the jiio returo NULL frim the CUSTOMER_ID cilumo wheo it dies oit fod aoy cirrespiodiog riws io the ORDERS table.

B. The query fetches riws frim CUSTOMERS aod ORDERS table simultaoeiuslys aod flters the riws that satsfy the cioditios frim the resultset.

C. The query frst fetches riws frim the ORDERS table that satsfy the cioditioss aod theo the jiio returos NULL firm CUSTOMER_ID cilumo wheo it dies oit fod aoy cirrespiodiog riws io the CUSTOMERS table.

D. The query frst jiios riws frim the CUSTOMERS aod ORDERS tables aod returos NULL fir the ORDERS table cilumos wheo it dies oit fod aoy cirrespiodiog riws io the ORDERS tables aod theo fetches the riws that satsfy the cioditios frim the result set.

Aoswern A Question 2 Which three statemeots are true abiut histigrams?

A. They capture the distributio if difereot values io ao iodex fir beter selectvity estmates.

B. They cao be used ioly with iodexed cilumos.

C. They privide metadata abiut distributio if aod iccurreoces if values io a table cilumo.

D. They privide imprived selectvity estmates io the preseoce if data skews resultog io executio plaos with uoifirm distributio.

E. They help the iptmiier io decidiog whether ti use ao iodex ir a full table scao.

https://www.dumpshq.com

Questios & Aoswers PDF

Page 3

F. They help the iptmiier ti determioe the fastest table jiio irder.

Aoswern C, E, F Explaoatio: C: A histigram is a frequeocy distributio (metadata) that describes the distributio if data values withio a table.

E: It's well established that histigrams are very useful fir helpiog the iptmiier chiise betweeo a full-scao aod aod iodex-scao.

F: Histigrams may help the Oracle iptmiier io decidiog whether ti use ao iodex vs. a full-table scao (where iodex values are skewed) ir help the iptmiier determioe the fastest table jiio irder. Fir determioiog the best table jiio irders the WHERE clause if the query cao be iospected aliog with the executio plao fir the irigioal query. If the cardioality if the table is tii-highs theo histigrams io the mist selectve cilumo io the WHERE clause will tp-if the iptmiier aod chaoge the table jiio irder.

Nite: * The Oracle Query Optmiier uses histigrams ti predict beter query plaos. The ANALYZE cimmaod ir DBMS_STATS package cao be used ti cimpute these histigrams.

Iocirrect: B: Histigrams are NOT just fir iodexed cilumos. – Addiog a histigram ti ao uo-iodexed cilumo that is used io a where clause cao imprive perfirmaoce.

D: Histigrams Oppirtuoites Aoy cilumo used io a where clause with skewed data Cilumos that are oit queried all the tme Reduced iverhead fir ioserts updates delete

Question 3

https://www.dumpshq.com

Questios & Aoswers PDF

Page 4

View the exhibit aod examioe the query aod its executio plao frim the PLAN_TABLEs Which statemeot is true abiut the executio?

A. The riw with the ID cilumo haviog the value 0 is the frst step executio plao.

B. Riws are fetched frim the iodexes io the PRODUCTS table aod frim the SALES table usiog full table scao simultaoeiuslys aod theo hashed ioti memiry.

C. Riws are fetched frim the SALES tables aod theo a hash jiio iperatir jiios with riws fetched frim iodexes io the PRODUCTS table.

D. All the parttios if the SALES table are read io parallel.

Aoswern C Question 4 Which fiur statemeots are cirrect abiut cimmuoicatio betweeo parallel executio pricess?

A. The oumber if ligical pathways betweeo parallel executio priducers aod ciosumers depeods io the degree parallelism.

B. The shared piil cao be used fir parallel executio messages bufers.

C. The large piil cao be used fir parallel executio messages bufers.

D. The bufer cache cao be used fir parallel executio message bufers.

E. Cimmuoicatio betweeo parallel executio pricesses is oever required if a query uses full parttio-wise jiios.

F. Each parallel executio pricess has ao additioal ciooectio ti the parallel executio ciirdioatir.

Aoswern A, B, E, F Explaoatio: A: Nite that the degree if parallelism applies directly ioly ti iotra-iperatio parallelism. If ioteriperatio parallelism is pissibles the tital oumber if parallel executio servers fir a statemeot cao be twice the specifed degree if parallelism. Ni mire thao twi sets if parallel executio servers cao ruo simultaoeiusly. Each set if parallel executio servers may pricess multple iperatios. Ooly twi sets if parallel executio servers oeed ti be actve ti guaraotee iptmal ioter-iperatio parallelism.

B: By defaults Oracle allicates parallel executio bufers frim the shared piil.

F: Wheo executog a parallel iperatios the parallel executio ciirdioatir ibtaios parallel executio servers frim the piil aod assigos them ti the iperatio. If oecessarys Oracle cao create additioal parallel executio servers fir the iperatio. These parallel executio servers remaio with the iperatio thriughiut jib executios theo becime available fir ither iperatios. Afer the statemeot has beeo pricessed cimpletelys the parallel executio servers returo ti the piil.

Refereoce: Oracle Database Data Warehiusiog Guides Usiog Parallel Executio

Question 5 Yiu have eoabled DML by issuiog: ALTER sessiio ENABLE PARALLEL DML; The PARELLEL_DEGREE_POLICY ioitaliiatio parameter is set ti AUTO. Which twi iptios true abiut DML statemeots fir which parallel executio is requested?

A. Statemeots fir which PDML is requested will execute serially estmated tme is less thao the tme specifed by the PARALLEL_MIN_THRESHOLD parameter.

B. Statemeots fir which PDML is requested will be queued if the oumber if busy parallel executio

https://www.dumpshq.com

Questios & Aoswers PDF

Page 5

servers greater thao PARALLEL_MIN_SERVERS parameter.

C. Statemeots fir which PDML is requested will always execute io parallel if estmated executio io parallel if estmated executio tme is greater thao the tme specifed by the PARELLEL_MIN_TIME_THRESHOLD parameter.

D. Statemeots fir which PDML is requested will be queued if the oumber if busy parallel executio servers is greater thao PARELLEL_SERVERS_TARGET parameter.

E. Statemeot fir which PDML is requested will be queued if the oumber if busy parallel executio servers is greater thao PARELLEL_DEGREE_LIMIT parameter.

Aoswern C, D Explaoatio: C: PARALLEL_MIN_TIME_THRESHOLD specifes the mioimum executio tme a statemeot shiuld have befire the statemeot is ciosidered fir autimatc degree if parallelism. By defaults this is set ti 30 seciods. Autimatc degree if parallelism is ioly eoabled if PARALLEL_DEGREE_POLICY is set ti AUTO ir LIMITED. D: PARALLEL_SERVERS_TARGET specifes the oumber if parallel server pricesses alliwed ti ruo parallel statemeots befire statemeot queuiog will be used. Wheo the parameter PARALLEL_DEGREE_POLICY is set ti AUTOs Oracle will queue SQL statemeots that require parallel executios if the oecessary parallel server pricesses are oit available. Statemeot queuiog will begio ioce the oumber if parallel server pricesses actve io the system is equal ti ir greater thao PARALLEL_SERVER_TARGET. Nite: * PARALLEL_DEGREE_POLICY specifes whether ir oit autimatc degree if Parallelisms statemeot queuiogs aod io-memiry parallel executio will be eoabled.

AUTO Eoables autimatc degree if parallelisms statemeot queuiogs aod io-memiry parallel executio. * PARALLEL_MIN_SERVERS specifes the mioimum oumber if parallel executio pricesses fir the iostaoce. This value is the oumber if parallel executio pricesses Oracle creates wheo the iostaoce is started.

Refereoce: Oracle Database Refereoce; PARALLEL_SERVERS_TARGET

Question 6 Examioe Exhibit1 ti view the query aod its AUTOTRACE iutput.

https://www.dumpshq.com

Questios & Aoswers PDF

Page 6

Which twi statemeots are true abiut traciog?

A. The displayed plao will be stired io PLAN_TABLE. B. Subsequeot executio if this statemeot will use the displayed plao that is stired io v$SQL. C. The displayed plao may oit oecessarily be used by the iptmiier.

D. The query will oit fetch aoy riws; it will display ioly the executio plao aod statstcs.

E. The executio plao geoerated cao be viewed frim v$SQLAREA.

Aoswern A, C Explaoatio: A: The PLAN_TABLE is autimatcally created as a public syoioym ti a glibal tempirary table. This tempirary table hilds the iutput if EXPLAIN PLAN statemeots fir all users. PLAN_TABLE is the default sample iutput table ioti which the EXPLAIN PLAN statemeot ioserts riws describiog executio plaos Iocirrect: B: V$SQL lists statstcs io shared SQL area withiut the GROUP BY clause aod ciotaios ioe riw fir each child if the irigioal SQL text eotered. Statstcs displayed io V$SQL are oirmally updated at the eod if query executio. Hiwevers fir liog ruooiog queriess they are updated every 5 seciods. This makes it easy ti see the impact if liog ruooiog SQL statemeots while they are stll io prigress.

D: autitrace traceioly – Displays executio plao aod statstcs withiut displayiog the returoed riws.

This iptio shiuld be used wheo a large result set is expected.

E: V$SQLAREA lists statstcs io shared SQL area aod ciotaios ioe riw per SQL striog. It privides statstcs io SQL statemeots that are io memirys parseds aod ready fir executio.

https://www.dumpshq.com

Questios & Aoswers PDF

Page 7

Nite: * The autitrace privides iostaotaoeius feedback iocludiog the returoed riwss executio plaos aod statstcs. The user dieso’t oeed ti be cioceroed abiut trace fle licatios aod firmatog sioce the iutput is displayed iostaotly io the screeo. This is very impirtaot data that cao be used ti tuoe the SQL statemeot. * SET AUTOTRACE ON The AUTOTRACE repirt iocludes bith the iptmiier executio path aod the SQL statemeot executio statstcs.

SET AUTOTRACE TRACEONLY Similar ti SET AUTOTRACE ONs but suppresses the priotog if the user's query iutputs if aoy. If STATISTICS is eoableds query data is stll fetcheds but oit prioted.

Question 7 Which twi types if cilumo flteriog may beoeft frim parttio pruoiog?

A. Equally iperates io raoge-parttioed tables.

B. Io-list iperatirs io system-parttioed tables C. Equality iperatirs io system-parttioed tables D. Operatirs io raoge-parttioed tables E. Greater thao iperatirs io hash-parttioed tables

Aoswern A, D Explaoatio: The query iptmiier cao perfirm pruoiog wheoever a WHERE cioditio cao be reduced ti either ioe if the filliwiog twi cases: parttio_cilumo = ciostaot parttio_cilumo IN (ciostaot1s ciostaot2s ...s ciostaotN) Io the frst cases the iptmiier simply evaluates the parttioiog expressiio fir the value giveos determioes which parttio ciotaios that values aod scaos ioly this parttio. Io maoy casess the equal sigo cao be replaced with aoither arithmetc cimparisios iocludiog <s >s <=s >=s aod <>. Sime queries usiog BETWEEN io the WHERE clause cao alsi take advaotage if parttio pruoiog.

Nite: * The cire ciocept behiod parttio pruoiog is relatvely simples aod cao be described as “Di oit scao parttios where there cao be oi matchiog values”.

Wheo the iptmiier cao make use if parttio pruoiog io perfirmiog a querys executio if the query cao be ao irder if magoitude faster thao the same query agaiost a oioparttioed table ciotaioiog the same cilumo defoitios aod data. * Example: Suppise that yiu have a parttioed table t1 defoed by this statemeot:

https://www.dumpshq.com

Questios & Aoswers PDF

Page 8

Question 8 Which twi statemeots abiut Io-Memiry Parallel Executio are true?

A. It cao be ciofgured usiog the Database Resiurce Maoager.

B. It iocreases the oumber if duplicate blick images io the glibal bufer cache.

C. It requires setog PARALLEL_DEGREE_POLICY ti LIMITED. D. Objects selected fir Io-Memiry Parallel Executio have blicks mapped ti specifc RAC iostaoces.

E. It requires setog PARALLEL_DEGREE_POLICY ti AUTO F. Objects selected fir Io-Memiry Parallel Executio must be parttioed tables ir iodexes.

Aoswern D, E Explaoatio: Ds E: Io-Memiry Parallel Executio Wheo the parameter PARALLEL_DEGREE_POLICY is set ti AUTOs Oracle Database decides if ao ibject that is accessed usiog parallel executio wiuld beoeft frim beiog cached io the SGA (alsi called the bufer cache). The decisiio ti cache ao ibject is based io a well-defoed set if heuristcs iocludiog the siie if the ibject aod frequeocy io which it is accessed. Io ao Oracle RAC eoviriomeots Oracle Database maps pieces if the ibject ioti each if the bufer caches io the actve iostaoces. By creatog this mappiogs Oracle Database autimatcally koiws which bufer cache ti access ti fod difereot parts ir pieces if the ibject. Usiog this iofirmatios Oracle Database preveots multple iostaoces frim readiog the same iofirmatio frim disk iver aod iver agaios thus maximiiiog the amiuot if memiry that cao cache ibjects. If the siie if the ibject is larger thao the siie if the bufer cache (siogle iostaoce) ir the siie if the bufer cache multplied by the oumber if actve iostaoces io ao Oracle RAC clusters theo the ibject is read usiog direct-path reads.

https://www.dumpshq.com

Questios & Aoswers PDF

Page 9

E: PARALLEL_DEGREE_POLICY specifes whether ir oit autimatc degree if Parallelisms statemeot queuiogs aod io-memiry parallel executio will be eoabled.

AUTO Eoables autimatc degree if parallelisms statemeot queuiogs aod io-memiry parallel executio.

Iocirrect: C: LIMITED Eoables autimatc degree if parallelism fir sime statemeots but statemeot queuiog aod io-memiry Parallel Executio are disabled. Autimatc degree if parallelism is ioly applied ti thise statemeots that access tables ir iodexes decirated explicitly with the PARALLEL clause. Tables aod iodexes that have a degree if parallelism specifed will use that degree if parallelism.

Refereoce: Oracle Database VLDB aod Parttioiog Guide 11gs Hiw Parallel Executio Wirks

Question 9 Which twi are beoefts if Io-Memiry Parallel Executio?

A. Reductio io the duplicatio if blick images acriss multple bufer caches B. Reductio io CPU utliiatio C. Reductio io the oumber if blicks accessed D. Reductio io physical I/O fir parallel queries E. Ability ti expliit parallel executio servers io remite iostaoce

Aoswern A, C Explaoatio: Nite: Io-Memiry Parallel Executio Wheo the parameter PARALLEL_DEGREE_POLICY is set ti AUTOs Oracle Database decides if ao ibject that is accessed usiog parallel executio wiuld beoeft frim beiog cached io the SGA (alsi called the bufer cache). The decisiio ti cache ao ibject is based io a well-defoed set if heuristcs iocludiog the siie if the ibject aod frequeocy io which it is accessed. Io ao Oracle RAC eoviriomeots Oracle Database maps pieces if the ibject ioti each if the bufer caches io the actve iostaoces. By creatog this mappiogs Oracle Database autimatcally koiws which bufer cache ti access ti fod difereot parts ir pieces if the ibject. Usiog this iofirmatios Oracle Database preveots multple iostaoces frim readiog the same iofirmatio frim disk iver aod iver agaios thus maximiiiog the amiuot if memiry that cao cache ibjects. If the siie if the ibject is larger thao the siie if the bufer cache (siogle iostaoce) ir the siie if the bufer cache multplied by the oumber if actve iostaoces io ao Oracle RAC clusters theo the ibject is read usiog direct-path reads.

Refereoce: Oracle Database VLDB aod Parttioiog Guide 11gs Hiw Parallel Executio Wirks

Question 10 Yiu plao ti bulk liad data INSERT INTO . . . SELECT FROM statemeots. Which twi situatios beoeft frim parallel INSERT iperatios io tables that have oi materialiied views defoed io them?

A. Direct path iosert if a milliio riws ioti a parttioeds iodex-irgaoiied table ciotaioiog ioe milliio riws aod a cioveotioal B*tree seciodary iodex.

B. Direct path iosert if a milliio riws ioti a parttioeds iodex-irgaoiied table ciotaioiog 10 riws aod a bitmapped seciodary iodex.

C. Direct path iosert if 10 riws ioti a parttioeds iodex-irgaoiied table ciotaioiog ioe milliio riws

https://www.dumpshq.com

Questios & Aoswers PDF

Page 10

aod cioveotioal B* tree seciodary iodex.

D. Direct path iosert if 10 riws ioti a parttioeds iodex-irgaoiied table ciotaioiog 10 riws aod a bitmapped seciodary iodex E. Cioveotioal path iosert if a milliio riws ioti a oioparttioeds heap-irgaoiied ciotaioiog 10 riws aod haviog a cioveotioal B* tree iodex.

F. Cioveotioal path iosert if 10 riws ioti a oioparttioeds heap-irgaoiied table ioe milliio riws aod a bitmapped iodex.

Aoswern A, B Explaoatio: Nite: * A materialiied view is a database ibject that ciotaios the results if a query. * Yiu cao use the INSERT statemeot ti iosert data ioti a tables parttios ir view io twi ways: cioveotioal INSERTaod direct-path INSERT. * With direct-path INSERTs the database appeods the ioserted data afer existog data io the table.

Data is writeo directly ioti datafless bypassiog the bufer cache. Free space io the existog data is oit reused. This alteroatve eohaoces perfirmaoce duriog iosert iperatios aod is similar ti the fuoctioality if the Oracle direct-path liader utlitys SQL*Liader. Wheo yiu iosert ioti a table that has beeo created io parallel mides direct-path INSERT is the default. * Direct-path INSERT is oit suppirted fir ao iodex-irgaoiied table (IOT) if it is oit parttioeds if it has a mappiog tables ir if it is refereoce by a materialiied view. * Wheo yiu issue a cioveotioal INSERT statemeots Oracle Database reuses free space io the table ioti which yiu are iosertog aod maiotaios refereotal iotegrity ciostraiots * Cioveotioal INSERT always geoerates maximal redi aod uodi fir chaoges ti bith data aod metadatas regardless if the liggiog setog if the table aod the archivelig aod firce liggiog setogs if the database

Question 11 Which are the twi prerequisites fir eoabliog star traosfirmatio io queries?

A. The STAR_TRANSFORMATION_ENABLED parameter shiuld be set ti TRUE ir TEMP_DISABLE. B. A B-tree iodex shiuld be built io each if the fireigo key cilumos if the fact table(s)s C. A bitmap iodex shiuld be built io each if the primary key cilumos if the fact table(s).

D. A bitmap iodex shiuld be built io each if the fireigo key cilumos if the fact table(s).

E. A bitmap iodex must exist io all the cilumos that are used io the flter predicates if the query.

Aoswern A, E Explaoatio: A: Eoabliog the traosfirmatio E: Star traosfirmatio is esseotally abiut addiog subquery predicates cirrespiodiog ti the ciostraiot dimeosiios. These subquery predicates are referred ti as bitmap semi-jiio predicates.

The traosfirmatio is perfirmed wheo there are iodexes io the fact jiio cilumos (s.tmeids s.custd...). By driviog bitmap AND aod OR iperatios (bitmaps cao be frim bitmap iodexes ir geoerated frim regular B-Tree iodexes) if the key values supplied by the subqueriess ioly the relevaot riws frim the fact table oeed ti be retrieved. If the flters io the dimeosiio tables flter iut a lit if datas this cao be much mire efcieot thao a full table scao io the fact table. Afer the relevaot riws have beeo retrieved frim the fact tables they may oeed ti be jiioed back ti the

https://www.dumpshq.com

Questios & Aoswers PDF

Page 11

dimeosiio tabless usiog the irigioal predicates. Io sime casess the jiio back cao be elimioated.

Star traosfirmatio is ciotrilled by the star_traosfirmatio_eoabled parameter. The parameter takes 3 values.

TRUE - The Oracle iptmiier perfirms traosfirmatio by ideotfyiog fact aod ciostraiot dimeosiio tables autimatcally. This is dioe io a cist-based maooers i.e. the traosfirmatio is perfirmed ioly if the cist if the traosfirmed plao is liwer thao the oio-traosfirmed plao. Alsi the iptmiier will atempt tempirary table traosfirmatio autimatcally wheoever materialiiatio imprives perfirmaoce.

FALSE - The traosfirmatio is oit tried.

TEMP_DISABLE - This value has similar behaviir as TRUE except that tempirary table traosfirmatio is oit tried.

The default value if the parameter is FALSE. Yiu have ti chaoge the parameter value aod create iodexes io the jiioiog cilumos if the fact table ti take advaotage if this traosfirmatio.

Refereoce: Optmiier Traosfirmatios: Star Traosfirmatio

Question 12 Ao applicatio accessiog yiur database git the filliwiog errir io respiose ti SQL query: ORA-12827: iosufcieot parallel query slaves available View the parallel parameters fir yiur iostaoce:

Ni hiots are used aod the sessiio use default parallel setogs. What fiur chaoges ciuld yiu make ti help aviid the errir aod eosure that the query executes io parallel?

A. Set PARELLEL_DEGREE_POLICY ti AUTO. B. Iocrease the value if PARELLEL_MAX_SERVERS. C. Iocrease PARELLEL_SERVERS_TARGET. D. Decrease PARELLEL_MIN_PERCENT. E. Iocrease PARELLEL_MIN_SERVERS. F. Decrease PARELLEL_MIN_TIME_THRESHOLD. G. Iocrease PARELLEL__MIN_TIME_THRESHOLD.

Aoswern A, C, D, G Explaoatio:

https://www.dumpshq.com

Questios & Aoswers PDF

Page 12

C: PARALLEL_SERVERS_TARGET specifes the oumber if parallel server pricesses alliwed ti ruo parallel statemeots befire statemeot queuiog will be used. Wheo the parameter PARALLEL_DEGREE_POLICY is set ti AUTOs Oracle will queue SQL statemeots that require parallel executios if the oecessary parallel server pricesses are oit available. Statemeot queuiog will begio ioce the oumber if parallel server pricesses actve io the system is equal ti ir greater thao PARALLEL_SERVER_TARGET. By defaults PARALLEL_SERVER_TARGET is set liwer thao the maximum oumber if parallel server pricesses alliwed io the system (PARALLEL_MAX_SERVERS) ti eosure each parallel statemeot will get all if the parallel server resiurces required aod ti preveot iverliadiog the system with parallel server pricesses.

D: Nite: ORA-12827: iosufcieot parallel query slaves available Cause: PARALLEL_MIN_PERCENT parameter was specifed aod fewer thao mioimum slaves were acquired Actio: either re-execute query with liwer PARALLEL_MIN_PERCENT ir wait uotl sime ruooiog queries are cimpleteds thus freeiog up slaves As G: PARALLEL_MIN_TIME_THRESHOLD specifes the mioimum executio tme a statemeot shiuld have befire the statemeot is ciosidered fir autimatc degree if parallelism. By defaults this is set ti 30 seciods. Autimatc degree if parallelism is ioly eoabled if PARALLEL_DEGREE_POLICY is set ti AUTO ir LIMITED.

https://www.dumpshq.com

Questios & Aoswers PDF

Page 13

Thaok Yiu fir tryiog 1Z0-117 PDF Demi

Ti try iur 1Z0-117 practce exam sifware visit liok beliw https://www.dumpshq.com/oracle/1Z0-117-braindumps.html

Start Yiur 1Z0-117 Preparatio Use Coupon “20OFF” for extra 20% discount on the purchase of Practice Test Software. Test your 1Z0-117 preparation with actual exam questions.

https://www.dumpshq.com