Thanks for the question, Adelmo.
Asked: April 28, 2022 - 3:50 pm UTC
Last updated: May 16, 2022 - 2:10 am UTC
Version: 19c
Viewed 1000+ times
You Asked
Greetings oracle DB gurus,
On this subject I want a recommendation, the database weighs 7 teras in total but 5 of that 7 teras is only the audit table, that table only has 3 years of data (The business needs to keep all the data) and it has more than 900 million record and 2 clob fields, it is a move table,
We have had several incidents related to this table, slowness in the Database for inserting that table, as it has clob fields that sometimes save 10 million characters, not if that is related, apart from that we have run out of disk space, tablespace or data file, the log is filling up very fast, it doesn't even let the alerts arrive before the disk is full, for example.
This table is used by several applications at the same time and saves all the activities that users perform, the clob fields are the details of the activities
The business wants to pull reports from this table when that table only has one index. here I leave the structure of the table
CREATE TABLE EBTDEV.ADMIN_AUDIT
(
ID NUMBER NOT NULL
, EVENT_TYPE NUMBER(1, 0)
, OWNER_ID NUMBER
, OWNER VARCHAR2(100 BYTE)
, OWNER_PERMISSIONS CLOB
, EVENT_DESCRIPTION VARCHAR2(200 BYTE)
, OBJECT_TYPE VARCHAR2(100BYTE)
, OBJECT_ID NUMBER
, BEFORE CLOB
, AFTER CLOB
, TERMINAL VARCHAR2(100 BYTE)
, EVENT_DATE TIMESTAMP(6)
, AGENCY VARCHAR2(10 BYTE)
,PORTAL VARCHAR2(20 BYTE)
, UPD_FILE_DW TIMESTAMP(6)
)
and this is the only index it has
CREATE INDEX EBTDEV.IX_EVENT_DT_UPD_FILE_DW ON EBTDEV.ADMIN_AUDIT (EVENT_DATE ASC, UPD_FILE_DW ASC)
my question is what is your recommendation to improve performance regarding the creation of reports and optimize the table so as not to have more issues of DB space and slowness in the DB
and Connor said...
Well, that is going to depend a LOT on the types of queries that you need to run, but some general guidelines for large tables and LOBs in them are:
1) look at partitioning. If reports are coming in via date ranges, then partitioning on that date can yield big gains. More details here:
2) How often are the CLOBs accessed in your reports? If not often, then look at storing them out of line which reduces the size of your table, at the expense of making the clobs a little bit more expensive to access.
3) Look at compression of the data and and also using securefiles to potentially compress the LOBs. Do some testing on a smaller scale, and if it helps, then here's a post showing examples of doing a migration online
https://connor-mcdonald.com/2015/07/03/continuous-delivery-moving-to-securefile/
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment