高正宗 System Consultant Manager DBXray for Sybase 高正宗 System Consultant Manager

Agenda 效能調校議題 產品定位 架構說明 功能說明

資料庫在效能調校的前提 事前周密的規畫, 可簡化日後解決效能問題之困難度 因應用系統及系統環境的多變, 針對特定的使用者, 很難判斷何種狀況資料庫為正常或不正常 事先收集相關資訊, 可在問題發生後, 快速釐清及分析問題

效能調校的基本步驟 Isolation query issues from system issues Optimizer issue, blocking issue, or resource issue? Gather data over time Establish baselines for all levels of activity Understand typical system loads at various times Look for trends Avoid tuning based on a single 5 minute sample Understand what you are changing Read documentation before tuning anything Document what you did and why you did it Don’t ever turn on a trace flag until you know what it does and what the side effects are!

Agenda 效能調校議題 產品定位 架構說明 功能說明

DBXray的產品定位 輔助資料庫管理 DBXray是個人的高效能工具, 提供不同層級的DBA 解決管理資料庫之挑戰 增強資料庫可用性( availability ) 提昇生產力( productivity ) 維護資料庫效能( performance ) DBXray是個人的高效能工具, 提供不同層級的DBA 解決管理資料庫之挑戰 Sybase ASE 選項產品

DBXray的功能概觀 主視窗提供ASE總體之診斷概觀, 可快速的讓你判斷效能之瓶頸與調校之機會 下鑽功能( Drilldown ), 可進入詳細監控視窗, 提供更詳盡之資料幫助你分析及解決效能問題 在主視窗提供警示功能, 通知你潛在之問題 在警示的項目中, 提供問題的描述及解決問題的建議方案 可設定發生警示之臨界值 可直接由DBXray中開啟Sybase Central

Agenda 效能調校議題 產品定位 架構說明 功能說明 產品展示

DBXray Application Server 架構說明 DBXray Application Server Client Monitor ASE Client Monitor (Win NT, 2000, XP) Application Server (Win NT and 2000) Database Server (any platform supported by ASE)

架構說明 DBXray 包含三個元件 Sybase ASE - New monitoring capability MDA in ASE 12.5.0.3 MDA = "Monitoring Data Access" 35 proxy tables in the master database all named "mon%" (i.e. "monObjectActivity") can be queried with regular SQL fast access to low-level information (native RPCs) DBXray Application Server - 收集Sybase ASE之資料

架構說明 Client Monitor - 以圖形化方式顯示資料 Main screen provides a graphical overview of the Sybase ASE server health 15 detail monitors provide additional information about specific areas of the main screen (i.e., connections) Alerts show visual warning and have flyover advice for resolving problems Expert advice is available for many of the parameters Sybase Central (Java version only) can be launched directly from main screen

Monitoring Data Access MDA tables vs. sp_sysmon MDA provide per-object, per-session data sp_sysmon provides cumulative data MDA tables vs. sysprocesses/syslocks MDA provide *much* more detail MDA tables vs. MonServer/HistServer MonServer/HistServer have great capabilities, but aren't easy to use (learning curve) MDA tables can be queried with straightforward SQL statements

Agenda 效能調校議題 產品定位 架構說明 功能說明

功能說明 – Main Window Number of connections Potential problem alerts And high-water mark Potential problem alerts And hints 24 Hour Graph CPU Usage Server Error Log Data Cache Efficiency The SQL-BackTrack programs are a collection of executables that perform the backup and recovery tasks as well as some administration tasks. The main SQL-BackTrack program is sbacktrack. Sbacktrack calls an interactive menu interface that allows the user to access other SQL-BackTrack programs in an easy to use manner. Key programs that are part of SQL- BackTrack include dtsbackup, dtsrecover, dtsdump, dtsload, dtsanalyze and dtscheck. Locks Database Space Usage

功能說明 – Detail Monitor User List SQL Text (and Query Plan) Statement Statistics Process Wait State History The SQL-BackTrack programs The SQL-BackTrack programs are a collection of executables that perform the backup and recovery tasks as well as some administration tasks. The main SQL- BackTrack program is sbacktrack. Sbacktrack calls an interactive menu interface that allows the user to access other SQL-BackTrack programs in an easy to use manner. Key programs that are part of SQL-BackTrack include dtsbackup, dtsrecover, dtsdump, dtsload, dtsanalyze and dtscheck.

功能說明 – Parameter Threshold List

功能說明 – Critical Errors Alert Figure 1-6 in the student manual, shows a Unix server Control Directory structure. SQL-BackTrack expects the hierarchy as viewed in figure Figure 1-6. SQL-BackTrack will create at least one of sbackups.physical and/or sbackups.logical control directories as requested by the user. Within these directories, directories representing each individual Sybase SQL-Server are created. The control files exist within the Server Directories. Control files are created for specific databases. For example, if you will always be backing up information in Publications you could create a Control File called pubs.

Agenda 效能調校議題 產品定位 架構說明 功能說明