You Can Never Be Too Smart

Smart Scans are part of what Kerry Osborne calls the ‘secret sauce’ of Exadata, and he’s right. Smart Scans can definitely improve throughput by using the various optimizations to reduce the amount of data read and the volume of data returned to the database servers. You really want to have them working on an Exadata machine. But how do you know they’re working? Let’s look at a couple ways to prove you have Smart Scans working for you.

The easiest way to prove you have a Smart Scan working (from the database server side) is to query the V$SQL view (or GV$SQL if you want to check across the RAC cluster). There are two ‘counters’ available which report on Smart Scan activity; these are IO_CELL_OFFLOAD_ELIGIBLE_BYTES and IO_CELL_OFFLOAD_RETURNED_BYTES. I’ve used this example in another post but it’s a good example for showing Smart Scan activity so I’ll use it again here:

SQL> select *
  2  from emp
  3  where empid = 7934;

     EMPID EMPNAME                                      DEPTNO
---------- ---------------------------------------- ----------
      7934 Smorthorper7934                                  15

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     1 |    26 |  1167   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| EMP  |     1 |    26 |  1167   (1)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - storage("EMPID"=7934)
       filter("EMPID"=7934)


Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
       4349  consistent gets
       4276  physical reads
          0  redo size
        680  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> set autotrace off timing off
SQL>
SQL> select       sql_id,
  2          io_cell_offload_eligible_bytes qualifying,
  3          io_cell_offload_returned_bytes actual,
  4          round(((io_cell_offload_eligible_bytes - io_cell_offload_returned_bytes)/io_cell_offload_eligible_bytes)*100, 2) io_saved_pct,
  5          sql_text
  6  from v$sql
  7  where io_cell_offload_returned_bytes > 0
  8  and instr(sql_text, 'emp') > 0
  9  and parsing_schema_name = 'BING';

SQL_ID        QUALIFYING     ACTUAL IO_SAVED_PCT SQL_TEXT
------------- ---------- ---------- ------------ --------------------------------------
gfjb8dpxvpuv6   35028992       6872        99.98 select * from emp where empid = 7934

SQL>

