jump to navigation

Friday Philosophy – Madness demands Attention May 28, 2010

Posted by mwidlake in Architecture, Friday Philosophy.
Tags: , ,
2 comments

Many years ago I had a good friend who was a psychiatric nurse. We were talking about his job once and he was saying how some patients just took up much more time than others. These were generally the ones who would be deemed “the most mad” in a non-clinical manner {and is pretty much how my friend the psychiatric nurse put it}. These patient’s actions or need for intervention would put demands on the staff far more than other patients. As a result, all the nurses tended to get to know (or know of) such patients better than others.

I thought of this the other day when a few of us were talking about some awful bit of application we were concerned about. This thing inserts rows from a MSSQL database into a table in an Oracle database. Triggers on the initial table fire and populate another table, in a 1-to-many relationship. This second table also has a trigger on it that further inserts into another set of tables. A regular process then aggregates this data – and sticks it back into the MSSQL database it came from.

Said process is done as a single transaction for all rows inserted for the day. Irrespective of the growth in rows. Or the fact that one source “application” has grown to 10 and soon will grow to 50. All rows in one transaction. The intermediate tables are never cleared out and get bigger and bigger. No one else needs any of this data in the Oracle side of the system.

There are several “madnesses” to this process – why put it into Oracle only to put it back in the source system, why use a busy production system to hold and process transient data, why no clean up, why are the records not processed in batches, the cascading triggers magnifies transaction data volumes…

This process is well-known in our group. I’ve been involved. Both the guys I was talking to have been involved. I can see from my desk 4 or 5 other people who have been roped into bullying this process thought before now. In fact, I reckon half the department have had to work on this damned thing at some point.

Can you see why I was reminded of my conversation with my old nurse friend?

The application is simply mad. And as a result it is demanding not only on our database but on all of the team, as so many of us have had to get involved working on it. We have all got to know it so well.

I’m glad to say that treatment for the application is planned and, hopefully, it will soon be a lot happier.

So will we.

A new Blog to watch May 22, 2010

Posted by mwidlake in Blogging.
Tags:
add a comment

I thought I would mention a new Blog that has started that promises to have some good stuff on it, Which is Nigel Noble’s Oracle Blog. I should say, more good stuff on it as Nigel has already put up a few excellent posts, one on PL/SQL array performance, another on connection pooling.

I’m lucky to be working in the same company as Nigel at present. He’s the sort of person I like to go and talk to when I can’t work out my own Oracle issues, as he appreciates how the Oracle database and the hardware all fit together. Well, better than I do at any rate. And he’s helpful, so I am pleased to be able to pay him back a little by this ad.

I’ve added him to my blog roll also.

Dynamic Sampling Hint Ignored May 20, 2010

Posted by mwidlake in internals, performance.
Tags: ,
27 comments

This is a bit of an odd one. On V10.2.0.3 (I have not tried other versions yet) then the dynamic sampling hint appears to be ignored if the stats for the table say there are zero rows in the table. Even if there are in fact lots of rows.

I’ve not had chance to dig into Metalink etc yet {I’m putting together this post before I yet again get overwhelmed and fail to put up something I think is of interest} but if this turns out to be a general bug impacting other versions, it could catch people out. This is because one tactic to handle queries where the data volumes shifts all the time is to not gather stats and use the dynamic sampling hint. “Not gathering stats” could also be “gather stats when the table is empty and ignore it – the dynamic sampling hint will take care of everything”.

Here is my worked example. I use two tables, PERSON and PERSON_NAME, related on a column PERS_ID. {If you want the exact table definitions etc, mail me}.

--preserve my data
create table pena_backup as select * from person_name;

Table created.

--ensure I have stats
exec dbms_stats.gather_table_stats(ownname=>user,tabname =>'PERSON')
PL/SQL procedure successfully completed.

exec dbms_stats.gather_table_stats(ownname=>user,tabname =>'PERSON_NAME')
PL/SQL procedure successfully completed.

--Show I have data
select table_name,num_rows,last_analyzed from dba_tables
where owner=USER  AND TABLE_NAME IN ('PERSON','PERSON_NAME');

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------
PERSON                              71913 20-MAY-2010 11:06
PERSON_NAME                        107567 20-MAY-2010 11:06

