Home » RDBMS Server » Performance Tuning » poor performance of Exchange partition on composite range-hash tables (Oracle 10.2.0.3)
| poor performance of Exchange partition on composite range-hash tables [message #366327] |
Fri, 12 December 2008 16:57  |
ora_tune
Messages: 4
Registered: December 2008
|
Junior Member |
|
|
We are doing data migration that involves 150 tables. These 150 tables are mix of 140 tables (range only partition) and 10 (composite i.e range-hash partition). We achieve this using 'alter table exchange partition' method.
It is noticed that the cpu activity is quite high (more than 30 seconds) for the 10 tables as compared to the other 140 tables (which takes less than a second).
The trace file for this whole migration process of 150 tables includes the following statement for those 10 composite range-hash partition tables.
update histgrm$ set obj#= decode(obj#,: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,:37,:38,:39,:40,:41,:42,:43,:44,:45,:46,:47,:48,:49,:50,
:51,:52,:53,:54,:55,:56,:57,:58,:59,:60,:61,:62,:63,:64,:65,:66,:67,:68,:69,
:70,:71,:72,:73,:74,:75,:76,:77,:78,:79,:80)
WHERE
obj# in (:1,:3,:5,:7,:9,:11,:13,:15,:17,:19,:21,:23,:25,:27,:29,:31,:33,:35,
:37,:39,:41,:43,:45,:47,:49,:51,:53,:55,:57,:59,:61,:63,:65,:67,:69,:71,:73,
:75,:77,:79)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.00 0.00 0 0 0 0
Execute 10 36.72 80.15 20200 281573 737038 80127
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 36.72 80.15 20200 281573 737038 80127
Why is the cpu activity high for range-hash tables as compared to range only tables. As per oracle documentation, Oracle tricks by only switching the pointers in the data dictionary and not the actual rows(irrespective of the number of rows processed for migration). IS there any way to improve performance for the range-hash tables ?? IS this got anything to do with the large size of C_OBJ#_INTCOL# cluster ?
Please help.
[Updated on: Fri, 12 December 2008 16:59] Report message to a moderator |
|
|
|
| Re: poor performance of Exchange partition on composite range-hash tables [message #376085 is a reply to message #366327] |
Tue, 16 December 2008 00:21  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Do you really have 40 hash sub-partitions per range partition. Seems like a lot.
According to the trace, it is updating 80,000 rows and you executed this 10 times; thats 8000 rows per execution.
At 40 sub-partitions per partition, thats 200 rows updated per partition.
Say you have a table with 20 columns, then your histogram would have 10 buckets.
All this sounds pretty reasonable. If you wanted to reduce the amount of time taken, you could either reduce the number of columns with histograms (some columns won't benefit) and/or reduce the number of hash sub-partitions (40 seems like a lot).
It is also possible that Oracle is performing a full table scan of the histgrm$ table. When you exchange a range partitioned table, it would pass in only a single OBJ# and choose an indexed scan, but your sub-partitions are passing in 40 OBJ# and POSSIBLY performing a full scan. The IO certainly seems a bit high for it to have just processed the 80000 rows by an index.
Unfortunately, it is difficult to control the execution plan for recursive SQL such as this. You could try creating a stored outline (search Plan Stability), but I have had trouble getting this to work for recursive SQL.
Ross Leishman
|
|
|
|
| Re: poor performance of Exchange partition on composite range-hash tables [message #376269 is a reply to message #376085] |
Tue, 16 December 2008 14:07 |
ora_tune
Messages: 4
Registered: December 2008
|
Junior Member |
|
|
Hi Ross,
Thanks for your valuable feedback.
I have pasted the complete TKPROF output for the 140 (Range) and 10 (Range-Hash) tables with details on update SQL statement update of hist_head$ and histgrm$ for both (range and range-hash).
You can also see that NO Full table scan is done but Index scan is used (which should be good). Look at the "time=44947498". Isn't that that culprit ??. But why is it taking so much time ??
*******************************************************************************************
-- TKPROF output on histogram updates for the 140 range partitioned tables
*******************************************************************************************
update hist_head$ set obj#= decode(obj#,:1,:2,:3,:4)
WHERE obj# in (:1,:3)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 140 0.00 0.04 0 0 0 0
Execute 140 2.21 2.80 22 3207 57159 4230
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 280 2.22 2.85 22 3207 57159 4230
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE HIST_HEAD$ (cr=13 pr=3 pw=0 time=41838 us)
14 INLIST ITERATOR (cr=13 pr=0 pw=0 time=213 us)
14 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=13 pr=0 pw=0 time=651 us)(object id 257)
update histgrm$ set obj#= decode(obj#,:1,:2,:3,:4)
WHERE obj# in (:1,:3)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 140 0.01 0.02 0 0 0 0
Execute 140 5.14 10.96 945 34852 108047 9780
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 280 5.16 10.98 945 34852 108047 9780
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE HISTGRM$ (cr=14 pr=8 pw=0 time=78036 us)
0 INLIST ITERATOR (cr=14 pr=8 pw=0 time=78022 us)
0 TABLE ACCESS CLUSTER HISTGRM$ (cr=14 pr=8 pw=0 time=77994 us)
7 INDEX RANGE SCAN I_OBJ#_INTCOL# (cr=7 pr=1 pw=0 time=6793 us)(object id 252)
****************************************************************************************
-- TKPROF output on histogram updates for the 10 Composite range-hash partitioned tables
****************************************************************************************
update hist_head$ set obj#= decode(obj#,: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,:37,:38,:39,:40,:41,:42,:43,:44,:45,:46,:47,:48,:49,
:50,:51,:52,:53,:54,:55,:56,:57,:58,:59,:60,:61,:62,:63,:64,:65,:66,:67,:68,
:69,:70,:71,:72,:73,:74,:75,:76,:77,:78,:79,:80)
WHERE
obj# in (:1,:3,:5,:7,:9,:11,:13,:15,:17,:19,:21,:23,:25,:27,:29,:31,:33,:35,
:37,:39,:41,:43,:45,:47,:49,:51,:53,:55,:57,:59,:61,:63,:65,:67,:69,:71,:73,
:75,:77,:79)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.00 0.00 0 0 0 0
Execute 10 2.09 2.35 22 3976 51676 3720
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 2.09 2.35 22 3976 51676 3720
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE HIST_HEAD$ (cr=301 pr=9 pw=0 time=209986 us)
260 INLIST ITERATOR (cr=295 pr=0 pw=0 time=2192 us)
260 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=295 pr=0 pw=0 time=17651 us)(object id 257)
update histgrm$ set obj#= decode(obj#,: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,:37,:38,:39,:40,:41,:42,:43,:44,:45,:46,:47,:48,:49,:50,
:51,:52,:53,:54,:55,:56,:57,:58,:59,:60,:61,:62,:63,:64,:65,:66,:67,:68,:69,
:70,:71,:72,:73,:74,:75,:76,:77,:78,:79,:80)
WHERE
obj# in (:1,:3,:5,:7,:9,:11,:13,:15,:17,:19,:21,:23,:25,:27,:29,:31,:33,:35,
:37,:39,:41,:43,:45,:47,:49,:51,:53,:55,:57,:59,:61,:63,:65,:67,:69,:71,:73,
:75,:77,:79)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.00 0.00 0 0 0 0
Execute 10 36.72 80.15 20200 281573 737038 80127
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 36.72 80.15 20200 281573 737038 80127
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE HISTGRM$ (cr=6289 pr=1404 pw=0 time=9815300 us)
1203 INLIST ITERATOR (cr=1076 pr=478 pw=0 time=6875601 us)
1203 TABLE ACCESS CLUSTER HISTGRM$ (cr=1076 pr=478 pw=0 time=44947498 us)
260 INDEX RANGE SCAN I_OBJ#_INTCOL# (cr=257 pr=0 pw=0 time=13943 us)(object id 252)
thanks
[Updated on: Tue, 16 December 2008 14:10] Report message to a moderator |
|
|
|
| Re: poor performance of Exchange partition on composite range-hash tables [message #376304 is a reply to message #376269] |
Tue, 16 December 2008 21:22 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If you enclosed your dump in CODE tags, it would be easier for me to read.
So the range partitioned tables took 5 seconds to update 10,000 histogram rows, and the composite partitioned tables took 36 seconds to update 80,000 rows.
That looks entirely consistent to me. You increase the volume of work by 8 times and the runtime increases 8 times.
It's not like Oracle is doing it "better" for the range partitioned tables, it's just that there is less information to update.
What worries me a bit is the IO:
Disk Query Current Rows
----- ------ ------- -----
20200 281573 737038 80127
This tells me that Oracle read about 1 million blocks in order to update 80,000 rows. Thats a ratio of 10 blocks per row.
The only explanations I can think of for such high IO is:
- Recursive SQL (eg. Triggers, foreign keys, other DD)
- Chaining in the data dictionary table
- Highly fragmented index
Are you able to test this in a completely separate database that is not a physical copy of this database (an export/import would do, just not a physical block-wise copy)? If you can try it on another database, then determine whether you get the same IO ratio (10 blocks per row).
If the ratio is much smaller on another database, it is is either chaining or fragmentation. Index fragmentation is MUCH more likely. Your DBA should be able to rebuild the index to defragment it.
Ross Leishman
|
|
|
|
| Re: poor performance of Exchange partition on composite range-hash tables [message #376514 is a reply to message #376304] |
Wed, 17 December 2008 12:07 |
ora_tune
Messages: 4
Registered: December 2008
|
Junior Member |
|
|
*******************************************************************************************
-- TKPROF output on histogram updates for the 140 range partitioned tables
*******************************************************************************************
update hist_head$ set obj#= decode(obj#,:1,:2,:3,:4)
WHERE obj# in (:1,:3)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 140 0.00 0.04 0 0 0 0
Execute 140 2.21 2.80 22 3207 57159 4230
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 280 2.22 2.85 22 3207 57159 4230
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE HIST_HEAD$ (cr=13 pr=3 pw=0 time=41838 us)
14 INLIST ITERATOR (cr=13 pr=0 pw=0 time=213 us)
14 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=13 pr=0 pw=0 time=651 us)(object id 257)
update histgrm$ set obj#= decode(obj#,:1,:2,:3,:4)
WHERE obj# in (:1,:3)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 140 0.01 0.02 0 0 0 0
Execute 140 5.14 10.96 945 34852 108047 9780
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 280 5.16 10.98 945 34852 108047 9780
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE HISTGRM$ (cr=14 pr=8 pw=0 time=78036 us)
0 INLIST ITERATOR (cr=14 pr=8 pw=0 time=78022 us)
[COLOR=darkblue] 0 TABLE ACCESS CLUSTER HISTGRM$ (cr=14 pr=8 pw=0 time=77994 us)
7 INDEX RANGE SCAN I_OBJ#_INTCOL# (cr=7 pr=1 pw=0 time=6793 us)(object id 252)[/COLOR]
****************************************************************************************
-- TKPROF output on histogram updates for the 10 Composite range-hash partitioned tables
****************************************************************************************
update hist_head$ set obj#= decode(obj#,: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,:37,:38,:39,:40,:41,:42,:43,:44,:45,:46,:47,:48,:49,
:50,:51,:52,:53,:54,:55,:56,:57,:58,:59,:60,:61,:62,:63,:64,:65,:66,:67,:68,
:69,:70,:71,:72,:73,:74,:75,:76,:77,:78,:79,:80)
WHERE
obj# in (:1,:3,:5,:7,:9,:11,:13,:15,:17,:19,:21,:23,:25,:27,:29,:31,:33,:35,
:37,:39,:41,:43,:45,:47,:49,:51,:53,:55,:57,:59,:61,:63,:65,:67,:69,:71,:73,
:75,:77,:79)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.00 0.00 0 0 0 0
Execute 10 2.09 2.35 22 3976 51676 3720
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 2.09 2.35 22 3976 51676 3720
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE HIST_HEAD$ (cr=301 pr=9 pw=0 time=209986 us)
260 INLIST ITERATOR (cr=295 pr=0 pw=0 time=2192 us)
260 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=295 pr=0 pw=0 time=17651 us)(object id 257)
update histgrm$ set obj#= decode(obj#,: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,:37,:38,:39,:40,:41,:42,:43,:44,:45,:46,:47,:48,:49,:50,
:51,:52,:53,:54,:55,:56,:57,:58,:59,:60,:61,:62,:63,:64,:65,:66,:67,:68,:69,
:70,:71,:72,:73,:74,:75,:76,:77,:78,:79,:80)
WHERE
obj# in (:1,:3,:5,:7,:9,:11,:13,:15,:17,:19,:21,:23,:25,:27,:29,:31,:33,:35,
:37,:39,:41,:43,:45,:47,:49,:51,:53,:55,:57,:59,:61,:63,:65,:67,:69,:71,:73,
:75,:77,:79)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.00 0.00 0 0 0 0
Execute 10 36.72 80.15 20200 281573 737038 80127
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 36.72 80.15 20200 281573 737038 80127
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE HISTGRM$ (cr=6289 pr=1404 pw=0 time=9815300 us)
1203 INLIST ITERATOR (cr=1076 pr=478 pw=0 time=6875601 us)
[COLOR=darkblue] 1203 TABLE ACCESS CLUSTER HISTGRM$ (cr=1076 pr=478 pw=0 time=44947498 us)
260 INDEX RANGE SCAN I_OBJ#_INTCOL# (cr=257 pr=0 pw=0 time=13943 us)(object id 252)[/COLOR]
If you compare the above two highlighted (blue) statements, you will notice that the Index of the Cluster (I_OBJ#_INTCOL#), NOT the Index of the histgrm$ table (I_H_OBJ#_COL#) is used but anyways, not much time is being spent here. The most time is spent in "TABLE ACCESS CLUSTER HISTGRM$". Why is so much time being spent on TABLE ACCESS (of cluster) when the INDEX RANGE SCAN (also, of the cluster) is already being done ??
if you look at the rows for each line :
0 UPDATE HISTGRM$ (cr=6289 pr=1404 pw=0 time=9815300 us)
1203 INLIST ITERATOR (cr=1076 pr=478 pw=0 time=6875601 us)
1203 TABLE ACCESS CLUSTER HISTGRM$ (cr=1076 pr=478 pw=0 time=44947498 us)
260 INDEX RANGE SCAN I_OBJ#_INTCOL# (cr=257 pr=0 pw=0 time=13943 us)(object id 252)
Why in the end "update histgrm$" has 0 rows and what do the other rows mean as they dont match with the number of fetched rows. Does it mean that after all this effort NOTHING is updated. Its the same for the other above updates too.
Also another interesting observation from the RAW trace file :
-- For the 140 tables
PARSING IN CURSOR #72 len=331 dep=2 uid=0 oct=6 lid=0 tim=1199652494163464 hv=2997034431 ad='72ceaf20'
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
END OF STMT
PARSE #72:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1199652494163460
EXEC #72:c=0,e=211,p=0,cr=5,cu=1,mis=0,r=1,dep=2,og=4,tim=1199652494163791
STAT #72 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE OBJ$ (cr=5 pr=0 pw=0 time=194 us)'
STAT #72 id=2 cnt=1 pid=1 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=5 pr=0 pw=0 time=102 us)'
EXEC #73:c=636903,e=977514,p=45,cr=1701,cu=216,mis=0,r=0,dep=1,og=1,tim=1199652494365412
STAT #75 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE HISTGRM$ (cr=14 pr=8 pw=0 time=78036 us)'
STAT #75 id=2 cnt=0 pid=1 pos=1 obj=0 op='INLIST ITERATOR (cr=14 pr=8 pw=0 time=78022 us)'
STAT #75 id=3 cnt=0 pid=2 pos=1 obj=253 op='TABLE ACCESS CLUSTER HISTGRM$ (cr=14 pr=8 pw=0 time=77994 us)'
STAT #75 id=4 cnt=7 pid=3 pos=1 obj=252 op='INDEX RANGE SCAN I_OBJ#_INTCOL# (cr=7 pr=1 pw=0 time=6793 us)'
STAT #77 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE HIST_HEAD$ (cr=13 pr=3 pw=0 time=41838 us)'
STAT #77 id=2 cnt=14 pid=1 pos=1 obj=0 op='INLIST ITERATOR (cr=13 pr=0 pw=0 time=213 us)'
STAT #77 id=3 cnt=14 pid=2 pos=1 obj=257 op='INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=13 pr=0 pw=0 time=651 us)'
XCTEND rlbk=0, rd_only=1
-- For the 10 tables
PARSING IN CURSOR #78 len=331 dep=2 uid=0 oct=6 lid=0 tim=1199652505061860 hv=2997034431 ad='72ceaf20'
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
END OF STMT
PARSE #78:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1199652505061857
EXEC #78:c=999,e=1640,p=0,cr=56,cu=1,mis=0,r=1,dep=2,og=4,tim=1199652505063616
STAT #78 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE OBJ$ (cr=56 pr=0 pw=0 time=1619 us)'
STAT #78 id=2 cnt=1 pid=1 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=56 pr=0 pw=0 time=1514 us)'
EXEC #79:c=1864716,e=10830261,p=1484,cr=13787,cu=23644,mis=0,r=0,dep=1,og=1,tim=1199652505198427
STAT #73 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE HISTGRM$ (cr=6289 pr=1404 pw=0 time=9815300 us)'
STAT #73 id=2 cnt=1203 pid=1 pos=1 obj=0 op='INLIST ITERATOR (cr=1076 pr=478 pw=0 time=6875601 us)'
STAT #73 id=3 cnt=1203 pid=2 pos=1 obj=253 op='TABLE ACCESS CLUSTER HISTGRM$ (cr=1076 pr=478 pw=0 time=44947498 us)'
STAT #73 id=4 cnt=260 pid=3 pos=1 obj=252 op='INDEX RANGE SCAN I_OBJ#_INTCOL# (cr=257 pr=0 pw=0 time=13943 us)'
STAT #77 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE HIST_HEAD$ (cr=301 pr=9 pw=0 time=209986 us)'
STAT #77 id=2 cnt=260 pid=1 pos=1 obj=0 op='INLIST ITERATOR (cr=295 pr=0 pw=0 time=2192 us)'
STAT #77 id=3 cnt=260 pid=2 pos=1 obj=257 op='INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=295 pr=0 pw=0 time=17651 us)'
XCTEND rlbk=0, rd_only=1
The above 2 highlighted STAT figures appear under update histgrm$ SQL statement (under TKPROF REPORT - See TOP above) instead of update obj$ (which is seen just above in RAW Trace report). This means that its a recursive statement. Update obj$ calls Update histgrm$.
[Updated on: Wed, 17 December 2008 14:15] Report message to a moderator |
|
|
|
| Re: poor performance of Exchange partition on composite range-hash tables [message #376564 is a reply to message #376514] |
Wed, 17 December 2008 20:40 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Don't really follow all of your logic there, but I think you are heading off on a tangent trying to interpret TKPROF when what you really need is more data.
We have already seen that updates to the HISTGRM$ table are consistent between Range partitioned and Composite partitioned tables.
We SPECULATE that those updates are slower than they need to be. We also speculate that there is more IO than can be reasonably explained by the update.
The question I want answered is: Is this behaviour "normal" or "abnormal". Abnormal would mean there is something wrong with your database that you could theoretically fix. Normal means there is a problem with your design that is harder to fix.
It seems to me that you have already jumed to "abnormal", but I haven't seen enough proof yet. As I said before, can you try this on another database that is not a block-wise copy of this database?
Ross Leishman
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 18:12:37 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
