blog menu1

Temp 4 Deepak


Some MOS notes helped me:
Note: 215858.1 – “Interpreting HANGANALYZE trace files to diagnose hanging and performance problems”
Note: 310830.1 – “How to Use Oradebug to Get Trace File Name and Location”
Note: 417879.1 – “-prelim Option When Running System State Dump”
Old Statistics of the dictionary collected - http://jhdba.wordpress.com/2009/05/19/purging-statistics-from-the-sysaux-tablespace/

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=794006208114638&id=958181.1&_afrWindowMode=0&_adf.ctrl-state=10bq8abjhf_64

Diagnosing Why a Query is Not Using an Index [ID 67522.1]
1. Block corruption 840978.1
2. debug Cluster service - 357808.1
Agent Blackout Initiated By Emctl Command Not Ending Doc ID: 559577.1

EMDiagkit Download and Master Index Doc ID: 421053.1

How to Troubleshoot the EM 10gR1 Blackout Sub-system Doc ID: 284024.1

Troubleshooting Blackouts in EM 10g Grid Control using EMDiag Kit Doc ID: 300671.1

Document 457531.1 How To Move SQL Profiles From One Database To Another Database

Document 1253696.1 How To Use SQL Profiles for Queries Using Different Literals
References
NOTE:262687.1 - Using the DBMS_SQLTUNE package to Run the Sql Tuning Advisor

NOTE:457531.1 - How To Move SQL Profiles From One Database To Another Database

NOTE:1253696.1 - How To Use SQL Profiles for Queries Using Different Literals

https://support.oracle.com/epmos/main/downloadattachmentprocessor?attachid=1115994.1:ACS_CONCEPT_DEMO_11G&width=100%&height=100%

https://support.oracle.com/epmos/faces/ui/km/SearchDocDisplay.jspx?_afrLoop=712456230288812&type=DOCUMENT&id=233112.1&displayIndex=3&_afrWindowMode=0&_adf.ctrl-state=5v1bdno0x_102

http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_30.shtml -- Very Good

http://docs.oracle.com/cd/B10501_01/server.920/a96533/optimops.htm#74098 -- 9i Optimizer details Book


