tuning

Index Tuning

Oracle indexes can be tuned looking to three main characteristics: SELECTIVITY: you can calculate index selectivity using this formula: SELECT DISTINCT_KEYS/NUM_ROWS SELECTIVITY FROM DBA_INDEXES / Values range from 0 to 1, where 1 is the optimal value (UNIQUE indexes and PK), while with values near to 0 performance is lower CLUSTERING FACTOR: measures how many keys are stored in a single index block. This value is stored in DBA_INDEXES.CLUSTERING_FACTOR column. If it’s near to DBA_INDEXES_BLOCKS value then the index is good, if instead it’s near to DBA_INDEXES.NUM_ROWS performance is lower BLEVEL: Oracle indexes are stored in a B-TREE structure where Continua a leggere

Loading SQL Plans into SPM using AWR

Spesso può esserci bisogno di inserire SQL Plans in SPM ma ciò non è possibile perché la query non è in memoria. Questa è una semplice procedura per caricare SQL Plans in SPM tramite AWR. Il primo posso è creare, se non esiste, un STS (SQL Tuning Set). exec dbms_sqltune.create_sqlset(sqlset_name => ‘1ffbnq9wwkhtz_sqlset_test’, description => ‘query test’); Nome e descrizione sono customizzabili. In sqlset_name viene utilizzato sql_id in modo da poter ricordare la query. Il passo successivo è quello di prendere gli id iniziale e finale dello snapshot. Io preferisco generare il report AWR per assicurarmi che la query sia in Continua a leggere

Create Oracle SQL Profile For Tuning

1024×768 Normal 14 false false false IT X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Tabella normale”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:”Calibri”,”sans-serif”; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:”Times New Roman”; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:”Times New Roman”; mso-bidi-theme-font:minor-bidi;} Looking for how to tune a SQL statement by creating a SQL Profile? The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans. Automatic SQL Tuning deals with this problem with its SQL Profiling capability. The Automatic Tuning Optimizer creates a profile of Continua a leggere