Toad Database Administration Best Practices Presenter Copyright 2006

Production Database Pressure Points Developers End Users DBAs

What is “Best Practice” ? • A set of guiding principles proposed by industry leading experts • Acknowledgement that the application is part of an evolving lifecycle which will continue to iterate over a period of time • Determination to deliver a quality product or what we used to call “taking pride in your work” ! Why use it ?

Administration Best Practices

Toad Database Administration Best Practices Oracle SQL Server DB 2

Challenges to implementing Best Practice • Resistance to use tools • Already have scripts • Yada yada….

Design • Access to data model – Ensures development matches business requirements – Ensures data model is correctly optimized – Ensures model integrity is maintained – Version history

Deploy (1) • • DDL Scripts automatically generated from data model Alter Script generation based on data model updates Version History retained for auditing Application scalability testing

Deploy (2) • Will the application work correctly and adequately under production conditions ? SQL statement or PL/SQL code scalability profile Does not scale well Scales well

Detect/Discover • Environment (O/S and Database) – Instance Manager – UNIX Monitor – Windows Service Manager • Instance Performance – Database Browser (all instances) – Database Monitor – Database Probe • SQL Performance – SQL Trace/Optimization – ASH Report – Index Monitoring – Database Browser (all instances) – Database Monitor – Oracle Alert Log analysis (FTP and Telnet windows) – Database Health Check – Session Browser – ADDM/AWR – Stats. Pack – Top Session Finder – SQL Monitor – SQL Scan/SQL Detective (Xpert/Dev Suite) • Space Management – Database Browser – Segment Advisor – Space/IO History – Undo Advisor

Diagnose • Instance Performance – – Database Probe Database Monitor Session Browser Health Check – Undo Advisor • SQL Performance – – Top Session Finder SQL Trace/Optimization Session Browser SQL Optimization (Xpert or Dev Suite) • Space Management – – Segment Advisor Tablespace Map Identify Space Deficits Log Switch Frequency Map

Resolve • Instance Performance – Oracle Parameters • Schema/Data Integrity – – Log Miner (access to Undo SQL) Schema Compare Data Duplicates - Flashback Table • SQL Performance – Explain Plan – SQL Optimization (Xpert or Dev Suite) • Space Management – – – Rebuild Multiple Objects Analyze Objects Repair Chained Rows Segment Shrink Advisor DBMS Redefinition Wizard

SQL Optimization - How hard can it be to rewrite SQL ? How many different ways can you write this query? Teach Yourself SQL in 30, 420 Minutes By Gabriel F. Gargiulo SELECT EMP_NAME, DPT_NAME, GRD_DESC FROM EMPLOYEE, DEPARTMENT 1, GRADE WHERE EMP_GRADE = GRD_ID AND EMP_DEPT = DPT_ID AND EXISTS (SELECT 'X' FROM DEPARTMENT 2 WHERE DPT_AVG_SALARY IN (SELECT MIN(DPT_AVG_SALARY) FROM DEPARTMENT 3) AND DPT_ID = EMPLOYEE. EMP_DEPT); 30, 491 !!!

Optimize SQL • Proactively identify problematic SQL directly from the source code • Thorough transformation of SQL producing every possible semantically equivalent rewrite • Optimize SQL in batch in context of source code • Generate replacement script • Automatic ranking of best alternatives • Minimal involvement which can save hours of time

Inform • Instance Performance – ADDM/AWR – Stats. Pack – Health Check Report (schedulable) • Schema Integrity – – – Schema Compare HTML Schema Report (schedulable) Reports Manager (schedulable) ER Diagram (Toad Data Modeler) Master-Detail Browser • SQL Performance – Plan Change Analyzer (Xpert or Dev Suite) • Space Management – Tablespace History – IO History

Administer • Instance – – ASM Manager Oracle Parameters Redo Log Manager Schema Browser (Rollback Segment, Jobs, Scheduler) • Schema Integrity – Object Audit – NLS Parameters – Export & Import – DBMS Flashback – Recycle Bin • Space Management – Schema Browser (Tablespaces) – Tablespace Menu • Users – Audit SQL / Sys Privs – Schema Browser (Users, Roles, Object Privs, etc)

TOAD DB Optimization Check-List