book

Oracle Database 11gR2 Performance Tuning Cookbook

by Ciro Fiorillo

January 2012

Intermediate to advanced

542 pages

11h 28m

English

Packt Publishing

Read now

Unlock full access

Support files, eBooks, discount offers and moreWhy Subscribe?Free Access for Packt account holdersInstant Updates on New Packt Books
What this book covers

Downloading the example codeErrataPiracyQuestions
IntroductionIncorrect session managementPoorly designed cursor managementInadequate relational designImproper use of storage structures
How to do it...How it works...There's more…See also
Getting readyHow to do it...How it works...There's more...
Getting readyHow to do it...How it works...There's more...See also
Getting readyHow to do it...How it works...There's more...Collecting different amounts of dataProducing a report on a specific SQLAutomating snapshot generationStatspack maintenance
Getting readyHow to do it...How it works...There's more...See also
Getting readyHow to do it...How it works...There's more...
Getting readyHow to do it...How it works...There's more...See also
Getting readyHow to do it...How it works...There's more...See also
Introduction
Getting readyHow to do it...How it works...There's more...Dedicated server versus shared serverWeb applicationsClient-server Online Transaction ProcessingBatch processingSee also
Getting readyHow to do it...How it works...There's more...PL/SQL and parsingDiagnosing soft and hard parsingSee also
How to do it...How it works...There's more...See also
How to do it...How it works...There's more...Correct definition of a sequenceSee also
How to do it...How it works...There's more...Materialized views in depthMaterialized views and grantsDatabase parameters to use query rewriteCan I use materialized views in an OLTP environment?
Getting readyHow to do it...How it works...There's more...Not 1NF structures
How to do it...How it works...There's more...See also
Introduction
Getting readyHow to do it...How it works...There's more...See also
How to do it...How it works...There's more...Estimating table size with different PCTFREE parameter
Getting readyHow to do it...How it works...There's more...
How to do it...How it works...There's more...Cluster sizeCluster indexClustering and truncating
How to do it...How it works...There's more...Sorted hash clustersCustom hash functionSingle-table hash clusters
How to do it...How it works...There's more...What is the "small percentage" of the data which assures we can improve performances using B-tree indexes?See also
Getting readyHow to do it...How it works...There's more...Index rebuild and statisticsSee also
How to do it…How it works...There's more...
How to do it...How it works...There's more...
How to do it...How it works...There's more...Bitmap join indexSee also
How to do it...How it works...There's more...INCLUDING, OVERFLOW, PCTTHRESHOLDLogical ROWIDSee also
How to do it...How it works...There's more...List partitioningHash partitioningComposite partitioning
Introduction
Getting readyHow to do it...How it works...There's more...Concurrency and scalabilitySecurity issuesSee also
How to do it...How it works...There's more...The High-Water MarkPctFree, PctUsed, and FREELISTsSee also
Getting readyHow to do it...How it works...There's more...See also
Getting readyHow to do it...How it works...There's more...See also
How to do it...How it works...There's more...When to use direct path loadSee also
How to do it...How it works...There's more...See also
How to do it...How it works...There's more...
Getting readyHow to do it...How it works...There's more...See also
Introduction
Getting readyHow to do it...How it works...There's more...See also
How to do it...How it works...There's more...See also
How to do it...How it works...There's more...See also
How to do it...How it works...There's more...See also
Getting readyHow to do it...How it works...There's more...See also
How to do it...How it works...There's more...Optimal storage parameters for temporary tablespacesSee also
Introduction
How to do it...How it works...There's more...See also
How to do it...How it works...There's more...See also
How to do it...How it works...There's more...
How to do it...How it works...There's more...
How to do it...How it works...There's more...See also
Getting readyHow to do it...How it works...There's more...See also
How to do it...How it works...There's more...See also
How to do it...How it works...There's more...See also
How to do it...How it works...There's more...Using WHEN and OF in trigger definitionAvoid FOR EACH ROW in triggers, when possibleSee also
Introduction
How to do it...How it works...There's more...Errors in hintsSee also
How to do it...How it works...There's more...Lock table statistics for load or highly volatile tablesOther procedures in DBMS_STATSSee also
How to do it...How it works...There's more...Height-based and value-based (frequency) histogramsSee also
Getting readyHow to do it...How it works...There's more...Private and public stored outlinesSee also
How to do it...How it works...There's more...See also
Getting readyHow to do it...How it works...There's more...See also
Getting readyHow to do it...How it works...There's more...See also
Getting readyHow to do it...How it works...There's more...See also
Introduction
Getting readyHow to do it...How it works...There's more...Configuring the client-side result cacheSee also
Getting readyHow to do it...How it works...There's more...Parallel query and I/OWhen to use parallel SQLSee also
How to do it...How it works...There's more...See also
How to do it...How it works...There's more...See also
How to do it...How it works...There's more...See also
Getting readyHow to do it...How it works...There's more...See also
Introduction
How to do it...How it works...There's more...See also
How to do it...How it works...There's more...How to minimize missesSee also
How to do it...How it works...There's more...Tuning the Dictionary CacheSee also
How to do it...How it works...There's more...See also
How to do it...How it works...There's more...See also
Introduction
Getting readyHow to do it...How it works...There's more...See also
How to do it...How it works...There's more...See also
Getting readyHow to do it...How it works...There's more...RAID level 0RAID level 1RAID level 5RAID level 0+1See also
How to do it...How it works...There's more...See also
How to do it...How it works...There's more...See also
How to do it...How it works...There's more...See also 
Introduction
Getting readyHow to do it...How it works...There's more...See also
Getting readyHow to do it...How it works...There's more...See also
How to do it...How it works...There's more...See also
Getting readyHow to do it...How it works...There's more...See also
Getting readyHow to do it...How it works...There's more...See also
ALL_OBJECTSFields
FieldsSee also
FieldsSee also
FieldsSee also
Fields
Fields
Fields
FieldsSee also
Fields
FieldsSee also
FieldsSee also
Fields
Fields
Fields
Fields
FieldsSee also
FieldsSee also
FieldsSee also
FieldsSee also
FieldsSee also
FieldsSee also
FieldsSee also
Fields
Fields
FieldsSee also
FieldsSee also
Fields
FieldsSee also
FieldsSee also
FieldsSee also
FieldsSee also
FieldsSee also
FieldsSee also
FieldsSee also
Fields
Fields
FieldsSee also
FieldsSee also
FieldsSee also
FieldsSee also
FieldsSee also
Fields
Fields
FieldsSee also
FieldsSee also
FieldsSee also
FieldsSee also
FieldsSee also
Fields
Fields
FieldsSee also
FieldsSee also
Fields
DBMS_ADDMProcedures
Procedures
Procedures
Procedures
Procedures
Procedures
Procedures
Procedures
Procedures
Procedures
Procedures
Procedures
Procedures
Procedures
Procedures

