TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Did you know ... 3) IOUG Live 2004 4) SQL Tip 5) In the News 6) ANSWER TO THE POP QUIZ -------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What is TIMED_STATISTICS used for? -------------------------------------------------------- 2) Did you know ... --------------------------------------------------------- Oracle Profession, the monthly newsletter distributed by Pinnacle Publishing, has now begun to publish technical articles on Oracle 10g? In the May, 2004 edition, Featured columnist, and world renowned PL/SQL expert, Steven Feuerstein, discusses the new features available to the PL/SQL developer. Some items discussed are the new quoting mechanism, floating point datatypes, compile-time warnings, new table functions, and bulk bind improvements to name a few. For more information about ordering a copy or to sign up for a 1 year subscription, visit: http://www.pinpub.com/op/. -------------------------------------------------------- 3) IOUG Live 2004 --------------------------------------------------------- Come join me at IOUG Live 2004, being held this year in Toronto, Canada from April 18-22. This is an excellent opportunity to meet new people and share ideas and tips with colleagues and Oracle experts. Oracle’s own Tom Kite (popular OTN forum producer and Oracle Magazine author) will be on site to discuss topics on using Analytical functions in SQL and how Oracle really handles Read and Write consistence under the covers. There’s more than 200 technical sessions to choose from, so for more information, visit: http://www.ioug.org/live2004/. --------------------------------------------------------- 4) SQL Tip --------------------------------------------------------- Suppose you have an employee table that has the following columns (emp_id, gender, name, hiredate) and you find yourself often querying on both the hiredate and/or gender columns. So you want to create a composite index, but were told that the order of columns is important. Well, that’s still true, but with Oracle 9i, there’s a new feature called an Index Skip Scan. This type of scan is advantageous when there are few distinct values in the leading column and many values in the non leading key of the index. So we can create a composite index on (gender, hiredate) and Oracle will split it into 2 logical subindexes, one for M and one for F. If you run a query like: SELECT * FROM employee WHERE hiredate = to_date(’05-apr-2004’,’dd-mon-yyyy’) A complete scan of the index is not performed, but the subindex with the value F is searched first, followed by a search of the subindex with the value M. Since scanning index blocks is often faster than entire table data blocks, your queries may run faster though the actually performance gains will depend heavily of the configuration of your application. But, at least Oracle is potentially giving us better performance even though we use a non-leading column in our WHERE clause. -------------------------------------------------------- 5) In the News -------------------------------------------------------- The takeover battle between Oracle and PeopleSoft has quieted down a little in the past month. The most recent news is that PeopleSoft has let their refund program expire on March 31. This program was designed to provide customers with up to 2 to 5 times their license fees if the company was acquired and its products discontinued. It was an attempt to scare away Oracle, or any other potential bidder, as it had accumulated $1.6 Billion of potential liabilities for an acquirer as of Dec 31, 2003. The PeopleSoft Board of Directors have not ruled out extending the program further at a later date, but speculation is they may not have to since the bid has been challenged by the Justice Department on antitrust grounds. A trial is slated for June. * * * * A class action suit was filed recently against Siebel Systems Inc. by shareholders of Siebel stock from October 1, 2001 and July 17, 2002. The allegations are that Siebel management overstated customer satisfaction of its products and failed to disclose that a customer satisfaction survey was actually performed by an affiliated company. On July 17th, 2002, the company reported large declines in revenues and profit causing the stock to drop 18%. For more information, you can read the story at: http://biz.yahoo.com/bw/040405/55668_1.html. * * * * On Monday, April 05, 2004, the U.S. Securities and Exchange Commission formally opened an investigation into the restatement of Nortel Networks’ financial statements. Nortel has already begun an internal audit following the suspension of two of its top finance officers last month. The Company has been cooperating with regulators, as it hopes to resolve the issue as quickly as possible. For more information on Nortel, visit: http://cbs.marketwatch.com/tools/quotes/news.asp?siteid=mktw&symb=NT&di st=mktwstorynews -------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- TIME_STATISTICS is an initialization parameter that enables and disables the collection of various timed statistics, such as CPU, elapsed times, disk reads, etc. used often by the SQL Trace facility. The default value of false in this parameter can be changed using ALTER SYSTEM or ALTER SESSION commands, such as: SQL> alter session set timed_statistics=true; Now in Oracle 9i, this parameter’s default value is also determined by the value of STATISTICS_LEVEL. If it is set to TYPICAL or ALL, then TIMED_STATISTICS will be true, if it’s set to BASIC, then it remains false. The May, 2004 Oracle Profession newsletter, discussed in section 2 here within, has an article by Dan Hotka on how to use the Oracle Trace facility to collect information on your SQL statements. It’s definitely worth the read if you are interested in gathering useful timing information from within your application.