Looking at the output from the V$SQL query we can see that a Smart Scan was executed since we had both eligible and returned bytes from the cell offload process. This is probably the quickest and easiest way to verify Smart Scan activity. There is another way, though, that is a bit more involved but gives more detailed stats on Smart Scan activity. This method involves the storage cells and the cellsrvstat program; since it involves the storage cells it can be a bit more work, especially if passwordless ssh connectivity isn’t configured between the database servers and the storage cells. [I won't go into configuring that in this post but there are various resources on the Internet available to assist with that task. http://www.google.com is a great place to start looking for those.] I will presume that such connectivity is configured; getting these stats is a fairly straightforward task that can be scripted and run from the command line on one of the database servers. The script would be similar to this:

#!/bin/ksh

ssh celladmin@exasrv1cel01-priv.mydomain.com “cellsrvstat -stat_group=smartio -interval=$1 -count=$2″
ssh celladmin@exasrv1cel02-priv.mydomain.com “cellsrvstat -stat_group=smartio -interval=$1 -count=$2″
ssh celladmin@exasrv1cel03-priv.mydomain.com “cellsrvstat -stat_group=smartio -interval=$1 -count=$2″

The script takes two parameters, the sample interval and the count of samples to execute; as an example of how this would look at the Linux command line:

$ cellsrvstat_smartio.sh 5 10

which would set a sampling interval of 5 seconds with 10 samples gathered. One nice aspect of setting this up with a script and ssh access is that you can redirect the output to a local file on the database server and look at it later:

$ cellsrvstat_smartio.sh 5 10 > cellsrvstat_all_cells_smartio.lst

This makes it easier to scan all of the output later for review. The output is presented in a tabular format, shown below:

===Current Time===                                      Fri Mar  8 14:37:15 2013

== SmartIO related stats ==
Number of active smart IO sessions                              8             11
High water mark of smart IO sessions                            0            130
Number of completed smart IO sessions                           4        8490054
Smart IO offload efficiency (percentage)                        0              7
Size of IO avoided due to storage index (KB)                    0  2511867327696
Current smart IO to be issued (KB)                              0         214456
Total smart IO to be issued (KB)                            69592  5335617009440
Current smart IO in IO (KB)                                     0              0
Total smart IO in IO (KB)                                   69592  5333234484896
Current smart IO being cached in flash (KB)                     0              0
Total smart IO being cached in flash (KB)                       0              0
Current smart IO with IO completed (KB)                         0           3200
Total smart IO with IO completed (KB)                       69592  5427224552840
Current smart IO being filtered (KB)                            0              0
Total smart IO being filtered (KB)                          69592  5426811394136
Current smart IO filtering completed (KB)                       0          24528
Total smart IO filtering completed (KB)                     69592  5332811276704
Current smart IO filtered size (KB)                             0           8075
Total smart IO filtered (KB)                                13258   378946996063
Total cpu passthru output IO size (KB)                          0      449965296
Total passthru output IO size (KB)                              0      472758949
Current smart IO with results in send (KB)                      0              0
Total smart IO with results in send (KB)                    69592  5332478889672
Current smart IO filtered in send (KB)                          0              0
Total smart IO filtered in send (KB)                        13258   378925244226
Total smart IO read from flash (KB)                             0              0
Total smart IO initiated flash population (KB)                  0              0
Total smart IO read from hard disk (KB)                     69592  2786064722152
Total smart IO writes (fcre) to hard disk (KB)                  0    35298961256
Number of smart IO requests < 512KB                            20      134456077
Number of smart IO requests >= 512KB and < 1MB                 65      116155599
Number of smart IO requests >= 1MB and < 2MB                    3        2226841
Number of smart IO requests >= 2MB and < 4MB                    0      188723594
Number of smart IO requests >= 4MB and < 8MB                    0     1085851766
Number of smart IO requests >= 8MB                              0              0
Size of the smart IO 1MB IO quota being used                    0              0
Hwm of the smart IO 1MB IO quota being used                     0           1002
Number of failures to get 1MB IO quota for smart IO             0         674099
Number of times smart IO buffer reserve failures                0              0
Number of times smart IO request misses                         0         296985
Number of times IO for smart IO not allowed to be issued        0    58504757930
Number of times smart IO prefetch limit was reached             2      125315622
Number of times smart scan used unoptimized mode                0              0
Number of times smart fcre used unoptimized mode                0              0
Number of times smart backup used unoptimized mode              0              0

The metrics are, to me, at least, clearly described in the table so you won’t be wondering what an abbreviated metric name actually represents. The left numeric column indicates current Smart I/O activity and the rightmost numeric column is a cumulative value since the storage cell came online.

Another nice aspect of the storage cell method is you can monitor Smart Scan activity in progress, instead of waiting for the process to finish and Oracle to update the stats in the V$SQL/GV$SQL views. To monitor all cells simultaneously you can create three (or more, depending upon how many storage cells you have in your Exadata configuration) separate scripts and run them at basically the same time in the background. To be ‘really cool’ you could wrap the individual storage cell scripts into a single driver script and, at the end of the monitoring, concatenate the log files together into one huge report. Neat. Since I have to leave something for you to do I won’t illustrate those scripts here.

Exadata provides not only Smart Scans but at least a couple of ways to monitor them; which one you use is your choice, really, and it depends on if you want real time monitoring or just a daily report on how much Smart Scan activity occurred on a given day. Either way you’ll know if you’re executing Smart Scans and how much time (and I/O) you’ve saved because of them.

Speedy and smart. That’s a winning combination.

Privilege Is Everything, Sometimes

A common problem surfaced again in an Oracle forum I frequent — a user received a ‘table or view does not exist’ error when creating a procedure against a table he/she didn’t own. A straight select against the table succeeds from SQL*Plus so what is the problem?

It all boils down to privilege in this case. The user has grants on the table through a role, but no direct grants on that table have been made by the owner to the affected user. Since PL/SQL operates a bit differently than SQL*Plus direct grants are a necessity to create procedures and functions against tables and views a user does not own. Let’s look at an example:

SQL> create table location(
  2          yankpurt number,
  3          snozwert varchar2(12),
  4          hunlzap number);

Table created.

SQL>
SQL> begin
  2          for i in 1..100 loop
  3                  insert into location
  4                  values(i, 'SMAZOO!!!!', i);
  5          end loop;
  6
  7          commit;
  8
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> grant all on location to loc_role;

Grant succeeded.

SQL>
SQL> create public synonym location for bong.location;

Synonym created.

SQL>
SQL> connect bing/bong
Connected.
SQL> set echo on
SQL>
SQL> CREATE or replace PROCEDURE TESTPROC
  2  AS
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE('testing');
  5    select count(*) from LOCATION;
  6  END;
  7  /

Warning: Procedure created with compilation errors.

SQL>
SQL> show errors
Errors for PROCEDURE TESTPROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PL/SQL: SQL Statement ignored
5/24     PL/SQL: ORA-00942: table or view does not exist
SQL>
SQL>
SQL> connect bong/bing
Connected.
SQL>
SQL> grant all on location to bing;

Grant succeeded.

SQL>
SQL> connect bing/bong
Connected.
SQL> set echo on serveroutput on size 1000000
SQL>
SQL> CREATE or replace PROCEDURE TESTPROC
  2  AS
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE('testing');
  5    select count(*) from LOCATION;
  6  END;
  7  /

Warning: Procedure created with compilation errors.

SQL>
SQL> show errors
Errors for PROCEDURE TESTPROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PLS-00428: an INTO clause is expected in this SELECT statement
SQL>
SQL>
SQL> CREATE or replace PROCEDURE TESTPROC
  2  AS
  3    loc_ct number;
  4  BEGIN
  5    DBMS_OUTPUT.PUT_LINE('testing');
  6    select count(*) into loc_ct from LOCATION;
  7    dbms_output.put_line(loc_ct);
  8  END;
  9  /

Procedure created.

SQL>
SQL> exec testproc;
testing
100

PL/SQL procedure successfully completed.

SQL>

SQL>

Besides the user-inflicted error (PLS-00428: an INTO clause is expected in this SELECT statement) the main problem was the lack of direct grants against the table in question. Correcting that cleared up the “PL/SQL: ORA-00942: table or view does not exist” error; since PL/SQL cannot recognize grants through roles on objects a user doesn’t own the procedure creation failed even though a select through SQL*Plus succeeded. Without direct grants on an object a user cannot create procedures or functions against that object.

Privilege, sometimes, is everything.

I Can’t Find My Keys

Exadata offers many features to improve performance — Smart Scans, offloading, Infiniband internal network — but the one feature not often mentioned is the storage index. Designed to prevent Exadata from reading data blocks unnecessarily its purpose is sometimes not clearly explained, leading to confusion. So what is a storage index, where is it found and what does it do? Let’s see if we can answer those questions, and possibly a few more.

Exadata storage indexes are … indexes … but not in the usual bitmap/b-tree sense. You can’t use one to pinpoint the exact location of a particular record and you can’t scan one for a particular range of values. You can’t build one with DDL like ‘create storage index …’ because they are dynamically created in memory based on predicates in a where clause. How, then, are they built, what do they contain and what good purpose do they serve?

Exadata divides the storage into 1 MB sections, on a per table basis; for a quarter rack configured with high capacity disks using the out-of-the-box 80/20 data to recovery distribution there is roughly 30 TB of usable storage to be divided into 1 MB ‘chunks’, making approximately 31 million storage segments, each with its own storage index. That’s not all, as each storage index can contain data for up to eight columns referenced in where clauses of queries that satisfy the conditions for a Smart Scan. For example, we can have, in segment 1, the following eight columns ‘indexed’:

Table name		Column name
======================  ======================
ORDERS			ITEM_NO
			SHIP_ID
			SHIP_DT
			SHIP_CD
			ORDER_NO
			ORDER_DT
			INVOICE_NO
			INVOICE_AMT

and in segment 2 we may have the following columns indexed:

Table name		Column name
======================  ======================
ORDERS			QTY
			UNIT_PRICE
			TTL_PRICE
			SHIP_TO
			ORDER_STATUS
			ORDER_DT
			INVOICE_NO
			INVOICE_AMT

Notice that only three columns occur in both segments: ORDER.DT, INVOICE_NO and INVOICE_AMT. This can go on for the other 31 million storage segments, a fact that can make discussing storage indexes confusing. Remember, though, that it’s not really what is in the storage index that makes the difference, it’s what ISN’T in the storage index that provides the performance. Each storage index includes, besides the column each entry references, the minimum and maximum value for that column within that 1 MB segment and whether or not that column contains NULLs. That information is what gives the storage index its power as, during a Smart Scan as tasks are offloaded to the storage cells, Exadata scans these storage indexes for columns and values of interest. If the storage index contains the desired column and the minimum and maximum values don’t fall within the desired range that 1 MB segment is skipped. If you’re looking for a rather narrow range of values such segment skipping can reduce the volume of data scanned by gigabytes, or even terabytes, and that is the power of the storage index. It tells Exadata which data blocks NOT to read so some people have described storage indexes as ‘reverse indexes’ which is, I suppose, an apt description of their behaviour.

Do storage indexes always help? No, there are conditions when a storage index can’t provide any benefit, such as when the columns of interest are not indexed or when the query in question doesn’t qualify for a Smart Scan. Smart scans are necessary to reap the benefits of a storage index because storage indexes exist at the storage cell level and are accessed (if they’re available) as part of the offloading process. It’s important to remember the conditions that trigger Smart Scans:

Full table scans and/or full index scans coupled with direct path reads, at least one predicate and using the following simple comparison operators
=,<,>,>=,=<,BETWEEN,IN,IS NULL,IS NOT NULL

- or -

Parallel query is in use, again with at least one predicate and using the above mentioned simple comparison operators

Notice the first set of conditions — just because a full table scan or full index scan is in use there is no guarantee that direct path reads will be used so not every full table scan or full index scan qualifies for a Smart Scan. Parallel query, by design, does implement direct path reads. [Oracle has supplied a hidden parameter, _serial_direct_read, in 11.2 which, when set to TRUE (the default is auto), will cause Oracle to favor direct path reads in serial access mode. Notice I said 'favor'; it doesn't guarantee serial direct path reads will be executed, it simply increases the likelihood that they will occur. Setting this parameter can also introduce performance problems as Oracle attempts to use serial direct path reads, thus it's best to test this on a non-production system.]

Storage indexes cannot be modified or tuned like traditional b-tree indexes, and they are not physical objects in the database. They are in-memory ‘tables’, if you will, built ‘on-the-fly’ which will need to be rebuilt if the cellsrv program (the operating system for the storage cells) is restarted so on a reboot the storage indexes created after the restart are not likely to be the same as those that existed before cellsrv was rebooted. Because they can’t be tuned or modified there are no hints to ensure their use; the optimizer won’t know and there are no wait events that monitor storage index usage so AWR and ASH won’t be able to report if they are used or not. That is not to say there isn’t SOME way of knowing if a storage index has been used — a single statistic, cell physical IO bytes saved by storage index, reports, in a cumulative manner by session, how many bytes were saved using storage indexes. Since it’s a cumulative statistic it will be necessary to query that statistic, using the v$sesstat view, before running a candidate query, execute the statement then query the statistic again afterwards. Subtracting the latter value from the former will provide the bytes saved by storage indexes for that query.

Do storage indexes work with Hybrid Columnar Compressed (HCC) tables? Yes, in fact storage indexes can work with compressed tables using any valid level of compression (HCC.OLTP). They also work with bind variables as those values are passed to the storage cells during the offload process. Partitioned tables can also make use of storage indexes, so for qualifying queries both partition pruning and storage indexes can combine to dramatically reduce I/O. Joins and multi-column predicates (again the limit is 8 columns per 1 MB segment) will also make use of storage indexes should a Smart Scan be executed. They will even benefit queries using subqueries as the returned value from the subquery will be compared to the minimum/maximum values for the given column.

Are there any other limitations to storage indexes? Yes, a storage index won’t be used for CLOB columns as CLOBS are not indexed, and predicate values using wildcards (‘%’,'_’) cannot be searched. Index-organized tables also won’t qualify for smart scans — with an IOT the row source cannot be offloaded so a Smart Scan won’t occur. Index range scans won’t trigger Smart Scans, nor will queries using reverse-key indexes. As with other, traditional indexes, functions applied to columns disable a storage index, such as using trunc() on a date column. Implicit conversions used to disable storage indexes on occasion, especially when the optimizer didn’t recognize the string as a convertible value (such as date strings). Recent releases of the Exadata storage server software have corrected most of these issues.

Storage indexes are part of the ‘secret sauce’ of Exadata, and can provide dramatic results for qualifying queries. Knowing when they can be used and when they can’t should help when tuning performance issues you may experience with an Exadata configuration.

 Sometimes it’s good to lose your keys.

“Ex”-Men

In 2008 Oracle introduced Exadata, and data warehousing, business intelligence and OLTP applications have benefited from this integrated technology stack. Exadata is not like anything most DBAs have ever seen or managed; it’s a tuned combination of database server, storage server and private interconnects that truly can outperform commodity hardware configurations. Let’s take a peek inside Exadata and get a glimpse of what Oracle hath wrought.

An Exadata machine is more than just the parts that comprise it. In its smallest configuration the rack houses two database servers running Oracle Enterprise Linux, a storage server managing storage ‘cells’ (running its operating systemn known as ‘cellsrv’) and a private interconnect known as InfiniBAND to allow the database servers to talk, albeit indirectly, to the storage server. Typically in this configuration the raw storage adds up to 104 terabytes, and it’s configured with normal redundancy providing, all told, 52 terabytes of storage. Accounting for the operating system and other management software the usable storage runs around 39 TB divided among diskgroups earmarked for database storage, recovery and an interesting feature known as DBFS (more on that later). How does all of this hardware, software, memory and storage work together? Quite nicely as Oracle has done an excellent job of creating a database appliance that can dramatically improve performance over conventional setups.

Exadata is built to efficiently use the resources to return results to the end user. Toward that end it has been designed to offload query tasks to the storage server, when possible, to reduce the amount of data passed to the calling session. Certain criteria must be met to offload to the storage server:

* Full table or index scans must be used
* Direct path reads must be used to access the data

If the first requirement is not met another option can trigger the offloading mechanism:

* Parallel query is in use

Parallel query, by default, uses direct path reads so even if full scans aren’t used offloading can occur. What is offloading? Exadata is smart enough to know that some operations can be transferred to the storage server and serviced by the cells directly resulting in far less data traversing the InfiniBAND ‘pipe’. Offloading itself doesn’t do this, it’s what Exadata does with the offloaded tasks that makes the difference. Offloading triggers a new execution path element called Smart Scans, which use another new item, Storage Indexes. Storage indexes are created in memory (so they’re resident as long as cellsrv is not restarted) and can contain up to 8 columns per storage segment. For Storage Indexes the storage cells are divided into segments of 1 MB and each segment has its own Storage Index. This configuration allows Exadata to create Storage Indexes such that on one segment 8 columns of table A are indexed, on the next segment 8 columns of table B are indexed, on the next segment 8 different columns from table A are indexed, and so on. Each storage index records the lowest and highest values from each column in the index, and also include NULL values. Indexing NULLs gives Storage Indexes their true power, as these are not ‘where do I find this value’ indexes as normal B-Tree indexes are. Storage indexes are used to tell Exadata where to NOT look for data; Exadata will skip over storage segments that have no possibility of containing the desired values and this dramatically speeds searches and data retrieval. If a Smart Scan is effected it can return data in mere seconds rather than the minutes it could take using a conventional database configuration (database server, switch, network storage array); remember, though, that not all queries submitted to Exadata will qualify for Smart Scans. That being the case the integrated design of the Exadata machine enables it to outperform conventional setups in most cases, with or without using a Smart Scan.

How do you know you’ve used a Smart Scan? The ‘cell smart table scan’ wait event or the ‘cell smart index scan’ wait event will show activity indicating a Smart Scan occurred. Nothing in the execution plan would be displayed to prove a Smart Scan was used; querying v$waitstat would be the only way to confirm that a Smart Scan was implemented.

Another interesting aspect of Exadata is that the database servers have no direct access to the storage cells. ASM is configured to be the bridge between the database and storage servers. The storage server has its own operating system and Oracle database which is how Smart Scans are executed. All of the components are integrated, which can be a challenge for organizations which separate UNIX/Linux system administration duties, Oracle DBA responsibilities, network configuration and storage duties as patching and maintenance for the Exadata machine is performed using bundles that ensure no software/firmware mismatches occur. Such mismatches could result in Exadata features not working properly or, at the worst case, failing to work at all. Oracle suggests a new role, the DMA (Database Machine Administrator) that has both Oracle admin rights and O/S admin rights (root) so that patching can be performed and verified by personnel trained in Exadata machine management. It is possible to divide the responsibilities provided there is good communication between the groups during maintenance outages.

Getting back to parallel query there is a new way, in 11.2, to manage parallel executions across sessions which is extremely effective in the Exadata environment — parallel statement queuing. This works in concert with another new feature, Auto Degree of Parallelism, which can dynamically reconfigure the parallelism during query execution. Auto DOP is controlled by the parallel_degree_policy parameter and when set to auto will implement auto DOP. No objects need to have DEGREE set as Oracle will determine, at run time, the optimal DOP for the query provided it will take longer than parallel_min_time_threshold, which defaults to 10 seconds. (As usual this parameter can also be set to a different value to enable more, or fewer, queries to run in parallel.) Oracle also provides some measure of control on how it determines the ideal DOP with the parallel_degree_limit parameter which defaults to CPU but can be set to CPU, IO, AUTO or an integer value. None of this will work on 11.2 if I/O calibration has not been completed. This is accomplished with the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure; it takes two IN parameters, the number of disks and the maximum disk latency, and three OUT parameters, placeholders for latency, maximum i/o per second and the maximum MB per second. The procedure can take as long as 15 minutes to run as it generates a random workload spread across all nodes in the RAC cluster. Once completed Auto DOP can be enabled and will function as expected. If the calibration has not been done then Oracle will bypass Auto DOP and report in the execution plan that the IO calibrate statistics are missing. Getting back to parallel statement queuing once parallel_degree_policy is set to auto and parallel_servers_target is set to a ‘reasonable’ value (‘reasonable’ meaning the desired maximum number of parallel servers you want to have running at one time) you’re ready to go. Queries will run using Auto DOP until the system hits the limit you set with parallel_servers_target at which time the remaining queries will be queued until resources (parallel query slaves) are available. By default it’s a First In / First Out queue — this behavior can be changed but I won’t cover that in this article. As slaves are made available by prior queries completing the next query in line is run and this continues until the system has freed enough resources to run the remaining queries in the queue or the queue comes to end. Monitoring the queue progress is fairly simple as the information is found in the V$SQL_MONITOR view; if the STATUS is QUEUED then the query is waiting for resources to be made available. V$PX_PROCESS_SYSSTAT can report how many slaves are in use; using both views will enable you to determine which queries are queued and when the next query will come out of the queue to be executed.

DBFS is a unique aspect of Exadata that allows Oracle to utilize a dedicated tablespace as a file system. It’s configured through the clusterware and mounted to a given directory at the O/S level to provide additional storage space for items such as expdp dump files, documents, patch bundle zip files, etc. It operates, at the O/S level, as a regular file system. One caveat of this configuration is the tablespace must be monitored for free space; if the free space drops below, say, 300 GB in a 2 TB file system the df -h call can hang and access to the mounted directories will be compromised. A simple extension of that datafile restores dbfs to it’s speedy self; since DBFS uses bigfile tablespaces (one per mounted file system) you can’t add another datafile to the tablespace to increase the storage (it’s designed that way to prevent ‘striping’ the O/S data across multiple datafiles which could be a performance hit on reads and writes). On reboots of the server some manual intervention is necessary to start the DBFS resource and mount the file system but this not difficult as the shell script to mount the file system is created in the DBFS setup and configuration procedures. More information on configuring DBFS is found in this Metalink document.

There is so much more to Exadata, more than can be put into a single article which is why there are several books on the subject. (My personal choice is “Expert Oracle Exadata”, from Apress, written by Kerry Osborne, Randy Johnson and Tanel Poder.) The intent of this article is to provide a starting point for the journey into Exadata by discussing some of the features and, well, wonders, of the Exadata machine and I hope my efforts have been successful. I am curently on a project where a client is migrating Oracle ERP databases to Exadata and I’m learning new areas of Exadata every day, and enjoying it immensely. Where once Exadata was a novelty it’s now a proven technology, implemented by many organizations world-wide. Some day soon it may come to you (if it hasn’t already); what I’ve covered here should prepare you for your first experience with Exadata.

X-Men all started somewhere.

Nullified Remains

Just recently a service request was opened with Oracle Support regarding the “when others then null” exception handler when it was found in an Oracle-supplied form for the Oracle Inventory application from the E-Business suite. It appears the person who opened the SR believes (and rightly so) this is not the most informative of exception handlers nor is it proper coding practice; the request was opened in hopes of disallowing such constructs. I originally posted:

“Sadly Oracle Support may not do anything about this for two reasons:

1) It’s valid code.
2) It’s not causing another error to surface.
“

Amending my post to reflect the actual nature of the SR it’s now clear why this was opened — Oracle should never issue forms or production code using this exception ‘non-handler’ so it’s understandable why Oracle Support filed a bug report (bug number 14237626) for it. Why is coding “when others then null” not the best idea? Let’s look at some examples and see what could be hidden behind that glorious construct. Creating a table used in a previous post:

SQL> --
SQL> -- Create a test table
SQL> --
SQL> create table pktest(
2       pk number,
3       val1 number,
4       val2 number,
5       val3 number
6 );

Table created.

SQL>
SQL> --
SQL> -- Add the primary key
SQL> --
SQL> alter table pktest add constraint pktestpk primary key (pk);

Table altered.

SQL>
SQL> --
SQL> -- Insert data
SQL> --
SQL> begin
2       for i in 1..25 loop
3             insert into pktest
4             values(i,i+1,i+2,i+3);
5       end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Commit the data
SQL> --
SQL> commit;

Commit complete.

SQL>

Using this table/key combination let’s try some transactions that will generate errors; we’ll hide them by using “when others then null”:

SQL> --
SQL> -- Try and insert an existing key
SQL> -- value into the test table
SQL> --
SQL> -- Intentionally obscure the original
SQL> -- error
SQL> --
SQL> begin
2       insert into pktest values (1,2, 3, 4);
3 exception
4       when others then
5             null;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL>

An insert was intentionally attempted that violates the primary key constraint yet no error was thrown — how lovely. The worst part of this is the code is declared to have run successfully; it appears that the insert was executed without error (which we know is NOT true) so the end user who ran this glorious piece of code has no idea his or her insert failed. These, of course, are not the only errors that can be hidden from view as other, more insidious errors can be ignored:

SQL>
SQL> --
SQL> -- Artificially generate some rather severe
SQL> -- errors
SQL> --
SQL> -- Ignore them in the exception handler
SQL> --
SQL> declare
  2          bad             exception;
  3          reallybad       exception;
  4          trulyreallybad  exception;
  5
  6          pragma exception_init(bad, -43); -- remote operation failed
  7          pragma exception_init(reallybad, -1019); -- unable to allocate memory on user side
  8          pragma exception_init(trulyreallybad, -1034); -- Oracle not available
  9  begin
 10          begin
 11                  raise bad;
 12          exception
 13                  when others then null;
 14          end;
 15          begin
 16                  raise reallybad;
 17          exception
 18                  when others then null;
 19          end;
 20          begin
 21                  raise trulyreallybad;
 22          exception
 23                  when others then null;
 24          end;
 25  end;
 26  /

