Memory Tuning Shared pool (LC, DDC) Database buffer cache (Default, Keep & Recycle) Redo buffer cache Library cache: To find out the issue we can use the following methods. V$librarycache , AWR & OEM V$librarycache Columns: Namespace – Object Name Gets – No. of parsing Gethistratio – Ratio of Phrasing Pins – No.of execution plan Pinhistratio – Ratio of execution plan Reloads – No. of rephrasing Invalidation – No. of rephrasing for DDL alter table, Column invalidation. Formula: Sql> select gethistratio100, pinhitration100 from v$librarycache; 90% – Good <90% – Problem on LB. OR Sql> select [sum(reloads)/sum(pins)] from v$librarycache; <5% – Good 5% – Problem on LB. Data Dictionary cache V$rowcache,…
-
-
Oracle AWR Report – Automatic Workload Repository PT-17
AWR Report – Automatic Workload Repository It will generated a performance related server reports. 10 g Feature. Prior to 10 g ‘Stats pack report’ has used. AWR Stats Pack Text (or) HTML Text Any timeframe with ASH Any timeframe Server level all resources reports Parameters: Statistics_level = Basic/Typical/All – Based on the parameter report will be generated. The information will gathered from following five categories. Base Statistics Matrix Statistics SQL Statistics Active Sessions Advisory Results Physical Reads, CPU Base+ Memory, Object memory utilization, Every past 10 minutes once Logical reads from the query All active session with RAM usage SQL,SQL Tuning, Memory, Segment & Undo Advisory…
-
Oracle Explain plan Performance tuning-16
Explain plan Analyze to gather statistics in Oracle 8i and above, Use DBMS_STATS. ANALYZE TABLE <table> VALIDATE STRUCTURE; — Validate table structure. dbms_stats.set_table_prefs – From 11g onwards. Designed to allow the DBA more control over the freshness of their statistics. DBMS_XPLAN.xxx DISPLAY – to format and display the contents of a plan table. DISPLAY_AWR – to format and display the contents of the execution plan of a stored SQL statement in the AWR. DISPLAY_CURSOR – to format and display the contents of the execution plan of any loaded cursor. DISPLAY_SQL_PLAN_BASELINE – to display one or more execution plans for the SQL statement identified by SQL handle DISPLAY_SQLSET – to…
-
Oracle Performance Tuning-15
Performance Tuning Optimizer mode. It will collect the statistics of the object. Optimizer will 1.transform the query – parsed 2.estimate 3. Generate a plan. Rule based optimizer (RBO) – deprecated from 10g onwards. Cost based optimizer (CBO) Keyword RBO – Rule. 15 objects can be gathered. CBO – Choose (or) All_rows, First_rows & first_rows_n. More than 256 objects can be gathered. The cost based optimizer will always select the low cost. It may be an index scan or table scan, which is lower. Parameters Choose / All_rows – It will gather the stats for all the rows of the table. First_rows – Only first row of the table First_rows_n –…
-
Oracle RMAN Configuration and Monitoring Scripts-14
RMAN Configuration and Monitoring Scripts RMAN – SHOW ALL is the code to know the RMAN configurations. RMAN backup configuration and schedule Find out the backup configuration info All rman> show all; List used block bakup rman> list backup; rman> list backup of datafile 4; rman> list backup of tablespace tools; List image copy backup rman> list copy of database; rman> list copy of datafile 4; rman> list copy of tablespace tools; Based on the application requirements this needs to be configured. By default backup will be stored in disk. We can also change this to tape. Keyword: Disk –Disk Tape — SBT/ SBT_TAPE rman> configure default…