Monitoring project, 16 sensors, sampling frequency 50 hz, built a table stores the data, structure is simple: time + numerical

 
The Create Table real_data (
Record_time timestamp (3), 
Ac_1 Float, 
Ac_2 Float, 
Ac_3 Float, 
Ac_4 Float, 
Ac_5 Float, 
Ac_6 Float, 
Ac_7 Float, 
Ac_8 Float, 
Ac_9 Float, 
Ac_10 Float, 
Ac_11 Float, 
Ac_12 Float, 
Ac_13 Float, 
Ac_14 Float, 
Ac_15 Float, 
Ac_16 Float 
) 
In Tablespace data_test; 

Now regularly for 10 minutes, remove the data table to another table, using the job to do,

There is a problem, 10 days after the table of high water level will reach more than 6000, query efficiency to reduce a lot of, in fact, high water level more than 4 w, retrieval efficiency has no obvious drop,

 
SQL> ANALYZE TABLE AC_REAL_DATA ESTIMATE STATISTICS; 
Table analyzed 

SQL> The SELECT table_name, num_rows, blocks, empty_blocks FROM user_tables WHERE table_name='AC_REAL_DATA'; 
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
AC_REAL_DATA 0 61109 331 

Doubt is:

1. The table is the capacity of the largest 10 minutes data, the article 3 w data, how high water level can reach 6 w?

2. What factors can lead to high water level? In addition to frequent to delete, and by the initialization parameter with the build table?

Thank you for your attention!

CodePudding user response:

The table first contraction, see will go up again;

CodePudding user response:

reference 1st floor wmxcn2000 response:

table first contraction, see will go up again;

Move, have you tried the shrink,

Good at first, but after 7 days, high water level went up again,

CodePudding user response:

If done the shrink, it has nothing to do with initial parameters;

Trying to identify the following two conditions:

1, at some point, it's probably the amount of data is written to, such as more than 10 w
2, there is a batch when written to, add the/* + append */options;

CodePudding user response:

reference wmxcn2000 reply: 3/f

if done the shrink, that has nothing to do with the initial parameters.

Trying to identify the following two conditions:

1, at some point, it's probably the amount of data is written to, such as more than 10 w
2, there is a batch when written to, add the/* + append */options;

Thank you,
1. There is no written large amount of data, each 10 minutes, I'll transfer time,
2. Write a database is to use the original way of the insert, there is no parallel operation,

CodePudding user response:

If you frequently deleted insert or modify, time for a long time of high water level will rise, because of your database must not only this table, had 10 data accounts for 1 piece, you delete the data room, but the other table data is inserted, and then you insert into, found that not enough has added a block

CodePudding user response:

reference 5 floor sxq129601 reply:

if you frequently deleted insert or modify, time for a long time of high water level will rise, because of your database must not only this table, had 10 data accounts for 1 piece, you delete the data room, but the other table data is inserted, and then you insert into, found that not enough has added a piece of

nd

The two tables, iyou do testing,

A real time table, a history table, real-time guide to the history table to table 10 minutes,

Isn't a single table of high water level and other tables and associated?
If so, all oracle data table after a long time, there is high water level, because there are insert, and delete operations?

CodePudding user response:

You 10 minutes into the how much is the water, after 10 days have increased much,
If your data into frequency and quantity is stable, in theory, the water should not be exploded
the condition of theIs likely to come from real time table is transferred to the history table is need time, and the process of your application in real time inserts, so there will be a real time table, the water level of increase,
If the table without modifying other action, reduce the Pctfree, let every piece of storing data as much as possible

CodePudding user response:

If there is no use after each transfer table turncate and only rely on spatial reuse after the delete, will gradually expand high water level,
http://blog.sina.com.cn/s/blog_6ff05a2c0100mjrw.html

CodePudding user response:

The building Lord, you are not on a regular basis for 10 minutes migration table data?
Suggest after the migration, truncate table, right now in this case, high water level will go down!