Oracle 10g R2

It's June 30th. The day is half done. I've got Linux. Where's 10gR2?

Deferrable Constraints

One of my developers asked me how to drop a constraint in PL/SQL today. "Don't do that" was my immediate answer.

Well, seems they have a legitimate case where they need to change a PK value in a controlled environment. I suggested deferrable constraints would give them some flexibility yet still maintain data integrity at the transaction level. I quickly got in over my head when peppered with questions about how it works and when errors are raised. So I cobbled up an example:

Lets create a master-detail relationship:

SQL> @c:\temp\create_tables.sql
SQL> drop table xyz_master cascade constraints;

Table dropped.

SQL> create table xyz_master (
 2     id          number(10) primary key,
 3     hire_date   date,
 4     common_name varchar2(200))
 5  /

Table created.

SQL> drop table xyz_detail cascade constraints
 2  /

Table dropped.

SQL> create table xyz_detail (
 2     id          number(10),
 3     pay_date    date,
 4     pay_amount  number(20,3),
 5     constraint xyz_detail_pk
 6        primary key (id, pay_date))
 7  /

Table created.

Now, lets put the deferrable fk on...

SQL> @c:\temp\add_constraints
SQL>
SQL> alter table xyz_detail
 2     add constraint xyz_master_fk1
 3        foreign key (id)
 4        references xyz_master(id)
 5     initially deferred deferrable
 6  /

Table altered.

Lets test to make sure the RI still works...

SQL> @c:\temp\add_bad1.sql
SQL> insert into xyz_detail values (1, trunc(sysdate), 100);

1 row created.

SQL> insert into xyz_detail values (1, trunc(sysdate+7), 100);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (JEFFH.XYZ_MASTER_FK1) violated - parent key
not found

Ah, I get it. The rows were added, but when I committed, the RI was checked at that point. Lets make sure the tables are clean...

SQL> select * from xyz_detail;

no rows selected

SQL> select * from xyz_master;

no rows selected

OK, that's what I expected. Lets put in some "real" data in the correct order.

SQL> @c:\temp\add_good1.sql
SQL> insert into xyz_master values (1, trunc(sysdate-365*5), 'jeff hunter');

1 row created.

SQL> insert into xyz_master values (2, trunc(sysdate-365*3), 'fred flintstone');

1 row created.

SQL> insert into xyz_master values (3, trunc(sysdate-(365*12.2)), 'mr. slate');

1 row created.

SQL> insert into xyz_detail values (1, trunc(sysdate), 100);

1 row created.

SQL> insert into xyz_detail values (1, trunc(sysdate-7), 100);

1 row created.

SQL> insert into xyz_detail values (1, trunc(sysdate-14), 100);

1 row created.

SQL> insert into xyz_detail values (1, trunc(sysdate-21), 100);
1 row created.

SQL> insert into xyz_detail values (2, trunc(sysdate), 22.22);

1 row created.

SQL> insert into xyz_detail values (2, trunc(sysdate-7), 22.22);

1 row created.

SQL> insert into xyz_detail values (2, trunc(sysdate-14), 22.22);

1 row created.

SQL> insert into xyz_detail values (2, trunc(sysdate-21), 22.22);

1 row created.

SQL> insert into xyz_detail values (3, trunc(sysdate), 1000);

1 row created.

SQL> insert into xyz_detail values (3, trunc(sysdate-7), 1000);

1 row created.

SQL> insert into xyz_detail values (3, trunc(sysdate-14), 1000);

1 row created.

SQL> insert into xyz_detail values (3, trunc(sysdate-21), 1000);

1 row created.

SQL> commit;

Commit complete.

I expected that to work. Lets try to insert the details and then the master record just for kicks.

SQL> @c:\temp\add_wrong_order1.sql
SQL>
SQL> insert into xyz_detail values (4, trunc(sysdate), 100);

1 row created.

SQL> insert into xyz_detail values (4, trunc(sysdate-7), 100);

1 row created.

SQL> insert into xyz_detail values (4, trunc(sysdate-14), 100);

1 row created.

SQL> insert into xyz_detail values (4, trunc(sysdate-21), 100);

1 row created.

SQL> insert into xyz_master values (4, trunc(sysdate-(365*12.2)), 'barney rubble');

1 row created.

