| 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
|
|
|
|
|
|
| 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
|
|
|
|
| Re: Partitioning in table [message #668415 is a reply to message #668414] |
Fri, 23 February 2018 05:48 |
cookiemonster
Messages: 13962
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 |
John Watson
Messages: 8963
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 |
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 |
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 |
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 [Updated on: Mon, 26 February 2018 01:03] by Moderator 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
|
|
|
|
| Re: Partitioning in table [message #668447 is a reply to message #668446] |
Mon, 26 February 2018 02:02 |
John Watson
Messages: 8963
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 |
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 #668457 is a reply to message #668455] |
Mon, 26 February 2018 03:41 |
John Watson
Messages: 8963
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?
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
