oracle 10g performance: chapter 03a oem demo
DESCRIPTION
TRANSCRIPT
EM Tuning Demo
Copyright 2006 Kyle Hailey
Four Tuning Paths
Database Home Page
Performance Page
Drilldowns
SQL Session
ADDM Page
Solutions
Copyright 2006 Kyle Hailey
Path 1
Database Home Page
Performance Page
Drilldowns
SQL Session
ADDM Page
Solutions
Actions
Copyright 2006 Kyle Hailey
Database Home Page
Copyright 2006 Kyle Hailey
ADDM Details
Solution ASSMSolution ASSM
Solution 2: PartitioningSolution 2: Partitioning
Solution 3: Free ListsSolution 3: Free Lists
TablespaceTablespace
ObjectObject
Copyright 2006 Kyle Hailey
Path 2
Database Home Page
Performance Page
Drilldowns
SQL Session
ADDM Page
Solutions
Actions
Copyright 2006 Kyle Hailey
Database Home Page
Copyright 2006 Kyle Hailey
ADDM Page
Copyright 2006 Kyle Hailey
ADDM Details
Copyright 2006 Kyle Hailey
EM Product Layout for Performance
Database Home Page
Performance Page
Drilldowns
SQL Session
ADDM Page
Solutions
Copyright 2006 Kyle Hailey
Database Home Page
Copyright 2006 Kyle Hailey
Database Performance Page
Lots of waitsLots of waits
ADDM ReportADDM Report
Copyright 2006 Kyle Hailey
ADDM Page
Copyright 2006 Kyle Hailey
ADDM Details
Copyright 2006 Kyle Hailey
EM Product Layout for Performance
Database Home Page
Performance Page
Drilldowns
SQL Session
ADDM Page
Solutions
Copyright 2006 Kyle Hailey
Database Performance Page
““click on the click on the big stuff”big stuff”
Copyright 2006 Kyle Hailey
Wait Drilldown : Concurrency
Copyright 2006 Kyle Hailey
SQL Drilldown
Copyright 2006 Kyle Hailey
Session Drilldown
Copyright 2006 Kyle Hailey
Session : Wait History
Copyright 2006 Kyle Hailey
Part II – Taking ADDM Advice 10 users inserting concurrently into same table
insert into toto1 values (t,lpad('a',1000,'a'));insert into toto1 values (t,lpad('a',1000,'a'));
Copyright 2006 Kyle Hailey
Test 1
Elapsed Time 14:44Elapsed Time 14:44
Copyright 2006 Kyle Hailey
ADDM Details
Copyright 2006 Kyle Hailey
ADDM Says
Use ASSM Use Freelists Partition Object
Trying out Freelists
Copyright 2006 Kyle Hailey
Test 2
Elapsed Time 14:59Elapsed Time 14:59
Was 14:44 – now slower! Was 14:44 – now slower!
Copyright 2006 Kyle Hailey
ADDM Details
Copyright 2006 Kyle Hailey
ADDM says
Increase Log File Size to 2000M to hold at least 20 minutes of redo
Copyright 2006 Kyle Hailey
Test 3
Elapsed 1:23Elapsed 1:2314:4414:44 14:5914:59
Elapsed Time 1:23Elapsed Time 1:23
10x Improvement! 10x Improvement!
Copyright 2006 Kyle Hailey
Final ADDM Details
Copyright 2006 Kyle Hailey
Final
Commit every 50 records instead of every record Got elapsed down from
1:23 0:47
Copyright 2006 Kyle Hailey
In Review
By usingFree ListsLarge Redo Log files
Job Processing went fromOver 14 minutes toJust over 1 minute
Thanks to ADDM and OEM 10g !