Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 1.6 November 15, 2000 TABLE OF CONTENTS 1) URLs of the Week: CASE Tools 2) SQL Tip: More Analytic Functions 3) Oracle News 4) 10-Second Tutorial: Database Buffer Cache --------------------------------------------------------- 1) URLs OF THE WEEK: CASE Tools --------------------------------------------------------- In this issue we'll examine a few links related to CASE (Computer Aided Software Engineering) tools. * ERWin is a GUI data modeling tool developed by Platinum, now owned by Computer Associates. http://www.ca.com/products/alm/erwin.htm * System Architect is developed by Popkin Software. It's a comprehensive tool for data and business modeling. http://www.popkin.com/ * Rational Software offers the Rational Rose product suite, which supports system modeling using UML (Universal Modeling Language). http://www.rational.com/products/rose/index.jsp * Last but not least, Oracle offers the Designer product suite, which supports data, process, and business modeling. It also includes code generators for Oracle forms, reports, and even Web applications. http://www.oracle.com/ip/develop/ids/index.html?designer.html * For academic research related to this subject area, please refer to publications by Carnegie Mellon University's Software Engineering Institute. http://www.sei.cmu.edu/ --------------------------------------------------------- 2) SQL TIP: More Analytic Functions --------------------------------------------------------- In the last issue of the Oracle Pro eXTRA, I introduced the Oracle analytic functions, used for data ranking and aggregation. These functions are available in Oracle8i Release 8.1.6. These functions are included in the SELECT clause, using the following format: ([]) OVER([]) where refers to the analytic function name, refers to the optional input parameters for the function, and refers to the additional directives for manipulating the data. Our examples will continue to be based on the EMP table in Oracle's demo account, SCOTT, as shown here: SQL> select deptno, empno, ename, job, sal from emp order by deptno, empno; DEPTNO EMPNO ENAME JOB SAL --------- --------- ---------- --------- --------- 10 7782 CLARK MANAGER 2450 10 7839 KING PRESIDENT 5000 10 7934 MILLER CLERK 1300 20 7369 SMITH CLERK 800 20 7566 JONES MANAGER 2975 20 7788 SCOTT ANALYST 3000 20 7876 ADAMS CLERK 1100 20 7902 FORD ANALYST 3000 30 7499 ALLEN SALESMAN 1600 30 7521 WARD SALESMAN 1250 30 7654 MARTIN SALESMAN 1250 30 7698 BLAKE MANAGER 2850 30 7844 TURNER SALESMAN 1500 30 7900 JAMES CLERK 950 14 rows selected. Let's rank the employees according to their earnings: SQL> select empno, ename, sal, row_number() over(order by sal desc) sal_rank from emp order by sal_rank; EMPNO ENAME JOB SAL SAL_RANK ---------- ---------- --------------- ---------- ---------- 7839 KING PRESIDENT 5000 1 7788 SCOTT ANALYST 3000 2 7902 FORD ANALYST 3000 3 7566 JONES MANAGER 2975 4 7698 BLAKE MANAGER 2850 5 7782 CLARK MANAGER 2450 6 7499 ALLEN SALESMAN 1600 7 7844 TURNER SALESMAN 1500 8 7934 MILLER CLERK 1300 9 7521 WARD SALESMAN 1250 10 7654 MARTIN SALESMAN 1250 11 7876 ADAMS CLERK 1100 12 7900 JAMES CLERK 950 13 7369 SMITH CLERK 800 14 How about ranking their earnings by department? SQL> select deptno, empno, sal, row_number() over (partition by deptno order by sal desc) rank_sal from emp; DEPTNO EMPNO SAL RANK_SAL ---------- ---------- ---------- ---------- 10 7839 5000 1 10 7782 2450 2 10 7934 1300 3 20 7788 3000 1 20 7902 3000 2 20 7566 2975 3 20 7876 1100 4 20 7369 800 5 30 7698 2850 1 30 7499 1600 2 30 7844 1500 3 30 7521 1250 4 30 7654 1250 5 30 7900 950 6 14 rows selected. You have probably noticed by now that the row_number function increases in value even if there's a tie. Oracle provides two additional functions, rank() and dense(), to address this. Let's see how they work: SQL> select empno, sal, row_number() over(order by sal desc) rownumber, rank() over(order by sal desc) rank, dense_rank() over(order by sal desc) denserank from emp; EMPNO SAL ROWNUMBER RANK DENSERANK ---------- ---------- ---------- ---------- ---------- 7839 5000 1 1 1 7788 3000 2 2 2 7902 3000 3 2 2 7566 2975 4 4 3 7698 2850 5 5 4 7782 2450 6 6 5 7499 1600 7 7 6 7844 1500 8 8 7 7934 1300 9 9 8 7521 1250 10 10 9 7654 1250 11 10 9 7876 1100 12 12 10 7900 950 13 13 11 7369 800 14 14 12 In the RANK column, both employees 7788 and 7902 now rank second, whereas employee 7566 ranks fourth. The dense_rank() function causes employee 7566 to rank third. It's important to note that analytic functions are applied AFTER the result set has been determined, just before the final ORDER BY clause is executed. This implies that the data filtering conditions in the WHERE clause will be performed before the analytic functions. Let's rank the salary of the employees, excluding the company president: SQL> select empno, ename, job, sal, row_number() over(order by sal desc) sal_rank from emp where job != 'PRESIDENT' order by sal_rank; EMPNO ENAME JOB SAL SAL_RANK ---------- ---------- --------------- ---------- ---------- 7788 SCOTT ANALYST 3000 1 7902 FORD ANALYST 3000 2 7566 JONES MANAGER 2975 3 7698 BLAKE MANAGER 2850 4 7782 CLARK MANAGER 2450 5 7499 ALLEN SALESMAN 1600 6 7844 TURNER SALESMAN 1500 7 7934 MILLER CLERK 1300 8 7521 WARD SALESMAN 1250 9 7654 MARTIN SALESMAN 1250 10 7876 ADAMS CLERK 1100 11 7900 JAMES CLERK 950 12 7369 SMITH CLERK 800 13 Compare this to the result of an earlier query without the WHERE clause, and you'll see that Scott and Ford have the highest rank, instead of King---the president of the company. Since the ORDER BY clause is the final clause to be executed, we can sort the data differently from the that specified in the analytic functions. SQL> select empno, ename, sal, row_number() over(order by sal desc) sal_rank from emp order by ename; EMPNO ENAME JOB SAL SAL_RANK ---------- ---------- --------------- ---------- ---------- 7876 ADAMS CLERK 1100 12 7499 ALLEN SALESMAN 1600 7 7698 BLAKE MANAGER 2850 5 7782 CLARK MANAGER 2450 6 7902 FORD ANALYST 3000 3 7900 JAMES CLERK 950 13 7566 JONES MANAGER 2975 4 7839 KING PRESIDENT 5000 1 7654 MARTIN SALESMAN 1250 11 7934 MILLER CLERK 1300 9 7788 SCOTT ANALYST 3000 2 7369 SMITH CLERK 800 14 7844 TURNER SALESMAN 1500 8 7521 WARD SALESMAN 1250 10 There are quite a few more variations of the analytic functions. Send me your feedback at mailto:GChan@ProcaseConsulting.com and let me know whether you'd like me to continue discussing them in future issues. --------------------------------------------------------- 3) ORACLE NEWS --------------------------------------------------------- The past two weeks have been relatively quiet, with the exception of an unusual rumor that Larry Ellison, Oracle's CEO, had passed away. The rumor was quickly put to rest with his scheduled appearance at COMDEX on November 13. Other than that, Oracle's stock has been under some pressure in the past little while, due to the overall weakness of the new economy stocks and the political uncertainty related to the U.S. presidential elections. Oracle is down from a peak of $46/share U.S. in early September to the current price of $25/share. --------------------------------------------------------- 4) 10-SECOND TUTORIAL: Database Buffer Cache --------------------------------------------------------- The database buffer cache is the memory allocated to Oracle for caching data blocks. It's allocated to Oracle at database startup, and its size is determined by two parameters, namely, DB_BLOCK_SIZE and DB_BLOCK_BUFFERS. The DB_BLOCK_SIZE parameter refers to the size of an Oracle data block. It's used as the unit of data storage and transfer. The value is determined at database creation time and is very difficult, if not impossible, to change afterwards. The block size ranges from 2KB to 32KB, depending on the Oracle version and operating system. In general, smaller block sizes such as 2KB or 4KB are applicable to OLTP (online transaction processing) applications, whereas larger block sizes such as 16KB are used for data warehouse and decision support systems. This is because OLTP applications typically have a large volume of transactions, with each transaction affecting a relatively small set of data. On the other hand, data warehouses require manipulation of larger amounts of data; hence, a larger block size will reduce the number of required physical I/Os. The DB_BLOCK_BUFFERS parameter refers to number of data blocks used for the database buffer cache. For instance, if the DB_BLOCK_SIZE is 4KB and the DB_BLOCK_BUFFERS parameter is 1000, then the database buffer cache is 4KB * 1000 = 4MB. Note that the DB_BLOCK_BUFFERS can be changed; however, the database must be restarted in order for the new value to take effect. To find out the size of the buffer cache in a running database, issue the following query: SQL> select * from v$sga; NAME VALUE -------------------- ---------- Fixed Size 96984 Variable Size 110542848 Database Buffers 65536000 Redo Buffers 5251072 4 rows selected. The row titled "Database Buffers" refers to the buffer cache in bytes. In this case, it is set to 64MB. Before changing the buffer cache size, you should monitor its performance. The idea is to ensure that during a SQL query, Oracle is able to find the required data blocks in memory---cache hit. If the block is now found in memory, Oracle has to retrieve the block from disk, necessitating a disk I/O---cache miss. Run the following query to determine the cache hit ratio: SQL> select name, value from v$sysstat where name in ('db block gets', 'consistent gets', 'physical reads'); NAME VALUE ------------------------------------------- ---------- db block gets 218419 consistent gets 4636620 physical reads 530758 The hit ratio is tabulated using the following formula: physical reads 1 - ------------------------------- db block gets + consistent gets In our example, the hit ratio is: 530758 1 - ---------------- = 0.8906789420229 218419 + 4636620 The hit ratio should approach 1. If not, increase the DB_BLOCK_BUFFERS parameter. --------------------------------------------------------- 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.