--Explain a simple select
explain plan for
select /* */ pers.surname, pers.first_forename
from    person pers, person_name pena
where pena.pers_id =pers.pers_id
and    pena.surname = 'BROWN'
/ 
Explained.

select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 2653984949

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |  1427 | 45664 |   637   (5)| 00:00:03 |
|*  1 |  HASH JOIN         |             |  1427 | 45664 |   637   (5)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL| PERSON_NAME |  1427 | 17124 |   320   (5)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| PERSON      | 71913 |  1404K|   314   (4)| 00:00:02 |
----------------------------------------------------------------------------------

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

   1 - access("PENA"."PERS_ID"="PERS"."PERS_ID")
   2 - filter("PENA"."SURNAME"='BROWN')

16 rows selected.

--Now explain with dynamic sampling
explain plan for
select /*+ dynamic_sampling (pena 6) */ pers.surname, pers.first_forename
from    person pers, person_name pena
where pena.pers_id =pers.pers_id
and    pena.surname = 'BROWN'
/
Explained.

select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 2653984949

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |  1427 | 45664 |   637   (5)| 00:00:03 |
|*  1 |  HASH JOIN         |             |  1427 | 45664 |   637   (5)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL| PERSON_NAME |  1427 | 17124 |   320   (5)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| PERSON      | 71913 |  1404K|   314   (4)| 00:00:02 |
----------------------------------------------------------------------------------

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

   1 - access("PENA"."PERS_ID"="PERS"."PERS_ID")
   2 - filter("PENA"."SURNAME"='BROWN')

Note
-----
   - dynamic sampling used for this statement

20 rows selected.

-- Note the appearance of the NOTE about dynamic sampling
-- If I did this again, I would add extra data to the table to get different stats
-- from the dynamic sampling


--truncate my person_name table and re-gather stats

truncate table person_name;
Table truncated.

exec dbms_stats.gather_table_stats(ownname=>user,tabname =>'PERSON_NAME')
PL/SQL procedure successfully completed.

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------
PERSON                              71913 20-MAY-2010 11:06
PERSON_NAME                             0 20-MAY-2010 11:06


--now try and dynamic sample a known empty table
explain plan for
select /*+ dynamic_sampling (pena 6) */ pers.surname, pers.first_forename
from    person pers, person_name pena
where pena.pers_id =pers.pers_id
and    pena.surname = 'BROWN'; 
Explained.

select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 1295262714

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |    50 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | PERSON      |     1 |    20 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |             |     1 |    50 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| PERSON_NAME |     1 |    30 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | PENA_SN     |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | PERS_PEID   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   4 - access("PENA"."SURNAME"='BROWN')
   5 - access("PENA"."PERS_ID"="PERS"."PERS_ID")

18 rows selected.

-- I initially thought Oracle might be detecting that the 
-- table was empty - never had data inserted or had been truncated
-- and thus knew there was no point sampling...But...
 
-- put the data back
insert into person_name select * from pena_backup;

107567 rows created.

commit;
Commit complete.

--and explain again

Explained.
explain plan for
select /*+ dynamic_sampling (pena 6) */ pers.surname, pers.first_forename
from    person pers, person_name pena
where pena.pers_id =pers.pers_id
and    pena.surname = 'BROWN';

select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 1295262714

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |    50 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | PERSON      |     1 |    20 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |             |     1 |    50 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| PERSON_NAME |     1 |    30 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | PENA_SN     |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | PERS_PEID   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   4 - access("PENA"."SURNAME"='BROWN')
   5 - access("PENA"."PERS_ID"="PERS"."PERS_ID")

18 rows selected.

-- Note, nothing about dynamic sampling, the rows and bytes expected are the same as
-- the query with no dynamic sampling hint

-- now gather stats again so oracle knows there is some data

exec dbms_stats.gather_table_stats(ownname=>user,tabname =>'PERSON_NAME') 
PL/SQL procedure successfully completed.