SQL> commit;

Commit complete.

That's cool, that works. Lets try to violate the PK to see what happens.

SQL> @c:\temp\add_bad2.sql
SQL> insert into xyz_detail values (1, trunc(sysdate), 100);
insert into xyz_detail values (1, trunc(sysdate), 100)
*
ERROR at line 1:
ORA-00001: unique constraint (JEFFH.XYZ_DETAIL_PK) violated


SQL> insert into xyz_detail values (1, trunc(sysdate-7), 100);
insert into xyz_detail values (1, trunc(sysdate-7), 100)
*
ERROR at line 1:
ORA-00001: unique constraint (JEFFH.XYZ_DETAIL_PK) violated


SQL> insert into xyz_detail values (1, trunc(sysdate-14), 100);
insert into xyz_detail values (1, trunc(sysdate-14), 100)
*
ERROR at line 1:
ORA-00001: unique constraint (JEFFH.XYZ_DETAIL_PK) violated


SQL> commit;

Commit complete.

Right. The PK is not deferrable. Lets change the PK value in the master and then change the PK value in the detail records.

SQL> @c:\temp\update1.sql
SQL> update xyz_master set id=10 where id=1;

1 row updated.

SQL> update xyz_detail set id=10 where id=1;

4 rows updated.

SQL> commit;

Commit complete.

That works, my developer will be happy. Just for kicks, lets try it the other way around.

SQL> @c:\temp\update2.sql
SQL> update xyz_detail set id=20 where id=2;

4 rows updated.

SQL> update xyz_master set id=20 where id=2;

1 row updated.

SQL> commit;

Commit complete.

Of course that works. Let's just double check to make sure RI stays in tact (like we have any doubts now)...

SQL> @c:\temp\update3.sql
SQL> update xyz_master set id=30 where id=3;

1 row updated.

SQL> update xyz_detail set id=30 where id=4;

4 rows updated.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (JEFFH.XYZ_MASTER_FK1) violated - child record
found

Sure enough. I still need to investigate any locking issues, but it looks like this might be what we are looking for in this particular case. If anybody has any hints on what to look out for, I'd appreciate it. Also, assuming we have a legitimate business case, do you think this is an appropriate use of deferrable constraints?

Process or No Process?

My wife and I are both in the IT world. She is a project manager that for most of her working life has been employed by Fortune 100 companies. She works on software products that go into the marketplace and whose teams are counted in hundreds. It can be years before a product gets from conception to the marketplace. Her products are developed for the target business community.

I, on the other hand, am a somewhat knowledgeable Oracle guy whose biggest company has been 400 people if you count the part-timers. I work on projects where most times the number of team members can be counted on one hand. If we come up with an estimate of 3 months, management says "break it up" to shorten the development time. My projects are all internal endeavors whose primary goal is to help the business make more money or be more efficient.

She has always been an advocate of following a development process. A development process allows you to measure the progress of each phase of the project. Each phase of the project is defined by the work product that must be produced by the end of phase. If the process is followed, the resulting product has been good quality and usually makes a lot of money for the company.

My group works in what you could call the 80/20 methodology. We try to get 80% of the functionality into the users hands in a very short time. The other 20% of functionality will come when the business decides spending time developing a solution is cost effective and will bring greater benefit to the business than not doing it. Each project is short in nature and its success can be measured in how much time we can save or how much revenue it brings in. Sure, we have bugs in our software, but who doesn't?

At the end of the day, we both get the job done. She tries to explain how the process works even when milestones are not met. She can tell you how much of the phase is complete and the day it will be done with about 95% accuracy. I don't get it. I try to explain how my users expect results, not a document explaining what we will do. We need to solve a problem today, not 14 months from today. She doesn't get it. Is software utopia in the middle somewhere?

Does Certification Matter Anymore?

I just finished the hiring process for my new DBA scheduled to start in a couple weeks. I was looking for a particular type of person to fill a particular role who would have the initiative to grow as the business grew. I've hired a few people in my time; made a couple mistakes, underestimated a couple of people. Overall, though, I've been pretty successful at evaluating individuals for the positions I want to fill. When I assembled the skills inventory to send to recruiters for my open DBA position it didn't even cross my mind to make certification required. When I look at resumes I certainly notice if the candidate is certified or not, but it doesn't sway my decision either way.

