Oracle Database Administration

1. Oracle Database Administration Session 12 Performance

2. Performance • Wholistic view • System level • Database level • Application level

3. Performance Tuning • Trade-offs Between Response Time and Throughput • Goals for tuning vary, depending on the needs of the application • OLTP applications define performance in terms of throughput • Decision Support Systems define performance in terms of response time

4. Performance Definition • Response time = service time + wait time • We can increase performance two ways: • by reducing service time • by reducing wait time.

5. Performance Definition • System throughput equals the amount of work accomplished in a given amount of time • Two techniques of increasing throughput exist • Get more work done with the same resources (reduce service time) • Get the work done quicker by reducing overall response time (reduce wait time)

6. Performance Definition • The service time for a task may stay the same, but wait time increases as contention increases • If many users are waiting for a service that takes 1 second, then the tenth user must wait 9 seconds for a service that takes 1 second

7. Critical Resources • Resources such as CPUs, memory, I/O capacity, and network bandwidth are key to reducing service time • Adding resources can give higher throughput and swifter response times

8. Areas of Interest • System • Memory • CPU • Disk • Database • Layout • Init.ora • Application • Sql • Most resource expensive sql statements

9. Areas of Interest • Network • Sql*net • Network structure

10. Percentage Gain • Application 75% to 90% gain • Database 10% to 15% gain • System 5% to 10% gain • Network Less than 5% gain

11. The Role of the DBA • The DBA mostly works on database tuning • Enabling the developers improve their code • Improving Application Architecture

12. System Tools • Top • Vmstat • Sar • Mpstat

13. System Tools • dmesg – message file. Can be used to get information on the CPU, the memory, etc. • prtconf – system configuration • psrinfo – Info about the processors • sysdef – current system definition

14. System Tools • Prtdiag - System Configuration • Prstat – Similar to TOP • Netstat – Network Configuration Most commands located in /usr/sbin on a Solaris system

15. Top • Overall view of the system • Use of Averages • AIX • Topas • nmon

16. Sar - System Activity Reporter • Sar examples • Sar -d 2 5 disk usage • Sar –g 2 5 paging • Sar –q 2 5 queue length • man sar

17. SAR • sar [ -aAbcdgkmpqruvwy ] [ -o filename ] t [ n ] • n intervals of t seconds, where t should be 5 or greater • -o filename, this option puts the output in a file, for latter use

18. Vmstat - Virtual Memory Statistics • Virtual memory is all physical memory and allocated swap space • vmstat looks at the system and reports statistics kept about a process, virtual memory, disk and CPU activity. • Without options, vmstat displays a one-line summary of the virtual memory activity since the system was booted.

19. Vmstat • vmstat [ -cisS ] [ disks ] [ interval [ count ] ] • Vmstat 5 5 • Vmstat –S to get swapping information

20. Mpstat - Reports Processor Statistics • Mpstat 5 5 • mpstat reports processor statistics in tabular form • Each row of the table represents the activity of one processor

21. Block Size • Use the largest block size possible for a DDS or data warehouse • For a mixed workload use a smaller block size, like 8K • Larger block sizes can cause block level contention

22. Fragmentation • Use locally managed tablespaces • Set pctincrease to a non zero value, if using DMT. This will force SMON to coalesce the tablespace. • Otherwise use • Alter tablespace ‘name’ coalesce

23. Indexes • Reduce index fragmentation by setting correct storage parameters. Use LMT. • Rebuild Indexes • Alter index <index name> rebuild • The rebuild option avoids the need to first drop and re-create the index • Unrecoverable option

24. Index Usage • Alter index index_name monitoring usage • Alter index index_name nonmonitoring usage • Query v$object_usage to view results

25. Chained Rows • Once the statistics has been collected for the schema • Select the table name and chained row count for any and all tables of that schema • Rebuild the table to remove the chained rows using export and import • Some tools, like TOAD can remove the chained rows on the fly.

26. Monitor DBWR and LGWR • Select event, total_waits, time_waited from v$system_event where event like '%file%' order by total_waits desc; • Db file sequential read event refers to when a foreground process is waiting for a sequential read from the database. It is higher if we have many table scans

27. Monitor DBWR and LGWR • Log file parallel writes refer to when the redo entries in the log buffer get written to the redo log file • Redo file sync reports the wait time of the LGWR writing redo entries for a given user session. When a user session commits a transaction, the redo information for the session is flushed to the redo log file

28. Database Tools • Estat/bstat • Statpack • ADDM • AWR • Sql trace • Tkprof

29. Statspack • STATSPACK is a performance diagnosis tool • Available since Oracle8i. • It is the successor to BSTAT/ESTAT's • STATSPACK • instance-wide performance problems • supports application tuning activities by providing data which identifies high-load SQL statements • it is used both proactively to monitor the changing load on a system and reactively to investigate a performance problem

