Going through your excellent book, I got enough idea of hash-clustered tables. It is now easy to understand why we have to specify SIZE and HASH-Keys before hand; and how they are used to pre-allocate the space needed.

But, I got confused when I reached hash-clustered Partitions. There we do not specify SIZE and HASH-Keys. Then, how does Oracle pre-allocate space? Or, if it is not pre-allocated, then why the same facility is not available with clusters?

Though hash partition does equal distributipn of the rows as per the hash function, I think that each partition does need to know its size in advance; otherwise hash partitioning may not be as efficient as hash cluster.

I once again went through your book, the chapter on partitioning. Yes, now I understand the matter. But, that is "Range Partition" where from I could get the clarity. In fact, that time I just tried to compare Normal Index to Range partition, and somehow I got the clarity.

Thanks, Tom, for your reply.

Well, one more thing. While giving THIS review, I could see none of my questions.

Thanks again.

<Yogesh>

Tom,

1) If you stripe all disks using Raid 0+1, are there any performance benefits for using hash partition other than the administrative benefits you outlined above?

For ex.

create table t (
x number(38))
partition by HASH(X) (
partition p1 tablespace ts_dat,
partition p2 tablespace ts_dat,
partition p3 tablespace ts_dat,
partition p4 tablespace ts_dat
)
/

alter table t
add (
constraint t_pk
primary key (x)
using index local
(PARTITION p1_idx tablespace peter,
PARTITION p2_idx tablespace peter,
PARTITION P3_idx tablespace peter,
PARTITION p4_idx tablespace peter)
)
/

Or is the performance better with no partition?

e.g.

create table t2 (
x number(38))
tablespace peter
/

alter table t2
add (
constraint t2_pk
primary key (x)
using index
tablespace peter
)
/

2) From a performance perspective, are there any benefits from partitioning the data but NOT create the partition on the index?

E.g.

create table t3 (
x number(38))
partition by HASH(X) (
partition p1 tablespace ts_dat,
partition p2 tablespace ts_dat,
partition p3 tablespace ts_dat,
partition p4 tablespace ts_dat
)
/

alter table t3
add (
constraint t3_pk
primary key (x)
using index
tablespace peter
)
/

If yes, why?

Your help is greatly appreciated.

-Peter

BTW, I have both of your Wrox books and they're great. I'm looking forward to your new one - currently on order.

Can hash partitioning ever be bad or abused?

If you have a table containing unique IDs (e.g. SSN), then using hash partitioning sounds like the way to go. Is there any reason not to use it for a table with SSN# as the PK?

Thanks,
-Peter

However, if I don't access by SSN, then any type of index on SSN (partition or not) will be a bad idea.

I'm re-reading chap. 14 (slower) to make sure I grasp all of the concept.

I missed the blurb about "Since global indexes may be partition by range only, you must use local indexes if you wish to have has or composite partitioned index."

I spent several futile minutes trying to force Oracle to create a global hash index.

-Peter

Tom,

Does the hash partition key have to be the part of index key? For e.g. if I have a table hash partitioned by deptno and have created partitioned indexes on the partitioned table, is it necessary to have deptno the part of my index key?

Thanks always!

Tom, thanks for the prompt answer!

"which should be almost unique" -- Do you mean the hash key should be unique in that partition? Do you mean every partition should have only 1 key value for better performance?

I mean, you want to pick a column that has MANY different values to hash on

Say you have 8 hash partitions
Say you pick a column with 10 different values (like deptno in emp).
Very good chance you have some empty partitions, some very very full partitions and some skimpy ones.

Now, take same data, hash on empno and you'll find a nice even distribution.


ops$tkyte@ORA920> drop table test1;
 
Table dropped.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> create table test1
  2  (
  3    empno  number,
  4    deptno number
  5  )
  6  partition by hash(empno) partitions 8
  7  /
 
Table created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> drop table test2;
 
Table dropped.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> create table test2
  2  (
  3    empno  number,
  4    deptno number
  5  )
  6  partition by hash(deptno) partitions 8
  7  /
 
Table created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into test1 select object_id, mod(rownum,10) from all_objects;
 
31641 rows created.
 
ops$tkyte@ORA920> insert into test2 select object_id, mod(rownum,10) from all_objects;
 
31641 rows created.
 
ops$tkyte@ORA920> commit;
 