After I got through with this whole process, I read with interest Don Tennant's article (Certifiably Concerned) in Computerworld about how certified professionals get smaller pay increases than their un-certified counterparts. Don says:

So if you perceive even a subtle cultural shift away from certification in your organization, do something about it. In the process, you'll be keeping the bar up high where it belongs.

I thought to myself for a second "Am I part of the problem?". I looked at the study, but was left with more questions than answers. For example, What was the pay differential between certified and non-certified employees? Does becoming certified in technology XYZ bring an immediate pay raise?

The big question is does certification deliver any real value to the business? I think it does, but probably not for the same reasons as Don Tennant thinks. When a person starts the certification process she proves to me that she has drive and wants to learn more. A DBA shows me they want to understand what's going on instead of just reacting to the situation. I know it's just a couple of tests, but when a person dedicates themself to a goal and achieves it, I know I want them to work for me.

On the other hand, I don't need certification to show me that either. I love it when a DBA questions how things are setup and can argue why things should be different. The guy that stays all night through 8 recoveries even though he has only been there 3 weeks is someone you want to hang on to.

Apparently, Don got lambasted for his comments. I disagree that you need a "consistent, quantifiable means of documenting the skills assets of your IT organization. Otherwise, expanding and improving that institutional skill set will be an adhoc activity, and efforts to optimize the quality and productivity of your workforce will suffer". I know the quality of my workforce by two easily measured metrics; how many mistakes they make and how many service interruptions my systems have had. I know the productivity of my workforce by how long it takes them to perform a given task the first time. If it's a repetitive task, we automate it.

Certification has it's place, but experience and drive trump certification every time.

Oracle Wait Interface

The conventional wisdom of tuning Oracle databases has undergone a shift in the last five or so years. Tuning an Oracle database used to mean measuring various ratios and tweaking init.ora parameters so the ratios were "right". Maybe the users' response got better, maybe it didn't. But the database was running better. Some people built businesses around this black magic of tweaking init.ora parameters so the numbers looked "right" without ever really solving the root cause of the slowness. Tuning today means resolving the root cause of the problem and making the users response time acceptable. A large part of measuring performance is the Oracle Wait interface.

I first learned about the Oracle Wait Interface (OWI) was in an informal conversation with Roman Kab from Optimum Computing after a users group meeting. Roman talked about some new 8i features and he mentioned the OWI and the higher degree of instrumentation. One of the most helpful parts of the new "Statspack" report was the top wait events. I thought it was interesting that my instance waited on "db file sequential read" the most, but what statement caused the majority of these waits? I had a good buffer cache hit ratio, so it couldn't be that relavent, could it?

For me, tkprof in version 9.2 brought the OWI to the forefront of wait event tuning. The ability to see which waits were affecting the longest running statements was a major step in diagnosing performance problems. I experience several "Ah-ha" moments when I looked the new 9.2 traces.

Two new books on the OWI are Optimizing Oracle Performance by Cary Milsap and Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (OWI:Practical) by Richmond Shee, Kirtikumar Deshpande and K. Gopalakrishnan. Optiming Oracle Performance is a roadmap of how to use information garnered from OWI and use it in a reproducable scientific method to solve performance problems. In this book, the author explains queueing theory in terms most DBAs can understand. In Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning by Richmond Shee, Kirtikumar Deshpande and K. Gopalakrishnan, the authors delve into each of the most populate wait events and explain in detail how to diagnose and resolve the different conditions which might cause excessive waits. Where Optimizing Oracle Performance was heavy in the theories behind the OWI, OWI:Practical concentrates on how to use the wait events in everyday situations.

I suggest reading OWI: Practical first to get a handle on how the wait interface works and how it can help you in day-do-day performance tuning. Once you get some practice with OWI and tkprof, you will be able to better comprehend Optimizing Oracle Performance and appreciate the performance tuning methodlogy outlined in it.

Tuesday all ready?

Geez. It's Tuesday all ready. I haven't posted anything worthwhile in a week. Keeping up this blog is a little more work than I bargained for. I've just finished two books on OWI and will be reporting on them shortly.

DBASupport.com Forums Back Up

Seems they've gone through an upgrade.

DBASupport.com Forums Down?