PL/SQL procedure successfully completed.

SQL>

Notice that even these errors were not reported, including the ‘Oracle not available’ error indicating connection to the database failed resulting in nothing being executed. Oracle reported that the PL/SQL ran successfully (meaning without error) all because of the ‘when others then null’ error mis-handler.

All of this transpires due to a lack of knowledge of what errors could be expected, a bout of laziness on the part of the developer or both — “Gee, I don’t know what other errors to expect but they can’t be serious so I’ll ignore them.” Any error is serious to the end user as it prevents work from completing, and if those errors are ignored and the code block execution looks successful then, when problems arise because of missing data, the issue is harder to troubleshoot. Absent an error message, no matter how trivial that error may seem to the developer, the end user has no indication that the insert/update/delete failed and has nothing to report to the help desk. Codng such mis-handlers also is a disservice to the developer/programmer as they won’t know what caused the problem any more than the user who ran the code. Not knowing the cause makes the solution that much more difficult to find. In that case everybody loses.

This is the third post on error handling and error reporting I’ve written, two on properly reporting error text so the end user can talk intelligently to the help desk on what went wrong, and this one, on making sure there IS an error message to report. All three should be read as a set (in my opinion) so that error messages are reported and the text that the end user sees provides useful information to the service desk personnel. This falls, really, on the developers and programmers as the error handlers they code directly affect what the end users see when errors arise, and the error messages should be clear enough for a non-technical Oracle user to describe to the help desk representatives with whom they speak. A little effort on the development side goes a long way in making troubleshooting easier if, or when, the time comes.

Third time is a charm.

Compound Interest

Oracle 11g offers a new twist on triggers, the compound trigger, a trigger than can act both before and after an update, insert or delete has occurred. This makes possible the abilty in one trigger to perform processing similar to a stored procedure without having to write such a procedure to call from a traditional trigger. Compound triggers can be used to avoid the dreaded mutating table error or to process and accept or reject updates to a table based upon desired criteria. Before we look at such an example a description of how a compound trigger is constructed is in order.

Compound triggers can have up to four sections:

the BEFORE section
the BEFORE EACH ROW section
the AFTER EACH ROW section
the AFTER section

At least two of the sections must be included (including only one of the four would result in traditional trigger) and it does not matter which two of the sections are used. For example such a trigger can include a BEFORE EACH ROW section and an AFTER section; the two sections need not be ‘matched’ (BEFORE, BEFORE EACH ROW, for instance). Also the COMPOUND TRIGGER STATEMENT must be included so Oracle will recognize the above four constructs and treat them accordingly. The general syntax is:

create or replace trigger <trigger name>
for <insert|update|delete> <of column_name> on <tablename>
      COMPOUND TRIGGER
      <declare section>
      BEFORE
      <before section>
      BEFORE EACH ROW
      <before each row section>
      AFTER EACH ROW
      <after each row section>
      AFTER
      <after section>
END;
/

Since compound triggers are relatively new and many may not have had the opportunity to write or use them I have provided a working example. Setting the stage for this trigger HR has set a restriction on the size of a raise to be given; based on the department the raise cannot exceed 12 percent of the department average salary. A compound trigger can be used to process the raise amounts assigned. Such a compound trigger is shown below, along with several ways of executing the raises:

SQL> create or replace trigger check_raise_on_avg
  2  for update of sal on emp
  3  COMPOUND TRIGGER
  4    Twelve_Percent        constant number:=0.12;
  5
  6    -- Declare collection type and variable:
  7
  8    TYPE Department_Salaries_t  IS TABLE OF Emp.Sal%TYPE
  9                                  INDEX BY VARCHAR2(80);
 10    Department_Avg_Salaries     Department_Salaries_t;
 11    TYPE Sal_t             IS TABLE OF Emp.Sal%TYPE;
 12    Avg_Salaries                Sal_t;
 13    TYPE Deptno_t       IS TABLE OF Emp.Deptno%TYPE;
 14    Department_IDs              Deptno_t;
 15
 16    BEFORE STATEMENT IS
 17    BEGIN
 18      SELECT               AVG(e.Sal), NVL(e.Deptno, -1)
 19        BULK COLLECT INTO  Avg_Salaries, Department_IDs
 20        FROM               Emp e
 21        GROUP BY           e.Deptno;
 22      FOR j IN 1..Department_IDs.COUNT() LOOP
 23        Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
 24      END LOOP;
 25    END BEFORE STATEMENT;
 26
 27    AFTER EACH ROW IS
 28    BEGIN
 29      IF :NEW.Sal - :Old.Sal >
 30        Twelve_Percent*Department_Avg_Salaries(:NEW.Deptno)
 31      THEN
 32        Raise_Application_Error(-20000, 'Raise too large');
 33      END IF;
 34    END AFTER EACH ROW;
 35  END Check_Raise_On_Avg;
 36  /

Trigger created.

SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL>
SQL> update emp set sal=sal*1.10 where empno = 7369;

1 row updated.

SQL>
SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        880
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> update emp set sal=sal*1.08 where deptno = 20;

5 rows updated.

SQL>
SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        864
      7499       1600
      7521       1250
      7566       3213
      7654       1250
      7698       2850
      7782       2450
      7788       3240
      7839       5000
      7844       1500
      7876       1188
      7900        950
      7902       3240
      7934       1300

14 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>

Does the trigger reject raises? It certainly does:

SQL> update emp set sal=sal*1.10 where deptno = 30;
update emp set sal=sal*1.10 where deptno = 30
       *
ERROR at line 1:
ORA-20000: Raise too large
ORA-06512: at "BING.CHECK_RAISE_ON_AVG", line 30
ORA-04088: error during execution of trigger 'BING.CHECK_RAISE_ON_AVG'

SQL>
SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL> update emp set sal=sal*1.10 where empno = 7698;
update emp set sal=sal*1.10 where empno = 7698
       *
ERROR at line 1:
ORA-20000: Raise too large
ORA-06512: at "BING.CHECK_RAISE_ON_AVG", line 30
ORA-04088: error during execution of trigger 'BING.CHECK_RAISE_ON_AVG'

SQL>
SQL> select empno, sal from emp;

     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

SQL>

The first rejected update unfortunately disallowed all of the raises based on the failure of a few; the second update shows one employee where a 10 percent raise would be greater than 12 percent of the departmental average salary. Of course it is usually rare to see such a large raise given throughout an entire department so such occurrences would be few as raises are usually processed (outside of cost-of-living adjustments) on an individual basis.

Please note that doing the above in a traditional trigger would have resulted in a mutating table error since the table being updated cannot be queried during the update; all successful raises were processed and no such error was thrown.

Compound triggers are a nice addition to an already robust database system; they may not be commonplace but having them available certainly makes application development simpler as business rules that may be unenforceable using a regular trigger can be successfully implemented. They may be considered as ‘specialty tools’ in the database realm but remember that plumbers, builders and mechanics also have tools they only use once in a while and when the situation arises where a compound trigger can be useful it’s good to have them around.

So when do I get my raise?

Collecting Thoughts

Collections are an interesting lot. They can be one of the most useful tools in the Oracle arsenal, yet they can also be very frustrating to implement. For those unfamiliar with them a collection/varray is defined as “an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection.” The definition seems simple enough but it may be deceptively so. To make matters even more confusing to use a collection you must create a database type for it to reference; a varray requires a type as well but that type can be declared in the PL/SQL block. To clear the air a bit let’s go through some examples of defining and using collections and varrays: The first example uses a collection to store vendor ids and then process them for a report. The code builds two ‘tables’ and compares the contents of them by loading collections and comparing one collection to the other; output is displayed for the conditions of the two tables being equal and the two tables not being equal:

SQL> 
SQL> set serveroutput on size 1000000
SQL> 
SQL> CREATE OR REPLACE type integer_table is table of integer;
  2  /

Type created.