Commit complete.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
  2          l_cnt number;
  3  begin
  4          for x in ( select partition_name from user_tab_partitions where table_name = 'TEST1' )
  5          loop
  6                  execute immediate 'select count(*) from test1 partition('||x.partition_name||')'
  7                  into l_cnt;
  8                  dbms_output.put_line( x.partition_name || ' ' || l_cnt );
  9          end loop;
 10  end;
 11  /
SYS_P564 3958
SYS_P565 4047
SYS_P566 3941
SYS_P567 3877
SYS_P568 3901
SYS_P569 4073
SYS_P570 3924
SYS_P563 3920
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920> declare
  2          l_cnt number;
  3  begin
  4          for x in ( select partition_name from user_tab_partitions where table_name = 'TEST2' )
  5          loop
  6                  execute immediate 'select count(*) from test2 partition('||x.partition_name||')'
  7                  into l_cnt;
  8                  dbms_output.put_line( x.partition_name || ' ' || l_cnt );
  9          end loop;
 10  end;
 11  /
SYS_P572 0
SYS_P573 3164
SYS_P574 6328
SYS_P575 0
SYS_P576 6328
SYS_P577 6328
SYS_P578 6329
SYS_P571 3164
 
PL/SQL procedure successfully completed.
 
 

What is the difference between a hash partitioned table with 5 tablespaces(say with around 20 datafiles) and a normal table with one tablespace that consists of all those 20 datafiles?
Which is a good option?

Tom, is it possible to know, on a hash partition, which partition contains which keys? Assume a table is hash partitioned by dept, into 6 partitions. Can we find what dept values are stored in individual 6 partitions?

Thanks in advance!

how can i say a particular dept_id will go into a particular partition. is there a way to do this. This will help me in allocating space in advance knowing the dept_no in advance.

Tom,
I'm having a table where there are going to be millions of rows.
There is a particular id that i'm generating every time i do a dataload into that table.
Say out of 10 million rows of data, the id which i'm generating is going to be 100 assuming i'm doing only 100 loads.
Is it worth hash partitioning based on those 100 values?

Is so(even if not) then i want to know how to find out how the data will go to different partition based on this value. What i've seen is all rows having same id used to go to same partion. how to predict which partition will be used assuming i've 8 partitions.
Thanks
Subbu

not really.  100 values won't spread the data out nicely/evenly.  

ops$tkyte@ORA920LAP> create table t ( x int )
  2  partition by hash(x) partitions 8
  3  /

Table created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> insert into t select rownum from all_objects where rownum <= 100;

100 rows created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> declare
  2      l_cnt    number;
  3  begin
  4      for x in ( select partition_name from user_tab_partitions where table_name = 'T' )
  5      loop
  6          execute immediate
  7          'select count(*) from t partition('||x.partition_name||')'
  8          into l_cnt;
  9
 10          dbms_output.put_line( to_char(l_cnt,'999') || ' ' || x.partition_name );
 11      end loop;
 12  end;
 13  /
  11 SYS_P313
  15 SYS_P314
  13 SYS_P315
  10 SYS_P316
   9 SYS_P317
  13 SYS_P318
  12 SYS_P319
  17 SYS_P320

PL/SQL procedure successfully completed.


see sys_p330 -- with 17% of the data, sys_p317 with 9% of the data.  one of the partitions would have 2x the data!

you want an ALMOST unique or unique value to hash on -- i keep saying that

with hashing -- all rows with the same key by definition hash to the same partition -- that is the very essence of hash partitioning. 

Tom

What factors should you take into account when deciding how many partitions to have in a hash partitioned table.

We will be creating a warehouse table that has over 100 Million rows where the partition key will not be unique (but very very close).

This table will be joined to many smaller tables based on the same key. These smaller tables would typically contain around 100k rows. Would you recommend partitioning these as well.

thanks

Hi

Was reading Jonathan Lewis book a couple of days back. It stated that partition elimination does not occur in hash partitions... But from your words it seems that it is possible. Hmmmmm

From optimizer's view how does hash partitioning help to speed up queries? Is hash partitioning suitable in DWH or OLTP systems? It seems to me in DWH (unless ubpartitioned) it is useless. But I dont see much use in OLTP neither (let's forget the administration tasks but focusing on optimizer's view).

Recently I have been in a customer site, they are running a in-house OLTP application using hash partitions and local hash partitioned index. One of the huge tables (around 90 million of rows) is partitioned on a date column, according to customer they partitioned that column because it has quite many distinct values (so far so good, nice for hash partitions) but also they have many queries which does range scan such as

