Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 2.12 June 12, 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) --------------------------------------------------------- How do you encrypt the source code of your database- stored programs? --------------------------------------------------------- 2) ORACLE STRATEGY FOR JAVA --------------------------------------------------------- Jim Skehill is back! 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. * * * * * As promised, this column will introduce Java Server Pages. In the next column, I illustrate their use in the context of the case study I presented a few columns back. Anyone who has coded Web pages in HTML is probably aware of HTML's inherent limitation. It's good for static content -- for Web pages that stay the same day to day -- but if you want dynamic content, you need to look elsewhere. Java has two technologies you could use. One is servlets. A Java servlet is Java code placed on the server (All major Web servers offer servlet support). The servlet is invoked by an HTTP request from a Web browser, will generate HTML code, and send it to the browser. Since the servlet is written in Java, it can easily, for example, get the top ten stock quotes from a database, wrap it in HTML code, and send it out. The second technology is Java Server Pages or JSPs. Whereas writing a servlet requires a lot of low-level Java coding, JSPs allow you to mix Java and HTML in a single file, and a JSP interpreter (again available on all major Web servers) will sort it out. Actually, calling this a second technology is somewhat deceptive. What the JSP interpreter does is read the JSP code, separating the Java and HTML code. It then writes a servlet that generates the desired output, compiles it, and runs it. So, in a very real sense, JSPs are built on top of servlet technology. JSPs may seems like really cool technology, an easy way to write Java code. But one should be careful about going too far with JSPs. JSP code, being a mix of Java and HTML code, gets very messy very fast. Also JSPs cannot be sub-classed -- that is, you cannot easily build a new JSP on top of an existing JSP. This makes code reuse a problem. But JSPs have various mechanisms to deal with these problems. For example, since most Web pages naturally break up into components, it make sense to write individual JSPs for each component and then write a main JSP file and use the "include" tag to paste the various components together. JSPs also have a "usebean" tag that lets you embed JavaBeans into a JSPs. It's a good practice to encapsulate any heavy Java code into a Bean and have your JSP use that bean. If they are well-designed, you may find that the same JavaBean (or a sub-class of it) can be used in several JSPs. The goal should be to make the JSP look as much like an HTML file as possible -- that is, mostly tags, very little Java. Ideally, the JSP file should be written and managed by a Web Designer whose job it is to write HTML, and all Java code is supplied by a Java programmer in the form of JavaBeans. --------------------------------------------------------- 3) ORACLE TUNING --------------------------------------------------------- Robert Mingov will continue his discussion of Oracle tuning. Robert's biography: Robert Mingov has a Masters of Science degree from the 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. * * * * * Oracle first introduced the cost-based (CB) optimizer in Oracle7, around 1993. Since this time, CB optimizer is continually enhanced. Several Oracle server features, such as partitioned tables and bitmap Indexes, are available only to CB optimizer. That means it is better to design new applications using cost-based optimizer. The CB approach uses statistics about the referenced objects to decide the execution plan. It builds all possible execution plans, and uses the statistics to calculate the execution cost incurred for each plan. The optimizer then chooses the execution plan that will produce results in the shortest time. The CB optimizer uses these statistics to estimate the I/O, CPU time, memory required to execute an SQL statement using a particular execution plan. The plan that is the least expensive, in terms of time taken to produce results, is chosen. To know which optimizer mode is in use in the database instance, we can examine a database initialization parameter OPTIMIZER_MODE in the init.ora file or by running the following query: SQL> select value from v$system_parameter where name like 'optimizer_mode'; If the parameter is not set, default mode for optimizer is CHOOSE. (Note that the parameter names are stored in lowercase, contrary to the other Oracle data dictionary objects whose names are stored in uppercase.) This default CHOOSE option means the following: - Rule-based optimization is in use if no statistics are available (this happens when tables are not analyzed). - CB optimization is in use if all tables referenced in the statement are analyzed (statistics are available). - CB optimization is in use if at least one table in the statement is analyzed. Missing statistics are estimated. Sometimes, based on estimated statistics, CB optimizer may implement Full Table Scan for tables that have not been analyzed. To avoid such behavior, set optimization mode explicitly to CB or RB optimization. In addition to default CHOOSE mode, Oracle optimizer has following modes: - ALL_ROWS -- CB approach, optimizes for overall throughput (good for data warehouses, and reporting databases) - FIRST_ROWS -- CB approach, optimizes for returning the first row (good for interactive applications) - RULE -- Forces rule-based approach, regardless of the presence of statistics. While the optimization mode can be determined for the entire database instance, at instance startup time, you can specify session-level optimization: SQL> select value from v$parameter where name like 'optimizer_mode'; By default, the session and instance optimization modes are the same. Here's how you can change it: SQL> alter session set optimizer_mode=ALL_ROWS; Now, Oracle uses CB optimizer for every Select, Insert, Update, and Delete (DML) statement within the session. Session-level optimization can be further overridden at the statement level. This is achieved by embedding query hints in an SQL statement. All optimizer hints, except the RULE hint, are forcing CB optimization. Hints should follow SQL command's keyword in the format of: /*+ hint1 hint2 */. For example SQL> SELECT /*+ ordered index(a index_name) */ a.column, b.column, ... FROM table_name1 a, table_name2 b, ... The above hints instruct Oracle to: - use CB optimization - access tables in the order listed in FROM clause - use the "index_name" index to access data in table_name1 In the next issue, I'll discuss how to generate statistics, and have more discussion on the optimization hints and operations. --------------------------------------------------------- 4) YOUR FEEDBACK --------------------------------------------------------- Jim Foos noted the following: "Your tip on the average row size works well for existing tables, and is very useful for evaluating storage parameters. "I would also be interested in software tools/scripts/documents, etc., that would help in developing appropriate storage parameters during design of new tables. Personally, I prefer to use tools that also at least educate the user about the underlying design principals or algorithms invoked by the solution. "I am sure that there already exists a large volume of information and tools that cover both initial design and on-going evaluation and maintenance of appropriate storage parameters of tables. Since newsletters are an excellent information resource for a large community, this may be an area to explore in a future edition." Definitely, Jim. I'll try to include this in a future issue of the eNewsletter. Also, David Cuffe mentioned a second method for calculating table row size. "Another method for getting the average row size of a table is to use the 'vsize' function. This function returns the actual storage space used by a column for a particular row (as opposed to the maximum storage size of that column, as specified by the column's datatype definition), so you'd have to write a script to select sum(vsize(col1)+vsize(col2)+...+vsize(colN)) from the table (using user/all/dba_tab_columns to generate the select statement) and then divide by the row count." --------------------------------------------------------- 5) ANSWER TO THE POP QUIZ --------------------------------------------------------- You can use the PLSQL Wrap Utility to encrypt PL/SQL program modules. The following function is stored in the asset.sql text file: create or replace function f_asset_value (i_portfolio_id in number) return number is l_amt number; begin l_amt := 0; -- -- Contains proprietary business logic -- ... -- return l_num; end; / The function's logic is exposed in two areas: the asset.sql is in clear text, and after compilation the program source code is stored in the Oracle data dictionary. SQL> select text from user_source where name ='F_ASSET_VALUE'; TEXT ------------------------------------------------------ function f_asset_value (i_portfolio_id in number) return number is l_amt number; begin l_amt := 0; -- -- Contains proprietary business logic -- ... -- return l_amt; end; 12 rows selected. Now, let's use the wrapper to encrypt the program source. The "wrap" command accepts an input parameter "iname," which defines the location and name of the file to be encrypted. A second, optional parameter, "oname," specifies the name and directory of the output file. Let's illustrate this with an example. Open the Command window in Windows, or enter the following command in Unix: C:\> wrap iname=asset.sql PL/SQL Wrapper: Release 8.1.7.0.0 - Production on Mon Jun 11 01:24:25 2001 Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved. Processing asset.sql to asset.plb The encrypted source code has a default file extension of ".plb". Now you can compile the encrypted version, as shown below: SQL> @c:\asset.plb Function created. Note that you're still able to display the function interface: SQL> desc f_asset_value; FUNCTION f_asset_value RETURNS NUMBER Argument Name Type In/Out Default? ------------------------------------------------------ I_PORTFOLIO_ID NUMBER IN However, you'll no longer be able to examine its source code: SQL> select text from user_source where name ='F_ASSET_VALUE'; TEXT ------------------------------------------------------ function f_asset_value wrapped 0 abcd abcd abcd ... A common usage of the wrap utility is to "wrap" the algorithm used to encrypt/decrypt application user passwords. --------------------------------------------------------- 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.