Content preview from Oracle Database 11gR2 Performance Tuning Cookbook

Using partitioning

Tables (and indexes) may become very large in a database, driving performance and maintenance problems.

Partitioning is the way to improve performance in large tables (Oracle suggests to partition tables with more than 2 GB of data), to ease the configuration and care of these objects and to reduce downtime in case of failures or scheduled maintenance such as move the tables or take some data offline.

In this recipe, we discuss partitioning a table using range partitioning. We will present hash, list, and composite partitioning as options.

How to do it...

We can create a table range partitioned adding the PARTITION BY RANGE clause to the CREATE TABLE statement:

CREATE TABLE SALES_RP (
 PROD_ID NUMBER NOT NULL,
 CUST_ID NUMBER NOT ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Start your free trial

Oracle Database 11g Performance Tuning Recipes: A Problem-Solution Approach

Publisher Resources

ISBN: 9781849682602Other

Oracle Database 11gR2 Performance Tuning Cookbook

by Ciro Fiorillo

Using partitioning

How to do it...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

You might also like

Oracle Database 11g Performance Tuning Recipes: A Problem-Solution Approach

Oracle Database 11g Release 2 Performance Tuning Tips & Techniques

Oracle SQL Revealed: Executing Business Logic in the Database Engine

RMAN Recipes for Oracle Database 12c: A Problem-Solution Approach, Second Edition

Publisher Resources