x_date between initial_date and end_date

This doest not seem right to me. It seems like they are thinking hash partitions as they were range partitions? Do you agree? I dont think hash partitioning would improve this kind of queries since most of the time Oracle would have to scan all partitions anyway.

Also I have doubts about how Oracle uses indexes to access hash partitions. Imagine we have local indexes on these hash partitions, if say I want to query rows between first of June and end of June will Oracle needs to scan all index partitions? I.E how are the row values distributed? Does Oracle treat all partitions as a single data structure and scan as the index were B-Tree or it treats them as four data structures (4 B-Tree)? How does Oracle know which index partition it has to scan to obtain appropriate values?

Hi
Your com ments:

"If they have a global range partitioned index on X_DATE, that is dandy. If not, they have likely increased the IO on their system by a factor of N if the index on X_DATE is locally partitioned."

What does dandy mean? Good?
The table is hash partitioned on X_DATE, I dont think we can create a global range partitioned index on X_DATE right? In fact they created a local hash partitioned index. How about creating a plain index, would that have same effect as global range partitioned index?


"If you have access to my new book "Effective Oracle By Design", I actually use an example almost IDENTICAL to what you describe -- and show you how to show it is "wrong" and ultimately how to "fix it" "

The example you are talking about is similar my customer´s case ? By the way I ordered the book 3 weeks ago, waiting for it to arrive.


"Yes, we MUST range scan all index partitions if you have a locally partitioned index on a hash partitioned table and use something other then "=" "

My common sense tells me Oracle has to range scan all partitions if we are not using "=" operator. My scientific sense however doesnt see how. A small example

I hash partition on a dummy column in 4 partitions, numbered sequentially, if I query

select *
from test
where dummy between 1 and 4

How does Oracle find the corresponding rows? Does it hash dummy (1, 2, 3,4) then find the index value thus rowid and then access the table partitions?

Or it scans 4 indexes since dummy is randomly distributed in index partitions?

Hi

Why the I/O can be increased factor of N on a hash partitioned table if the index on is locally partitioned?

Imagine we have 8 hash table partitions and 4 hash index partitions (local of course since there is no such hash partitioned global index concept). Partitioned by a date column, order_date if we query

order_date = to_date('20031010', 'YYYYMMDD')

we would eliminate unnecesary partitions

if we query

order_date between to_date('20031010', 'YYYYMMDD')
and to_date('20031110', 'YYYYMMDD')

we would scan all 4 index partitions, but where is the factor of N?

Finally which index would you use, a plain index or a range partitioned index on order_date?

the table being hash partitioned only contains a pk and a LOB?

the LOB is just a pointer, so excluding managability issues, would a query that looks like select lob from table pk_col=:x have any benefit from partition pruning on the pk?

I suspect that a traditional B-Tree for this same row might be faster or negligible with regards to the difference.
I am sure this is why you say that for a query of a transactional nature partition pruning with hash partitions will provide no benefit.

Tom, Recently we partitioned few tables by range on column id. After that the data loading got slower becase the paritioned key is not used in the query which involves the processing of data load.
I'm thinking of atleast having a partitioned index on the column which they are using in the processing of data load.
I found that i can't do global hash partition but if i do local then will it help as the partition is still going to be based on the key i used in table partition.
Thanks

CREATE TABLE part.P_UNIT
(
UV_ID NUMBER(9) NOT NULL,
P_ID NUMBER(9) NOT NULL,
C_ID NUMBER(9) NOT NULL,
U_ID NUMBER(9) NOT NULL
)
TABLESPACE PART_DEF2
PARTITION BY RANGE (P_ID)
(
partition p1 values less than (100) ,
partition p2 values less than (200) ,
partition p3 values less than (300) ,
partition p4 values less than (400) )
/

create index part.part_test_ind on part.P_UNIT(u_id)
tablespace PART_DEF2
global
/

The above script created the range partitioned table and my index on u_id is global.

so queries like

select * from part.P_UNIT
where p_id=90;

will run faster.

Will partitioning the index help with queries like

select * from part.P_UNIT where
u_id < 100 ;

Note that i partitioned the table based on p_id

If i create local index will that help? for the above query.

