Sumário Itens Encontrados: 350Part 1: Basic ConceptsChapter 1: SQL FeaturesSQL and Declarative Programming LanguagesStatements and SQL_IDsCross-Referencing Statement and SQL_IDArray InterfaceSubquery FactoringThe Concept of Subquery FactoringJoinsInner Joins and Traditional Join SyntaxOuter Joins and ANSI Join SyntaxChapter 2: The Cost-Based OptimizerThe Optimal Execution PlanThe Definition of CostThe CBOâs Cost-Estimating AlgorithmCalculating CostThe Quality of the CBOâs Plan SelectionThe Optimization ProcessParallelismQuery TransformationFinal State Query OptimizationChapter 3: Basic Execution Plan ConceptsDisplaying Execution PlansDisplaying the Results of EXPLAIN PLANEXPLAIN PLAN May Be MisleadingDisplaying Output from the Cursor CacheDisplaying Execution Plans from the AWRUnderstanding OperationsWhat an Operation DoesHow Operations InteractHow Long Do Operations Take?Chapter 4: The Runtime EngineCollecting Operation Level Runtime DataThe GATHER_PLAN_STATISTICS HintSetting STATISTICS_LEVEL=ALLEnabling SQL TracingDisplaying Operational Level DataDisplaying Runtime Engine Statistics with DBMS_XPLAN.DISPLAY_CURSORDisplaying Runtime Engine Statistics with V$SQL_PLAN_STATISTICS_ALLDisplaying Session Level Statistics with SnapperThe SQL Performance MonitorWorkareasOperations Needing a WorkareaAllocating Memory to a WorkareaOptimal,One-Pass,and Multipass OperationsShortcutsScalar Subquery CachingJoin ShortcutsResult and OCI CachesFunction Result CacheChapter 5: Introduction to TuningUnderstanding the ProblemUnderstanding the Business ProblemUnderstanding the Technical ProblemUnderstanding the SQL StatementUnderstanding the DataUnderstanding the Problem Wrap UpAnalysisRunning the Statement to CompletionAnalyzing Elapsed TimeWhen the Elapsed Times Doesnât Add UpWhen the Time Does Add UpFixing the ProblemCheck the StatisticsChanging the CodeAdding HintsMaking Physical Changes to the DatabaseMaking Changes to the EnvironmentRunning the SQL Tuning AdvisorRethink the RequirementChapter 6: Object Statistics and DeploymentThe Principle of Performance ManagementThe Royal Mail ExampleThe Airport ExampleService Level Agreements in ITNon-database Deployment StrategiesThe Strategic Direction for the CBOThe History of Strategic FeaturesImplications of the CBO StrategyWhy We Need to Gather StatisticsHow Often Do We Need to Change Execution Plans?Wolfgang Breitlingâs Tuning by Cardinality FeedbackThe TCF CorollaryConcurrent Execution PlansSkewed Data and HistogramsWorkload VariationsConcurrent Execution Plans Wrap UpOracleâs Plan Stability FeaturesStored OutlinesSQL ProfilesSQL Plan BaselinesIntroducing TSTATSAcknowledgementsAdjusting Column StatisticsTSTATS in a NutshellAn Alternative to TSTATSDeployment Options for Tuned SQLWhen Just One SQL Statement Needs to ChangeWhen Multiple SQL Statements Need to ChangePart 2: Advanced ConceptsChapter 7: Advanced SQL ConceptsQuery Blocks and SubqueriesTerminologyHow Query Blocks are ProcessedFunctionsAggregate FunctionsAnalytic FunctionsCombining Aggregate and Analytic FunctionsSingle-row FunctionsThe MODEL ClauseSpreadsheet ConceptsA Moving Median with the MODEL ClauseWhy Not Use PL/SQL?Chapter 8: Advanced Execution Plan ConceptsDisplaying Additional Execution Plan SectionsDBMS_XPLAN Formatting OptionsRunning EXPLAIN PLAN for AnalysisQuery Blocks and Object AliasOutline DataPeeked BindsPredicate InformationColumn ProjectionRemote SQLAdaptive PlansResult Cache InformationNotesUnderstanding Parallel Execution PlansOperations That Can Be Run in ParallelControlling Parallel ExecutionGranules of ParallelismData Flow OperatorsParallel Query Server Sets and DFO TreesTable Queues and DFO OrderingMultiple DFO TreesParallel Query Distribution MechanismsWhy Forcing Parallel Query Doesnât Force Parallel QueryFurther ReadingUnderstanding Global HintsHinting Data Dictionary ViewsApplying Hints to Transformed QueriesThe NO_MERGE HintChapter 9: Object StatisticsThe Purpose of Object StatisticsCreating Object StatisticsGathering Object StatisticsExporting and Importing StatisticsTransferring StatisticsSetting Object StatisticsCreating or Rebuilding Indexes and TablesCreating Object Statistics Wrap UpExamining Object StatisticsExamining Object Statistics in the Data DictionaryExamining Exported Object StatisticsStatistic DescriptionsTable StatisticsIndex StatisticsColumn StatisticsStatistics Descriptions Wrap-upStatistics and PartitionsGathering Statistics on Partitioned TablesHow the CBO Uses Partition-level StatisticsWhy We Need Partition-level StatisticsStatistics and Partitions Wrap-upRestoring StatisticsLocking StatisticsPending StatisticsA Footnote on Other Inputs to the CBOInitialization ParametersSystem StatisticsOther Data Dictionary InformationPart 3: The Cost-Based OptimizerChapter 10: Access MethodsAccess by ROWIDROWID ConceptsB-tree Index AccessINDEX FULL SCANINDEX RANGE SCANINDEX SKIP SCANINDEX UNIQUE SCANINDEX FAST FULL SCANINDEX SAMPLE FAST FULL SCANINDEX JOINAND_EQUALBitmap Index AccessFull Table ScansTABLE and XMLTABLECluster AccessChapter 11: JoinsJoin MethodsNested loopsHash joinsMerge joinsCartesian joinsJoin OrdersJoin orders without hash join input swappingJoin orders with hash join input swappingSemi-joinsStandard semi-joinsNull-accepting semi-joinsAnti-joinsStandard anti-joinsNull-aware anti-joinsDistribution Mechanisms for Parallel JoinsThe PQ_DISTRIBUTE hint and parallel joinsFull partition-wise joinsPartial partition-wise joinsBroadcast distributionRow source replicationHash distributionAdaptive parallel joinsData bufferingBloom filteringChapter 12: Final State OptimizationJoin OrderJoin MethodAccess MethodIN List IterationChapter 13: Optimizer TransformationsNo-brainer TransformationsCount TransformationPredicate Move-aroundSet and Join TransformationsJoin EliminationOuter Join to Inner JoinFull Outer Join to Outer JoinSemi-Join to Inner JoinSubquery UnnestingPartial JoinsJoin FactorizationSet to JoinAggregation TransformationsDistinct AggregationDistinct PlacementGroup by PlacementGroup by PushdownSubquery TransformationsSimple View MergingComplex View MergingFactored Subquery MaterializationSubquery PushdownJoin Predicate PushdownSubquery DecorrelationSubquery CoalescingMiscellaneous TransformationsOr ExpansionMaterialized View RewriteGrouping Sets to Union ExpansionOrder by EliminationTable ExpansionStar TransformationThe Distributed Join Filter ProblemSolving the Distributed Join Filter ProblemIn the FuturePart 4: OptimizationChapter 14: Why Do Things Go Wrong?Cardinality ErrorsCorrelation of ColumnsStatistics Feedback and DBMS_STATS.SEED_COL_USAGE FeaturesStale StatisticsDaft Data TypesCaching EffectsTransitive ClosureUnsupported TransformationsMissing InformationBad Physical DesignContentionChapter 15: Physical Database DesignAdding and Removing IndexesRemoving IndexesIdentifying Required IndexesManaging ContentionSequence ContentionThe Hot-block ProblemPartitioningFull Table Scans on Partitions or SubpartitionsPartition-wise JoinsParallelization and PartitioningDenormalizationMaterialized ViewsManual Aggregation and Join TablesBitmap Join IndexesCompressionIndex CompressionTable CompressionLOBsChapter 16: Rewriting QueriesUse of Expressions in PredicatesEquality Versus Inequality PredicatesImplicit Data-Type ConversionsBind VariablesUNION,UNION ALL,and ORIssues with General Purpose ViewsHow to Use Temporary TablesAvoiding Multiple Similar SubqueriesChapter 17: Optimizing SortsThe Mechanics of SortingMemory Limits for SortsDisk-based SortsAvoiding SortsNon-sorting Aggregate FunctionsIndex Range Scans and Index Full ScansAvoiding Duplicate SortsSorting Fewer ColumnsTaking Advantage of ROWIDsSolving the Pagination ProblemSorting Fewer RowsAdditional Predicates with Analytic FunctionsViews with Lateral JoinsAvoiding Data DensificationParallel SortsChapter 18: Using HintsAre Hints Supportable?The PUSH_SUBQ storyThe DML error logging storyDocumented versus undocumented hintsThe MODEL clause corollarySupportability conclusionTypes of HintsEdition -based redefinition hintsHints that cause errorsRuntime engine hintsOptimizer hints that are hintsProduction-hinting case studiesThe bushy joinMaterialization of factored subqueriesSuppressing order by elimination and subquery unnestingThe v$database_block_corruption viewChapter 19: Advanced Tuning TechniquesLeveraging an INDEX FAST FULL SCANSimulating a Star TransformationSimulating an INDEX JOINJoining Multi-Column IndexesUsing ROWID Ranges for Application-Coded Parallel ExecutionConverting an Inner Join to an Outer JoinPart 5: Managing Statistics with TSTATSChapter 20: Managing Statistics with TSTATSManaging Column StatisticsTime-based columnsColumns with NUM_DISTINCT=1Skewed column values and range predicatesCorrelated columns and expressionsUse of sample data for complex statistical issuesManaging column statistics wrap upThe DBMS_STATS.COPY_TABLE_STATS mythCardinality estimates with global statisticsCosting full table scans of table partitionsTemporary TablesThe pros and cons of dynamic samplingFabricating statistics for temporary tablesHow to Deploy TSTATS