In this section, I will cover how to set up an environment capable of executing the examples in this book. Specifically:
How to set up the SCOTT/TIGER
demonstration schema properly
The environment you need to have up and running
Configuring AUTOTRACE, a SQL*Plus facility
Installing Statspack
Installing and running runstats, and other custom utilities used throughout the book
The coding conventions I use in this book
All of the non-Oracle supplied scripts are available for download from the www.apress.com
website.
The SCOTT/TIGER
schema will often already exist in your database. It is generally included during a typical installation, but it is not a mandatory component of the database. You may install the SCOTT
example schema into any database account; there is nothing magic about using the SCOTT
account. You could install the EMP/DEPT
tables directly into your own database account if you wish.
Many of my examples in this book draw on the tables in the SCOTT
schema. If you would like to be able to work along with them, you will need these tables. If you are working on a shared database, it would be advisable to install your own copy of these tables in some account other than SCOTT
to avoid side effects caused by other users mucking about with the same data.
In order to create the SCOTT
demonstration tables, you will simply:
cd [ORACLE_HOME]/sqlplus/demo
run demobld.sql
when connected as any user
In Oracle 10g and above, you must install the demonstration subdirectories from the Companion CD. I have reproduced the necessary components of demobld.sql
below as well.
demobld.sql
will create and populate five tables. When it is complete, it exits SQL*Plus automatically, so don't be surprised when SQL*Plus disappears after running the script—it's supposed to do that.
The standard demo tables do not have any referential integrity defined on them. Some of my examples rely on them having referential integrity. After you run demobld.sql
, it is recommended you also execute the following:
alter table emp add constraint emp_pk primary key(empno); alter table dept add constraint dept_pk primary key(deptno); alter table emp add constraint emp_fk_dept foreign key(deptno) references dept; alter table emp add constraint emp_fk_emp foreign key(mgr) references emp;
This finishes off the installation of the demonstration schema. If you would like to drop this schema at any time to clean up, you can simply execute [ORACLE_HOME]/sqlplus/demo/demodrop.sql
. This will drop the five tables and exit SQL*Plus.
In the event you do not have access to demobld.sql
, the following is sufficient to run the examples in this book:
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7, 2), COMM NUMBER(7, 2), DEPTNO NUMBER(2) ); INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20); INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30); INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30); INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20); INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30); INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30); INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10); INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20); INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10); INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30); INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20); INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30); INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20); INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10); CREATE TABLE DEPT (DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13) ); INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK'), INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS'), INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO'), INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON'),
If you create the schema by executing the commands above, do remember to go back to the previous subsection and execute the commands to create the constraints.
Most of the examples in this book are designed to run 100 percent in the SQL*Plus environment. Other than SQL*Plus though, there is nothing else to set up and configure. I can make a suggestion, however, on using SQL*Plus. Almost all of the examples in this book use DBMS_OUTPUT
in some fashion. In order for DBMS_OUTPUT
to work, the SQL*Plus command
SQL> set serveroutput on
must be issued. If you are like me, typing this in each and every time would quickly get tiresome. Fortunately, SQL*Plus allows us to setup a login.sql
file, a script that is executed each and every time we start SQL*Plus. Further, it allows us to set an environment variable, SQLPATH
, so that it can find this login.sql
script, no matter what directory it is in.
The login.sql
script I use for all examples in this book is:
define _editor=vi set serveroutput on size 1000000 set trimspool on set long 5000 set linesize 100 set pagesize 9999 column plan_plus_exp format a80 column global_name new_value gname set termout off define gname=idle column global_name new_value gname select lower(user) || '@' || substr( global_name, 1, decode( dot, 0, length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name ); set sqlprompt '&gname> ' set termout on
An annotated version of this file is as follows:
define _editor=vi - Set up the default editor SQL*Plus would use. You may set that to be your favorite text editor (not a word processor) such as Notepad or emacs.
set serveroutput on size unlimited - Enable DBMS_OUTPUT
to be on by default (hence we don't have to type set serveroutput on
every time). Also set the default buffer size to be as large as possible.
set trimspool on - When spooling text, lines will be blank-trimmed and not fixed width. If this is set off (the default), spooled lines will be as wide as your linesize
setting
set long 5000 - Sets the default number of bytes displayed when selecting LONG
and CLOB
columns.
set linesize 100 - Set the width of the lines displayed by SQL*Plus to be 100 characters.
set pagesize 9999 - Set the pagesize
, which controls how frequently SQL*Plus prints out headings, to a big number (we get one set of headings per page).
column plan_plus_exp format a80 - This sets the default width of the explain plan output we receive with AUTOTRACE. a80
is generally wide enough to hold the full plan.
The next bit in the login.sql
sets up my SQL*Plus prompt for me:
define gname=idle column global_name new_value gname select lower(user) || '@' || substr( global_name, 1, decode( dot, 0, length(global_name), dot-1) ) global_name from (select global_name, instr(global_name,'.') dot from global_name ); set sqlprompt '&gname> '
The directive column global_name new_value gname
tells SQL*Plus to take the last value it retrieves for any column named global_name
, and place it into the substitution variable gname
. I then select the global_name
out of the database, and concatenate this with the username I am logged in with. That makes my prompt look like this
ops$tkyte@ora11gr2>
so I know who I am as well as where I am.
AUTOTRACE is a facility within SQL*Plus to show us the explain plan of the queries we've executed, and the resources they used. This book makes extensive use of this facility. There is more than one way to get AUTOTRACE configured.
This is what I like to do to get AUTOTRACE working:
cd [ORACLE_HOME]/rdbms/admin
log into SQL*Plus as SYSTEM
run @utlxplan
run CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
run GRANT ALL ON PLAN_TABLE TO PUBLIC;
You can replace the GRANT TO PUBLIC
with some user if you want. By making it public, you let anyone trace using SQL*Plus (not a bad thing, in my opinion). This prevents every user from having to install their own plan table. The alternative is for you to run @utlxplan
in every schema from which you want to use AUTOTRACE.
The next step is creating and granting the PLUSTRACE
role:
cd [ORACLE_HOME]/sqlplus/admin
log into SQL*Plus as SYS or AS SYSDBA
run @plustrce
run GRANT PLUSTRACE TO PUBLIC
;
Again, you can replace PUBLIC
in the GRANT
command with some user if you want.
You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML (that is, SELECT, DELETE, UPDATE, MERGE
, and INSERT
) statements. It is useful for monitoring and tuning the performance of these statements.
You can control the report by setting the AUTOTRACE system variable.
SET AUTOTRACE OFF
- No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN
- The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS
- The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON
- The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY
- Like SET AUTOTRACE ON
, but suppresses the printing of the user's query output, if any.
StatsPack is designed to be installed when connected as SYSDBA (CONNECT/AS SYSDBA
). In order to install, you must be able to connect in the SYSDBA role. In many installations, installing StatsPack will be a task that you must ask the DBA or administrators to perform.
Once you have the ability to connect, installing StatsPack is trivial. You simply run @spcreate.sql
. This script will be found in [ORACLE_HOME]
dbmsadmin
and should be executed when connected as SYSDBA via SQL*Plus.
You'll need to know three pieces of information before running the spcreate.sql
script. They are:
The password you would like to use for the PERFSTAT
schema that will be created
The default tablespace you would like to use for PERFSTAT
The temporary tablespace you would like to use for PERFSTAT
Running the script will look something like this:
$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Fri May 28 10:52:52 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options sys%ORA11GR2> @spcreate Choose the PERFSTAT user's password ----------------------------------- Not specifying a password will result in the installation FAILING Enter value for perfstat_password: ... <output omitted for brevity> ...
The script will prompt you for the needed information as it executes. In the event you make a typo or inadvertently cancel the installation, you should use spdrop.sql
found in $ORACLE_HOME/rdbms/admin
to remove the user and installed views prior to attempting another install of StatsPack. The StatsPack installation will create a file called spcpkg.lis
. You should review this file for any possible errors that might have occurred. The user, views, and PL/SQL code should install cleanly, however, as long as you supplied valid tablespace names (and didn't already have a user PERFSTAT
).
In this section, I will describe the requirements (if any) needed by various scripts used throughout this book. As well, we will investigate the code behind the scripts.
Runstats is a tool I developed to compare two different methods of doing the same thing and show which one is superior. You supply the two different methods and Runstats does the rest. Runstats simply measures three key things:
Wall clock or elapsed time— This is useful to know, but not the most important piece of information.
System statistics—This shows, side by side, how many times each approach did something (such as a parse call, for example) and the difference between the two.
Latching—This is the key output of this report.
As we'll see in this book, latches are a type of lightweight lock. Locks are serialization devices. Serialization devices inhibit concurrency. Applications that inhibit concurrency are less scalable, can support fewer users, and require more resources. Our goal is always to build applications that have the potential to scale—ones that can service one user as well as 1,000 or 10,000. The less latching we incur in our approaches, the better off we will be. I might choose an approach that takes longer to run on the wall clock but that uses 10 percent of the latches. I know that the approach that uses fewer latches will scale substantially better than the approach that uses more latches.
Runstats is best used in isolation; that is, on a single-user database. We will be measuring statistics and latching (locking) activity that result from our approaches. We do not want other sessions to contribute to the system's load or latching while this is going on. A small test database is perfect for these sorts of tests. I frequently use my desktop PC or laptop, for example.
I believe all developers should have a test bed database they control to try ideas on, without needing to ask a DBA to do something all of the time. Developers definitely should have a database on their desktop, given that the licensing for the personal developer version is simply "use it to develop and test with, do not deploy, and you can just have it." This way, there is nothing to lose! Also, I've taken some informal polls at conferences and seminars. Virtually every DBA out there started as a developer! The experience and training developers could get by having their own database—being able to see how it really works—pays dividends in the long run.
In order to use Runstats, you need to set up access to several V$ views, create a table to hold the statistics, and create the Runstats package. You will need access to four V$ tables (those magic, dynamic performance tables): V$STATNAME, V$MYSTAT, V$TIMER
and V$LATCH
. Here is a view I use:
create or replace view stats as select 'STAT...' || a.name name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# union all select 'LATCH.' || name, gets from v$latch union all select 'STAT...Elapsed Time', hsecs from v$timer;
The actual object names you need to be granted access to will be V_$STATNAME, V_$MYSTAT
, and so on—that is, the object name to use in the grant will start with V_$
not V$
. The V$
name is a synonym that points to the underlying view with a name that starts with V_$
. So, V$STATNAME
is a synonym that points to V_$STATNAME
– a view. You need to be granted access to the view.
You can either have SELECT
on V$STATNAME, V$MYSTAT, V$TIMER
, and V$LATCH
granted directly to you (so you can create the view yourself) or you can have someone that does have SELECT
on those objects create the view for you and grant SELECT
privileges on the view to you.
Once you have that set up, all you need is a small table to collect the statistics:
create global temporary table run_stats ( runid varchar2(15), name varchar2(80), value int ) on commit preserve rows;
Last, you need to create the package that is Runstats. It contains three simple API calls:
RS_START
(Runstats Start) to be called at the beginning of a Runstats test
RS_MIDDLE
to be called in the middle, as you might have guessed
RS_STOP
to finish off and print the report
The specification is as follows:
ops$tkyte%ORA11GR2> create or replace package runstats_pkg 2 as 3 procedure rs_start; 4 procedure rs_middle; 5 procedure rs_stop( p_difference_threshold in number default 0 ); 6 end; 7 / Package created.
The parameter, p_difference_threshold
, is used to control the amount of data printed at the end. Runstats collects statistics and latching information for each run, and then prints a report of how much of a resource each test (each approach) used and the difference between them. You can use this input parameter to see only the statistics and latches that had a difference greater than this number. By default, this is zero, and you see all of the outputs.
Next, we'll look at the package body procedure by procedure. The package begins with some global variables. These will be used to record the elapsed times for our runs:
ops$tkyte%ORA11GR2> create or replace package body runstats_pkg 2 as 3 4 g_start number; 5 g_run1 number; 6 g_run2 number; 7
Next is the RS_START
routine. This will simply clear out our statistics holding table and then populate it with the "before" statistics and latches. It will then capture the current timer value, a clock of sorts that we can use to compute elapsed times in hundredths of seconds:
8 procedure rs_start 9 is 10 begin 11 delete from run_stats; 12 13 insert into run_stats 14 select 'before', stats.* from stats; 15
16 g_start := dbms_utility.get_cpu_time; 17 end; 18
Next is the RS_MIDDLE
routine. This procedure simply records the elapsed time for the first run of our test in G_RUN1
. Then it inserts the current set of statistics and latches. If we were to subtract these values from the ones we saved previously in RS_START
, we could discover how many latches the first method used, how many cursors (a statistic) it used, and so on.
Last, it records the start time for our next run:
19 procedure rs_middle 20 is 21 begin 22 g_run1 := (dbms_utility.get_cpu_time-g_start); 23 24 insert into run_stats 25 select 'after 1', stats.* from stats; 26 g_start := dbms_utility.get_cpu_time; 27 28 end; 29
The next and final routine in this package is the RS_STOP
routine. Its job is to print out the aggregate CPU times for each run and then print out the difference between the statistic/latching values for each of the two runs (only printing out those that exceed the threshold):
30 procedure rs_stop(p_difference_threshold in number default 0) 31 is 32 begin 33 g_run2 := (dbms_utility.get_cpu_time-g_start); 34 35 dbms_output.put_line 36 ( 'Run1 ran in ' || g_run1 || ' cpu hsecs' ); 37 dbms_output.put_line 38 ( 'Run2 ran in ' || g_run2 || ' cpu hsecs' ); 39 if ( g_run2 <> 0 ) 40 then 41 dbms_output.put_line 42 ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) || 43 '% of the time' ); 44 end if; 45 dbms_output.put_line( chr(9) ); 46 47 insert into run_stats 48 select 'after 2', stats.* from stats; 49 50 dbms_output.put_line 51 ( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) || 52 lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) ); 53 54 for x in 55 ( select rpad( a.name, 30 ) || 56 to_char( b.value-a.value, '999,999,999' ) || 57 to_char( c.value-b.value, '999,999,999' ) ||
58 to_char( ( (c.value-b.value)-(b.value-a.value)), '999,999,999' ) data 59 from run_stats a, run_stats b, run_stats c 60 where a.name = b.name 61 and b.name = c.name 62 and a.runid = 'before' 63 and b.runid = 'after 1' 64 and c.runid = 'after 2' 65 66 and abs( (c.value-b.value) - (b.value-a.value) ) 67 > p_difference_threshold 68 order by abs( (c.value-b.value)-(b.value-a.value)) 69 ) loop 70 dbms_output.put_line( x.data ); 71 end loop; 72 73 dbms_output.put_line( chr(9) ); 74 dbms_output.put_line 75 ( 'Run1 latches total versus runs -- difference and pct' ); 76 dbms_output.put_line 77 ( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) || 78 lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) ); 79 80 for x in 81 ( select to_char( run1, '999,999,999' ) || 82 to_char( run2, '999,999,999' ) || 83 to_char( diff, '999,999,999' ) || 84 to_char( round( run1/decode( run2, 0, to_number(0), run2) *100,2 ), '99,999.99' ) || '%' data 85 from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2, 86 sum( (c.value-b.value)-(b.value-a.value)) diff 87 from run_stats a, run_stats b, run_stats c 88 where a.name = b.name 89 and b.name = c.name 90 and a.runid = 'before' 91 and b.runid = 'after 1' 92 and c.runid = 'after 2' 93 and a.name like 'LATCH%' 94 ) 95 ) loop 96 dbms_output.put_line( x.data ); 97 end loop; 98 end; 99 100 end; 101 / Package body created.
Now you are ready to use Runstats. By way of example, we'll demonstrate how to use Runstats to see which is more efficient, a single bulk INSERT
versus row-by-row processing. We'll start by setting up two tables into which we'll insert 1,000,000 rows (the BIG_TABLE
creation script is provided later in this section):
ops$tkyte%ORA11GR2> create table t1 2 as 3 select * from big_table.big_table 4 where 1=0; Table created. ops$tkyte%ORA11GR2> create table t2 2 as 3 select * from big_table.big_table 4 where 1=0; Table created.
And now we are ready to perform the first method of inserting the records, using a single SQL statement. We start by calling RUNSTATS_PKG.RS_START
:
ops$tkyte%ORA11GR2> exec runstats_pkg.rs_start; PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> insert into t1 2 select * 3 from big_table.big_table 4 where rownum <= 1000000; 1000000 rows created. ops$tkyte%ORA11GR2> commit; Commit complete.
Now we are ready to perform the second method, row by row insertion of data:
ops$tkyte%ORA11GR2> exec runstats_pkg.rs_middle; PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> begin 2 for x in ( select * 3 from big_table.big_table 4 where rownum <= 1000000 ) 5 loop 6 insert into t2 values X; 7 end loop; 8 commit; 9 end; 10 / PL/SQL procedure successfully completed.
And finally, we'll generate the report:
ops$tkyte%ORA11GR2> exec runstats_pkg.rs_stop(1000000) Run1 ran in 411 cpu hsecs Run2 ran in 6192 cpu hsecs run 1 ran in 6.64% of the time Name Run1 Run2 Diff STAT...opened cursors cumulati 213 1,000,365 1,000,152 STAT...execute count 213 1,000,372 1,000,159
LATCH.shared pool 2,820 1,006,421 1,003,601 STAT...recursive calls 3,256 1,014,103 1,010,847 STAT...physical read total byt 122,503,168 124,395,520 1,892,352 STAT...cell physical IO interc 122,503,168 124,395,520 1,892,352 STAT...physical read bytes 122,503,168 124,395,520 1,892,352 STAT...db block changes 110,810 2,087,125 1,976,315 STAT...file io wait time 5,094,828 438,102 −4,656,726 LATCH.cache buffers chains 571,469 5,510,416 4,938,947 STAT...undo change vector size 3,885,808 67,958,316 64,072,508 STAT...redo size 120,944,520 379,497,588 258,553,068 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 804,522 6,840,599 6,036,077 11.76% PL/SQL procedure successfully completed.
This confirms you have the RUNSTATS_PKG
package installed and shows you why you should use a single SQL statement instead of a bunch of procedural code when developing applications whenever possible!
Mystat.sql
and its companion, mystat2.sql
, are used to show the increase in some Oracle "statistic" before and after some operation. Mystat.sql
captures the begin value of some statistic
set echo off set verify off column value new_val V define S="&1" set autotrace off select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%' || lower('&S')||'%' / set echo on
and mystat2.sql
reports the difference (&V
is populated by running the first script, mystat.sql
—it uses the SQL*Plus NEW_VAL
feature for that. It contains the last VALUE
selected from the query above):
set echo off set verify off select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%' || lower('&S')||'%' / set echo on
For example, to see how much redo is generated by some UPDATE
statement we can:
big_table@ORA11GR2> @mystat "redo size" big_table@ORA11GR2> set echo off NAME VALUE ------------------------------ ---------- redo size 496 big_table@ORA11GR2> update big_table set owner = lower(owner) 2 where rownum <= 1000; 1000 rows updated. big_table@ORA11GR2> @mystat2 big_table@ORA11GR2> set echo off NAME V DIFF ------------------------------ ---------- ---------------- redo size 89592 89,096
This shows our UPDATE
of 1,000 rows generated 89,096 bytes of redo.
The SHOW_SPACE
routine prints detailed space utilization information for database segments. Here is the interface to it:
ops$tkyte@ORA11GR2> desc show_space PROCEDURE show_space Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P_SEGNAME VARCHAR2 IN P_OWNER VARCHAR2 IN DEFAULT P_TYPE VARCHAR2 IN DEFAULT P_PARTITION VARCHAR2 IN DEFAULT
The arguments are as follows:
P_SEGNAME
- Name of the segment—the table or index name, for example.
P_OWNER
- Defaults to the current user, but you can use this routine to look at some other schema.
P_TYPE
- Defaults to TABLE and represents the type of object you are looking at. For example, select distinct segment_type from dba_segments
lists valid segment types.
P_PARTITION
- Name of the partition when you show the space for a partitioned object. SHOW_SPACE
shows space for only a partition at a time.
The output of this routine looks as follows, when the segment resides in an Automatic Segment Space Management (ASSM) tablespace:
big_table@ORA11GR2> exec show_space('BIG_TABLE'), Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ..................... 0 FS2 Blocks (25-50) ..................... 0 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ..................... 14,469 Total Blocks............................ 15,360 Total Bytes............................. 125,829,120 Total MBytes............................ 120 Unused Blocks........................... 728 Unused Bytes............................ 5,963,776 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 43,145 Last Used Block......................... 296 PL/SQL procedure successfully completed.
The items reported are as follows:
Unformatted Blocks – The number of blocks that are allocated to the table below the high water mark, but have not been used. Add unformatted and unused blocks together to get a total count of blocks allocated to the table but never used to hold data in an ASSM object.
FS1 Blocks-FS4 Blocks – Formatted blocks with data. The ranges of numbers after their name represent the emptiness of each block. For example, (0–25) is the count of blocks that are between 0 and 25 percent empty.
Full Blocks – The number of blocks that are so full that they are no longer candidates for future inserts.
Total Blocks, Total Bytes, Total Mbytes - The total amount of space allocated to the segment measured in database blocks, bytes, and megabytes.
Unused Blocks, Unused Bytes – Represents a portion of the amount of space never used. These are blocks allocated to the segment but are currently above the high water mark of the segment
Last Used Ext FileId – The file ID of the file that contains the last extent that contains data.
Last Used Ext BlockId – The block ID of the beginning of the last extent; the block ID within the last-used file.
Last Used Block – The block ID offset of the last block used in the last extent.
When you use SHOW_SPACE
to look at objects in user space managed tablespaces, the output resembles this:
big_table@ORA11GR2> exec show_space( 'BIG_TABLE' ) Free Blocks............................. 1 Total Blocks............................ 147,456 Total Bytes............................. 1,207,959,552 Total MBytes............................ 1,152 Unused Blocks........................... 1,616 Unused Bytes............................ 13,238,272 Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 139,273 Last Used Block......................... 6,576 PL/SQL procedure successfully completed.
The only difference is the Free Blocks
item at the beginning of the report. This is a count of the blocks in the first freelist group of the segment. My script reports only on this freelist group. You would need to modify the script to accommodate multiple freelist groups.
The commented code follows. This utility is a simple layer on top of the DBMS_SPACE API
in the database.
create or replace procedure show_space ( p_segname in varchar2, p_owner in varchar2 default user, p_type in varchar2 default 'TABLE', p_partition in varchar2 default NULL ) -- this procedure uses authid current user so it can query DBA_* -- views using privileges from a ROLE and so it can be installed -- once per database, instead of once per user that wants to use it authid current_user as l_free_blks number; l_total_blocks number; l_total_bytes number; l_unused_blocks number; l_unused_bytes number; l_LastUsedExtFileId number; l_LastUsedExtBlockId number; l_LAST_USED_BLOCK number; l_segment_space_mgmt varchar2(255); l_unformatted_blocks number; l_unformatted_bytes number; l_fs1_blocks number; l_fs1_bytes number; l_fs2_blocks number; l_fs2_bytes number; l_fs3_blocks number; l_fs3_bytes number; l_fs4_blocks number; l_fs4_bytes number; l_full_blocks number; l_full_bytes number; -- inline procedure to print out numbers nicely formatted -- with a simple label procedure p( p_label in varchar2, p_num in number ) is begin dbms_output.put_line( rpad(p_label,40,'.') || to_char(p_num,'999,999,999,999') ); end; begin -- this query is executed dynamically in order to allow this procedure -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES -- via a role as is customary. -- NOTE: at runtime, the invoker MUST have access to these two -- views! -- this query determines if the object is an ASSM object or not begin
execute immediate 'select ts.segment_space_management from dba_segments seg, dba_tablespaces ts where seg.segment_name = :p_segname and (:p_partition is null or seg.partition_name = :p_partition) and seg.owner = :p_owner and seg.tablespace_name = ts.tablespace_name' into l_segment_space_mgmt using p_segname, p_partition, p_partition, p_owner; exception when too_many_rows then dbms_output.put_line ( 'This must be a partitioned table, use p_partition => '), return; end; -- if the object is in an ASSM tablespace, we must use this API -- call to get space information, else we use the FREE_BLOCKS -- API for the user managed segments if l_segment_space_mgmt = 'AUTO' then dbms_space.space_usage ( p_owner, p_segname, p_type, l_unformatted_blocks, l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes, l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes, l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition); p( 'Unformatted Blocks ', l_unformatted_blocks ); p( 'FS1 Blocks (0-25) ', l_fs1_blocks ); p( 'FS2 Blocks (25-50) ', l_fs2_blocks ); p( 'FS3 Blocks (50-75) ', l_fs3_blocks ); p( 'FS4 Blocks (75-100)', l_fs4_blocks ); p( 'Full Blocks ', l_full_blocks ); else dbms_space.free_blocks( segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, freelist_group_id => 0, free_blks => l_free_blks); p( 'Free Blocks', l_free_blks ); end if; -- and then the unused space API call to get the rest of the -- information dbms_space.unused_space ( segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, partition_name => p_partition, total_blocks => l_total_blocks,
total_bytes => l_total_bytes, unused_blocks => l_unused_blocks, unused_bytes => l_unused_bytes, LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId, LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, LAST_USED_BLOCK => l_LAST_USED_BLOCK ); p( 'Total Blocks', l_total_blocks ); p( 'Total Bytes', l_total_bytes ); p( 'Total MBytes', trunc(l_total_bytes/1024/1024) ); p( 'Unused Blocks', l_unused_blocks ); p( 'Unused Bytes', l_unused_bytes ); p( 'Last Used Ext FileId', l_LastUsedExtFileId ); p( 'Last Used Ext BlockId', l_LastUsedExtBlockId ); p( 'Last Used Block', l_LAST_USED_BLOCK ); end; /
For examples throughout this book, I use a table called BIG_TABLE
. Depending on which system I use, this table has between one record and four million records and varies in size from 200MB to 800MB. In all cases, the table structure is the same.
To create BIG_TABLE
, I wrote a script that does the following:
Creates an empty table based on ALL_OBJECTS
. This dictionary view is used to populate the BIG_TABLE
.
Makes this table NOLOGGING
. This is optional. I did it for performance. Using NOLOGGING
mode for a test table is safe; you won't use it in a production system, so features like Oracle Data Guard will not be enabled.
Populates the table by seeding it with the contents of ALL_OBJECTS
and then iteratively inserting into itself, approximately doubling its size on each iteration.
Creates a primary key constraint on the table.
Gathers statistics.
To build the BIG_TABLE
table, you can run the following script at the SQL*Plus prompt and pass in the number of rows you want in the table. The script will stop when it hits that number of rows.
create table big_table as select rownum id, a.* from all_objects a where 1=0 / alter table big_table nologging; declare l_cnt number; l_rows number := &1; begin insert /*+ append */
into big_table select rownum, a.* from all_objects a where rownum <= &1; l_cnt := sql%rowcount; commit; while (l_cnt < l_rows) loop insert /*+ APPEND */ into big_table select rownum+l_cnt, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME from big_table where rownum <= l_rows-l_cnt; l_cnt := l_cnt + sql%rowcount; commit; end loop; end; / alter table big_table add constraint big_table_pk primary key(id); exec dbms_stats.gather_table_stats( user, 'BIG_TABLE', estimate_percent=> 1);
I estimated baseline statistics on the table. The index associated with the primary key will have statistics computed automatically when it is created.
The one coding convention I use in this book that I would like to point out is how I name variables in PL/SQL code. For example, consider a package body like this:
create or replace package body my_pkg as g_variable varchar2(25); procedure p( p_variable in varchar2 ) is l_variable varchar2(25); begin null; end; end; /
Here I have three variables: a global package variable, G_VARIABLE
; a formal parameter to the procedure, P_VARIABLE
; and a local variable, L_VARIABLE
. I name my variables after the scope they are contained in. All globals begin with G_
, parameters with P_
, and local variables with L_
. The main reason for this is to distinguish PL/SQL variables from columns in a database table. For example, a procedure such as
create procedure p( ENAME in varchar2 ) as begin for x in ( select * from emp where ename = ENAME ) loop Dbms_output.put_line( x.empno ); end loop; end;
would always print out every row in the EMP
table where ENAME
is not null. SQL sees ename = ENAME
, and compares the ENAME
column to itself (of course). We could use ename = P.ENAME
; that is, qualify the reference to the PL/SQL variable with the procedure name, but this is too easy to forget, leading to errors.
I just always name my variables after the scope. That way, I can easily distinguish parameters from local variables and global variables, in addition to removing any ambiguity with respect to column names and variable names.