These are my notes from a web training by the product team on Diagnostics and Tuning packs for OEM 10gR4.
new features in 10gR3
- EBiz Suite
- PeopleSoft
- Seibel
- SOA Management
- Identity Management
- Remedy connector
- MOM connector
- SAP
- JBoss
- IBM WebSphere MQ
new features in 10gR4
- Data Masking
- BI
- Tomcat
- VMWare
- PeopleSoft Helpdesk
- Microsoft Exchange
- BEA
- BEZ
- Blue Lane
- Check Point
- Citrix
- Dell
- Egenera
- EMC
- F5
- IBM
- Juniper
- Microsoft
- NetApp
- Nortel
- Onaro
- Pillar Data
- Radware
- JBoss
IT Challenges
– Reduct cost of managing IT -> grid automation/OEM
– assess status -> configuration management
– transaction from reacting to proactively resolving issues -> app performance management
– improve alignment between business customers and IT staff -> SLM
Database Diagnostic Pack
features improved each release
- wait events instrumentation (7.3)
- statspack (8i)
- YAPP methodology (8i)
- enhanced time-wait model (10g)
- AWR – automatic workload repository (10g)
- ASH – active session history (10g)
- ADDM – automatic database diagnostic monitor (10g)
AWR – performance statistics data warehouse. Consists of snapshots of ASH. Everything stored in SYSAUX tablespace.
ADDM – top down analysis using AWR snapshots. optimizes to reduce database time. real time results. pinpoints root cause and eliminates non-problem areas.
OEM 10g works on 9i. The summary database screen does not have any differences in OEM for 9i systems. When we dive down into a specific database and look at the Performance tab, we see similar data but the 9i does not have the ADDM notices. Everything else shows this. Both do show the same wait event issues.
SQL Tuning does show rich information in 9i like expensive full table scans or cartesion joins as well as unnecessary rewrites. This data is not as rich as the SQL Tuning advisor in 10g but it does give information that the DBA can resolve on their own.
Top SQL data is available in 9i throught he STATSPACK to look for duplicate SQL or blocking sessions. The tool in 10g is more informative and integrated with SQL Tuning Advisor.
Database Tuning Pack
Tuning pack does require the diagnostics pack. It does provide features like SQL Tuning Advisor and SQL Access Advisor.
SQL Tuning Advisor looks at statistics, SQL profiles, access path analysis, and SQL structure analysis. It then recommends a new SQL profile, a new index, a recommendation on modifying SQL statements, or gathering more stats.
SQL Profile is additional information based on previous execution of SQL statements. The profile optimizes the execution of the SQL statements. It can be used to schedule I/O or populate the buffer cache properly. It makes this persistent across shutdowns and can be applied to other databases that are running the same code. 10g has a new feature in SQL Tuning Sets that allows for capture of SQL workloads and tuning sets better. It also records creation of a tuning set from a variety of sources; cache, ADDM, etc.
Data masking is a new frature in 10gR4. It allows you to anonymize columns so that you can send copies to off-site developers, third parties, or internal developers. The format and layout stays the same but the contents are masked.
The demo emphasized the main screen pointing out the systems down, security policy issues (critical and warning) as well as alerts (critical and warnings). It does allow you to sort based on a specific type of target. The examples given were IBM and Microsoft applications. If you look at the database tab it allows you to look at the different database types, different versions, and if the systems are up or down. If you pick one database you go to the database home page and see all of the graphs as well as more detailed reports. The performance tab allows you to look at the historical utilization, active sessions, disk I/O, and instance throughput.
This training was relatively good for an introduction. Unfortunately, it did not go as deep as the TDS demos are capable of doing. I personally like to use something like the PetShop demo and show blocked processes, slow querries, and how you can fix these.