SQL> 
SQL> DECLARE
  2  
  3  
  4   vendor_key_table   integer_table;
  5   vendor_key_table2   integer_table;
  6   CURSOR tst
  7   IS
  8      SELECT   purch_order, SUM (dollars),
  9        CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
 10          FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
 11           FROM DUAL
 12         UNION ALL
 13         SELECT 1 purch_order, 8 dollars, 123452 vendor_key
 14           FROM DUAL
 15         UNION ALL
 16         SELECT 2 purch_order, 7 dollars, 433738 vendor_key
 17           FROM DUAL
 18         UNION ALL
 19         SELECT 2 purch_order, 4 dollars, 383738 vendor_key
 20           FROM DUAL
 21         UNION ALL
 22         SELECT 2 purch_order, 5 dollars, 387118 vendor_key
 23           FROM DUAL)
 24      GROUP BY purch_order;
 25  
 26  
 27    CURSOR tst2
 28   IS
 29    SELECT purch_order, SUM (dollars),
 30        CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
 31          FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
 32           FROM DUAL
 33         UNION ALL
 34         SELECT 2 purch_order, 4 dollars, 383738 vendor_key
 35           FROM DUAL
 36         UNION ALL
 37         SELECT 2 purch_order, 7 dollars, 433738 vendor_key
 38           FROM DUAL
 39         UNION ALL
 40         SELECT 2 purch_order, 5 dollars, 387118 vendor_key
 41           FROM DUAL)
 42      GROUP BY purch_order;
 43   v_purch_order    NUMBER;
 44   v_dollars    NUMBER;
 45  
 46  
 47   mystr     VARCHAR2 (4000);
 48  
 49  
 50   v_purch_order2     NUMBER;
 51   v_dollars2     NUMBER;
 52  
 53  
 54   mystr2      VARCHAR2 (4000);
 55  BEGIN
 56   OPEN tst;
 57   open tst2;
 58  
 59  
 60   LOOP
 61      mystr := NULL;
 62      mystr2 := NULL;
 63  
 64  
 65      FETCH tst
 66       INTO v_purch_order, v_dollars, vendor_key_table;
 67  
 68  
 69      FETCH tst2
 70       INTO v_purch_order2, v_dollars2, vendor_key_table2;
 71  
 72  
 73      IF tst%NOTFOUND
 74      THEN
 75         EXIT;
 76      END IF;
 77  
 78  
 79      if vendor_key_table = vendor_key_table2 then
 80          dbms_output.put_line('equal');
 81      else
 82          dbms_output.put_line(' not equal');
 83      end if;
 84  
 85  
 86      -- loop through the collection and build a string so that
 87      -- we can display it and prove that it works
 88      FOR cur1 IN (SELECT COLUMN_VALUE vendor_key
 89       FROM TABLE (vendor_key_table))
 90      LOOP
 91         mystr := mystr || ',' || cur1.vendor_key;
 92         -- /* based on the value of the sum, you can do something with each detail*/
 93         -- if v_dollars > 12 then
 94         --   UPDATE VENDOR SET paid_status = 'P' where vendor_key = cur1.vendor_key;
 95         -- end if;
 96      END LOOP;
 97  
 98  
 99      DBMS_OUTPUT.put_line (   'Purchase Order-> '
100       || TO_CHAR (v_purch_order)
101       || ' dollar total-> '
102       || TO_CHAR (v_dollars)
103       || ' vendorkey list-> '
104       || SUBSTR (mystr, 2));
105  
106  
107      -- loop throught the collection and build a string so that
108      -- we can display it and prove that it works
109      FOR cur2 IN (SELECT COLUMN_VALUE vendor_key
110       FROM TABLE (vendor_key_table2))
111      LOOP
112         mystr2 := mystr2 || ',' || cur2.vendor_key;
113      END LOOP;
114  
115  
116      DBMS_OUTPUT.put_line (   'Purchase Order-> '
117       || TO_CHAR (v_purch_order2)
118       || ' dollar total-> '
119       || TO_CHAR (v_dollars2)
120       || ' vendorkey list-> '
121       || SUBSTR (mystr2, 2));
122  
123  
124   END LOOP;
125   CLOSE tst;
126   CLOSE tst2;
127  END;
128  /
not equal
Purchase Order-> 1   dollar total-> 11   vendorkey list-> 435235,123452
Purchase Order-> 1   dollar total-> 3   vendorkey list-> 435235
equal
Purchase Order-> 2   dollar total-> 16   vendorkey list-> 433738,387118,383738
Purchase Order-> 2   dollar total-> 16   vendorkey list-> 383738,387118,433738

PL/SQL procedure successfully completed.

SQL> 

Comparing the collections rather than looping through each table makes the work much easier to complete. Notice we only needed one type created; the same type satisfied the conditions for both collection tables.

The next example shows how things can go astray with the bulk collect operation when the limit does not evenly divide the result set. In the first part of the example we use the well-known ‘exit when cursor%notfound;’ directive with less than stellar results (we miss inserting 5 records into the second table); the second part of the example shows how to properly implement an exit from a bulk collect operation; this one uses a varray:

 
SQL> 
SQL> set echo on linesize 150 trimspool on
SQL> 
SQL> create table emp_test as select * From emp where 0=1;

Table created.

SQL> 
SQL> declare
  2        type empcoltyp is table of emp%rowtype;
  3        emp_c empcoltyp;
  4  
  5        cursor get_emp_data is
  6        select * from emp;
  7  
  8  begin
  9        open get_emp_data;
 10        loop
 11        fetch get_emp_data bulk collect into emp_c limit 9;
 12        exit when get_emp_data%notfound;
 13  
 14        for i in 1..emp_c.count loop
 15         insert into emp_test (empno, ename, sal)
 16         values (emp_c(i).empno, emp_c(i).ename, emp_c(i).sal);
 17        end loop;
 18  
 19        end loop;
 20  
 21        commit;
 22  
 23  end;
 24  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH                                            800
      7499 ALLEN                                           1600
      7521 WARD                                            1250
      7566 JONES                                           2975
      7654 MARTIN                                          1250
      7698 BLAKE                                           2850
      7782 CLARK                                           2450
      7788 SCOTT                                           3000
      7839 KING                                            5000

9 rows selected.

SQL> 
SQL> truncate table emp_test;

Table truncated.

SQL> 
SQL> declare
  2        type empcoltyp is table of emp%rowtype;
  3        emp_c empcoltyp;
  4  
  5        cursor get_emp_data is
  6        select * from emp;
  7  
  8  begin
  9        open get_emp_data;
 10        loop
 11        fetch get_emp_data bulk collect into emp_c limit 9;
 12        exit when emp_c.count = 0;
 13  
 14        for i in 1..emp_c.count loop
 15         insert into emp_test (empno, ename, sal)
 16         values (emp_c(i).empno, emp_c(i).ename, emp_c(i).sal);
 17        end loop;
 18  
 19        end loop;
 20  
 21        commit;
 22  
 23  end;
 24  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH                                            800
      7499 ALLEN                                           1600
      7521 WARD                                            1250
      7566 JONES                                           2975
      7654 MARTIN                                          1250
      7698 BLAKE                                           2850
      7782 CLARK                                           2450
      7788 SCOTT                                           3000
      7839 KING                                            5000
      7844 TURNER                                          1500
      7876 ADAMS                                           1100

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES                                            950
      7902 FORD                                            3000
      7934 MILLER                                          1300

14 rows selected.

SQL> 

What happened in the first part? Since the limit was more than the number of remaining records the NOTFOUND indicator was set at the end of the fetch. We had 5 records left to process in the varray but the ‘exit when cursor%notfound;’ statement terminated the loop BEFORE we could get the remainng 5 records inserted into our table, thus they were lost. Using the ‘exit when collection.count = 0;’ construct prevents us from missing records since the count was greater than 0 even when the NOTFOUND indicator was set. This allows us to process the remaining records in the varray before exiting the loop. [Yes, the exit could be coded at the end of the loop rather than the beginning and the 'exit when cursor%NOTFOUND;' would process the remaing records but that, to me, defeats the purpose of the conditional exit. As I see it we want to exit the loop when no more work is to be done, not look for partial sets of data to apply then exit before the next (unsuccessful) fetch.]

Our next example does two things: loads data using bulk collect into a varray then uses the FORALL loop construct to quickly process the collection and insert the data into a staging table (I believe this originally appeared in ‘Morgan’s Library’ on the psoug.org website). The second part is a bit contrived as it uses a collection to process deletes from a table — deletes that could have easily been executed with a single SQL statement — but it does show the power of using collections and varrays:

SQL> 
SQL> set echo on timing on
SQL> 
SQL> create table temp_stg(
  2        usrname varchar2(30),
  3        usrjob varchar2(20),
  4        usrsal number
  5  );

Table created.

SQL> 
SQL> begin
  2        for i in 1..1000000 loop
  3        insert into temp_stg
  4        values ('Blorp'||i, 'Job'||i, 1200*(mod(i,3)));
  5        end loop;
  6  
  7        commit;
  8  
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> create table temp_ld(
  2        usrname varchar2(30),
  3        usrjob varchar2(20),
  4        usrsal number
  5  );

Table created.

SQL> 
SQL> CREATE OR REPLACE PROCEDURE bulk_load IS
  2  
  3  TYPE dfarray IS TABLE OF temp_stg%ROWTYPE;
  4  ld_dat dfarray;
  5  
  6  CURSOR stg IS
  7  SELECT *
  8  FROM temp_stg;
  9  
 10  BEGIN
 11   OPEN stg;
 12   LOOP
 13     FETCH stg BULK COLLECT INTO ld_dat LIMIT 1000;
 14  
 15     FORALL i IN 1..ld_dat.COUNT
 16        INSERT INTO temp_ld VALUES ld_dat(i);
 17  
 18     EXIT WHEN ld_dat.count=0;
 19    END LOOP;
 20    COMMIT;
 21    CLOSE r;
 22  END bulk_load;
 23  /

Procedure created.

SQL> 
SQL> exec bulk_load;

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from temp_stg where usrname like '%99999%';

USRNAME                        USRJOB                   USRSAL
------------------------------ -------------------- ----------
Blorp99999                     Job99999                      0
Blorp199999                    Job199999                  1200
Blorp299999                    Job299999                  2400
Blorp399999                    Job399999                     0
Blorp499999                    Job499999                  1200
Blorp599999                    Job599999                  2400
Blorp699999                    Job699999                     0
Blorp799999                    Job799999                  1200
Blorp899999                    Job899999                  2400
Blorp999990                    Job999990                     0
Blorp999991                    Job999991                  1200

