List of Common Oracle Metrics Monitoring Methodologies Reactive Proactive Reactive monitoring is not a recommend approach but at times it becomes inevitable. Oracle 10g provide tools that aid in proactively monitoring database. Server-generated alerts and Automated Database Diagnostic Monitor(AADM) are going to be discussed in another post. Database and Instance Metrics Performance measurements [...]
Archive for the ‘Performance Tuning’ Category
28 Jun
Join Types NL,Hash and Sort-Merge
Click the following link of Sachin for Join Types Nested Loops, Hash and Sort Merge Join Types by Sachin Previously, i copied most of the important points from his post for my own request but now deleting as the author does not like it. Related articles by Zemanta Optimization of Joins
25 Jun
Optimize Joins and Query Plan Changes
Joins and Query Plans Details of the article can be viewed at ask Tom Below are two queries posted by a user on askTom site. </pre>1) SELECT distinct D.deptno, D.dname FROM dept D, emp E WHERE E.deptno = D.deptno ORDER BY D.deptno; 2) SELECT D.deptno, D.dname FROM dept D WHERE EXISTS ( SELECT 1 FROM [...]
29 May
Basic SQL Performance Diagnosis
Article is based on Randolf’s Post on Basic SQL statement performance diagnosis-HOW TO The following steps should be taken to gather detailed information about the SQL statement: 1. Gather information about parameters relevant to the optimizer 2. Gather the output of the SQL*Plus AUTOTRACE and TIMING feature when executing your statement 3. Get the [...]
19 May
Creating SQL Tuning Task
DECLARE my_task_name VARCHAR2(30); my_sqltext CLOB; BEGIN my_sqltext := ‘SELECT a16.mkt_oid mkt_oid,’|| ‘a16.mkt_nm mkt_nm,’|| ‘ a16.mkt_cd mkt_cd,’||’a15.consolidated_brand_nm consolidated_brand_nm,’|| ‘SUM (a11.xp_trx) wjxbfs3′ || ‘FROM prescrip_sales a11 JOIN month a12 ON (a11.mo_id = a12.mo_id)’|| ‘JOIN zzmq00 pa13 ON (a12.mo_oid = pa13.mo_oid) JOIN d_product a14 ON (a11.src_prod_oid = a14.src_prod_oid) ‘GROUP BY a16.mkt_oid, a16.mkt_nm, a16.mkt_cd, a15.consolidated_brand_nm, a18.spec_group_oid, a18.spec_group_nm, pa13.mo_oid, pa13.mo_disp’; [...]
19 May
SQL optimization using Oracle Hints
ORACLE HINT REFERENCE PARALLEL Scanning full table with parallel degree of 8. Parallel always assumes full table scan. SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 8 ) */ last_name FROM employees hr_emp; PARALLEL_INDEX The PARALLEL_INDEX hint instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans for partitioned [...]
29 Apr
Optimizing Import/Export
EXPORT: Set the BUFFER parameter to a high value (e.g. 2Mb — entered as an integer "2000000") Set the RECORDLENGTH parameter to a high value (e.g. 64Kb — entered as an integer "64000") Use DIRECT=yes (direct mode export) Stop unnecessary applications to free-up resources for your job. If you run multiple export sessions, ensure they [...]
29 Apr
When to Rebuild Indexes
How can you determine if an index needs to be dropped and rebuilt? Expected answer: Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the [...]
15 Apr
Bitmap and BTree Indexes
According to conventional wisdom, Bitmap index is a preferred indexing technique for cases where the indexed attributes have few distinct values (i.e., low cardinality). The query response time is expected to degrade as the cardinality of indexed columns increase due to a larger index size. On the other hand, B-tree index is good if the [...]
14 Apr
OPEN CURSORS
Open cursors take up space in the shared pool, in the library cache. OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. Monitoring open cursors v$open_cursor shows cached cursors, [...]