####################################################
Master Note for OLTP Compression [ID 1223705.1]
Note 785787.1 11g new features:Advanced Compression overview and advantages
Note 882712.1 11g New feature:All About Advanced Table Compression(Overview,Use,Examples,Restrictions)
Note 1101900.1 Performance Issue with Update Statement in Compressed Tablespace
Note 987049.1 Performance Issue After Enabling Compression
Note 829068.1 COMPRESS FOR ALL OPERATIONS generates lot of redo
Note 1149283.1 The OLTP Compression Saves No Space As Expected Using A Row Too Big
Note 805205.1 My Data are not compressed
###################################################
Master Note for Data Pump [ID 1264715.1]
Master Note for Automatic Storage Management (ASM) [ID 1187723.1]
Master Note for Real Application Clusters (RAC) and Grid Infrastructure (Doc ID 1096952.1)
Master Note for Data Guard [ID 1101938.1]
Master Note: SQL Query Performance Overview [ID 199083.1]
Oracle10g / 11g - Getting Started with Recovery Manager (RMAN) [ID 360416.1]
* Diagnosing Why a Query is Not Using an Index [ID 67522.1]
Query Performance Degradation - Recommended Actions [ID 742112.1]
Master Note for Handling Oracle Database Corruption Issues [ID 1088018.1]
Performance URL -> SQL PROFILES:
http://nocoug.org/download/2008-08/a-tour-of-the-awr-tables.nocoug-Aug-21-2008.abercrombie.html#script-aas-per-hour
http://www.gplivna.eu/papers/v$session_longops.htm
http://structureddata.org/2008/01/06/oracle-11g-real-time-sql-monitoring-using-dbms_sqltunereport_sql_monitor/
http://oracle-randolf.blogspot.com/
http://oracle-randolf.blogspot.com/2009/03/plan-stability-in-10g-using-existing.html
http://hemantoracledba.blogspot.com/2009/01/when-not-to-use-vsessionlongops.html
http://kerryosborne.oracle-guy.com/2009/07/why-isnt-oracle-using-my-outline-profile-baseline/
http://kerryosborne.oracle-guy.com/
http://kerryosborne.oracle-guy.com/?s=create_baseline.sql
http://kerryosborne.oracle-guy.com/2011/07/cardinality-feedback/
http://prutser.wordpress.com/2009/04/19/flushing-a-cursor-out-of-the-library-cache/
http://www.ora600.be/node/3998
http://rwijk.blogspot.com/2008/03/dbmsxplandisplaycursor.html
http://jonathanlewis.wordpress.com/2011/01/12/fake-baselines/
http://kerryosborne.oracle-guy.com/2009/04/oracle-11g-sql-plan-management-sql-plan-baselines/
http://kerryosborne.oracle-guy.com/2009/07/why-isnt-oracle-using-my-outline-profile-baseline/
http://blog.tanelpoder.com/2007/08/21/oracle-11g-internals-part-1-automatic-memory-management/
http://arup.blogspot.in/2008_08_01_archive.html
TKProf Interpretation (9i and above) [ID 760786.1]
Steps to generate HANGANALYZE trace files [ID 175006.1]
Interpreting HANGANALYZE trace files to diagnose hanging and performance problems [ID 215858.1]
Diagnosing and Resolving Error ORA-04031 on the Shared Pool or Other Memory Pools [Video] [ID 146599.1]
ORA-4031 Common Analysis/Diagnostic Scripts [Video] [ID 430473.1]
Master Note for Diagnosing ORA-600 [ID 1092832.1]
Top 11 Things to do NOW to Stabilize your RAC Cluster Environment [ID 1344678.1]
==============================
Scheduler Jobs Info
==============================
Master Note - DBMS_SCHEDULER and DBMS_JOB [ID 1268716.1]
============================
Datapump Performance Issue With Content=Metadata_only [ID 1290574.1]
How To Diagnose And Troubleshoot Import Or Datapump Import Hung Scenarios [ID 795034.1]
=============================
Troubleshooting and Diagnosing ORA-4031 Error [Video] [ID 396940.1]
=============================
http://dbpedias.com/wiki/Oracle:Memory_Advisor_Functionality_and_Views
UPGRADES:
============
ASM 11.2 Configuration KIT (ASM 11gR2 Installation & Configuration, Deinstallation, Upgrade, ASM Job Role Separation (Standalone Only). [ID 1092213.1]
ASM 11gR2: How To Upgrade An ASM Instance To Release 11gR2 (11.2)? (***Updated***) [ID 950200.1]
BUG Fixes in 11.2.0.2 and 11.2.0.3
===================================
11.2.0.2 Patch Set - List of Bug Fixes by Problem Type [ID 1179583.1]
11.2.0.3 Patch Set - List of Bug Fixes by Problem Type [ID 1348303.1]
<span style="background-color: #ffffff; color: #333333; font-family: Arial,Helvetica,sans-serif; font-size: 14px; line-height: 1.5;">**References**</span>
NOTE:344135.1 - Ordering of Result Data
NOTE:70135.1 - Index Fast Full Scan Usage To Avoid Full Table Scans
NOTE:212391.1 - Index Skip Scan Feature
NOTE:67409.1 - When will an ORDER BY use an Index to Avoid Sorting?
NOTE:10577.1 - Driving ORDER BY using an Index (Oracle7)
NOTE:62153.1 - Optimization of large inlists/multiple OR`s
NOTE:754931.1 - Cost Based Optimizer - Common Misconceptions and Issues - 10g and Above
NOTE:1369591.1 - Optimizer Statistics - Central Point
NOTE:44961.1 - Statistics Gathering: Frequency and Strategy Guidelines
NOTE:160089.1 - TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance
NOTE:73167.1 - Handling of equally ranked (RBO) or costed (CBO) indexes
NOTE:50607.1 - How to specify an INDEX Hint
NOTE:29236.1 - QREF: SQL Statement HINTS
NOTE:28934.1 - Use of indexes with NOT IN subquery
NOTE:70067.1 - All about Bitmap Indexes
NOTE:199070.1 - Optimizing statements that contain views or subqueries
NOTE:68809.1 - Distributed Queries
NOTE:68084.1 - Using hints to optimize an Update with subquery not using index on updated table
NOTE:68992.1 - Predicate Selectivity
NOTE:69992.1 - Why is my hint ignored?
NOTE:28426.1 - Partition Views and the use of Indexes (7.1 & 7.2)
NOTE:43194.1 - Partition Views in 7.3: Examples and Tests
NOTE:30779.1 - Init.ora Parameter "NLS_SORT" Reference Note
NOTE:227335.1 - Linguistic Sorting - Frequently Asked Questions

Useful Blog Posts:

SQL Plan Management (Part 1 of 4) Creating SQL plan baselines:http://blogs.oracle.com/optimizer/entry/sql_plan_management_part_1_of_4_creating_sql_plan_baselines
SQL Plan Management (Part 2 of 4) SPM Aware Optimizer:http://blogs.oracle.com/optimizer/entry/sql_plan_management_part_2_of_4_spm_aware_optimizer
SQL Plan Management (Part 3 of 4): Evolving SQL Plan Baselines:http://blogs.oracle.com/optimizer/entry/sql_plan_management_part_3_of_4_evolving_sql_plan_baselines_1
SQL Plan Management (Part 4 of 4): User Interfaces and Other Features:http://blogs.oracle.com/optimizer/entry/sql_plan_management_part_4_of_4_user_interfaces_and_other_features

Plan Transportation & Loading/Dropping

Document 787692.1 Loading Hinted Execution Plans into SQL Plan Baseline
Note:880485.1 Transporting SQL PLAN Baselines from one database to another.
Note:456019.1 HOW TO TRANSPORT A SQL TUNING SET
Note:789888.1 HOW TO LOAD SQL PLANS INTO SPM FROM AWR
Note:801033.1 HOW TO MOVE 10gR2 EXECUTION PLANS AND LOAD INTO 11g SPM

Note:790039.1 HOW TO DROP PLANS FROM SPM REPOSITRY

Known Issues

Note:788853.1 Sql Plan Baseline Not always created
Note:1187767.1 Baseline Not Used Under Plsql When OFE Is Changed
Note:1204524.1 Dbms_spm.Load_plans_from_sqlset Getting Xml Parsing Error

Document 12732879.8 Bug 12732879 - Execution Plan of Query with non-peeked binds is not reproducible
Document 11719151.8 Bug 11719151 - SQL Plan Management capture causes slowness
Document 9910484.8 Bug 9910484 - SQL Plan Management Capture uses excessive space in SYSAUX
Note that the latest Patchset release contains fixes for known issues across the spectrum of server products. For example: 


Document 1348303.1 11.2.0.3 Patch Set - List of Bug Fixes by Problem Type

Tracing and Debugging

Note:789520.1 SQL PLAN MANAGEMENT TRACING

Pre 11g

Plan stability in earlier versions was ensured by Stored Outlines and SQL Profiles. These features have a number of deficiencies when compared to SPM but can provide stable systems. Outlines alone are in-flexible so when data volume, profile or shape does change, adjustments cannot be made. SQL Profiles on the other hand provide a static plan initially, but can change with volume changes meaning that they do not necessarily provide the static plan that may be desired.


Note:
Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines.

Stored Outline documents:

Note:67536.1 Stored Outline Quick Reference

Note:445126.1 HOW TO: Create a Stored Outline Based Upon an Existing Cursor

Note:728647.1 How to Transfer Stored Outlines from One Database to another (9i and above)
Note:102311.1 How to Move Stored Outlines for One Application from One Database to Another (8i)

Note:730062.1 How to Edit a Stored Outline to Use the Plan from Another Stored Outline
Note:726802.1 Editing Stored Outlines in Oracle10g and Oracle11g

Note:463288.1 How to generate an outline with a good plan loaded into shared_pool

References

BUG:11719151 - MERGE STATEMENT USED WHEN SPM CAPTURE CAUSING PERFORMANCE ISSUE
NOTE:1187767.1 - Baseline Not Used Under Plsql When OFE Is Changed
NOTE:1204524.1 - Dbms_spm.Load_plans_from_sqlset Getting Xml Parsing Error
NOTE:12732879.8 - Bug 12732879 - Execution Plan of Query with non-peeked binds is not reproducible
NOTE:1323444.1 - Database Hangs after loading plan baselines from sqlset from 10gR2 to 11gR2
NOTE:1348303.1 - 11.2.0.3 Patch Set - List of Bug Fixes by Problem Type
NOTE:1353272.1 - Uploaded STS Execution plan to SPM Differs from the STS Original
BUG:12732879 - SPM: COULDN'T REPRODUCE ANY ENABLED+ACCEPTED PLAN SO USING THE COST-BASED PLAN
BUG:9910484 - UNNECESSARY UPDATES ON SQLOBJ$DATA CAUSING OBJECT AND TABLESPACE (SYSAUX) GROWTH
NOTE:102311.1 - How to Transfer Stored Outlines from One Database to Another (8i)
NOTE:11719151.8 - Bug 11719151 - SQL Plan Management capture causes slowness
NOTE:445126.1 - HOW TO: Create a Stored Outline Based Upon an Existing Cursor
NOTE:456019.1 - HOW TO TRANSPORT A SQL TUNING SET
NOTE:456518.1 - How to Use SQL Plan Management (SPM) - Example Usage
NOTE:567104.1 - Init.ora Parameter "OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES" Reference Note
NOTE:567107.1 - Init.ora Parameter "OPTIMIZER_USE_SQL_PLAN_BASELINES" Reference Note
NOTE:601807.1 - Oracle 11gR1 Upgrade Companion
NOTE:67536.1 - Stored Outline Quick Reference
NOTE:726802.1 - Editing Stored Outlines in Oracle10g and Oracle11g
NOTE:728647.1 - How to Transfer Stored Outlines from One Database to Another (9i and above)
NOTE:730062.1 - How to Edit a Stored Outline to Use the Plan from Another Stored Outline
NOTE:785351.1 - Oracle 11gR2 Upgrade Companion
NOTE:787692.1 - Loading Hinted Execution Plans into SQL Plan Baseline.
NOTE:788853.1 - Sql Plan Baseline Not always created
NOTE:789520.1 - SQL PLAN MANAGEMENT TRACING
NOTE:789888.1 - HOW TO LOAD SQL PLANS INTO SPM FROM AWR
NOTE:790039.1 - How to Drop Plans from the SQL Plan Management (SPM) Repository
NOTE:801033.1 - HOW TO MOVE 10gR2 EXECUTION PLANS AND LOAD INTO 11g SPM
NOTE:880485.1 - Transporting SQL PLAN Baselines from one database to another.
NOTE:9910484.8 - Bug 9910484 - SQL Plan Management Capture uses excessive space in SYSAUX
•215187.1 SQLTXPLAIN (SQLT) tool
•1454160.1 SQLT FAQ
•1366133.1 SQL Health-Check (SQLHC) script
•1417774.1 SQLHC FAQ
•1455583.1 SQLHC Video
•224270.1 Trace Analyzer TRCANLZR (TRCA) tool
•243755.1 PL/SQL Profiler script
•1460440.1 PX Health-Check (PXHCDR) script

No comments:

Post a Comment

Subscribe to: Posts (Atom)
  • Redshift - dataypes
    Amazon Redshift Data Types When you are setting up a connection to an external data source, Spotfire needs to map the data types in the...
  • Oracle DB Growth
    SET   SERVEROUT   ON   SET   VERIFY   OFF   SET   LINES 200   SET   PAGES 2000   DECLARE      v_ts_id                   ...
  • Kafka - Overview
    Producer  --->>  Kakfa server (Kafka Broker), cluster is a group of broker which has  a toic - ---> consumer (request back for mo...