30. Statspack - Setup • For Oracle10g • Connect / as sysdba • @?/rdbms/admin/spcreate • spreport.sql to generate a report • More next week on statspack and it’s successor AWR

31. Statspack Installation • Scripts located in • /u01/app/oracle/10.2.0/rdbms/admin • connect / as sysdba • Create a perfstat tablespace • Create a perfstat user • sqlplus perfstat/perfstat@<SID> • Test • Exit

32. Statspack Installation • Before Installation • Run catdbsyn.sql as sys • Run dbmspool.sql as sys • Sqlplus /nolog • Connect / as sysdba • At SQL prompt run @?/rdbms/admin/spcreate

33. Statspack Levels • Statspack level • Level 0 general statistics • Level 5 (default) high SQL • Level 6 sql plans • Level 7 segment level satistics • Level 10 child latches • All include data from lower levels

34. Statspack Snaps • To captute a snapshot run statspack.snap. • Run at default level • Run statspack.snap(i_snap_level => 7) to run as a different level. • To make this level permanent for all snaps use statspack.snap(i_snap_level => 7, i_modify_parameter => ‘true’)

35. Statspack Report • SQL>sqlplus perfstat/perfstat • SQL> @spreport.sql • DB Id DB Name Inst Num Instance • ----------- ---------- -------- ---------- • Completed Snapshots • Instance DB Name SnapId Snap Started Snap Level <SID> <SID> 1 17 Jan 2002 14:07:26 5 2 17 Jan 2002 14:11:55 5

36. Statspack Report • Parse ratio • Top Waits • Object statistics • CPU usage • High sql • Hash Value

37. Statspack Report • SQL>sqlplus perfstat/perfstat • SQL>sprepsql.sql • You are requested for the hash value from the first report • Displays the sql text and the execution plan and its relative cost • Values lower than 100 are good.

38. Statspack Information • www.oraperf.com • YAPP Yet another performance profiler • www.akadia.com/services/ora_statspack_survival_guide.html • www.rmoug.org/TD2004_Pres_Sum.htm#db_admin • Sysytemwide tuning using UTLSTAT Reports in Oracle 7/8 (metalink Note: 62161.1)

39. Automatic Workload Repository • AWR collects and processes performance metrics automatically • This is available with the creation of the database • Snapshots are taken every hour and maintained for 14 days by default • Procedures exist to make adjustments to these defaults

40. Automatic Workload Repository • AWR can be integrated with Oracle Enterprise manager (OEM) • Reports can be run using Oracle provided scripts

41. DBMS_STATS • DBMS_STATS - Gathers, Views, Modifies and Delete optimizer statistics for database objects • To gather stats for the current schema EXEC DBMS_STATS.gather_schema_stats()The above will COMPUTE global and partition-level statistics.

42. System Statistics • The gathered systems statistics are: • single block readtime in ms • multiblock readtime in ms • cpu speed in mhz • average multiblock_read_count in number of blocks

43. System Statistics • begin • DBMS_STATS.CREATE_STAT_TABLE('SYS','GATHERED_STATS'); • DBMS_STATS.GATHER_SYSTEM_STATS( • gathering_mode =>'INTERVAL', • interval => &interval, • stattab => 'GATHERED_STATS', • statid => 'SYSTEM_DAY_STAT', • statown => 'SYS' • ); • exception • WHEN OTHERS THEN • RAISE_APPLICATION_ERROR(-20001,'Script Failed' || sqlerrm); • end; • /

44. System Statistics Example • StatusCOMPLETED • cpu in mhz : 156 • single block readtime in ms : 1.15 • multiblock readtime in ms : 2.386 • average multiblock readcount : 7

45. Fixed Statistics • execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (stattab => 'GATHERED_STATS', statid => 'FIXEDOBJ_STAT', statown => 'SYS');

46. SQL Trace • The diagnostic tool 'sql trace' provides performance information about individual SQL statements and generates the following statistics for each statement: • parse, execute, and fetch counts • CPU and elapsed times • physical reads and logical • reads number of rows processed • misses on the library cache

47. SQL Trace • This information is input to a trace (.trc) file • Sql trace can be enabled/disabled for a session or an instance.

48. SQL Trace • SQL_TRACE • Enable/Disable SQL Trace for the instance. • TRUE Enable statistics to be collected for all sessions. • FALSE Disable statistics to be collected for all sessions.

49. SQL Trace • TIMED_STATISTICS Enable/Disable the collection of timed statistics, such as CPU and elapsed times. • TRUE Enable timing (usually recommended) • FALSE Default value.

50. SQL Trace • MAX_DUMP_FILE_SIZE Specifies the maximum size of trace files operating system blocks. • The default value for this is 500M but if your trace file is truncated then increase this value • USER_DUMP_DEST Specifies the destination for the trace file. • The default value for this parameter is the default destination for system dumps on your operating system