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

Diagnosing performance issues using the alert log

To diagnose certain performance issues, even the alert log can be used successfully.

Getting ready

There are some parameters to look at in the init.ora file of our database instance.

The parameter BACKGROUND_DUMP_DEST indicates the directory in which the alert log is located. If the parameter LOG_CHECKPOINTS_TO_ALERT is set to TRUE, we will find even checkpoint information in the alert log. By default this parameter is set to FALSE.

Before starting, we can issue the following command:

ALTER SYSTEM SET LOG_CHECKPOINTS_TO_ALERT = TRUE;
SHOW PARAMETER BACKGROUND_DUMP_DEST

This writes checkpoint information to the alert log and shows the directory in which we will find the alert log file (named alert_<instance_name>.log ...

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

Sam R. Alapati, Darl Kuhn, Bill Padfield

Oracle Database 11g Release 2 Performance Tuning Tips & Techniques

Richard Niemiec

Oracle SQL Revealed: Executing Business Logic in the Database Engine

Alex Reprintsev

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

Darl Kuhn, Sam Alapati, Arup Nanda

Publisher Resources

ISBN: 9781849682602Other