select table_name,num_rows,last_analyzed from dba_tables
where owner=USER  AND TABLE_NAME IN ('PERSON','PERSON_NAME');

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------
PERSON                              71913 20-MAY-2010 11:06
PERSON_NAME                        107567 20-MAY-2010 11:09

--and now try dynamic sampling again

explain plan for
select /*+ dynamic_sampling (pena 6) */ pers.surname, pers.first_forename
from    person pers, person_name pena
where pena.pers_id =pers.pers_id
and    pena.surname = 'BROWN';
Explained.

select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 2653984949

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |  1427 | 45664 |   637   (5)| 00:00:03 |
|*  1 |  HASH JOIN         |             |  1427 | 45664 |   637   (5)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL| PERSON_NAME |  1427 | 17124 |   320   (5)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| PERSON      | 71913 |  1404K|   314   (4)| 00:00:02 |
----------------------------------------------------------------------------------

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

   1 - access("PENA"."PERS_ID"="PERS"."PERS_ID")
   2 - filter("PENA"."SURNAME"='BROWN')

Note
-----
   - dynamic sampling used for this statement

20 rows selected.

What prompted me to find this is I had to provide a DYNAMIC SAMPLING hint to a development team and I wanted to make sure it was working correctly first (it was a MERGE statement and I was not sure where to best put it). As you get no warnings about syntax errors with hints, it is best to prove they are correct before handing them over :-)
I was doing this in a dev system and, try as I might I could not get the hint to work. Because, as I now know, the dev system had no data in the driving table and stats had been gathere on it. I wasted 30 mins in a critical fix-on-fail due to this oddity.

I have not tried to see if DYNAMIC SAMPLING stated at the instance level is also ignored if there are zero-row stats against the table.

I’m still here, honest May 18, 2010

Posted by mwidlake in Blogging, Perceptions, Private Life.
Tags: ,
3 comments

In the last 24 hours I’ve had four emails asking if I am OK – Apparently I am not blogging or bothering people via email! Well, I am OK (and thanks guys for the concern), I was just knackered.

Rule 1 of blogging “no one cares about you as an individual” (and, I am glad to say, that is a myth. Even in the zero-physical-contact medium of electronic communication, some have been concerned about my silence. Humanity may yet have a future)

Truth is, I damaged myself trying to get healthier {so it is back to the eating pies and drinking beer for me!*} and that caused lack of sleep and more unhealth and I ended up very, very, very tired and I was reduced to putting all my energy into doing the day job.

This has nothing to do with a Blog on technology and database, of course.

Ahh, but Yes, it does, it actually has a hell of a lot to do with it. I have been tired, hard-pressed and under-performing. So I concentrated on doing my primary job and nothing else. So I have not blogged and I have not emailed people and I have not generally helped as much as I would like.

The thing is, if you think of your comrades and fellow staff (and, for some of you, the people who work for you) most people around you could well be the same. The primary directive of business, at present, is to get everything for your current task out of the staff right now. That is the prime directive, push the staff hard to get x, y and z done. Or, for those of you working under an Agile Methodology, the handful of tasks in front of you for this sprint {or whatever the hell terminology is for your take on the “Get It Done NOW” methodology}.

I have had no bandwidth to do more than my day job of late. And I stopped helping. I think some current working practices and philosophies have the same, chronic effect.

Is this a good thing? I will let you decide.

For myself, I’ve had a week walking in Snowdonia (and I was not fit enough to get the best out of the time, but mentally it was a God-send). I thought nothing about technology; I thought very little at all. I walked up hills, I drank beer and wine and I ate lots of pies. And I now feel good.

I know I am doing better work now than I was 2 weeks ago.

So, I hope to start doing proper technical blogs again in the next week or so. But right now, having had a week of total down-time, I am ready and need to do my day job again. And they pay me, so I better go off and do it! Expect a proper technical blog next week.

{* I joke about damaging myself getting fitter, but I feel condemned to point out that being generally fitter and healthier is a good thing, even if you hurt yourself getting there. It is better to be old and fit than old and decrepit. Or old and dead. :-) I’m full of happy thoughts like that…}

Follow

Get every new post delivered to your Inbox.

Join 159 other followers