They seem to be down for me. From http://www.dbasupport.com/forums:

There seems to have been a slight problem with the DBAsupport.com Forums database.
Please try again by pressing the refresh button in your browser.

An E-Mail has been dispatched to our Technical Staff, who you can also contact if the problem persists.

We apologise for any inconvenience.

Building a better dbms_stats

"Everybody Knows" that to get the Cost Based Optimizer (CBO) to choose a good execution plan you need accurate statistics. In the ideal world you would have up-to-date statistics at every moment. However, as tables shrink and grow their statistics no longer reflect the true condition of the table. The optimizer may choose a "wrong" path based on the information it has.

Prior to 8.1.5, calculating statistics used to be a trade off of time spent calculating statistics vs. the amount of time saved when your queries ran faster. Oracle provided the ANALYZE command or the dbms_utility package that you used to calculate statistics on your data. Automating the process of collecting statistics was either a script generating a bunch of ANALYZE commands or a call to dbms_utility.

The enhancements to the optimizer in 8.1.5 made calculating statistics a necessary evil. Oracle provided dbms_stats to assist with statistics gathering. My first attempt at using dbms_stats was replacing calls to dbms_utility with calls to dbms_stats. Needless to say, this method failed horribly wrong for a couple reasons:

1. dbms_stats didn't work exactly the same as dbms_utility.
2. dbms_stats calculation took longer than dbms_utility.

Once I learned what options for dbms_stats were good for my environment, I setup my analyze jobs and let them run. After a while, the weekly statistics calculation was taking 20 hours to run. Surely there must be a better way. My four requirements for a "better" dbms_stats were:

• The new method must be able to analyze more than one object at a time. The degree option of dbms_stats is great, but I found my CPUs were at most 50% busy during statistics gathering, even with degree=2*CPU. I've got multiple processors and I want them 100% busy during statistics gathering.
  
• The new method must be able to give me feedback on where it is and how long it took to analyze each object. I don't want to issue a command and wait for 5 hours to tell me "done".
  
• 20 hours is totally unacceptable. I was analyzing every table every weekend when probably 90% of them never changed. I'll admit it, I was lazy when converting from another version.
  
• When the optimizer chose a radically different execution path, I had to be able to "restore" the previous stats.

I read more about dbms_stats. I talked to people. I read what others were doing for their statistics collection. I read just about every link on AskTom about dbms_stats. I experimented.

First, I turned on monitoring and am letting Oracle tell me what objects I need to gather statistics for. This will immediately speed up my process because I don't have to analyze tables that don't change. Less work = faster.

Next, I configured job queues so I could use dbms_job. The job queues will give me the ability to run multiple analyze jobs at the same time.

Last, I had to write a little code. I basically broke up the processing into two pieces; one step to figure out what needs to be analyzed and submit those jobs to the job queue and another step to analyze the object. Below is the pseudo code I used to figure out which objects had to be analyzed:

BEGIN

   Get A list of objects (o) that need analyzing;
   FOREACH o LOOP
      Record attributes about the object you are going to analyze;
      Setup a DBMS_JOB;
      DBMS_JOB.SUBMIT;
   END LOOP;

END;

Then, each dbms_job was a simple call to a piece of code that used this logic:

BEGIN
   Record the time this job started;

   Export the stats for this object to a "history" table;
   IF the table is partitioned THEN
      IF the object to analyze is the partitioned table/index THEN
         dbms_stats.gather_table/index_stats(
            ownname=>o.ownname,
            tabname=>o.tabname,
            granularity=>'GLOBAL');
      ELSE
         dbms_stats.gather_table/index_stats(
            ownname=>o.ownname,
            tabname=>o.tabname,
            partname =>o.partname);
      END IF;
   ELSE
      dbms_stats.gather_table/index_stats(
         ownname=>o.ownname,
         tabname=>o.tabname);
   END;
   Record time job ended;
END;

I'm testing this in development right now to see how it compares to the old method. Right now it looks promising.

Where can I find help?

I'd like to consider myself as someone that contributes to the Oracle learning process for aspiring Database Administrators. Oracle is a big piece of software that we all need a little help in figuring out. The one thing you need to remember is someone has done what you are trying to do. When I get stuck on something, where do I go for help?

