OraFAQ Forum: Performance Tuning » Partitioning in table

Home » RDBMS Server » Performance Tuning » Partitioning in table (Oracle 11gr2 rhel)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Partitioning in table [message #668412]

Fri, 23 February 2018 04:19

dba4oracle
Messages: 100
Registered: June 2010

Senior Member

Hi ,

I checked on table and queries on this table is having some issues in one of our db, size is about 600 mb(19000 rows) and having 32 hash partitions

i feel it is not good candidate for partitioning,what are your views or should we increase number of partitions instead to 64

Thanks

Report message to a moderator

Re: Partitioning in table [message #668413 is a reply to message #668412]

Fri, 23 February 2018 04:28

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

you've partitioned a table with 19 thousand rows?
Why?
It's tiny

Report message to a moderator

Re: Partitioning in table [message #668414 is a reply to message #668413]

Fri, 23 February 2018 04:50

dba4oracle
Messages: 100
Registered: June 2010

Senior Member

Thanks

its existing partitions,i also feel its tiny for partitioning,now there is slow response on this table,some suggestions to increase number of partitions to double

but i also feel already this table should not be partitioned in first place so increasing more partitions will not help

what are your views

Report message to a moderator

Re: Partitioning in table [message #668415 is a reply to message #668414]

Fri, 23 February 2018 05:48

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

My views are I would never consider partitioning a table that small but that I wouldn't do anything to the existing partitioning without first knowing what affect it has on the performance.

So - look at the slow queries and see where the time is being spent - the partitions may be a red herring.

Report message to a moderator

Re: Partitioning in table [message #668432 is a reply to message #668414]

Sat, 24 February 2018 04:10

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

I learnt the hard way: do not partition a table unless you have proven conclusively that your chosen partitioning strategy (there are so many possibilities....) will fix a defined problem, and not introduce other problems. In your case, there is the possibility that local indexes are being used inappropriately: 32 index searches, not one. Or perhaps the partitions are so small that you are getting serial direct scans when indirect scan or index access would be preferable.
You need to gather information. Execution plans and statistics and wait events for your problem queries would be a good start.

Report message to a moderator

Re: Partitioning in table [message #668442 is a reply to message #668432]

Mon, 26 February 2018 00:16

dba4oracle
Messages: 100
Registered: June 2010

Senior Member

We suspect below insert which was new sql as compared to good period(when db was not having performance issue)
it seems to be simple insert (but along with this update on same table also running)
insert into config_table values (:1 , :2 , :3 , :4 )

both this update and insert on table was top sql

is any other issue on table we can check (its already having partition which seems not helping)

below is stats of insert
insert into table_config...

Code: [Select all] [Show/ hide]

Stat Name	Statement Total	Per Execution	% Snap Total
Elapsed Time (ms)	382,558	25.20	9.52
CPU Time (ms)	30,146	1.99	2.24
Executions	15,180	 	 
Buffer Gets	956,450	63.01	0.20
Disk Reads	227,853	15.01	3.12
Parse Calls	1,720	0.11	0.05
Rows	15,180	1.00	 
User I/O Wait Time (ms)	332,626	 	 
Cluster Wait Time (ms)	0	 	 
Application Wait Time (ms)	0	 	 
Concurrency Wait Time (ms)	0	 	 
Invalidations	0	 	 
Version Count	147	 	 
Sharable Mem(KB)	1,022

elapsed time (ms) 382,558 for 3 hrs snap time

in awr for issue period
per exec elapsed time for both sql is 0.03 sec:

update table_config set (c...
insert into table_config...

--moderator update: added [code] tags, made it a bit easier to read.

[Updated on: Mon, 26 February 2018 01:02] by Moderator

Report message to a moderator

Re: Partitioning in table [message #668443 is a reply to message #668442]

Mon, 26 February 2018 00:20

dba4oracle
Messages: 100
Registered: June 2010

Senior Member

-------------------------------

------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time |
------------------------------------------------------------------------------
---------------
| 0 | INSERT STATEMENT | | 1 | 101 | 1
(0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | TABLE_CONFIG | | |
| |
------------------------------------------------------------------------------

Report message to a moderator

Re: Partitioning in table [message #668444 is a reply to message #668443]

Mon, 26 February 2018 00:31

dba4oracle
Messages: 100
Registered: June 2010

Senior Member

stats for update on table_config during 3 hrs snap:

Elapsed time (ms):1,046,148
executions:30,303

Code: [Select all] [Show/ hide]

Stat Name	Statement Total	Per Execution	% Snap Total
Elapsed Time (ms)	1,046,148	34.52	26.04
CPU Time (ms)	85,570	2.82	6.36
Executions	30,303	 	 
Buffer Gets	3,183,849	105.07	0.68
Disk Reads	647,236	21.36	8.87
Parse Calls	295	0.01	0.01
Rows	30,303	1.00	 
User I/O Wait Time (ms)	913,909	 	 
Cluster Wait Time (ms)	0	 	 
Application Wait Time (ms)	0	 	 
Concurrency Wait Time (ms)	0	 	 
Invalidations	0	 	 
Version Count	141	 	 
Sharable Mem(KB)	1,171

plan

Code: [Select all] [Show/ hide]

Id	Operation	Name	Rows	Bytes	Cost (%CPU)	Time	Pstart	Pstop
0	UPDATE STATEMENT	 	 	 	2 (100)	 	 	 
1	   UPDATE	TABLE_CONFIG	 	 	 	 	 	 
2	     PARTITION HASH SINGLE	 	1	2286	1 (0)	00:00:01	KEY	KEY
3	       INDEX UNIQUE SCAN	TABLE_CONFIG_PK	1	2286	1 (0)	00:00:01	KEY	KEY

s.-moderator update: added [code] tags, please do it yourself in future

[Updated on: Mon, 26 February 2018 01:03] by Moderator

Report message to a moderator

Re: Partitioning in table [message #668445 is a reply to message #668444]

Mon, 26 February 2018 01:08

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Please read How to use code tags and make your code easier to read and use the tags in future.

Partitioning is neither good nor bad for those statements. What is the problem? Do you have some target for performance that you need to reach?

Report message to a moderator

Re: Partitioning in table [message #668446 is a reply to message #668445]

Mon, 26 February 2018 01:47

dba4oracle
Messages: 100
Registered: June 2010

Senior Member

Issue is that previous week there was slowness in database overall,checked awr,ash,awrsqrpt found insert statement sql which was new and not in other days
when performance was good so we suspect it may have caused performance degradation as it is simple insert not seems any scope of tuning so checking any improvement
we can do in table structure like increasing partitions(which may not help i think) ,adding indexes etc
same time update also running on same table

Report message to a moderator

Re: Partitioning in table [message #668447 is a reply to message #668446]

Mon, 26 February 2018 02:02

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Quote:

previous week there was slowness in database overall

That is useless. You have to identify a business process which has degraded below an acceptable level. For example: a screen refresh is too slow; an overnight job that doesn't finish until lunchtime; a report that now takes ten minutes which used to take one. You cannot tune something unless you identify what the "something" is.

Then running a few AWR reports may be useful, but not running them for the problem time only. You need to run reports for comparable workloads when performance was good and for when it was bad.

Report message to a moderator

Re: Partitioning in table [message #668455 is a reply to message #668447]

Mon, 26 February 2018 03:34

dba4oracle
Messages: 100
Registered: June 2010

Senior Member

AWR is compared for problem time and past week when performance was good

User reported overall database is slow for any process that specific sql or batch job or screen

Report message to a moderator

Re: Partitioning in table [message #668456 is a reply to message #668455]

Mon, 26 February 2018 03:35

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

>>AWR is compared for problem time and past week when performance was good

So what does it tell you?

Report message to a moderator

Re: Partitioning in table [message #668457 is a reply to message #668455]

Mon, 26 February 2018 03:41

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Quote:

User reported overall database is slow for any process that specific sql or batch job or screen

So he should be able to give you an example. Seriously, how do you think you can tune an undefined problem?

Report message to a moderator

Re: Partitioning in table [message #668463 is a reply to message #668457]

Mon, 26 February 2018 09:28

dba4oracle
Messages: 100
Registered: June 2010

Senior Member

thanks john you are right but sometimes user just say whole db is slow and expect to find root cause by awr or other db stats

Report message to a moderator

Re: Partitioning in table [message #668464 is a reply to message #668463]

Mon, 26 February 2018 09:55

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

And sometimes the users need to be asked to give workable information.

Report message to a moderator

Previous Topic:	How long lock should be in order to take snapshot of locks
Next Topic:	SQL Query performance Tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 05:27:04 CDT 2025