USRNAME                        USRJOB                   USRSAL
------------------------------ -------------------- ----------
Blorp999992                    Job999992                  2400
Blorp999993                    Job999993                     0
Blorp999994                    Job999994                  1200
Blorp999995                    Job999995                  2400
Blorp999996                    Job999996                     0
Blorp999997                    Job999997                  1200
Blorp999998                    Job999998                  2400
Blorp999999                    Job999999                     0

19 rows selected.

SQL> 

Trust me that the data loads took very little time to process. Here is the contrived part, but it is still a good example of the power of using collections:

SQL> CREATE OR REPLACE PROCEDURE data_del IS
  2  
  3  TYPE dfarray IS TABLE OF temp_stg.usrname%TYPE;
  4  ld_dat dfarray;
  5  
  6  CURSOR stg IS
  7  SELECT usrname
  8  FROM temp_stg
  9  where usrname like '%9999%';
 10  
 11  BEGIN
 12   OPEN stg;
 13   LOOP
 14     FETCH stg BULK COLLECT INTO ld_dat LIMIT 1000;
 15  
 16     FORALL i IN 1..ld_dat.COUNT
 17        delete from temp_ld where usrname = ld_dat(i);
 18  
 19     EXIT WHEN ld_dat.count=0;
 20    END LOOP;
 21    COMMIT;
 22    CLOSE stg;
 23  END data_del;
 24  /

Procedure created.

SQL> 
SQL> show errors
No errors.
SQL> 
SQL> exec data_del;

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from temp_ld where usrname like '%99999%';

no rows selected

SQL> 

Collections can be a real timesaver for bulk processng of data; they may not be applicable in every sitution but when the conditions are right they can make your job so much easier. Master collections and varrays and you may be able to amaze your friends.

Collections, anyone?

At The Touch Of A Button

It intrigues me that some DBAs can be lost without tools like Oracle Enterprise Manager or TOAD, so much so that they can’t complete a task without a GUI. What makes this even more disconcerting is these DBAs can execute tasks that they may be unable to complete absent such tools. If what the tool does ‘behind the scenes’ is a mystery to the users it stands to reason that a user, using a GUI, could do some damage to a database by executing misunderstood tasks simply by pressing ‘buttons’.

When I started as a DBA [the earth's crust was still cooling and dirt didn't yet have its official name] there was the command line. That was it. Nothing else. No GUI, no OEM, no slick and nifty applications coded to make DBA life easier. A database was managed at the SQL> or SVRMGR> prompt (depending upon what needed to be done). Pretty graphs didn’t exist, alerts didn’t get generated unless the DBA wrote a script and scheduled it through cron (or the Windows scheduler) to check the database for space or memory or process count and send an email to the DBA should any of the acceptable criteria be violated. Yes, it was a hard life for a DBA, with all of that scripting and manual labor [typing is such back-breaking work]. A DBA had to know what commands did what and when to use them. A DBA also had to know where to excavate performance data, storage numbers, memory usage and user activity from the data dictionary by actually using the manuals and looking things up. Now tools like OEM, TOAD and others make it easy for someone to be a DBA by making most tasks as easy as ‘point and click’, which is a real disservice to the modern DBA, in my opinion.

What if other, daily tasks were modified so that even the uneducated could perform them? Would anyone want someone behind the wheel of a car who didn’t have any instruction at all in how to drive or operate the vehicle? Would anyone want a carpenter, plumber, electrician or mechanic performing any work with the newest power tools but having absolutely no idea how to operate them safely and properly? Clearly no one would want a surgeon operating with the latest gadgets but absent a medical degree. Yet, this is what allows people to be DBAs in the modern world — no knowledge of the intricacies of the database they manage, no knowledge of the commands necessary to perform basic functions such as adding a datafile to a tablespace, resizing a datafile in a database, adding a user account, creating roles, granting roles — the list can go on. Sit them in front of a GUI tool and explain the basics to them (“navigate here, press this button”) and they’re immediately DBAs. The prospect is disturbing.

It’s my privilege to know a number of really good DBAs in this world, DBAs who do know how to create a database, turn on and off archivelogging, restore and recover a database from a reliable backup, how to take reliable backups and do it all from the command line interface. These same DBAs use OEM, RMAN and TOAD to make their lives a bit easier, and I do the same thing so I see no issue with that. I also know (and know of) some DBAs who can’t do the job without OEM or TOAD — I’ve been told this in several interviews I’ve held when looking for additional DBA resources. Some of the most basic questions weren’t answered satisfactorily as I was given step-by-step directions on how to navigate to the page where that particular button resides instead of being told the commands necessary to complete the task in question. In an emergency situation OEM or TOAD may not be available and DBAs who don’t know the command line may be looking for another employer.

It’s my belief that enterprises who train DBAs need to concentrate not only on the tools but on the basic knowledge as well, educating their students not only in OEM but in how to go about managing a database absent those nifty tools. Understanding how the tool works only makes for better DBAs and frees them from being tethered to a graphical user interface, an interface they are dependent upon to perform the most basic and mundane of DBA tasks.

Education and training are demanded by society for teachers, doctors, lawyers, dentists, even insurance agents (not to disparage insurance agents). Why the industry doesn’t demand the same of DBAs is a mystery. [Certification, in many cases, is a requirement on the resume but 'brain dumps' and courses exist to 'train' those uneducated in the chosen DBMS so such 'credentials' can be acquired absent any real work experience. Many of these courses are centered around GUI management tools; sadly the underlying framework is glossed over in deference to learning to navigate the chosen graphical interface. Such an environment produces, in the Oracle arena, Oracle Certified Professionals completely absent any professional experience.] Yes, experience counts but if that experience is nothing more than a set of rote instructions on how to navigate a GUI tool how much worth does it bring to the employer? Not much, really.

Database administration is a respected profession, and most DBAs in the workforce are qualified and capable. Occasionally a few get through who meet the description I’ve given here. It’s those few I write about, and ask that they further their education and learn how their chosen DBMS works and how, in an emergency, to do their jobs absent any flashy graphic tools.

I don’t believe that’s too much to ask.

My, How You’ve Grown

Indexes are interesting objects — they can dramatically improve performance but their management can be, well, tricky. Depending upon how data is inserted into and deleted from a table the size an index can attain could be surprising to the DBA. How can the size be surprising? Let’s take an example through a number of iterations and see what Oracle does with the index, and explain why the results shouldn’t be unexpected.

Setting the stage we’ll create a table and a primary key index, load 200000 rows, delete the existing rows and insert new keys then see how the index responds. We’ll do this several times, under differing conditions, to see if the behaviour changes and, if so, why. By the end of the example we should know how index leaf blocks are used and re-used and why some dead space can remain in an index even though general wisdom says otherwise. We begin:

  
SQL> 
SQL> --
SQL> -- Create our test table and primary key index
SQL> --
SQL> 
SQL> create table biggy (id number constraint biggy_pk primary key, name varchar2(100));

Table created.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        374          1

SQL> 
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in
SQL> --
SQL> 
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
  2  as
  3       n number;
  4       m number;
  5  begin
  6       select min(id),max(id) into n,m from biggy;
  7       for i in 1..200000 loop
  8    delete from biggy where id=n+i-1;
  9    insert into biggy values(m+i,'Big index test');
 10    if mod(i,p_commit_after)=0 then
 11         commit;
 12    end if;
 13       end loop;
 14       commit;
 15  end;
 16  /

Procedure created.

SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     245378       45378        495          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             45378       200000   18.49310

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     293295       93295        587          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             93295       200000   31.80927

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69706

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                3     484820      284820       1011          5

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                            284820       200000   58.74758

We find that the way we’ve deleted and added the rows has affected how the leaf blocks are managed using a standard primary key index as the index has more than doubled in size; a good portion of that storage is empty leaf blocks that could not be reused by the new keys because they are out of the key range for the associated branch block. Let’s reverse the index and try this exercise again:

  
SQL> 
SQL> --
SQL> -- Drop the existing table and index then recreate
SQL> -- 
SQL> 
SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> --
SQL> -- Recreate our test table and primary key index
SQL> -- This time the PK is a reverse-key index
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) reverse;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202645        2645        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2645       200000    1.30524

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> select count(*), min(id), max(id) from biggy;

  COUNT(*)    MIN(ID)    MAX(ID)
---------- ---------- ----------
    200000     400001     600000

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     205095        5095        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              5095       200000    2.48421

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69709

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210249       10249        523          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10249       200000    4.87470

SQL> 

The reverse-key index made a substantial impact on the number of empty leaf blocks left unused in the index. Let’t see if manual segment space management causes a change in behaviour over the prior tests using ASSM:

  
SQL> 
SQL> --
SQL> -- Create our test table and primary key index
SQL> -- Use a tablespace with manual segment management
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) tablespace bing_idx;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        374          1

SQL> 
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in when ASSM is used
SQL> --
SQL> -- MSSM may eliminate the behaviour
SQL> --
SQL> 
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
  2  as
  3       n number;
  4       m number;
  5  begin
  6       select min(id),max(id) into n,m from biggy;
  7       for i in 1..200000 loop
  8    delete from biggy where id=n+i-1;
  9    insert into biggy values(m+i,'Big index test');
 10    if mod(i,p_commit_after)=0 then
 11         commit;
 12    end if;
 13       end loop;
 14       commit;
 15  end;
 16  /

