8
8 SORT_AREA_SIZE Set high enough to prevent constant generation of temporary segments (I/O) and at the same time leave enough memory for other processes. Check % of Memory and Disk sorts in V$SYSSTAT table. Use STATSPACK (UTLBSTAT/UTLESTAT) to find the sorting that is performed on memory. Increase SORT_AREA_SIZE If disk sorts is high, but remember - SORT_AREA_SIZE is a per user value. Basic Memory Tuning (Cont..)

9
9 Keep the Right Data in Memory ALTER TABLE [TABLENAME] CACHE; This ensures that full table-scan results are on the MRU list instead of the LRU list. Use the CACHE hint within a query to pin the table and keep it in the cache from first use on SQL> SELECT /*+ CACHE(EMP) */ empno, job FROM emp WHERE empno > 1; Basic Memory Tuning (Cont..)

10
10 Check free Memory before Pinning Tables. Do checking after working loads. Increase DB_BLOCK_BUFFERS (DB_CACHE_SIZE) if no free buffers are available within the first 30 minutes of system startup. Basic Memory Tuning (Cont..)

23
23 Watch Indexed WHERE Conditions Assume index on address (city, state) non-leading index column references cannot use indexes WHERE state = 'TX' [Index Not used] WHERE city = 'DALLAS' [Index Used] WHERE state = 'TX' and city = 'DALLAS'[Index Used] NOT, != and <> disable index use WHERE state not in ('TX', 'FL','OH') [Index Not used] WHERE state != 'TX' [Index Not used] NULL value references can never use indexes WHERE state IS NULL [Index Not used] WHERE state IS NOT NULL [Index Not used] expression references can never use indexes WHERE substr(city,1,3) = 'DAL' [Index Not used] WHERE city like 'DAL%' [Index Used] WHERE city || state = 'DALLASTX' [Index Not used] WHERE city = 'DALLAS' and state = 'TX[Index Used] WHERE salary * 12 >= [Index Not used] WHERE salary >= 2000 [Index Used] SQL Tips (Cont…)

25
25 Order Table in the FROM Clause SQL Tips (Cont…) Important under rule based optimizer, and won't hurt under cost based optimizer Order FROM clauses in descending order of table sizes based upon row counts For example SELECT * FROM larger table, smaller table SELECT * FROM larger table, smaller table, smallest table SELECT * FROM larger table, smaller table, associative table

26
26 Consider IN or UNION in place of OR SQL Tips (Cont…) if columns are not indexed, stick with OR if columns are indexed, use IN or UNION in place of OR IN example Bad: SELECT * FROM address WHERE state = 'TX or state = 'FL or state = 'OH Good: SELECT * FROM address WHERE state in ('TX','FL','OH') UNION example Bad: SELECT * FROM address WHERE state = TX or areacode = 972 Good: SELECT * FROM address WHERE state = TX UNION SELECT * FROM address WHERE areacode = 972

27
27 Weigh JOIN versus EXISTS Sub-Query SQL Tips (Cont…) Use table join instead of EXISTS sub-query When the percentage of rows returned from the outer sub-query is high Use EXISTS sub-query instead of table join When the percentage of rows returned from the outer sub-query is low

29
29 Consider NOT EXISTS in place of NOT IN SQL Tips (Cont…) Avoid sub-queries that use NOT IN, use NOT EXISTS instead: Bad: SELECT * FROM emp WHERE deptno NOT IN (SELECT deptno FROM dept WHERE deptstatus = A) Good: SELECT * FROM emp WHERE NOT EXISTS (SELECT X FROM dept WHERE deptstatus = A AND dept.deptno = emp.deptno)

31
31 Ordering Via the WHERE Clause SQL Tips (Cont…) A dummy WHERE clause referencing an indexed column will Retrieve all records in ascending order (descending for 8i descending index) Not perform a costly sort operation Bad: SELECT * FROM address ORDER BY city Good: SELECT * FROM address WHERE city >

