Oracle英文版培訓(xùn)課件之Data Warehouse：les07

ParallelismConceptsObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:ExplainthebenefitofusingparalleloperationsDescribesystemconditionsunderwhichtouseparalleloperationsDescribehowparalleloperationsworkControltheparallelexecutionserverpoolListoperationsthatcanbeparallelizedIntroductiontoParallelExecutionServerwithoutparallelismServerwithparallelismCPUidleCPUidleCPUidleCPUscanCPUscanCPUscanCPUscanCPUscanOperationsThatCanBeParallelizedAccessmethods:Tablescans,fastfullindexscansPartitionedindexrangescansVariousSQLoperationsJoins:Nestedloop,sortmergeHash,startransformation,partitionwisejoinDDLstatements:CTAS,CREATEINDEX,REBUILDINDEX[PARTITION]MOVE,SPLIT,COALESCE

PARTITIONDMLstatements:INSERT

SELECT,UPDATE,DELETE,MERGEHowParallelExecutionWorksWithserialexecution,onlyoneprocessisused.Withparallelexecution:OneparallelexecutioncoordinatorprocessisusedManyparallelexecutionserversareusedTableisdynamicallydividedintogranulesSerialprocessSALESCoordinatorprocessParallelexecutionserversSELECTCOUNT(*)FROMsalesSALESSELECTCOUNT(*)FROMsalesTheGranuleThebasicunitofworkinparallelismiscalledthegranule.Typeofgranules:Blockrangegranulesaredynamicallygeneratedatexecutiontime.Partitiongranulesarestaticallydeterminedbythenumberofpartitions.Onegranuleisreadperparallelexecutionserver.Parallelexecutionserversprogressfromonegranuletothenext.Thetypeofgranuleusedisdependentonthekindofparalleloperationbeingperformed.ParallelOperationssortA-KscanSQLDataTable’sdynamicpartitioning(granules)IntraparallelismscanscansortL-SsortT-ZExecutionServersSELECTcust_last_name,cust_first_nameFROMcustomersORDERBYcust_last_name;CoordinatorTableondiskDispatching

resultsInterparallelismIntraparallelismDOP=3ProducersConsumersParallelExecutionwith

RealApplicationClustersExecutionslaveshavenodeaffinityforexecutioncoordinatorbutwillexpandifneeded.ExecutioncoordinatorParallelexecutionserverShareddisksNode4Node1Node2Node3HowParallelExecution

ServersCommunicateRowsdistribution:PARTITIONHASHRANGEBROADCASTQC(RANDOM)QCParallelexecutionserverset1Parallelexecutionserverset2DOP=3DegreeofParallelism(DOP)Degreeofparallelismisthenumberofparallelexecutionserversusedbyoneparalleloperation.DOPappliesonlytointraoperationparallelism.Ifinteroperationparallelismisused,thenthenumberofparallelexecutionserverscanbetwicetheDOP.Nomorethantwosetsofparallelexecutionserverscanbeusedforoneparallelizedstatement.Whenusingpartitiongranules,usearelativelyhighnumberofpartitions.DefaultDegreeofParallelismThedefaultDOP:IsusedforaparalleloperationthatdoesnotspecifyaDOPIsdynamicallycalculatedatruntimeDependson:TotalnumberofCPUsPARALLEL_THREADS_PER_CPUMaybereduceddependingontheavailabilityofparallelexecutionserversParallelExecutionPlanForthesamestatement,aparallelplangenerallydiffersfromthecorrespondingserialplan.Inordertogeneratetheexecutionplan,usetheEXPLAINPLANcommand,orexecutethestatement.Inordertoviewtheexecutionplan:SelectdirectlyfromPLAN_TABLESelectdirectlyfromV$SQL_PLANRun$ORACLE_HOME/rdbms/admin/utlxplp.sql

UsetheDBMS_XPLAN.DISPLAYtablefunctionColumnsofinterest:OBJECT_NODEOTHER_TAGDISTRIBUTIONOTHER_TAGColumnParallelexecution:InputfrompreviousstepusedbysamePXPARALLEL_COMBINED_WITH_CHILD(PCWC)Parallelexecution:OutputusedbythesamePXinthenextstepPARALLEL_COMBINED_WITH_PARENT(PCWP)Parallelexecution:OutputreturnstocoordinatorPARALLEL_TO_SERIAL(P->S)

Parallelexecution:OutputrepartitionedtosecondsetofPXPARALLEL_TO_PARALLEL(P->P)

Serialexecution:OutputpartitionedorbroadcasttoPXPARALLEL_FROM_SERIAL(S->P)

