数据库检查脚本 by Jonathan LewisPosted on September 22, 2013 at 11:51 am by 中国 Oracle 用户组
文章来源：http://feedproxy.google.com/~r/acoug/~3/K7lNGqZrexo/740.html
Jonathan Lewis是Oracle业界著名的人物。他拥有超过20年的数据库的从业经验，曾出版两本专业书籍Oracle Core: Essential Internals for DBAs and Developers、Cost-Based Oracle Fundamentals (Expert’s Voice in Oracle) ，并定期的在相关新闻组，论坛，用户群的杂志上发表文章，经常参加Oracle世界性的活动，为Oracle的技术推广做出了杰出贡献。
个人博客：http://jonathanlewis.wordpress.com (被墙)
由于他的个人博客被墙，为了墙内的朋友可以更轻松的访问到他的文章和脚本，经Jonathan Lewis授权，ACOUG可在官网转载他的代码和部分技术文章。欢迎大家浏览学习。本页面内容转自Jonathan Lewis博客：Script Catalogue
特别说明：没有认真看过的脚本就绝不要执行。对于 DBA 来说，如果一个脚本你从来没有认真读取了解过，就不要去执行，脚本中的一个错误就可能导致严重的数据灾难。——《DBA手记4 数据库安全警示录》
Extracts from the AWR 趋势信息统计脚本
trend_awr_stat.sql: Report a single statistic across time from the AWR history of v$sysstat.
trend_awr_os_stat.sql: Report a single statistic across time from the AWR history of v$osstat.
Simple diagnostic probes 简单诊断脚本
Pga Leaks: an article that includes three queries to check memory usage for a session.
Recent SQL: an article that describes a check for SQL that has recently appeared in the SGA heap
Segment Scans: a simple script for finding objects subject to a large number of tablescans or index fast full scans
Log file switches: a script (for single instance) to report the time of, and time between, log file switches
Time to collect stats: a script to report the start time and run time of the automatic stats collection job
SGA Resizing: a script to report resizing operations (v$sga_resize_ops)
What have I done: a script to report the work done, time spent, and wait events for my session
Tracking one statement: an article describing a way of watching the work done by a critical statement as time passes
Snapshot my workload: an example of creating a package in the SYS schema to take snapshot of dynamic performance views
Hidden Parameters: Very old queries (2001) to report all parameters, including the hidden ones – session and system level
Parallel Workload: a query to run immediately after a parallel operation in the same session to report the distribution of work
Data Diagnostics 进阶诊断脚本
Partition Count: an article with a script to count the number of rows in each partition of a partitioned table
tablespace usage: a script to list the extents and free space chunks in a tablespace in file and block order
Index definitions: a script to describe the indexes on a single table – column and statistical information
Oversized Indexes: a data dictionary scan for simple B-tree indexes that may be unreasonably large for the data held
Index Leaf Block scanner: a labour-intensive analysis of leaf block usage for a simple B-tree index.
Drawing an Index: An example of reading a treedump and using an analytic function to “draw a picture” of an index.
Reporting Branch blocks: Similar to “drawing an index”, but listing just the branch blocks in index order.
Stale Stats: A simple piece of pl/sql to report objects with stale or missing stats
Hakan Factor: A procedure – owned by SYS – that reports the Hakan factor for an object.
Technical Demonstrations 其他脚本
ora_hash(): a script demonstrating that ora_hash works with numeric partition keys, published in response to this comment.
|
