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 <= 200000;
200000 rows created.
SQL>
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 <= 200000;
200000 rows created.
SQL>
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 <= 200000;
200000 rows created.
SQL>
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 <= 200000;
200000 rows created.
SQL>
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 <= 200000;
200000 rows created.
SQL>
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 <= 200000;
200000 rows created.
SQL>
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 <= 200000;
200000 rows created.
SQL>
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 <= 200000;
200000 rows created.
SQL>
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.87470
SQL>
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.
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 <= 200000;
200000 rows created.
SQL>
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 <= 200000;
200000 rows created.
SQL>
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 <= 200000;
200000 rows created.
SQL>
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 <= 200000;
200000 rows created.
SQL>
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 <= 200000;
200000 rows created.
SQL>
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 <= 200000;
200000 rows created.
SQL>
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 <= 200000;
200000 rows created.
SQL>
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 <= 200000;
200000 rows created.
SQL>
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.87470
SQL>
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 <= 36
5 )
6 select *
7 from age_list;
AGE
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
36 rows selected.
SQL>
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 <= 36
5 ),
6 product_check as (
7 select
8 age1.age as youngest,
9 age2.age as middle,
10 age3.age as oldest,
11 age1.age+age2.age+age3.age as sum,
12 age1.age*age2.age*age3.age as product
13 from age_list age1, age_list age2, age_list age3
14 where age2.age >= 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 <= 36
5 ),
6 product_check as (
7 select
8 age1.age as youngest,
9 age2.age as middle,
10 age3.age as oldest,
11 age1.age+age2.age+age3.age as sum,
12 age1.age*age2.age*age3.age as product
13 from age_list age1, age_list age2, age_list age3
14 where age2.age >= 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 <= 36
5 ),
6 product_check as (
7 select
8 age1.age as youngest,
9 age2.age as middle,
10 age3.age as oldest,
11 age1.age+age2.age+age3.age as sum,
12 age1.age*age2.age*age3.age as product
13 from age_list age1, age_list age2, age_list age3
14 where age2.age >= 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 ...
Parallel Universe
An oft-used (and subsequently oft-abused) execution path is parallel execution, usually 'instigated' by some sort of parallel hint. Developers, albeit with the best intentions, misuse and abuse this mechanism because of faulty logic, that 'logic' being that if one process executes in X amount of time then Y parallel processes will execute in X/Y amount of time, and nothing could be further from the truth in many cases. Yes, there are opportunities to use parallelism to speed up processing but in most of the cases I've seen it's doing more harm than good.To illustrate this in a 'real world' situation say you're in a restaurant and the service appears to be slow; you think 'if I had three waitresses instead of one I'd get my food faster', but let's look at that from a different point of view. Three waitresses for one table means that all three waitresses need to communicate with each other to avoid repeating work done by the others (this, of course, takes time). Each waitress needs to be assigned specific duties but also must be available to take over for another if something happens (one broke her toe, for instance); such an occurrence requires reassignment of duties and adjusting the schedule to accomodate the change. Eventually you get your order but it will take MORE time than if a single waitress performed all of the necessary tasks.
Parallel processing does more than simply 'divide and conquer' as it requires several steps most developers may not know about or may ignore entirely. DSS and DW systems, running with large numbers of CPUs, can benefit from parallel processing as the load can be distributed among the CPUs reducing the load on a single processor. OLTP systems, on the other hand, usually involve operations which are quick to begin with and the overhead of implementing parallel processing is quite large compared to the overall execution time; additionally it may take longer to complete the parallel execution than it would to properly tune the query for single-threaded processing and eliminate the parallelism altogether.
So what does parallel processing bring to the table? Obviously the
SQL> select id, txtval, status
2 from para_tst pt
3 where id between 9001 and 34001;
ID TXTVAL STATUS
---------- ------------------------------ -------
9389 ALL_SCHEDULER_RUNNING_JOBS VALID
9390 USER_SCHEDULER_RUNNING_JOBS VALID
9391 USER_SCHEDULER_RUNNING_JOBS VALID
[...]
20772 /130d52e2_JDK2Sorter VALID
20773 /130d52e2_JDK2Sorter VALID
20774 /4c28cb16_ToolLogOptions VALID
20775 /4c28cb16_ToolLogOptions VALID
20776 /cbd9a55f_AbortException VALID
20777 /cbd9a55f_AbortException VALID
591960 rows selected.
Elapsed: 00:02:03.68
Execution Plan
----------------------------------------------------------
Plan hash value: 350193380
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 593K| 20M| 1698 (2)| 00:00:21 | | |
| 1 | PARTITION RANGE ITERATOR| | 593K| 20M| 1698 (2)| 00:00:21 | 19 | 21 |
|* 2 | TABLE ACCESS FULL | PARA_TST | 593K| 20M| 1698 (2)| 00:00:21 | 19 | 21 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<=34001)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
45396 consistent gets
156 physical reads
0 redo size
21517854 bytes sent via SQL*Net to client
434616 bytes received via SQL*Net from client
39465 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
591960 rows processed
SQL>
SQL> select /*+ parallel(pt 2) pq_distribute(pt partition) */
2 id, txtval, status
3 from para_tst pt
4 where id between 9001 and 34001;
ID TXTVAL STATUS
---------- ------------------------------- -------
18404 /79bc64f9_JvmMemoryMeta VALID
18405 /b80019c2_EnumJvmThreadContent VALID
18406 /b80019c2_EnumJvmThreadContent VALID
18407 /bcfa29b5_EnumJvmThreadCpuTime VALID
18408 /bcfa29b5_EnumJvmThreadCpuTime VALID
17997 /b3bd73eb_CommonClassObject VALID
17998 /b3bd73eb_CommonClassObject VALID
17999 /c5a69e17_ServerSchemaObject1 VALID
[...]
20724 /4bd3ef8d_KnownOptions4 VALID
20725 /4bd3ef8d_KnownOptions4 VALID
20726 /75d2b0ba_KnownOptions5 VALID
20727 /75d2b0ba_KnownOptions5 VALID
20728 /75e9b2d4_KnownOptions VALID
20729 /75e9b2d4_KnownOptions VALID
591960 rows selected.
Elapsed: 00:02:23.59
Execution Plan
----------------------------------------------------------
Plan hash value: 1393746857
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distr |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 593K| 20M| 942 (1)| 00:00:12 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 593K| 20M| 942 (1)| 00:00:12 | | | Q1,00 | P->S | QC (RAND)|
| 3 | PX BLOCK ITERATOR | | 593K| 20M| 942 (1)| 00:00:12 | 19 | 21 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| PARA_TST | 593K| 20M| 942 (1)| 00:00:12 | 19 | 21 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID"<=34001)
Statistics
----------------------------------------------------------
11 recursive calls
1 db block gets
6396 consistent gets
6154 physical reads
96 redo size
21534533 bytes sent via SQL*Net to client
434616 bytes received via SQL*Net from client
39465 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
591960 rows processed
SQL>
On a system running 11.2.0.2 with
When is it good practice to use parallel processing? One situation which comes to mind is in creating/populating tables using complex queries that may return faster in parallel than with standard serial processing. An application on which I worked was populating a table with a rather benign join but it was taking far too long to complete the load -- the elapsed time exceeded the batch processing window. Using parallelism (along with regularly updated statistics) dramatically reduced the response time from over an hour to less than two minutes, well within the batch window allowing the rest of the processing to continue. [Note that this was a batch process, run outside of the normal business day, which freed resources normally allocated to user sessions.] There are others, outside of data warehousing applications, but they're exceptions and not the rule.
So, we've learned that even though parallelism would appear to make things go faster in reality that's not often the case due to the extra overhead in managing additional processes and consolodating the individual results into the final result set. We've also learned that simply slapping a /*+ parallel */ hint into a query doesn't constitute tuning and doing so can make performance worse instead of better. The correct choice is to properly tune the query or queries in question using resources such as AWR and ASH reports, execution plans and wait statistics to pinpoint the problem area or areas to address. Parallelism isn't a silver bullet and wasn't intended to be and should be used sparingly, if at all.
Now, where's my sandwich?
Sponsors