Can i create global hash partitioned index on u_id (i'm on version 8.1.7.4) which i know is not possible.
So what best can be done in this case where i've created partitioned table and got 2 queries based on 2 different ids but the partitioning is on only one of them.

How to make both queries to run efficiently.

excellent

Hi

Is it a good idea using a composite key as partiotning key (HASH)?

Tom,

Partitioning is advertised as improving administration by breaking the table up into manageable chunks.

In our OLTP environment we have a 74G/277M row table.
I plan on partitioning this table into 16, 32, or 64 hash partitions (based on non-unique account_id) which will improve managing the table...
...but this table has 11 indexes.
Any time we do anything to a partition that would invalidate the indexes we will have to rebuild at least 10 of the indexes globally which could take days.
This is a bitter-sweet situation for implementing partitioning.

Could you comment on this, please?

Thanks for all your help!

Robert.

Tom,

Small amount of data!? ... I thought this was a lot in an OLTP environement... :) ...but anyway...
We have a requirement to archive old data (e.g. 18 months) off of this table periodically. (insert into archive table, then delete from live table). I figured that after archiving data in this manner a few times, we would need to reorg the live table.

What do you think?

Thanks,

Robert.

PS. Will definitely look into 'maintain global indexes'.

Tom,

I am partitioning on 'account_id' but I will be archiving by some date field.... so my deletes will be honeycombed throughout all partitions.
Could you please address this scenario?

Thanks,

Robert.

Tom,

1) We cannot partition by our date field... 70% of the queries against this table are OLTP direct hits on the account_id... therefore for performance we *must* partition by this account_id field... to partition by our archiving date field would cause these online OLTP queries to run poorly... would it not? (I do plan on benchmarking this, however, in order to prove it).
2) Why do I want to partition? .... To be able better manage the reorging that must eventually occur after much archiving/deleting from this table.

Is this not sound?

Thanks,

Robert.

Tom,

You've convinced me to try partitioning by archiving date... however I don't think we can RANGE partition the account_id index because the distribution is not even.

1) Would HASH partitioning (or even NOT partitioning) the account_id index work as well?

2) I plan to test and benchmark whatever I do on a subset of the table which is approx 10% the size of the real one. Do you think this 10% test table could give reasonable results?

Thanks,

Robert.

Tom,

This has reference to your book "Effective Oracle by Design" Page 183.

I would appreciate if you could clarify the following :
(4*2+6*3+1) = 27
1 is for the table access by rowid --no problems
But the others ,
It has to probe 10 partitioned indexes -so 10*3 or 10*2 or 4*2+6*3 .
Depending upon the height of the index it will read the root then branch .
So it should be either do 1 or 2 LIO to find out that the row does not exists in this partition .
Why will it ever have go to the leaf block ?
And if it goes then for what information ?

I did the same test :
the results are :
Statistics
----------------------------------------------------------
0 recursive calls
30 db block gets
27 consistent gets
0 physical reads
0 redo size
661 bytes sent via SQL*Net to client
311 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed

Why am i getting these 30 db_block_gets ?
Also ,how do I find out the height of all the partitioned indexes ?

Thanks

Tom ,

Here are my findings :

* My DB is 8.1.7.3 and it is DMT
* All indexes have been analyzed using analyze table <table_name> estimate statistics for table for all indexes for all indexed columns

1.select * from big_hash where owner = 'USA'

Index Range Scan

0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
662 bytes sent via SQL*Net to client
210 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Now I change the parallelism on the index from noparallel to parallel .
1 0 PARTITION HASH* (ALL) :Q636910
000

2 1 TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'ABIG_HASH_ID' ( :Q636910
Cost=122 Card=1273 Bytes=114570) 000

3 2 INDEX* (RANGE SCAN) OF 'ABIG_ID' (NON-UNIQUE) (Cost=23 :Q636910
Card=1273) 000



1 PARALLEL_COMBINED_WITH_PARENT
2 PARALLEL_TO_SERIAL SELECT /*+ Q636910000 NO_EXPAND INDEX(A1 "ABIG_ID") */ OBJECT_NAME",A1."OBJECT_ID", A1."DATA_OBJECT_ID", A1."OBJECT_TYPE",A1."CREATED"
,A1."LAST_DDL_TIME",A1."TIMESTAMP",A1."STATUS",A1."TEMPORARY",A1."GENERATED",A1."SECONDARY" FROM "ABIG_HASH_ID" PX_GRANULE(0, PARTITION, DYNAMIC) A1 WHERE A1."OWNER"='USA'
3 PARALLEL_COMBINED_WITH_PARENT
tatistics
---------------------------------------------------------
221 recursive calls
38 db block gets
78 consistent gets
4 physical reads
652 redo size
661 bytes sent via SQL*Net to client
211 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
1 rows processed

