Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 2.11 May 30, 2001 TABLE OF CONTENTS 1) Pop Quiz 2) Did You Know ... 3) Oracle Strategy for Java 4) Oracle Tuning 5) Oracle News 6) Answer to the Pop Quiz --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- How do you estimate the average size of records in a particular table? --------------------------------------------------------- 2) DID YOU KNOW... --------------------------------------------------------- Hewlett Packard provides a utility for monitoring Oracle environments. Called HP OpenView SMART Plug-In (SPI) for Oracle Database Servers, it helps administrators efficiently monitor distributed enterprise-wide Oracle environments from a central console. The Web site is: http://smartfriend.hp.com/smartfriend/cgi-bin/sfem.pl?EML33103=84741 --------------------------------------------------------- 3) ORACLE STRATEGY FOR JAVA --------------------------------------------------------- Jim Skehill is on vacation this week, and his columns will be back shortly. --------------------------------------------------------- 4) ORACLE TUNING --------------------------------------------------------- Since Oracle database operations are performed using SQL, it is very important to know how to tune SQL statements. Applications often suffer from poor performance if the SQL statements, particularly queries, are not optimized. I've invited my colleague at Procase, Robert Mingov, to share his SQL tuning knowledge with us. Robert has in- depth knowledge of Oracle technology. Most recently, he is our Oracle team lead, spearheading efforts to tune over 100 SQL-based reports that access tens of millions of rows of data. In the coming few issues of this newsletter, Robert will contribute articles on Oracle tuning. He will start with basic tuning concepts, then proceed to advanced SQL tuning techniques. Robert's biography: Robert Mingov has a Masters of Science degree from University of Yakutsk, Russia. He's been programming in Oracle for over 10 years and is a senior consultant at Procase. You can reach him at mailto:RMingov@ProcaseConsulting.com. * * * * * SQL is a powerful and flexible language and allows different ways of getting required data from a database. Although different SQL statements may produce the same result, not all of them yield the same performance. To ensure SQL statements perform optimally, we have to understand steps that Oracle performs to retrieve the data from the database. In this first article of the Tuning series, we'll cover the basic tuning concepts: rule-based vs. cost-based optimization. When a user submits an SQL statement for execution, Oracle has to compile it prior to execution. The compilation, or parsing, allows Oracle to check the syntax of the SQL statement and come up with the execution plan. The parsed SQL is then "cached" in memory to maximize reuse of the statement. In other words, if the same SQL statement is requested, Oracle can skip the parsing stage and execute the statement based on the "cached" execution plan. The optimizer is the component of Oracle that chooses the most efficient way of executing an SQL statement. Oracle's optimizer uses either a Rule-based (RB) or a Cost-based (CB) approach to determine the most effective SQL execution plan. In general, the rule-based approach enables Oracle to devise an execution plan based on the syntax of the SQL statement. For the RB optimizer, the term "SQL syntax sensitive" is used to note that the execution plan depends on the way we construct the SQL statement -- for example, how the FROM and WHERE clauses are coded. The optimizer uses the ranking approach if there is more than one way of executing a SQL statement -- that is, multiple access paths. Each access path has a corresponding rank, numbered 1 to 15. For instance, access path "Single row by ROWID" has a rank of 1 -- that is, it is the fastest method of retrieving data, whereas access path "Full Table Scan" is ranked 15 -- the slowest method. When deciding which access path to use, RB optimizer always uses the method with lowest rank. Nevertheless, this approach may not always produce the best performance. Let's take a look at an example. When we select data from small tables, it may be better to use the "Full Table Scan" method -- that is, the lowest rank from RB's perspective. Further, for queries that return a large percentage of rows in a table it is better to use the "Full Table Scan" method once again. Since RB optimizer focuses on the syntax of the SQL statement, it does not take into consideration the underlying characteristics of the tables referenced in the SQL statement, such as the number of rows in a table, selectivity of indexed data, utilization of CPU, and other statistics. To use RB optimizer, we have to consider following: - We must know data very well (always needed). - Put tables in the right order in FROM clause, keeping in mind that tables will be accessed from right to left (from bottom to top). - Suppress index usage (force Full Table Scan) where it is needed. In the next article, we'll discuss cost-based optimization. --------------------------------------------------------- 5) ORACLE NEWS --------------------------------------------------------- Oracle's conference, Oracle OpenWorld 2001, will be held in San Francisco from December 2 to 7, 2001. Their Web site is http://www.oracle.com/go/?&Src=668068&Act=4 The conference is now accepting conference paper proposals. You can submit your proposal at http://www.oracle.com/go/?&Src=668068&Act=4 One advantage of your paper being selected is that you will receive free admission to the conference (estimated value of $1,395). Oracle is looking for proposals in the following focus areas: * DEVELOPING, DEPLOYING, AND MANAGING E-BUSINESS APPLICATIONS AND ONLINE SOFTWARE SERVICES * DATA WAREHOUSING AND BUSINESS INTELLIGENCE * FAST TRACK TO ORACLE9i * E-STRATEGIES FOR E-BUSINESS * THE E-BUSINESS SUITE, E-BUSINESS SOLUTIONS The detailed description of each area can be found at the proposal submission Web site. Good luck! --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- There's a column in the user_tables view (also applicable to the all_tables and dba_tables views) that displays the average length of rows in a particular table, as shown below: SQL> select avg_row_len from user_tables where table_name = 'EMP'; Note that avg_row_len is refreshed each time you run the ANALYZE TABLE command, as shown below: SQL> analyze table emp compute statistics; Do you know of other methods? If so, please let me know, and I'll share them with the subscribers. --------------------------------------------------------- Well, that's it for this week. I welcome your feedback, input, tips, suggestions, Web sites, and other Oracle- related news. If you send me something, please let me know whether I can use your name with your comments. I apologize in advance if I don't respond personally to each of your questions or suggestions, but I'll get to as many as I can in the eNewsletter if not personally. Garry Chan, Editor Database Architect mailto:GChan@ProcaseConsulting.com This eNewsletter is brought to you compliments of Pinnacle Publishing, Inc. Copyright(c) 2001 http://www.pinnaclepublishing.com All rights reserved.