Procedure created.

SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     201000        1000        379          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              1000       200000     .49751

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202000        2000        382          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2000       200000     .99010

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69711

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210000       10000        422          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10000       200000    4.76190

SQL> 

We note that manual segment space management didn’t really make much of a difference in the storage with the standard primary key index; the index is still much larger than it ‘should’ be. Will it make a difference with the reverse-key version? Let’s test it and see:

  

SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> --
SQL> -- Recreate our test table and primary key index
SQL> -- This time the PK is a reverse-key index
SQL> -- Again we use a tablespace with manual extent management
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) reverse tablespace bing_idx;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202645        2645        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2645       200000    1.30524

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     205095        5095        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              5095       200000    2.48421

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69714

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210249       10249        523          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10249       200000    4.87470

SQL> 

No real difference seen with the reverse-key index so the segment space management (at least in 11gR2) isn’t a factor. One thought on mitigating this behaviour is to set session_cached_cursors to 0; let’s see what that does:

  
SQL> 
SQL> --
SQL> -- Set session_cached_cursors to 0
SQL> --
SQL> -- May improve the situation further
SQL> --
SQL> 
SQL> alter session set session_cached_cursors = 0;

Session altered.

SQL> 
SQL> --
SQL> -- Go through the whole process one more time
SQL> -- with and without a reverse-key index
SQL> -- and using ASSM then MSSM to see which is better
SQL> --
SQL> 
SQL> --
SQL> -- Create our test table and primary key index
SQL> --
SQL> 
SQL> create table biggy (id number constraint biggy_pk primary key, name varchar2(100));

Table created.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        374          1

SQL> 
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in
SQL> --
SQL> 
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
  2  as
  3       n number;
  4       m number;
  5  begin
  6       select min(id),max(id) into n,m from biggy;
  7       for i in 1..200000 loop
  8    delete from biggy where id=n+i-1;
  9    insert into biggy values(m+i,'Big index test');
 10    if mod(i,p_commit_after)=0 then
 11         commit;
 12    end if;
 13       end loop;
 14       commit;
 15  end;
 16  /

Procedure created.

SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     225031       25031        440          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             25031       200000   11.12336

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     228120       28120        446          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             28120       200000   12.32685

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69716

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     237132       37132        495          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             37132       200000   15.65879

SQL> 

Nothing changed for the standard index; let’s again test the reverse-key index and see what that produces:

  

SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> --
SQL> -- Recreate our test table and primary key index
SQL> -- This time the PK is a reverse-key index
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) reverse;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202645        2645        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2645       200000    1.30524

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     205095        5095        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              5095       200000    2.48421

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69718

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210249       10249        523          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10249       200000    4.87470

SQL> 

Again no change is realized; we try again with manual segment space management and collect the results:

  
SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> 
SQL> --
SQL> -- Create our test table and primary key index
SQL> -- Use a tablespace with manual segment management
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) tablespace bing_idx;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        374          1

SQL> 
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in when ASSM is used
SQL> --
SQL> -- MSSM may eliminate the behaviour
SQL> --
SQL> 
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
  2  as
  3       n number;
  4       m number;
  5  begin
  6       select min(id),max(id) into n,m from biggy;
  7       for i in 1..200000 loop
  8    delete from biggy where id=n+i-1;
  9    insert into biggy values(m+i,'Big index test');
 10    if mod(i,p_commit_after)=0 then
 11         commit;
 12    end if;
 13       end loop;
 14       commit;
 15  end;
 16  /

Procedure created.

SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     201000        1000        379          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              1000       200000     .49751

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202000        2000        382          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2000       200000     .99010

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69720

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210000       10000        422          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10000       200000    4.76190

SQL> 

Again, no change. One more time with the reverse-key index, using manual segment space management:

  

SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> --
SQL> -- Recreate our test table and primary key index
SQL> -- This time the PK is a reverse-key index
SQL> -- Again we use a tablespace with manual extent management
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) reverse tablespace bing_idx;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202645        2645        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2645       200000    1.30524

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     205061        5061        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              5061       200000    2.46805

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69722

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210249       10249        523          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10249       200000    4.87470SQL> 

Notice the setting didn’t do much of anything to improve the situation.

So, what happened? The initial pass, with the standard index, causes Oracle to wait to reuse empty leaf blocks until the branch block they are attached to is empty; since we deleted the smallest available key then inserted a new largest key the leaf block released could not immediately be reused as the branch block still had leaf blocks attached to it. Somewhere around the middle of the whole delete/insert process the leaf blocks we released at the beginning of the process were finally available for reuse. Reversing the key on the primary key index allowed reuse of the leaf blocks by the new keys since, in reverse order, they could ‘fit in’ to the key order of the index. Manual segment space management didn’t do much to improve this nor did setting session_cached_cursors to 0.

Of course the ideal method is to delete the rows in batches with the intent of freeing the branch block so the empty leaf blocks can be reused but piecemeal deletes and inserts can and will happen in OLTP systems so such a scenario can be repeated in a running production database. An interesting side note on this is that primary key indexes aren’t usually rebuilt as reverse-key indexes unless block contention is high for the index yet that action can also dramatically reduce the number of empty leaf blocks in the index after rows are deleted. It may be worth considering the use of a reverse-key primary key index to keep the index size ‘reasonable’.

It may be a rare occurrence to have an ever-increasing index even though volumes of data have been deleted but knowing what to do to help correct the situation may prove invaluable should the situation arise. In my opinion it’s better to know something you may not need rather than need something you do not know.

My two cents.

That’s Your Problem

Many times a problem can appear to be more complicated than it actually is. This is due, I think, to being ‘locked into’ a thought process not conducive to solving the problem. Knowing how to read the problem and discover the information provided can help tremendously in working toward a solution. Let’s look at a few problems and their solutions to see how to change the way you think about, and look at, a problem.

Jonathan Lewis provides the first problem we consider, although it actually appeared in the comp.databases.oracle.server newsgroup several years ago. It’s gone through several iterations since its original offering and we’ll consider the most recent of those here. The problem:

Two mathematicians met at their college reunion. Wanting to keep current they started discussing their lives as only mathematicians can:

Mathematician #1: So, do you have any children?
Mathematician #2: Yes, three girls.
Mathematician #1: Wonderful! What are their ages?
Mathematician #2: I’ll give you a clue: the product of their ages is 36.
Mathematician #1: Hmmm, good clue but not nearly enough information.
Mathematician #2: Well, the sum of their ages is the number of people in this room.
Mathematician #1: (After looking about the room) That’s still not enough information.
Mathematician #2: One more clue: my oldest daughter has a pet hamster with a wooden leg.
Mathematician #1: I have it now — say, are the twins identical?

Given that all of the information needed to solve the problem is in the problem, what are the ages of the three girls?

The problem seems unsolvable at first glance but there is more information available than is originally seen. Let’s state what we know from the problem:

1 — There are three girls
2 — Their ages, multiplied together, have a product of 36
3 — The sum of their ages is (to us, anyway) an undisclosed number
4 — The oldest daughter has a hamster with a wooden leg