SerialexecutionataremotesiteSERIAL_FROM_REMOTE(S->R)SerialexecutionSERIALInterpretationOTHER_TAGSerialandParallelExecutionPlansSELECTcust_city,sum(amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYcust_city;HASHGROUPBY

HASHJOINTABLEACCESSFULLCUSTOMERSPARTITOIONRANGEALLTABLEACCESSFULLSALES13.PXCOORDINATOR12.PXSENDQCP->SQC(RANDOM)11.

HASHGROUPBYPCWP10.PXRECEIVEPCWP9.PXSENDP->PHASH8.

HASHGROUPBYPCWP7.

HASHJOINPCWP6.PXRECEIVEPCWP5.PXSENDP->P(BROADCAST)4.PXBLOCKITERATORPCWP3.TABLEACCESSFULLCUSTOMERSPCWP2.PXBLOCKITERATORPCWP1.TABLEACCESSFULLSALESPCWPParallelplanSerialplanParallelPlanInterpretationSELECT/*+PARALLEL*/cust_city,sum(amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYcust_city;HashJoinPXReceiveFullScanSalesPXBlockIterator1234567PXCoordinatorPXSendQCHashGroupBy8910111213PXReceivePXSendHashHashGroupByFullScanCustomersPXSendBroadcastPXBlockIteratorParallelPlanInterpretationPXCoordinatorFullScanCustomersHashJoinFullScanSalesPXBlockIteratorSet1PCWPSet1PXSendQCHashGroupByPXReceivePXSendHashHashGroupByPXReceivePXSendBroadcastPXBlockIteratorParallelPlanInterpretationPXCoordinatorFullScanCustomersHashGroupByHashGroupByHashJoinPXReceiveFullScanSalesPXBlockIteratorSet1PtoPSet2:TQ10PXReceivePXSendHashPXSendQCPXSendBroadcastPXBlockIteratorParallelPlanInterpretationPXCoordinatorFullScanCustomersHashGroupByHashJoinPXReceiveFullScanSalesPXBlockIteratorSet1PtoPSet2:TQ11Set1PCWPPXSendQCHashGroupByPXReceivePXSendHashPXSendBroadcastPXBlockIteratorParallelPlanInterpretationFullScanCustomersHashJoinPXSendBroadcastPXBlockIteratorFullScanSalesPXBlockIteratorSet2PtoS:TQ12PXSendQCHashGroupByPXReceivePXSendHashHashGroupByPXCoordinatorPXReceiveParallelExecutionServerPoolApoolofserversarecreatedatinstancestartup.MinimumpoolsizeisdeterminedbyPARALLEL_MIN_SERVERS.Poolsizecanincreasebasedondemand.MaximumpoolsizeisdeterminedbyPARALLEL_MAX_SERVERS.Ifaparallelexecutionserverisidleformorethanathresholdperiodoftime,itisterminated.Slavesspecifiedintheminimumsetareneverterminated.MinimumNumberofParallel

ExecutionServersInorderforanoperationtobeparallelized,atleasttwoparallelexecutionserversmustbestarted.Youcanspecifyaminimumpercentageofavailableparallelexecutionserversforoneoperationtobeparallelized.PARALLEL_MIN_PERCENTspecifiesthisminimumpercentage.Ifthisminimumpercentageisnotavailable,anerrorisreported(ORA-12827).Defaultvalueis0.Object’sPARALLELClauseCanbespecifiedfortablesandindexesViewdegreeofparallelismintheDEGREEcolumnofDBA_TABLES(dictionaryDOP)ModifiedbyusingcorrespondingALTERcommandUsedtospecifytheDOPduringobject’sDDLCREATEINDEXCREATETABLE…ASSELECTPartitionmaintenancecommandsNOPARALLELPARALLELintegerPARALLELClause:ExamplesCREATEINDEXord_customer_ixONorders(customer_id)NOLOGGINGPARALLEL;ALTERTABLEcustomersPARALLEL5;ALTERTABLEsalesSPLITPARTITION

sales_q4_2000AT('15-NOV-2000')INTO(PARTITIONsales_q4_1,PARTITIONsales_q4_2)PARALLEL2;EnablingParallelDML/DDL/QUERYTheALTERSESSIONstatementenablesparallelmode:UsedtooverridedictionaryDOPswithFORCEENABLEALTERSESSIONDISABLEPARALLELFORCEDMLDDLPARALLELnQUERYEnablingParallelDML/DDL/QUERYYoucanuseV$SESSIONtolookatsessionstatus:PDML_STATUSPDDL_STATUSPQ_STATUSValuesfortheabovecolumnscanbe:ENABLEDDISABLEDFORCEDUsingParallelizationHintsThefollowingparallelizationhintsareusedtooverrideexistingDOPs.PARALLEL(table_name,DOP_value)NOPARALLEL(table_name)PARALLEL_INDEX(table_name,index,DOP_value)NOPARALLEL_INDEX(table_name,index)SELECT/*+PARALLEL(SALES,9)*/*FROMSALES;SELECT/*+

PARALLEL_INDEX(c,ic,3)*/*FROMcustome