Why do I see the 38 db block gets and 652 redo size ?

2.Based on your earlier comment can I conclude that it is always not necessary for oracle to go to the leaf block of the Index if it can get the answer from the root and branch blocks.--Only is cases where it will not find a rowid in the table .

3.How do I find out the height of all the partitions of any local indexes ie someting like validate index < >


Thanks a lot !

Tom,

I hash partitioned the big table on owner and created a local index on owner .I have also analyzed it.

select /*+ INDEX(A) */ * from big_hash a where owner = 'USA'


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

Pstart 5
Pstop 5

1. Why only 2 LIO ?
One is for table and one for index.
This means that it found the rowid of the record from the root block ?
How is this possible ?

2.How does the optimizer know that this record is in the fifth partition ?
This whole operation is done using only one LIO .

Thanks again ,

Tom,
Do you think an IOT (WH) with 10+ million rows improves perfomrance by hash partitioning ? (Just looking for the peformance only..)
Query always uses primary key. I am thinking that index height would be less than regular table thus reduce some IO.
Please advise.
Ramesh.

Do you think it is advisable hash cluster if we do not know number of keys.

Hi,
We've a table with batch id and a book id and each combinations can have 6 mil records or some of the other book id will have less number of records

for ex.

Batch ID Book ID Number of records
101 1001 6 mil record
102 1002 150 rows
103 1003 700 rows
104 1001 6 mil records.

As you can see 1001 book id has maximum number of records and other book Id will have less number of records.

We'd hash partitioned this table on Batch ID column with 50 partitions. Some of the partitions has more than one batch IDs in it . One partition has batch id 101 and 104 which are heavy volume batch ids.

Is there anyway to make sure that they go into different partitions?. We're on 9.2.0.6.

All other sub systems which pulls our data uses batch_id in their query.

thanks

Tom,

Can you provide some insight on data load into a hash partitioned table.

Currently we have a LIST partition (on product). Into this table we load the data from flat files - using sql loader. When I tested the same data load into Hash partitioned table...it has taken more time. I guess this is bcoz, for each record Oracle needs to apply hashing algorithm and decide the partition into which records needs to be sent.
**Is there any way to improve the load speed in this case. we use conventional load.

Also, ours is RAC with 4 instances. What we currently do is... we distribute all the files into 4 groups and do the load from all the 4 instances (we maintain data files based on products - so just by looking at the file we can come know into which partition entire file (its records) would be put in). We choose this files distribution method in order to avoid partition level contention. --- suppose if two files of same product are being loaded by 2 instances simultaneously (obviously into a same partition) due to partition level contention it takes more time. Currently load time is truly impressive.

But now the situation is changed..instead of list partition on product, we want to go for hash partition on account_id.

Even loading from single instance itself is taking more time...but how come we do parallel data load from all the 4 instances - since it is hash on account_id we would not know into which a record could be inserted. any help that you provide would be truly appreciated.

Many Thanks,
Karteek

Hi Tom,

I was trying to find out hashing algorithm used by Oracle to allocate rows to a particular hash partition. Then I stumbled upon ORA_HASH function.

I observed below behavior

Consider, these 4 variables (part_col, no_of_part, bucket_size, prev_2_power)

part_col be candidate partitioning column

no_of_part is the number of partitions we are considering

bucket_size is derived from no_of_part as follows:

if no_of_part is powers of 2(2,4,8,16...) then
bucket_size = no_of_part - 1

else if no_of_part not powers of 2 then
bucket_size = "next higher power of 2" - 1
for eg, if you have 24 no_of_part then (32 -1) = 31

prev_2_power is no_of_part if its a power of 2 number else its previous power of 2. for ex. for 24 partitions, 16 would be prev_2_power

THEN
Partition Number =

case
ora_hash(part_col, bucket_size)+ 1 > no_of_part
then 
ora_hash(part_col, bucket_size)+ 1 - prev_2_power
else
ora_hash(part_col, bucket_size)+ 1 /* +1 since bucket start with 0 but partitions start with 1 */
end;

I tested above algo for couple of cases and it worked correct. Also it explains why there is skew if we dont use power of 2 partitions. as anyways oracle bucket data into next 2power number of buckets and then just merges them using above logic to make desired no of partitions.

