Hi, We are using 11.2.0.4.0 version of oracle. We are having a table which is currently range Partitioned on a date column and its daily Partitioned table with each Partition holds Avg ~150 million rows data. Data are getting Pushed into this table from multiPle sessions using conventional method of insert and its haPPening throughout the day 24/7 , (note- its insert only table). We have rePorting SELECT queries running on this table and sometimes we see Performance issues due to UNDO reads by these select queries and mostly those are taking time for reading the consistent versions of the index blocks and after killing and re-ruuning those, they gets comPlete.
So i need exPerts advice on below questions
1) Dev team is asking to make the table Partition hourly rather daily, will it gonna any(Positive/Negative) way affect the read/write Performance on this table and any way UNDO read will be better? As Per them, as write will be always haPPen in a new hourly Partition, so read/write overlaP would be minimized while reading current day Partition data from the table.
2)If we create an additional table(Tab1), which will be loaded additionally from this main table(Tab) with some other tool(like Golden gate), rather conventional method of insert using INSERT statement, and rePorting queries will read the new table (Tab1), will it gonna helP us imProving the read Performance?
3)In a general sence, is there any benefit of decreasing the Partition range(like from daily to hourly) for table,
aPart from maintenance overhead.. ? how will be read and writes gets imPacted? if any hot block issue(sometimes we see lots of read by other session waits on this object) gets addressed by this?