34
34 Disk-I/O bottlenecks can be a major factor for poor response times. Minimize disk I/O by balancing the load on your disks Be sure to tune SQL statements before modifying the disk layout. Improve performance by defining alternative hardware configurations, such as disk striping or different RAID solutions. Tuning Disk I/O

35
35 First, identify possible disk bottlenecks. 25 percent of the I/Omeasured as the number of reads, writes, or bothshould be considered hot. Once hot disks are identified, drill down to find which files and tables on the disks experience most of the activity and move them to less-active disks as needed. Identify hot files and disks by running data- collection utilities, such as Oracle's Statspack and the UNIX iostat utility. Tuning Disk I/O (Cont…)

36
36 Short-duration bottleneck may repeat itself thousands of times each day. Collect I/O statistics at frequent intervals of no more than ten minutes between samplesover a representative time period, such as a week. Store the results in Oracle database tables for easy access and report generation. Tuning Disk I/O (Cont…)

38
38 RAID 0+1 helps performance but is expensive. RAID 5/7/S is less expensive but may decrease performance but good enough for read-intensive applications such as decision- support systems (DSS).. Use different RAID levels and stripe sizes for different disk volumes. Different Oracle files can be placed on volumes with different stripe sizes according to their access characteristics. Tuning Disk I/O (Cont…)

40
40 Redo logs (especially if placed on dedicated disk arrays) can be striped with a smaller size. The Temporary Tablespace should be striped as a factor of SORT_AREA_SIZE. Data and non-unique indexes Tablespaces may be placed on volumes with larger stripe sizes. Tuning Disk I/O (Cont…)

43
43 Find out how much of your CPU time is being spent on parsing and read-consistency waits CPU Problems COLUMN total_cpu heading 'Total CPU|(seconds)' COLUMN parsing heading 'Parsing|(seconds)' COLUMN waiting heading 'Read Consistency|Wait (seconds)' COLUMN pct_parsing heading 'Percent|Parsing SELECT total_cpu,parse_cpu parsing, parse_elapsed-parse_cpu waiting,trunc(100*parse_elapsed/total_cpu,2) pct_parsing FROM (SELECT value total_cpu FROM v$sysstat WHERE name = 'CPU used by this session'),(SELECT value parse_cpu FROM v$sysstat WHERE name = 'parse time cpu'),(SELECT value parse_elapsed FROM v$sysstat WHERE name = 'parse time elapsed');

44
44 As from Oracle you can improve your Sql timings by having the database reuse a similar cursor without having a bind variable. Reuse the Cursor CURSOR_SHARING={SIMILAR|EXACT|FORCE}

45
45 Calculate the best approximation of response time, in seconds, per transaction during a sample period. Useful in further diagnosing performance issues by drilling down into individual components like Waits, CPU etc. Calculate Average Response Time Per TXN

46
46 Calculate Average Response Time Per TXN (Cont…) ResponseTime = (DeltaTotalWait + DeltaCpuTime) / DeltaCommits + DeltaRollbacks) Difference of each SQL between sample end and start. DeltaTotalWait = Sum of time waited for all wait events in v$system_event DeltaCpuTime = SELECT value FROM v$sysstat WHERE name='CPU used by this session' DeltaCommits = SELECT value FROM v$sysstat WHERE name='user commits DeltaRollbacks = SELECT value FROM v$sysstat WHERE name='user rollbacks'

47
47 Transaction count represents the total number of commits and rollbacks performed during a sample period. A drop in transaction rates and an increase in response time may be indicators of problems. Transaction loads may vary widely across different times of the day Calculate Transaction Count TXNcount = DeltaCommits + DeltaRollbacks Difference of each SQL between sample end and start. DeltaCommits = SELECT value FROM v$sysstat WHERE name='user commits' DeltaRollbacks = SELECT value FROM v$sysstat WHERE name='user rollbacks'