Above expression can be used along with a group by to evaluate partitioning skew before actually creating partitioned table.

I am not sure though that if this the logic or documented somewhere but seems to hold good.

Is hash partition the index on a date column (that has unique values inserted everytime there is an insert/update) a good idea to minimize/eliminate gc buffer busy waits in a two node RAC (10.2.0.4.0)? Thanks.

Thank you for the response.
Inserts on that table take about 10 minutes to complete during the busy hours - we see 40% of the dbtime on gc buffer busy wait. what can we do to alleviate it? reverse key index also seems to be bad as we do a lot of range scan on the date column. Thanks.

Tom,
sorry for going slightly off-topic, but you stated:
> Most people set their connection pools way way way too high (higher than say 4 times the number of cpus)

Where did you get this number from? Could you suggest any further reading on how to size a connection pool? It's just that all of the production configurations I've seen so far are way higher than 4 times the number of cpus.

Cheers,
Uwe

Tom,
probably I haven't made myself clear enough. I'm aware of the facts and principles you mention and I also know how do "do math". My two points are:
1. To do the math, I need numbers. Actually, I'm looking for guidelines to find those numbers (like, how does one find out how much concurrent requests a cpu can handle).
2. I didn't want to say "the facts are against you" but "I find that many connection pools SEEM oversized to me, and I need to find information on how to scientifically PROVE they are oversized."

Kind regards,
Uwe

Here's one reason connection pools are oversized: connections tend to be held without being used.

Suppose I hold a connection for one second and actually give the database work for one hundredth of a second: my Application Server could in theory have 100 connections per CPU.

A good DBA comes along and says "all these connections are hardly being used, downsize your connection pool." So the J2EE admin reduces the connection pool to 5, and my throughput goes down to 5 transactions per second...

You may ask why I am holding a connection without using it? The answer is: I, the application developer, am not holding the connection, I am closing it. However, the Application Server is not really putting the connection back into the pool, it is holding it for me in case I ask for it again. The connection only goes back in the pool when I have done all my other work and exited.

Why on earth does the Application Server do this? Because the connection is configured as "shareable".

Here is an excerpt from an IBM article on the subject:
"When the application closes a shareable connection, the connection is not truly closed, nor is it returned to the Free pool. Rather, it remains in the Shared connection pool, ready for another request.." http://www.ibm.com/developerworks/websphere/library/techarticles/0506_johnsen/0506_johnsen.html "Shareable" is the default configuration for Websphere AND Weblogic!

I learned this last year the hard way.

The moral of the story: Application Server configuration is just as important as coding practice, for good or ill.

Tom,
that seminar you mention looks pretty interesting. I'll be there, hoping to gain some more insights on that topic. And some figures to argue over with AppServer admins.
Kind regards,
Uwe

1)

UPDATE ABCPROD.ABC_XYZ set MSISDN_STATUS_ID=:1 ,UPDATED_DATE=SYSDATE, UPDATED_BY=:"SYS_B_0" where CIRCLE_ID=:2 and MSISDN_NUMBER=:3 and IS_ACTIVE=:"SYS_B_1" AND MSISDN_STATUS_ID IN(:"SYS_B_2",:"SYS_B_3",:"SYS_B_4");

This table consists of list partitioning on CIRCLE_ID. Below is the execution plan of above statement.

-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 3 (100)| | | |
| 1 | UPDATE | ABC_XYZ | | | | | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| ABC_XYZ | 1 | 49 | 3 (0)| 00:00:01 | ROW L | ROW L |
|* 3 | INDEX UNIQUE SCAN | UK_C0012643_UK | 1 | | 2 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------------

1)As you can see,it is not using the partition,i think that,since it is a bind variable. optimizer is not able to use the partition.

If i hard core the circle_id it is using the partition.

UPDATE ABCPROD.ABC_XYZ set MSISDN_STATUS_ID=:1 ,UPDATED_DATE=SYSDATE, UPDATED_BY=:"SYS_B_0" where CIRCLE_ID=04 and MSISDN_NUMBER=:3 and IS_ACTIVE=:"SYS_B_1" AND MSISDN_STATUS_ID IN(:"SYS_B_2",:"SYS_B_3",:"SYS_B_4");


