Home » RDBMS Server » Performance Tuning » Perfomance problem with partition table
| Perfomance problem with partition table [message #148777] |
Mon, 28 November 2005 01:42  |
chandanbhamra
Messages: 84
Registered: April 2005
Location: India
|
Member |
|
|
Hi
I have one partition table EVENT_T having 33 partitions. Have a look on some of the partitions of the table
TABLE_NAME COM PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE
------------------------------ --- ------------------------------ ------------------ ---------------
EVENT_T NO PARTITION_MIGRATE 0 17592186044416
EVENT_T NO PARTITION_LAST 0 MAXVALUE
EVENT_T NO P_217404235136892928 0 217404235136892928
EVENT_T NO P_218477358485602304 0 218477358485602304
EVENT_T NO PARTITION_HISTORIC 0 35184372088832
EVENT_T NO P_216331111788183552 0 216331111788183552
EVENT_T NO P_217932000718225408 0 217932000718225408
EVENT_T NO P_219022716252979200 0 219022716252979200
EVENT_T NO P_216858877369516032 0 216858877369516032
.
.
.
.
.
upto 33.
Now when i issue one query like select * from event_t where account_obj_id0 = 1 and (poid_type = 2)
then it takes hours to execute. I have partitioned index created on column account_obj_id0,poid_type.
Also when i checked its execution plan is shows that its using the index.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=52 Card=3 Bytes=477)
1 0 PARTITION RANGE (ALL)
2 1 SORT (UNIQUE) (Cost=52 Card=3 Bytes=477)
3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'EVENT_T' (Cost=49 Card=3 Bytes=477)
4 3 INDEX (RANGE SCAN) OF 'I_EVENT__ACCTOBJ_END_T' (NON-UNIQUE) (Cost=100 Card=103)
Can anybody tell me what i can do to improve the performance of this simple query ??
If further i split the partitions that will help ??
Thanks * Regards
Chandan Singh
|
|
|
|
|
|
| Re: Perfomance problem with partition table [message #148873 is a reply to message #148813] |
Mon, 28 November 2005 16:25  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Run the attached in SQL*Plus and paste the results back into this thread.
SET ECHO ON
SELECT column_name
FROM user_part_key_columns
WHERE table_name = 'EVENT_T';
SELECT /*+FULL(t)*/ COUNT(*)
FROM event_t t
WHERE account_obj_id0 = 1;
SELECT /*+FULL(t)*/ COUNT(*)
FROM event_t t
WHERE poid_type = 2;
SELECT /*+FULL(t)*/ COUNT(*)
FROM event_t t
WHERE account_obj_id0 = 1
AND poid_type = 2;
Or, if you want to learn about tuning, you could try the Oracle Performance Tuning Manual or My Website.
_____________
Ross Leishman
|
|
|
|
| Re: Perfomance problem with partition table [message #149037 is a reply to message #148777] |
Tue, 29 November 2005 15:29 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
No response from the OP, but as has been implied by the others, we need more information. If an index is locally partitioned, for example, then it will make "separate" index structures for each partition, meaning you would have to scan numerous indexes. So it all depends on how you have indexed, and the columns you have partitioned by, and the statistics, and well, it depends on a lot of information that you would need to provide.
|
|
|
|
| Re: Perfomance problem with partition table [message #149064 is a reply to message #149037] |
Tue, 29 November 2005 22:25 |
chandanbhamra
Messages: 84
Registered: April 2005
Location: India
|
Member |
|
|
Index is locally partition & its range index. Total no of records in this table is 590,994,526. What i have seen is there is distinct clause in original query. If i run the query without distinct clause then it hardly takes 1-2 secs to execute when with distinct clause it do sorting. after that it take hours to get the results.
What i can do, if i have to maintain the distinct clause and also to improve the performance ? It can be because of index ? I have similar problem with order by clause also.
Thanks & Regards
Chandan Singh
|
|
|
|
|
|
| Re: Perfomance problem with partition table [message #149180 is a reply to message #148777] |
Wed, 30 November 2005 08:11 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Well you pretty much need to either:
a) avoid the sort
b) speed the sort up
For a, this gets to your table structure and data model and relational integrity and constraints and the query you are running and to the keys you are partitioning by as to why you need the distinct. An order by, if the results need to be ordered, can't be avoided.
For b, you can decrease the volume of data being sorted, based on how the query executes and data is materialized, and based on indexes and partition keys used, and that decrease will speed the sort up. Can also tune how your system sorts, such as the sort_area_size or the pga_target, both the RAM params as well as temp tablespace size and disk location.
|
|
|
|
| Re: Perfomance problem with partition table [message #149183 is a reply to message #149073] |
Wed, 30 November 2005 08:19 |
chandanbhamra
Messages: 84
Registered: April 2005
Location: India
|
Member |
|
|
1* select count(*) from event_t where account_obj_id0 = 720260
SQL> /
COUNT(*)
----------
103049
SQL> select count(*) from event_t where account_obj_id0 = 1;
COUNT(*)
----------
20345158
SQL> SELECT COUNT(*)
2 FROM event_t t
3 WHERE poid_type = '/event/session'
4 ;
COUNT(*)
----------
18661053
SQL> SELECT COUNT(*)
2 FROM event_t t
3 WHERE account_obj_id0 = 1
4 AND poid_type = '/event/session';
COUNT(*)
----------
17500507
SQL> SELECT column_name
2 FROM user_part_key_columns
3 WHERE name = 'EVENT_T';
COLUMN_NAME
--------------------------------------------------------------------------------
POID_ID0
When i query with account_obj_id0 = 1 then initially it takes 35 minutes to get the results and after that it takes 25 secs to get the result. for another account account_obj_id0 = 720260 it hardly takes any time & executes in seconds. when i remove the distinct clause it executes in 1 secs.
Is it because of distinct that account with 1 is taking time ?
Thanks & Regards
Chandan Singh
|
|
|
|
| Re: Perfomance problem with partition table [message #149187 is a reply to message #148777] |
Wed, 30 November 2005 08:32 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
This is more info, but still need more. How about statistics? Did you run something like dbms_stats.gather_table_stats(owner,table,esitmate_percent,cascade=>true,method_opt=>'for all columns size 250')?
And notice the counts you got from Ross's queries. With one id you only have 100,000 rows. With another, you have 20 million. That is a significant difference that would affect response time, sort time, and the decision to do a FTS vs index scan.
But how many total rows are in your table? If you only have 20 million or so, and only have 2 partitions, one with almost all the rows and one with hardly any, then you might want to re-evaluate your partitioning scheme as it doesn't sound it is very effective.
|
|
|
|
| Re: Perfomance problem with partition table [message #149243 is a reply to message #149187] |
Wed, 30 November 2005 16:56 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
It seems that none of the predicates you are supplying are terribly selective.
smartin is right, we really need to know the number of rows in the table and the distribution of rows across partitions, but you should try a FTS for comparison.
SELECT /*+ FULL(a)*/ ...
FROM event_t a
WHERE ...
If you need to do the DISTINCT then there is really no practical way to avoid the sort.
I can think of one very creative way, but it will take a bit of effort to code, and won't necessarily be faster. But it will start spitting out rows immediately.
You could create a pipelined table function (search this forum and Ask Tom for "Table Function" or "Pipelined") that does the following:
- declare an associative array, keyed on the combination of columns that make a row unique.
- open a cursor for your SELECT statement WITHOUT the DISTINCT clause.
- For each row, check if it exists in the associative array. If not, add it to the array and PIPE ROW out of the function. If it is found, ignore it.
_____________
Ross Leishman
|
|
|
|
| Re: Perfomance problem with partition table [message #149395 is a reply to message #149243] |
Thu, 01 December 2005 08:23 |
chandanbhamra
Messages: 84
Registered: April 2005
Location: India
|
Member |
|
|
I cannot change query as it is out of box query. so i have to tune it by using indexes. just have a look on this partitions
Partition Name Rows ACCOUNT_OBJ_ID0=1
P_231882604251447296 0.00 0.00
P_231337246484070400 0.00 0.00
P_230791888716693504 15,609,348.00 38,181.00
P_230264123135361024 15,518,844.00 141,796.00
P_229718765367984128 19,973,632.00 263,189.00
P_229190999786651648 25,613,253.00 1,529,229.00
P_228645642019274752 27,574,702.00 2,263,835.00
P_228100284251897856 27,068,514.00 2,014,909.00
P_227572518670565376 22,789,874.00 1,450,255.00
P_227027160903188480 23,875,573.00 1,179,897.00
P_226499395321856000 27,354,784.00 1,011,181.00
P_225954037554479104 20,336,597.00 758,014.00
P_225461456345235456 21,441,655.00 968,888.00
P_224916098577858560 24,681,292.00 834,113.00
P_224370740810481664 17,124,516.00 656,291.00
P_223842975229149184 16,320,066.00 741,001.00
P_223297617461772288 16,744,300.00 793,478.00
P_222769851880439808 15,414,352.00 500,066.00
P_222224494113062912 14,464,253.00 386,085.00
P_221679136345686016 14,493,906.00 386,085.00
P_221151370764353536 11,714,806.00 296,121.00
P_220606012996976640 11,714,806.00 437,767.00
P_220078247415644160 12,355,635.00 349,211.00
P_219532889648267264 12,355,635.00 290,605.00
P_216858877369516032 7,015,023.00 146,938.00
P_219022716252979200 11,098,860.00 573,903.00
P_217932000718225408 7,735,277.00 166,536.00
P_216331111788183552 4,243,427.00 90,367.00
PARTITION_HISTORIC 74,544,899.00 0.00
P_218477358485602304 10,312,976.00 249,374.00
P_217404235136892928 7,059,809.00 140,897.00
PARTITION_LAST 0.00 0.00
PARTITION_MIGRATE 57,867,181.00 1,747,926.00
594,417,795.00 20,406,138.00
I have been suggested to create global index on those two columns or we can create composite index on ACCOUNT_OBJ_ID0 and POID_TYPE.
Which one will be better Global Index or Composite Index of those two columns ? please suggest me on this.
Thanks & Regards
Chandan Singh
|
|
|
|
| Re: Perfomance problem with partition table [message #149433 is a reply to message #148777] |
Thu, 01 December 2005 15:17 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
A global index may indeed help your query, probably a global composite one, but you can't just think of query performance must also think of adding/updating/deleting entries to and from that index, as well as dba maintenance of that index over time, and the higher number of operations that will require a full index rebuild as opposed to a rebuild of only one or a subset of partitions.
Back in your original post, you mentioned:
| Quote: |
Now when i issue one query like select * from event_t where account_obj_id0 = 1 and (poid_type = 2)
|
But later you said you partitioned by poid_id0. Is there any relationship between a specific value of poid_id0 and either account_obj_id0 or poid_type? If for example in the above quoted combo of 1 and 2 meant that poid_id0 would always be 1, then you could add that in as an extra condition to the query to limit the number of index partitions that needed to be scanned to one.
Oh wait though, you can't change the query?
So what made you choose the field that you have partitioned by? Can you change that? Is this the only query in your system or would changing the partitioning structure affect other things? The thing is that with local index partitions, one index is created for each partition. And your partitioning key seems to have nothing to do with the results, meaning the partitions play no helpful role, and in fact a harmful one, when it comes to performance of this specific query.
Would this and other queries be more selective if you partitioned instead of poid_id0 some other column that would be more selective for the query column combinations that you typically run?
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 17:45:15 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
