Home » RDBMS Server » Performance Tuning » Partitioning in table (Oracle 11gr2 rhel)
Partitioning in table [message #668412] Fri, 23 February 2018 04:19 Go to next message
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
Re: Partitioning in table [message #668413 is a reply to message #668412] Fri, 23 February 2018 04:28 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
you've partitioned a table with 19 thousand rows?
Why?
It's tiny
Re: Partitioning in table [message #668414 is a reply to message #668413] Fri, 23 February 2018 04:50 Go to previous messageGo to next message
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

Re: Partitioning in table [message #668415 is a reply to message #668414] Fri, 23 February 2018 05:48 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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.
Re: Partitioning in table [message #668432 is a reply to message #668414] Sat, 24 February 2018 04:10 Go to previous messageGo to next message
John Watson
Messages: 8270
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.
Re: Partitioning in table [message #668442 is a reply to message #668432] Mon, 26 February 2018 00:16 Go to previous messageGo to next message
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...


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 Go to previous messageGo to next message
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 | | |
| |
------------------------------------------------------------------------------
Re: Partitioning in table [message #668444 is a reply to message #668443] Mon, 26 February 2018 00:31 Go to previous messageGo to next message
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




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



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 Go to previous messageGo to next message
John Watson
Messages: 8270
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?
Re: Partitioning in table [message #668446 is a reply to message #668445] Mon, 26 February 2018 01:47 Go to previous messageGo to next message
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
Re: Partitioning in table [message #668447 is a reply to message #668446] Mon, 26 February 2018 02:02 Go to previous messageGo to next message
John Watson
Messages: 8270
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.
Re: Partitioning in table [message #668455 is a reply to message #668447] Mon, 26 February 2018 03:34 Go to previous messageGo to next message
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
Re: Partitioning in table [message #668456 is a reply to message #668455] Mon, 26 February 2018 03:35 Go to previous messageGo to next message
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?
Re: Partitioning in table [message #668457 is a reply to message #668455] Mon, 26 February 2018 03:41 Go to previous messageGo to next message
John Watson
Messages: 8270
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?
Re: Partitioning in table [message #668463 is a reply to message #668457] Mon, 26 February 2018 09:28 Go to previous messageGo to next message
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
Re: Partitioning in table [message #668464 is a reply to message #668463] Mon, 26 February 2018 09:55 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
And sometimes the users need to be asked to give workable information.
Previous Topic: How long lock should be in order to take snapshot of locks
Next Topic: SQL Query performance Tuning
Goto Forum:
  


Current Time: Mon Jun 01 21:48:57 CDT 2020