-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 56 | 3 (0)| 00:00:01 | | |
| 1 | UPDATE | ABC_XYZ | | | | | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| ABC_XYZ | 1 | 56 | 3 (0)| 00:00:01 | 4 | 4 |
|* 3 | INDEX UNIQUE SCAN | UK_C0012643_UK | 1 | | 2 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------------

How to overcome this. how can i force the optimizer to use the partition?

Hi Tom,

Pls help me with this query..

SELECT * FROM ( SELECT unbar_data.unbar_data_request_id AS unbarreqid,unbar_data.sim_number AS simnumber,
unbar_data.msisdn_number AS msisdnnumber,
unbar_data.retailer_org_id AS retailerorgid,
unbar_data.dist_org_id AS distorgid,
unbar_data.api_code AS apicode,
unbar_data.initiated_by_number AS initiatedby,
account_info_id,
simdtl.imsi_no,
COALESCE (zon.zone_description,
(SELECT circle_name FROM ABCPROD.ABC_cmn_mst_circle WHERE circle_id = unbar_data.circle_id))
ZONE_DESC FROM
ABCPROD.ABC_crs_trn_cad_unbar_data unbar_data JOIN ABCPROD.ABC_invt_mst_item_msisdn_dtl msisdn ON
(msisdn.msisdn_number=unbar_data.msisdn_number) JOIN ABCPROD.ABC_invt_sim_msisdn_asso asso ON (msisdn.msisdn_dtl_id=asso.msisdn_id) JOIN
ABCPROD.ABC_invt_mst_sim_dtl simdtl ON (simdtl.sim_dtl_id = asso.sim_dtl_id AND simdtl.sim_no || simdtl.sim_no_checksum = unbar_data.sim_number ) JOIN
ABCPROD.ABC_cmn_mst_ent_org org ON (org.ent_org_id = unbar_data.dist_org_id ) LEFT JOIN ABCPROD.ABC_cmn_mst_zone zon ON
(zon.zone_id = org.zone_id AND zon.circle_id = org.circle_id ) WHERE unbar_data.circle_id =:1 AND msisdn.circle_id = unbar_data.circle_id AND
asso.circle_id = msisdn.circle_id AND org.circle_id = asso.circle_id AND unbar_status = :"SYS_B_0" AND hac1_status = :"SYS_B_1"
AND ROWNUM <= :"SYS_B_2" AND retry_count <= :"SYS_B_3" AND asso.is_paired = :"SYS_B_4" AND msisdn.is_active = :"SYS_B_5" AND
unbar_data.created_date > (SYSDATE - :"SYS_B_6") ORDER BY created_date) A;




PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------
Plan hash value: 2146329685

------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 170 | 9137 (3)| 00:01:07 | | |
| 1 | TABLE ACCESS BY INDEX ROWID | ABC_CMN_MST_CIRCLE | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 2 | INDEX UNIQUE SCAN | PKCIRCLEID | 1 | | 0 (0)| 00:00:01 | | |
| 3 | VIEW | | 1 | 170 | 9137 (3)| 00:01:07 | | |
| 4 | SORT ORDER BY | | 1 | 221 | 9137 (3)| 00:01:07 | | |
|* 5 | COUNT STOPKEY | | | | | | | |
| 6 | NESTED LOOPS OUTER | | 1 | 221 | 9136 (3)| 00:01:07 | | |
| 7 | NESTED LOOPS | | 1 | 191 | 9135 (3)| 00:01:07 | | |
| 8 | NESTED LOOPS | | 1 | 176 | 9133 (3)| 00:01:07 | | |
|* 9 | HASH JOIN | | 1500 | 188K| 6131 (4)| 00:00:45 | | |
|* 10 | HASH JOIN | | 1424 | 141K| 2863 (3)| 00:00:21 | | |
| 11 | TABLE ACCESS BY INDEX ROWID | ABC_CRS_TRN_CAD_UNBAR_DATA | 1424 | 115K| 260 (0)| 00:00:02 | | |
|* 12 | INDEX RANGE SCAN | IDX_CRS_UNBAR_US_HS_CI_CD_RC | 256 | | 12 (0)| 00:00:01 | | |
| 13 | PARTITION LIST SINGLE | | 2911K| 52M| 2579 (3)| 00:00:19 | KEY | KEY |
|* 14 | TABLE ACCESS FULL | ABC_INVT_MST_ITEM_MSISDN_DTL | 2911K| 52M| 2579 (3)| 00:00:19 | KEY | KEY |
| 15 | PARTITION LIST SINGLE | | 5337K| 137M| 3227 (4)| 00:00:24 | KEY | KEY |
|* 16 | TABLE ACCESS FULL | ABC_INVT_SIM_MSISDN_ASSO | 5337K| 137M| 3227 (4)| 00:00:24 | KEY | KEY |
|* 17 | TABLE ACCESS BY GLOBAL INDEX ROWID| ABC_INVT_MST_SIM_DTL | 1 | 47 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 18 | INDEX UNIQUE SCAN | PK_SIM_DTL_ID | 1 | | 1 (0)| 00:00:01 | | |
|* 19 | TABLE ACCESS BY INDEX ROWID | ABC_CMN_MST_ENT_ORG | 1 | 15 | 2 (0)| 00:00:01 | | |
|* 20 | INDEX UNIQUE SCAN | PK_C0012205_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 21 | TABLE ACCESS BY INDEX ROWID | ABC_CMN_MST_ZONE | 1 | 30 | 1 (0)| 00:00:01 | | |
|* 22 | INDEX UNIQUE SCAN | PK_ZONE_ID | 1 | | 0 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------------------


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