The first place I usually look is the online documentation at tahiti.oracle.com. There's usually a wealth of information on every little aspect of the piece of functionality you are searching for. The hardest part is filtering out all the extraneous things you don't need.

Next, I logon to Metalink and start searching for documents related to my problem. If you what you are looking for, Metalink is a great resource. My first seach is usually restricted to documents. If that doesn't yield enough results, I'll open it up to the forums as well.

If I still haven't found my answer, I'll navigate over to AskTom. The searching capability on this site is phenomenal. It has five years worth of questions and followup on just about every topic immaginable. If you get the rare chance to ask Tom a new question, definitely take it. You may even want to formulate your question beforehand and check back from time to time to see if he is accepting new ones.

Then I navigate to some other sites maintained by some people I really respect. You might not get a specific answer to your question, but may get some background on the subject you are researching. A great resource for install and "how-to" articles is OracleBase by Dr. Tim Hall. If you're dealing with latches or locking, definitely navigate to Steve Adams's IXOra. Howard J. Rogers site www.dizwell.com also has a lot of information and tips for DBAs and developers. Last, but not least check out Jonathan Lewis' site at http://www.jlcomp.demon.co.uk.

Then I'll start looking at "the forums". There are lots of question and answer forums for Oracle support and you have to pick and choose which ones you trust. I may be a little biased, but I prefer DBASupport.com and dizwell.com. Those forums are usually frequented by people that really want to help. As a hint, the people that answer questions really get turned off by subjects that contain "URGENT".

If all that fails solve the problem, I'll submit a TAR to OracleSupport. You won't get an immediate answer, and you might have to state your problem two or three times, but you'll eventually get either an answer or they can file a bug. If you're not happy with the progress of the TAR, you can always call the main support line and have the TAR "duty managed". Don't "duty manage" all your TARs, but only the ones that are important to business that might not be Sev 1's.

As a last resort, search the web. However, there's so much bad information out there that you really need to have an idea of what the solution is to help you in your search.

If anybody has any additional sites, please post them and share with the rest of us.

NYOUG June Meeting

I got to the New York Oracle Users Group meeting about 30 minutes early today. As I was reviewing resumes' for my open position, I noticed out the corner of my eye Rachel Carmichael talking to someone about publishing books. I turned around to listen a little more closely and found Rachel talking to AskTom himself; Tom Kyte.

Rachel's keynote talk was titled "Life in the Trenches - Database Administation in the Real World" and was a survival guide to corporate politics. Some of her "pearls of wisdom":

Your job is do do what your boss tells you to do as long as it's not illegal or immoral.
Sure, you were hired as a DBA, but your boss asks you to write report XYZ. Do it and you get your boss' respect and he thinks your a team player.


There will always be somebody who knows more than you. There will always be somebody who knows less than you.
Life is a learning process. Help others learn what you know and others will help you learn.

Train your replacement.
This is actually not the first time I heard this. A former manager, Joe Murphy, always said his job as a manager was to work himself out of a job. His charges grow and he gets to move on to things more critical to the business.

As for Tom Kyte's keynote, lets just say he has a career as a comedian awaiting him when this Oracle gig is up. His presentation was very entertaining while exploring some of the "old school" thinking and how it relates to today's technology. 60 minutes of Tom Kyte and bind variables didn't come up once.

Gather Stale for Partitions #2

I found my answer to gathering statistics on partitioned tables. In short, the "LIST STALE"
option of dbms_stats.gather_database_stats will report the stale partition when
enough data has changed. In addition, if enough of the table structure has
changed, it will report that the entire table needs analyzing.

See example below:

Lets setup a partitioned table with 20 partitions and turn on monitoring for the table...

ops$jeffh@dev920.us> !more part_test_setup.sql

