Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 1.4 October 17, 2000 TABLE OF CONTENTS 1) Pop Quiz 2) Did You Know... 3) SQL Tip: SHARED_POOL_SIZE 4) URLs of the Week: Oracle Books 5) Answer to the Pop Quiz 6) In the News: Oracle9i --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- How do you collect runtime information about a PL/SQL program, such as the runtime performance of its SQL commands? --------------------------------------------------------- 2) DID YOU KNOW... --------------------------------------------------------- * that Oracle offers the certification programs for developers and database administrators? According to Oracle, the Oracle Certified Professional (OCP) program is designed to "recognize technology professionals who have the knowledge and the practical experience needed to fully support an Oracle environment." The OCP home page is at http://education.oracle.com/certification/. The OCP program is divided into a number of certification tracks: - Database Administrator - Oracle Application Developer - Database Operator - Oracle Java Developer - Oracle Financial Applications Consultant A candidate must pass a number of tests in order to become "certified," and each certification track has its own list of required tests. For details, go to the test information page at http://education.oracle.com/certification/cp.testinfo.html and download the appropriate candidate guide for your certification track. To prepare for the exams, you can follow one or more of the options below: - Take instructor-led training from Oracle and authorized training vendors. - Purchase specially designed computer-based training software. - Try out practice exams at http://education.oracle.com/certification/sts.html. - Purchase study guides from Oracle Press at http://www.osborne.com/oracle/. You can schedule a test by registering at http://www.2test.com, or by calling the Prometric Regional Center (http://www.prometric.com). For further information, go to the OCP page at http://education.oracle.com/certification/cp.testreg.html. To justify the cost of the OCP program, you may want to download the IT Managers OCP Data Sheet at http://otn.oracle.com/training/index.htm. Once you're "certified" (i.e. you've passed all of the required exams), you can include the OCP logo (downloadable from Oracle by those who are certified) and the OCP designation in your resume. Hopefully, you'll make more $$$ as a result! --------------------------------------------------------- 3) SQL TIP: SHARED_POOL_SIZE --------------------------------------------------------- Database tuning is a complex topic. We're going to touch on this subject by focusing on a database initialization parameter: SHARED_POOL_SIZE. We'll look at other tuning parameters in future issues of this eNewsletter. An Oracle instance (the Oracle runtime environment) consists of the background processes and the allocated computer memory. The allocated memory is known as the system global area; it is made up of three components: database buffer cache (for caching data blocks), the redo log buffer (for recording information for database recovery), and the shared pool. The shared pool is used by Oracle to store the compiled form of the SQL statements submitted by the database users; it is also used to cache Oracle's data dictionary and store runtime information about the database user processes. The size of the SGA cannot be specified directly; however, the size of its main components can be defined in the initialization file (init.ora). The SHARED_POOL_SIZE parameter, denominated in bytes, is used to determine the size of the shared pool. If you set its value to be too small, Oracle may not be able to cache its data dictionary in memory. In addition, it may cause greater contention for the area that stores the compiled SQL statements. Run the following query to see whether the data dictionary is cached: SELECT parameter, gets, getmisses FROM v$rowcache WHERE gets != 0 or scans != 0 or modifications != 0 ORDER BY parameter; The ratio of getmisses to gets should be less than 0.2, meaning that Oracle is able to find its data dictionary components in memory. If not, increase the SHARED_POOL_SIZE, restart the database, and resubmit this query after the database has been running for a while. Run the following query to see whether the SQL statements are cached properly: SELECT sum(pins) pins, sum (reloads) misses FROM v$library; The ratio of misses to pins should be kept to a minimum. Increase the SHARED_POOL_SIZE parameter as required. As I said earlier, database tuning is a major undertaking, and it properly occupies a large part of every DBA's daily routine. I've included a couple of simple queries to see whether your shared pool is defined properly. You are advised to refer to Oracle's Tuning Guide for more in-depth discussion of the subject. --------------------------------------------------------- 4) URLs OF THE WEEK: Oracle Books --------------------------------------------------------- As I mentioned in a prior issue of the Oracle Pro eXTRA, Oracle documentation is available on the Web at http://technet.oracle.com. Oracle also provides a number of books on its products through the publisher Osborne/McGraw-Hill at http://www.osborne.com/oracle/. At the site you can obtain a full listing of all of the available books and buy them online. For best pricing, you may also want to check a few more book sites such as Amazon (http://www.amazon.com), Borders (http://www.borders.com), and Barnes and Noble (http://www.barnesandnoble.com). Sometimes they offer more competitive prices than Osborne on the same books. O'Reilly (http://www.oreilly.com/) provides a number of books on Oracle. Go to http://www.oreilly.com/catalog/ to see their full catalog of books. Once you're on the page, you can search for the "Oracle" books by issuing the Find function from the Web browser. Click on the link to find out the details of a particular book. In fact, you can even peruse a sample chapter of the book---for free! Another publisher that offers sample chapters is Relational Business Solutions (http://www.rbsbooks.com/). Although it offers fewer books on Oracle and is not as well known as the publishers mentioned above, I think it's still worthwhile to take a look at their sample chapters to see the quality of their work for yourself. John Wiley & Sons publishes several books on Oracle. Check out their catalog at http://www.wiley.com/compbooks/. However, they do not give away free chapters! * * * * Book publishers have been producing Oracle books at an impressive rate, with at least four new Oracle books released in the past month or so. O'Reilly released "Building Oracle XML Applications" in September. The URL for the book is http://www.ora.com/catalog/orxmlapp. Another O'Reilly book to be released soon is "Oracle SQL: The Essential Reference" (http://www.oreilly.com/catalog/orsqlter). Other new books include "Oracle Applications Performance Tuning Handbook" by Andy Tremayne (Oracle Press) and "Essential Oracle8i Data Warehousing: Designing, Building, and Managing Oracle Data Warehouses" (John Wiley & Sons). --------------------------------------------------------- 5) ANSWER TO THE POP QUIZ --------------------------------------------------------- SQL Trace is used to collect runtime information about a PL/SQL program. To enable SQL Trace, you must include the following entry in the database initialization file (init.ora): TIMED_STATISTICS = TRUE Setting this parameter alone will not be enough; you'll need to start the actual tracing by one of the following options: Option 1: Edit initialization file SQL_TRACE = TRUE Option 2: Enable manually prior to program execution SQL> ALTER SESSION SET SQL_TRACE = TRUE; SQL> execute p_slowing_running_program; Once SQL tracing is enabled, Oracle will create trace files in the directory specified by the USER_DUMP_DEST parameter in the database initialization file. The trace file will have a name of ora99999.trc, where 99999 is an Oracle-generated sequence number. The advantage of Option 1 is that it's simple to configure. The downside is that it is a system-wide parameter, meaning that Oracle will generate a separate trace file for every new connection to the database. You'll have a hard time finding your trace file, and you'll also use up the disk space very quickly if you have a lot of users connecting to the database. In general, Option 2 is a better approach because you can control when you want a trace file to be created. The content of the generated trace file isn't intended for the "naked eye." You should feed this trace file to an Oracle-supplied utility called "tkprof" to analyze the trace file. Here's how you invoke tkprof: $ tkprof sys=no explain= sort=exeela fchela where refers to the SQL trace file produced by Oracle, refers to the output text file produced by tkprof, and refers to the database account user name and password that owns the tables referenced by the user process. Once the output file is generated, you can open it in a text editor such as "vi" or Microsoft NotePad. You'll notice that it contains a listing of SQL statements issued by the process. For each statement, you'll see its EXPLAIN PLAN (because the explain option is included when invoking tkprof) and its runtime fetch counts. You'll also note that the SQL statements are sorted such that the most time-consuming ones (with the highest number of fetches, and taking the longest time to execute) are placed at the top of the trace file. This is a result of the sort option specified in the tkprof command line. Have fun tracing! --------------------------------------------------------- 6) IN THE NEWS: Oracle9i --------------------------------------------------------- As you know, Oracle has announced the pending release of Oracle9i: http://www.oracle.com/ip/deploy/database/9i/index.html. Not much technical information is available about the new release. Judging from the marketing literature, however, it is apparent that Oracle9i will continue to strive to be a robust database for the Internet. Its focus on clustering (http://www.oracle.com/ip/deploy/database/9i/continuity/index.html?scale.html) and high availability (http://www.oracle.com/ip/deploy/database/9i/continuity/index.html?availability.html) offers evidence to corroborate this view. In addition, Oracle seems to be positioning itself for the wireless market with its Oracle9i Wireless Edition (http://www.oracle.com/ip/deploy/database/9i/wireless/index.html). It will be interesting to see how it competes with Sybase, another database vendor that provides wireless solutions (http://www.sybase.com/solutions/mobilewireless/). --------------------------------------------------------- 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) 2000 http://www.pinnaclepublishing.com All rights reserved.