48
48 Unbalanced indexes will cause sections that are accessed more than others causing increased disk I/O and a potential slow down. When to Rebuild The Indexes SQL> ANALYZE index scott.emp_pk COMPUTE STATISTICS; Check the B-Level of the index from the DBA_INDEXES view. SQL> SELECT blevel FROM dba_indexes WHERE index_name = 'EMP_PK' AND owner = 'SCOTT'; The BLEVEL (or branch level) is a part of the b-tree index format that correlates to the number of times Oracle needs to narrow the search in order to find a particular record. Rebuild an index with a BLEVEL value above 4.

49
49 Customize Data Dictionary Storage by editing the $ORACLE_HOME/rdbms/admin/SQL.bsq File. Edit the CREATE … Commands to set new Storage Parameters ( INITIAL, NEXT and PCTINCREASE ) How to know what values to give ? After creation of a database, investigate the actual values for INITIAL, NEXT and EXTENTS in DBA_SEGMENTS for Segments owned by SYS. Next time you create a Database give proper values for INITIAL and NEXT. Optimizing Data Dictionary Storage

50
50 Coalescing free space Combining adjoining free space chunks to a single large free space. PCTINCREASE > 0 causes defragmentation. Automatic coalesce causes system overhead while SMON is searching for chunks to coalesce. IMPORTANT - do not set PCTINCREASE > 0 on Rollback or Temporary Tablespaces because of their dynamic nature of allocating and deallocating extents.

51
51 Coalescing free space (Cont…) Avoid Using PCTINCREASE>0 on Default Storage of Tablespace. Create Sql Script File Containing the Coalesce Command ( ALTER TABLESPACE COALESCE ) and Schedule it through Scheduler. On systems with many sort operations it is Recommended to coalesce the Temporary Tablespaces just before shutdown in order to minimize the time Spent on housekeeping (releasing extents and updating the Data Dictionary)

52
52 Determine the Optimal Value for PCTFREE and PCTUSED Pctused - The amount of used space in a block that below, the block will be put on the free list for further inserts. Pctfree - The amount of space in the block that is kept for row expansion.

53
53 Determine the Optimal Value for PCTFREE and PCTUSED (Cont…) The higher Pctused is, the less space will be available for inserts when block is put on the free list. Small amounts of free space in blocks will cause more I/Os when inserting to the block, because new blocks are needed for the Insert operation. The lower pctused is, less I/Os are needed when inserting but blocks are not efficient with storing data (almost empty).

55
55 Startup after shutdown abort After a Shutdown Abort, when Startup is issued, an automatic Instance Recovery is occurring. Time of recovery is a function of the number of Redo entries that have not been applied yet to Datafile. To minimize this time issue an ALTER SYSTEM CHECKPOINT prior to the Shutdown Abort command to write Dirty Blocks to Datafiles.

56
56 Database starts faster by deferring the phase of rollback at instance recovery. Unrecovered Blocks are recovered at the time they are accessed. Can lead to difficulties with recovering the Database. A good practice is to startup the database in three phases rather by one: 1. Startup mount 2. Alter database recover 3. Alter database open FAST START ON-DEMAND ROLLBACK

58
58 Determine if number of rollback segments are sufficient. A count value greater than 0 shows that transactions are waiting for rollback segment and adding a new segment should be considered. Rollback segments (Cont…) Monitor transactions that are waiting for a rollback segment: SQL> SELECT class,count FROM v$waitstat WHERE class in (undo header,undo block); CLASS COUNT Undo header 0 Undo block 0

60
60 Dropping Datafiles Whenever it is possible, do not drop Datafiles since Data Dictionary keeps track of the Datafiles even the dropped ones. Unrecovered transactions could reside in the rollback segments and after startup can look for objects in the dropped Datafiles. Rollback segments should not be used. This is done by the hidden init.ora parameter called _offline_rollback_segments=(RBS01,RBS02,…)