Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 2.14 July 11, 2001 TABLE OF CONTENTS 1) Pop Quiz 2) Oracle Strategy for Java 3) Oracle Tuning 4) Your feedback 5) Answer to the Pop Quiz --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- Q1. After you've connected to Oracle, how do you determine the database version? Q2. How do you find out if the Java option is enabled for your database? --------------------------------------------------------- 2) ORACLE STRATEGY FOR JAVA --------------------------------------------------------- Contributor: Jim Skehill Jim's biography: Jim Skehill has a BSc in Computer Science from the University of Toronto, Canada. He's been programming in C/C++ for 11 years and in Java for four years, mostly in the financial services field. You can reach him at mailto:JSkehill@ProcaseConsulting.com. * * * * * I've spent a few columns talking about the wonders of Oracle's Business Components for Java (BC4J). But there are downsides to BC4J, and there are cases when BC4J should NOT be used. This column examines these areas. To a certain extent, BC4J competes with Enterprise Java Beans (EJBs), in that both schemes present an architecture for n-tier Web-based applications: a toolset that takes care of the basic "plumbing" and provides you with slots to plug in the business logic. BC4J is at a disadvantage in that it is a proprietary solution, and, while BC4J claims to work with any database with the appropriate JDBC drivers, this is not something I would attempt. Strictly speaking, however, BC4J and EJB can complement each other because you could place your BC4J classes with an EJB container and access the BC4J classes through an appropriately written interface. But that's a clumsy set- up -- a framework within a framework -- and I wouldn't recommend it. Some people in Oracle have told me that BC4J will become more EJB-like in the future and that some day soon you'll be able to deploy a BC4J Entity class as an EJB entity bean. In the meantime, it's best to consider BC4J and EJB competing technologies. By the way, BC4J has one BIG advantage over EJB. That is the wizards that allow you to point at a schema and, within minutes, generate a corresponding set of Java classes. If you go with an EJB solution, you'll have to hand-code all the schema rules yourself. Another point to keep in mind is that BC4J is a framework for building applications that allow you to display and update multiple tables with various complex relationships across the Web. On the other hand, if you have some Web page that you want to blast out 100 times a second, you will find that using BC4J to generate that page may hinder the performance. Very simply, it is NOT the tool for the job. Also I have had some grief getting timely help and advice about BC4J. The Oracle documentation is relatively complete although out of date in parts. However, I'm sure this could be solved with some more code examples and sample applications. Happily, this situation is improving. There is a lot happening at Oracle on the Application Server/EJB front these days. At the JavaOne conference, Larry Ellison announced that Oracle were scrapping their current application server code because it wasn't fast enough. Oracle had, in fact, licensed the Orion application server from a small Swedish company called Ironflare and dubbed it Oracle Container For J2EE (OC4J). This new application server is very small and very fast. More about it in my next column. --------------------------------------------------------- 3) ORACLE TUNING --------------------------------------------------------- Contributor: Robert Mingov Robert's biography: Robert Mingov has a Masters of Science degree from University of Yakutsk, Russia. He has been in the IT industry for more than 15 years, and he has been programming in Oracle for over six years. Robert is a senior consultant at Procase, and he can be reached at mailto:RMingov@ProcaseConsulting.com. * * * * * In this column, we'll continue our discussion on Oracle's cost-based (CB) optimizer. In order for the CB optimizer to work properly, we have to collect data characteristics -- for instance, statistics on the relevant database objects. These statistics are stored in Oracle's data dictionary. For instance, table statistics can be displayed by querying the USER_TABLES or ALL_TABLES views; column statistics can be accessed using the USER_TAB_COL_STATISTICS or ALL_TAB_COL_STATISTICS views; index statistics can be retrieved by querying the USER_INDEX or ALL_INDEX views; and cluster statistics can be selected from the USER_CLUSTERS or ALL_CLASTERS views. The ANALYZE command provides a simple way of collecting statistics about the specified object (table, index, or cluster). Its syntax is as follows: ANALYZE TABLE|INDEX|CLUSTER object_name COMPUTE|DELETE|ESTIMATE STATISTICS; Where object_name is the name of the table, index, or cluster. The COMPUTE STATISICS option forces Oracle to calculate exact statistics by examining every single row in the underlying object. This option can be very costly for large objects -- for example, tables with tens of millions of rows. Essentially, Oracle has to perform a full scan of the underlying object. Here's an example of how to use the ANALYZE TABLE command: SQL> analyze table employees compute statistics; Table analyzed. To display table statistics use the following query: SQL> select blocks, empty_blocks, num_rows from user_tables where table_name = 'EMPLOYEES'; BLOCKS EMPTY_BLOCKS NUM_ROWS --------- ------------ --------- 1 3 4 The ESTIMATE STATISTICS option provides quicker response because Oracle collects the statistics based on a sample of the data instead of a full scan. In fact, we can control the size of the data sample in rows or percentage points, as illustrated below: SQL> analyze table pma_ports estimate statistics; or SQL> analyze table pma_ports estimate statistics sample 20 percent; Note that Oracle always uses the COMPUTE option if a table has less then 1064 rows or if the sample size for the ESTIMATE option includes is set to a value greater than 50%. Further, the ANALYZE command replaces existing statistics. To clear the existing statistics, use the DELETE option. Index statistics can also be created using the ANALYZE command, as shown below: SQL> create index ind_name on table_name(column) compute statistics; In fact, we can rebuild index and generate statistics in one command: SQL> alter index ind_name rebuild compute statistics; In addition to the ANALYZE command, Oracle provides a procedural interface. The DBMS_DDL utility package contains the ANALYZE_OBJECT procedure for collecting statistics. This command is identical to the ANAYLZE command. To analyze all the tables, clusters, and indexes in a particular database account (schema), use the ANALYZE_SCHEMA procedure in the same utility package. Further, you can use the ANALYZE_DATABASE procedure to analyze all tables, clusters, and indexes in a database. --------------------------------------------------------- 4) YOUR FEEDBACK --------------------------------------------------------- Prashant Gijare wrote that he could use the CHR function to include a line break in the SQL output: SQL> select '|' || chr(32) || '|' from dual; '|' --- | | I use the CHR function to add a carriage return while coding dynamic SQL to create a separate line following the CHR -- SQL> select ' Alter table '|| tname || chr(10) || 'modify column tdate varchar2(20)' from user_tables; This returns many alter table statements like... alter table table_a modify column tdate varchar2(20) alter table table_b modify column tdate varchar2(20) alter table table_c modify column tdate varchar2(20) Thanks, Prashant, for your feedback. --------------------------------------------------------- 5) ANSWER TO THE POP QUIZ --------------------------------------------------------- Q1. SQL> select * from v$version; Q2. SQL> select * from v$option; --------------------------------------------------------- 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.