2 - access("CIRCLE_ID"=:B1)
5 - filter(ROWNUM<=TO_NUMBER(:SYS_B_2))
9 - access("ASSO"."CIRCLE_ID"="MSISDN"."CIRCLE_ID" AND "MSISDN"."MSISDN_DTL_ID"="ASSO"."MSISDN_ID")
10 - access("MSISDN"."CIRCLE_ID"="UNBAR_DATA"."CIRCLE_ID" AND "MSISDN"."MSISDN_NUMBER"="UNBAR_DATA"."MSISDN_NUMBER")
12 - access("UNBAR_DATA"."UNBAR_STATUS"=:SYS_B_0 AND "UNBAR_DATA"."HAC1_STATUS"=:SYS_B_1 AND
"UNBAR_DATA"."CIRCLE_ID"=TO_NUMBER(:1) AND "UNBAR_DATA"."CREATED_DATE">SYSDATE@!-TO_NUMBER(:SYS_B_6) AND
"UNBAR_DATA"."CREATED_DATE" IS NOT NULL)
filter("UNBAR_DATA"."RETRY_COUNT"<=TO_NUMBER(:SYS_B_3))
14 - filter("MSISDN"."IS_ACTIVE"=:SYS_B_5)
16 - filter("ASSO"."IS_PAIRED"=:SYS_B_4)
17 - filter("UNBAR_DATA"."SIM_NUMBER"="SIMDTL"."SIM_NO"||TO_CHAR("SIMDTL"."SIM_NO_CHECKSUM"))
18 - access("SIMDTL"."SIM_DTL_ID"="ASSO"."SIM_DTL_ID")
19 - filter("ORG"."CIRCLE_ID"=TO_NUMBER(:1))
20 - access("ORG"."ENT_ORG_ID"="UNBAR_DATA"."DIST_ORG_ID")
21 - filter("ZON"."CIRCLE_ID"(+)=TO_NUMBER(:1))
22 - access("ZON"."ZONE_ID"(+)="ORG"."ZONE_ID")



----> ABC_INVT_MST_ITEM_MSISDN_DTL(14 column) and ABC_INVT_SIM_MSISDN_ASSO(16 column) are having partitions but full table scan is increasing the cost.
IS_ACTIVE column on ABC_INVT_MST_ITEM_MSISDN_DTL table has only two values ('Y' and 'N') and same with IS_PAIRED column too. I am planning to create
local bitmap index on partitioned tables. but it is an OLTP environment. So can you suggest any querychane or something else on this. Coz this is causing a lot of issue in the database.

Hi Tom,

I have a table as defined below:

SYS@VDDS1 SQL> desc vdds.stb_config_string
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 STB_CONFIG_ID                             NOT NULL NUMBER
 PARAM_KEY                                 NOT NULL VARCHAR2(1000)
 PARAM_VALUE                                        VARCHAR2(2000)
 MOD_DATE                                  NOT NULL TIMESTAMP(6)

with ID is the PK, a value generated from a sequence. The insert rate is very high but not using batch(we have to insert one row at a time), and most of the queries are using the STB_CONFIG_ID in the where clause. For example:

delete stb_config_string where stb_config_id=:X;

During the performance test, there is high on "gc buffer wait".  Will has partition the table using the PK will improve the performance?

Thanks,
Danny