A strange collection of facts, some might say. But, looking deeper into the problem we can find some logic and answers not obvious from casual inspection. Let’s start with the product of the ages:

  
SQL> --
SQL> -- Generate an age list for the girls
SQL> --
SQL> -- Maximum age is 36
SQL> --
SQL> with age_list as (
  2        select rownum age
  3        from all_objects
  4        where rownum  
SQL> --
SQL> -- Return only the age groupings whose product
SQL> -- is 36
SQL> --
SQL> -- Return, also, the sum of the ages
SQL> --
SQL> -- This restricts the set of values needed to
SQL> -- solve the problem
SQL> --
SQL> with age_list as (
  2        select rownum age
  3        from all_objects
  4        where rownum = age1.age
 15        and age3.age >= age2.age
 16        and age1.age*age2.age*age3.age = 36
 17  )
 18  select *
 19  from product_check
 20  order by 1,2,3;

  YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT                          
---------- ---------- ---------- ---------- ----------                          
         1          1         36         38         36                          
         1          2         18         21         36                          
         1          3         12         16         36                          
         1          4          9         14         36                          
         1          6          6         13         36                          
         2          2          9         13         36                          
         2          3          6         11         36                          
         3          3          4         10         36                          

8 rows selected.

SQL> 

Notice we return not only the product of the ages but also the sums of the various combinations, as we’ll need this information later on in the problem. Now another ‘fact’ emerges:

5 — Knowing the sum of the ages doesn’t help matters much

This reveals that there is more than one combination of ages which produce the same sum:

  
QL> --
SQL> -- Find, amongst the acceptable values,
SQL> -- those sets where the summed value is
SQL> -- the same
SQL> --
SQL> -- This is necessary as providing the sum
SQL> -- was of little direct help in solving the
SQL> -- problem
SQL> --
SQL> with age_list as (
  2        select rownum age
  3        from all_objects
  4        where rownum = age1.age
 15        and age3.age >= age2.age
 16        and age1.age*age2.age*age3.age = 36
 17  ),
 18  summed_check as (
 19        select youngest, middle, oldest, sum, product
 20        from (
 21         select youngest, middle, oldest, sum, product,
 22         count(*) over (partition by sum) ct
 23         from product_check
 24        )
 25        where ct > 1
 26  )
 27  select *
 28  from summed_check;

  YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT                          
---------- ---------- ---------- ---------- ----------                          
         2          2          9         13         36                          
         1          6          6         13         36                          

SQL> 

Now we know the number of people in the room and why the sum wasn’t enough information to solve the problem. The final ‘nail in the coffin’ (so to speak) is the owner of the hamster with the wooden leg; the problem states:

Mathematician #2: One more clue: my oldest daughter has a pet hamster with a wooden leg.

It’s not the hamster, it is the fact that the oldest daughter (there’s only one) exists. Knowing that last piece of information provides the final answer:

  
SQL> 
SQL> --
SQL> -- Return the one set of values meeting all of
SQL> -- the criteria:
SQL> --
SQL> -- Product of 36
SQL> -- Sum of some unknown number
SQL> -- Oldest child exists
SQL> --
SQL> with age_list as (
  2        select rownum age
  3        from all_objects
  4        where rownum = age1.age
 15        and age3.age >= age2.age
 16        and age1.age*age2.age*age3.age = 36
 17  ),
 18  summed_check as (
 19        select youngest, middle, oldest, sum, product
 20        from (
 21         select youngest, middle, oldest, sum, product,
 22         count(*) over (partition by sum) ct
 23         from product_check
 24        )
 25        where ct > 1
 26  )
 27  select *
 28  from summed_check
 29  where oldest > middle;

  YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT                          
---------- ---------- ---------- ---------- ----------                          
         2          2          9         13         36                          

SQL> 

The ages of the girls are 9, 2 and 2 which also clarifies the question of identical twins.

The problem was solved in a systematic and (to me, at least) logical way by breaking the problem down into workable pieces.

So you don’t encounter such problems at college reunions or parties (what a dull life that must be); you may encounter them at work. This next problem was presented in the Oracle PL/SQL group:

Hi,

I have 3 columns of data

Column 1:subscription
Column 2: invoice number
Column 3: Service

I need to seperate the subscription types into new, renewals and additional
which is fine but the next bit i am having trouble

Each invoice number can have 1 or more service

e.g.
Invoice Number Service
123 Photocopying
123 Printing
123 Scan & Store
234 Photocopying
234 Scan & Store
345 Photocopying
345 Printing

I apply a rate for each service e.g.
photocopying = 1.5
printing = 1.7

but if Scan and store is in an invoice with photocopying we charge an extra
1.5

but if printing is a service with the scan and store a different rate
applies 1.7

so i can’t just count scan and store and apply a rate i have to figure out
if it is with photocopying or with printing and then apply the rate

What I want to be able to do is creat a table with columns that calculates
this
so i get a 4 columns:

Service usage rate total
photocopying 3 1.5 4.5
Printing 2 1.7 3.4
Scan & Store 1 1.5 1.5
Scan & Store w/Print 1 1.7 1.7

The problem comes in when i’m trying to count scan and store wit/without
printing. I can’t figure it out.

I import the report from an excel spreadsheet into acces and want to run a
query that does all this…

thanks in advance,
ainese

With this problem I decided to change the table a bit and add a numeric SERVICE_CD column:

  
SQL> Create table subscription(
  2          subscr_type varchar2(15),
  3          invoice number,
  4          service varchar2(40),
  5          service_cd number
  6  );

Table created.

SQL>
SQL> insert all
  2  into subscription
  3  values('RENEWAL',123,'Photocopying',0)
  4  into subscription
  5  values('RENEWAL',123,'Printing',2)
  6  into subscription
  7  values('RENEWAL',123,'Scan '||chr(38)||' Store',5)
  8  into subscription
  9  values('ADDITIONAL',234,'Photocopying',0)
 10  into subscription
 11  values('ADDITIONAL',234,'Scan '||chr(38)||' Store',5)
 12  into subscription
 13  values('NEW',345,'Photocopying',0)
 14  into subscription
 15  values('NEW',345,'Printing',2)
 16  select * From dual;

7 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>

Using the wm_concat() function and the BITAND operator produced results that will make the final solution easier to code; using BITAND allows Oracle to generate a result based upon the sum of the SERVICE_CD values and by properly choosing those SERVICE_CD entries make it easier to isolate the various combinations:

  
SQL> select subscr_type, invoice, services,
  2         bitand(service_cds, 0) col1,
  3         bitand(service_cds, 2) col2,
  4         bitand(service_cds, 7) col3
  5  from
  6  (select subscr_type, invoice, wm_concat(service) services, sum(service_cd) service_cds
  7  from subscription
  8  group by subscr_type,invoice);

SUBSCR_TYPE        INVOICE SERVICES                                       COL1       COL2       COL3
--------------- ---------- ---------------------------------------- ---------- ---------- ----------
NEW                    345 Photocopying,Printing                             0          2          2
RENEWAL                123 Photocopying,Printing,Scan & Store                0          2          7
ADDITIONAL             234 Photocopying,Scan & Store                         0          0          5

SQL>

Knowing which BITAND results indicate which chargeable combinations allows using DECODE to produce a version of the desired results:

  

SQL> column services format a40
SQL> break on report skip 1
SQL> compute sum  of photocopy printing scan_and_store scan_and_store_w_prt on report
SQL>
SQL> select subscr_type, invoice, services,
  2         decode(bitand(service_cds, 0), 0, 1.5, 0) photocopy,
  3         decode(bitand(service_cds, 2), 2, 1.7, 0) printing,
  4         decode(bitand(service_cds, 7), 5, 1.5, 0) scan_and_store,
  5         decode(bitand(service_cds, 7), 7, 1.7, 0) scan_and_store_w_prt
  6  from
  7  (select subscr_type, invoice, wm_concat(service) services, sum(service_cd) service_cds
  8  from subscription
  9  group by subscr_type,invoice);

SUBSCR_TYPE INVOICE SERVICES                           PHOTOCOPY PRINTING SCAN_STORE SCAN_STORE_PRT
----------- ------- ---------------------------------- --------- -------- ---------- --------------
NEW             345 Photocopying,Printing                    1.5      1.7          0              0
RENEWAL         123 Photocopying,Printing,Scan & Store       1.5      1.7          0            1.7
ADDITIONAL      234 Photocopying,Scan & Store                1.5        0        1.5              0
                                                       --------- -------- ---------- --------------
sum                                                          4.5      3.4        1.5            1.7

SQL>

All services in this example are charged the appropriate rates, including the adjustments made for certain combinations of service.

One last problem is one found often on the web:

Display the second highest salary in the employee table
Display the employee id, first name, last name and salary for employees earning the second highest salary

Depending on which question is asked several solutions present themselves. The first is the ‘obvious’ solution:

  
SQL> select salary
  2  from
  3  (select salary from employees order by 1 desc)
  4  where rownum = 2;

no rows selected

SQL>

which doesn’t work because ROWNUM is never set to 1 so it can’t get to 2. A modest rewrite produces:

  
SQL> select salary
  2  from
  3  (select rownum rn, salary from
  4  (select salary from employees order by 1 desc))
  5  where rn = 2;

    SALARY
----------
     17000

1 row selected.

SQL>

which still might not be the correct answer as more than one person may have the same salary, including the highest. Another rewrite, using DENSE_RANK() provides the solution:

  
SQL> select salary from
  2  (select salary, dense_rank() over (order by salary desc) rk
  3          from employees)
  4  where rk=2;

    SALARY
----------
     17000
     17000

2 rows selected.

SQL>

To produce more information the above query needs a small modification:

  
SQL> select employee_id, first_name, last_name, salary
  2  from
  3  (select employee_id, first_name, last_name, salary, rank() over (order by salary desc) rk
  4   from employees)
  5  where rk =2;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        101 Neena                Kochhar                        17000
        102 Lex                  De Haan                        17000

2 rows selected.

SQL>

RANK() and DENSE_RANK() do just what they’re named — rank the requested values — but only DENSE_RANK() will not skip ranking numbers when duplicate values exist:

  
SQL> select salary, rank() over (order by salary desc) rk
  2          from employees;

    SALARY         RK
---------- ----------
     24000          1
     17000          2
     17000          2
     14000          4
     13500          5
     13000          6
     12000          7
     12000          7
     12000          7
     11500         10
...

Notice that the third highest salary is ranked 4 with RANK(); not so with DENSE_RANK():

  
SQL> select salary, dense_rank() over (order by salary desc) rk
  2          from employees;    SALARY         RK
---------- ----------
     24000          1
     17000          2
     17000          2
     14000          3
     13500          4
     13000          5
     12000          6
     12000          6
     12000          6
     11500          7
     11000          8
     11000          8
     11000          8
     10500          9
     10500          9
     10000         10
     10000         10
     10000         10
     10000         10
...

which is why DENSE_RANK() was used to solve the problem.

Problem solving, when given a little thought, isn’t a terrible chore if you know how to read the problem and extract known information. From that you can eventually arrive at a solution (and, yes, multiple solutions can exist depending upon how you think about the problem). The above are examples to get you started thinking in the ‘right’ direction. As always, some practice at solving problems is recommended so take these problems, work them through, change data, work them through again (and you may find holes in my solutions that I didn’t consider). The more you practice, the more you learn.

A train leaves station A at 3:30 PM and travels west at 50 miles per hour …