CREATE TABLE PartTest (
   dt    DATE,
   txt   VARCHAR2(60)
)
PARTITION BY RANGE (dt) (
   PARTITION part_01 VALUES LESS THAN 
      (to_date('07/01/2005','mm/dd/yyyy')),
   PARTITION part_02 VALUES LESS THAN 
      (to_date('08/01/2005','mm/dd/yyyy')),
   PARTITION part_03 VALUES LESS THAN 
      (to_date('09/01/2005','mm/dd/yyyy')),
   PARTITION part_04 VALUES LESS THAN 
      (to_date('10/01/2005','mm/dd/yyyy')),
   PARTITION part_05 VALUES LESS THAN 
      (to_date('11/01/2005','mm/dd/yyyy')),
   PARTITION part_06 VALUES LESS THAN 
      (to_date('12/01/2005','mm/dd/yyyy')),
   PARTITION part_07 VALUES LESS THAN 
      (to_date('01/01/2006','mm/dd/yyyy')),
   PARTITION part_08 VALUES LESS THAN 
      (to_date('02/01/2006','mm/dd/yyyy')),
   PARTITION part_09 VALUES LESS THAN 
      (to_date('03/01/2006','mm/dd/yyyy')),
   PARTITION part_10 VALUES LESS THAN 
      (to_date('04/01/2006','mm/dd/yyyy')),
   PARTITION part_11 VALUES LESS THAN 
      (to_date('05/01/2006','mm/dd/yyyy')),
   PARTITION part_12 VALUES LESS THAN 
      (to_date('06/01/2006','mm/dd/yyyy')),
   PARTITION part_13 VALUES LESS THAN 
      (to_date('07/01/2006','mm/dd/yyyy')),
   PARTITION part_14 VALUES LESS THAN 
      (to_date('08/01/2006','mm/dd/yyyy')),
   PARTITION part_15 VALUES LESS THAN 
      (to_date('09/01/2006','mm/dd/yyyy')),
   PARTITION part_16 VALUES LESS THAN 
      (to_date('10/01/2006','mm/dd/yyyy')),
   PARTITION part_17 VALUES LESS THAN 
      (to_date('11/01/2006','mm/dd/yyyy')),
   PARTITION part_18 VALUES LESS THAN 
      (to_date('12/01/2006','mm/dd/yyyy')),
   PARTITION part_19 VALUES LESS THAN 
      (to_date('01/01/2007','mm/dd/yyyy')),
   PARTITION part_20 VALUES LESS THAN 
      (to_date('02/01/2007','mm/dd/yyyy'))
)
/
ALTER TABLE PartTest MONITORING
/

ops$jeffh@dev920.us> @part_test_setup.sql

Table created.

Elapsed: 00:00:00.34

Table altered.

Elapsed: 00:00:00.02

Great. Now we need to fill that table with some data. We don't need random data, just a bunch of data in some of the partitions. I'll also analyze the table and see what modifications Oracle thinks is done.

ops$jeffh@dev920.us> !more part_test_fill.sql
DECLARE
   myDate DATE;
BEGIN

   FOR i IN 1..1000000 LOOP

      myDate := SYSDATE+MOD(i,400);

      INSERT INTO PartTest 
      VALUES (
         myDate, 
         to_char(myDate,'YYYYMMDDHH24MISS')
      );

      IF mod(i,10000) = 0 THEN
         COMMIT;
      END IF;

   END LOOP;

   COMMIT;

END;
/
analyze table parttest compute statistics
/
set pages 2000

select partition_name, num_rows
from dba_tab_partitions
where table_name = 'PARTTEST'
order by partition_name
/

select 
   partition_name, 
   INSERTS, 
   UPDATES, 
   DELETES, 
   TIMESTAMP, 
   TRUNCATED, 
   DROP_SEGMENTS
from user_tab_modifications
where table_name = 'PARTTEST'
/

ops$jeffh@dev920.us> @part_test_fill

PL/SQL procedure successfully completed.

Elapsed: 00:06:02.39

Table analyzed.

Elapsed: 00:00:38.23

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
PART_01                             62500
PART_02                             77500
PART_03                             77500
PART_04                             75000
PART_05                             77500
PART_06                             75000
PART_07                             77500
PART_08                             77500
PART_09                             70000
PART_10                             77500
PART_11                             75000
PART_12                             77500
PART_13                             75000
PART_14                             25000
PART_15                                 0
PART_16                                 0
PART_17                                 0
PART_18                                 0
PART_19                                 0
PART_20                                 0

20 rows selected.

Elapsed: 00:00:00.40

no rows selected

Elapsed: 00:00:00.00
ops$jeffh@dev920.us>

Lets add some data to the later partitions just so Oracle will have something to report.

ops$jeffh@dev920.us> !more part_test_fill15.sql
DECLARE
   myDate DATE;
