=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= SEARCHDATABASE.COM | Database Administrator Tips February 20, 2002
Essential tools and advice for the enterprise DBA: http://searchDatabase.techtarget.com/tips/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= IN THIS ISSUE | Table of Contents 1. DATABASE ADMINISTRATOR TIPS - SQL Server: Estimate table size using command extensions - Oracle: Query the Process ID and SQLTEXT area - DB2: Memory management for DB2 - More recent tips from members 2. IMPORTANT ANNOUNCEMENTS AND LINKS - Database Training: Oracle Performance with Don Burleson - Ask the Expert: Oracle, SQL Server, DB2 questions answered - NEW Online Event: Don Burleson on Oracle 9i tuning - Featured Topic: Spotlight on SQL ___________________________________________________________________ ************* Sponsored by Precise Software Solutions ************* Heard this before? The application is too slow! Our end-users are complaining! You still have some time between midnight and 3am, what is the problem? Work more efficiently and let Precise watch over application performance for you. Check out the FREE WHITE PAPERS on our web site and take control of your day! http://ad.doubleclick.net/clk;3925248;5058249;r?http://www.precise.com/takecontrol/ ___________________________________________________________________ ___________________________________________________________________ THIS WEEK'S TIPS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ESTIMATE TABLE SIZE USING COMMAND EXTENSIONS | by Johnny Agsalog This batch file demonstrates the power of both OSQL and Windows command extensions. It is used to calculate the size of user tables -- very useful in big databases. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci803193,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= QUERY THE PROCESS ID AND SQLTEXT AREA | by Geoff Heaton Here's how to view the details and I/O stats per session. You can then use one of the SIDs to query any SQL held in the SQLAREA. The output is spooled to a file. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci801865,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= MEMORY MANAGEMENT FOR DB2 | by David Quigley If your trying to optimize your system, the following parameters are related and can be set to a maximum of 1.7 GB: UTIL_HEAP_SZ, BACKBUFSZ, RESTBUFSZ, PCKCVACHESZ, BUFFPAGE, LOCKLIST, SORT_HEAP, DBHEAP, LOGBUFSZ, CATALOGCACHE_SZ. Remember, if they are set as 4k pages, you need to mulitply the value you have assigned to them. You can track how much you need to assign to each one by doing a snapshot on the entire database. This should help in memory managment. >> CLICK for the online version of the tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci801911,00.html __________________________________________________________________ MORE RECENT TIPS FROM SEARCHDATABASE MEMBERS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= CONVERTING AN ADJACENCY LIST MODEL TO A NESTED SET | by Joe Celko http://www.searchDatabase.com/tip/1,289483,sid13_gci801943,00.html One way of representing trees is to show them as nested sets. Since SQL is a set-oriented language, this is a better model than the usual adjacency list approach you see in most text books. Here is a detailed article, with sample code, about converting between the two models. ORACLE ALERT LOG MINER | by Bill Robillard http://www.searchDatabase.com/tip/1,289483,sid13_gci801900,00.html This is an extensive C program that extracts lines from the Oracle alert log for a specified date (or date range). The lines extracted depend on the entries in a 'pattern' file. Included is the syntax and an example of a pattern file and a sample command. Now also available in Java! TROUBLESHOOTING LOCKING CONFLICTS | by Baya Pavliashvili http://www.searchDatabase.com/tip/1,289483,sid13_gci800525,00.html Troubleshooting locking problems in SQL Server is one of those actvities that DBA's would much rather avoid; alas, most have to it do at one point or another. In fact, understanding database transactions and locking is what differentiates many beginners from the real pros. This article is an overview of potential locking problems, what causes them, how to detect and resolve them. AUTOMATICALLY FORMATTING YOUR T-SQL CODE | by Cade Bryant http://www.searchDatabase.com/tip/1,289483,sid13_gci800526,00.html It is good business practice to format your T-SQL code in order to make it more readable to others. The accepted standard includes capitalizing keywords, applying indentations before certain clauses, using appropriate spacing, and others. However, many developers find it inconvenient to be format-conscious while trying to code. Here's a way to accomplish this quickly and easily. TOTAL RECORD COUNT PER SCHEMA | by Rohit Sinha http://www.searchDatabase.com/tip/1,289483,sid13_gci799183,00.html **JANUARY TIP CONTEST PRIZE WINNER!** This Oracle script counts the number of records in all tables in a particular schema in a particular instance. It prompts for USER and INSTANCE. Hundreds more free tech tips submitted by members: http://searchdatabase.techtarget.com/tips/0,289484,sid13_tax281808,00.html Share your knowledge, submit a tip: win a COMPAQ DISK-ON-KEY! http://searchdatabase.techtarget.com/tipsSubmit/1,289485,sid13,00.html ___________________________________________________________________ IMPORTANT MESSAGES AND LINKS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= DATABASE TRAINING | Oracle Performance with Don Burleson In recognition of Oracle guru Don Burleson's Feb. 27 Live Expert Q&A, searchDatabase members will receive a free copy of his newest book, "Oracle High-Performance SQL Tuning" with each paid order of Burleson's informative CD-ROM training video, "New Features of Oracle 8i and 9i." Both products give the Oracle user valuable insight from one of the top experts. Click here for full details: http://searchdatabase.techtarget.com/buyersGuideProductDetail/0,289826,sid13_gci779856_vid395118,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ASK THE EXPERT | This week's toughest database questions answered DB2: Clustering explained http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid447727_tax285651,00.html SQL: Listing data from multiple rows http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid445917_tax285649,00.html Oracle: Adding capacity to existing tablespace http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid447733_tax285650,00.html SQL Server: Migrating to SQL Server from Access http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid446666_tax285648,00.html Object-oriented databases: Web sites and downloads OORDBMS http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid446630_tax288550,00.html Dozens more expert answers here: http://searchdatabase.techtarget.com/ateExperts/0,289622,sid13,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= LIVE EXPERT Q&A | Self-tuning in Oracle 9i Who: Don Burleson When: February 27, 2002 at 12:30 PM EST (17:30 GMT) NEW! Join renowned Oracle guru Don Burleson, author of "Oracle High Performance SQL Tuning," in this discussion about tuning Oracle 9i for maximum performance. Learn about I/O tuning and RAM tuning and ask Don your toughest questions! >> CLICK: http://searchdatabase.techtarget.com/onlineEvents/0,289675,sid13,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= SPOTLIGHT ON SQL | SearchDatabase Featured Topic The structured query language is the lingua franca of all the major database management systems. Here is a collection of SQL tips, tutorials, and expert advice. Newbie and guru alike will find something useful here! >> CLICK for our current Featured Topic... http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci802922,00.html >> CLICK for previous Featured Topics... http://searchdatabase.techtarget.com/featuredTopics/0,290043,sid13,00.html ___________________________________________________________________ *********************SEARCHDATABASE CONTACTS*********************** TIP MAILBOX, (mailto:[EMAIL PROTECTED]) >> Submit your original tip here. TIM DICHIARA, Site Editor (mailto:[EMAIL PROTECTED]) >> Send your feedback and original articles. SARA CUSHMAN, Assistant Editor (mailto:[EMAIL PROTECTED]) >> Send your White Papers and favorite Web sites. ED PARRY, News Editor (mailto:[EMAIL PROTECTED]) >> Send your product announcements and poll ideas. TOM CLICK, Sales (mailto:[EMAIL PROTECTED]) >> Sponsor this or any other TechTarget newsletter. ___________________________________________________________________ **********************ABOUT THIS NEWSLETTER************************ Created by TechTarget (http://www.techtarget.com) TechTarget - The Most Targeted IT Media Copyright 2002, All Rights Reserved. If you no longer wish to receive this newsletter simply reply to this message with "REMOVE" in the subject line. Or, visit http://searchDatabase.techtarget.com/register and adjust your subscriptions accordingly. If you choose to unsubscribe using our automated processing, you must send the "REMOVE" request from the email account to which this newsletter was delivered. Please allow 24 hours for your "REMOVE" request to be processed.