BEGIN

   FOR i IN 1..10000 LOOP

      myDate := to_date('09/01/2006','mm/dd/yyyy') 
         - mod(i, 30);

      INSERT INTO PartTest 
      VALUES (
         myDate, 
         to_char(myDate,'YYYYMMDDHH24MISS')
      );

   END LOOP;

   COMMIT;

END;
/
set pages 2000

select 
   partition_name, 
   INSERTS, 
   UPDATES, 
   DELETES, 
   TIMESTAMP, 
   TRUNCATED, 
   DROP_SEGMENTS
from user_tab_modifications
where table_name = 'PARTTEST'
/
ops$jeffh@dev920.us> @part_test_fill15

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.64

no rows selected

Elapsed: 00:00:00.01

Now, we have to wait 15 minutes until SMON picks up the changes.

OK. Lets see what dbms_stats.gather_database_stats tells us...

ops$jeffh@dev920.us> @need_to_analyze
OPS$JEFFH.PARTTEST(TABLE)|PART_15
OPS$JEFFH.PARTTEST(TABLE)|PART_16

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.87

Alrighty, then. dbms_stats.gather_database_stats is telling us that PART_15 and PART_16 need to be analyzed.

Just for fun, lets see what happens when we drop a couple of the partitions in the "front" of the table...

ops$jeffh@dev920.us> alter table parttest 
   drop partition part_01;

Table altered.

Elapsed: 00:00:00.54
ops$jeffh@dev920.us> alter table parttest 
   drop partition part_02;

Table altered.

Elapsed: 00:00:00.06
ops$jeffh@dev920.us> alter table parttest 
   drop partition part_03;

Table altered.

Elapsed: 00:00:00.07
ops$jeffh@dev920.us> alter table parttest 
   drop partition part_04;

Table altered.

Elapsed: 00:00:00.05
ops$jeffh@dev920.us> alter table parttest 
   drop partition part_05;

Table altered.

Elapsed: 00:00:00.05
ops$jeffh@dev920.us> @need_to_analyze
OPS$JEFFH.PARTTEST(TABLE)|PART_15
OPS$JEFFH.PARTTEST(TABLE)|PART_16
OPS$JEFFH.PARTTEST(TABLE)|

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.70
ops$jeffh@dev920.us>

Sweet. Oracle thinks we need to analyze PART_15, PART_16, and the entire PARTTEST table.

Ever Notice?

Did you ever notice how four day weeks turn into 60 hour weeks? Monday (the holiday, of course) started out with a call about report X running 3 times longer than normal. After a couple of conversations with the developer found out that the execution plan had changed over the weekend. Tuesday and Wednesday had me figuring out why a query between two tables was doing 140+ LIOs per row and killing scalabiligy. Thursday I got to do a little research on statistics and partitioned tables. Friday, a developer comes to me and says the optimizer is choosing one plan when using the OCI JDBC Driver and another using the thin JDBC Driver. I say "No Way", we run some tests, and sure enough different plans. Maybe this week I'll get to finish my research on statistics. Arrr....

Gather Stale for Partitions

I'm modifying my statistics gathering process to take advantage of Oracle's ability to figure out what's stale and what's not. I know I can use dbms_stats.gather_schema_stats(options=>'GATHER STALE') to get statistics, but that just goes off into oblivion and I can't track it's progress. I decided to use the 'LIST STALE' and 'LIST EMPTY' options to get the tables that need statistics according to Oracle. I'm then going to record that list of tables and the amount of time spent analyzing each object.

One thing that I'm not sure of is with partitioned tables. Say I have a table with 20 partitions partitioned by date named part_01 through part_20. Each partition contains a month's worth of data. Months 1 through 17 contain about 120,000 records each. On the first day of the month, part_18 gets 20,000 rows and I COMPUTE statistics on the entire table. On the second day of the month, I add another 20,000 rows. I expect that dbms_stats.gather_schema_stats(options=>'LIST STALE') would list the partition as having stale statistics, but would it list the entire table as well?

I'm setting up a test....

Lessons the Hard Way #1

dbms_stats.alter_database_tab_monitoring(monitoring=>true) is not a setting. If you execute this method it just turns monitoring on for all your tables. New ones don't automatically get created with MONITORING turned on. Surely there's got to be a default setting...