Impact of space management on system performance in Oracle (1)

In Oracle databases, DBAs can observe tables or views to understand the usage of the current space and make possible adjustments.

I. Free Space for tablespace

By observing the free space of a table space, you can determine whether the space allocated to a tablespace is too much or not. See the following statements:

SQL > select a.file_id "FileNo"，a.tablespace_name "Tablespace_name"， 2 a.bytes "Bytes"，a.bytes-sum(nvl(b.bytes，0)) "Used"， 3 sum(nvl(b.bytes，0)) "Free"， 4 sum(nvl(b.bytes，0))/a.bytes*100 "%free" 5 from dba_data_files a， dba_free_space b 6 where a.file_id=b.file_id(+) 7 group by a.tablespace_name ， 8 a.file_id，a.bytes order by a.tablespace_name; File Tablespace No _nameBytes Used Free %free ------ --------- -------- --------- --------- --------- 11IDX_JF .146E+09 849305600 1.297E+09 60.431806 9 JFSJTS 2.146E+09 1.803E+09 343793664 16.016961 10JFSJTS 2.146E+09 1.359E+09 787431424 36.685546 2 RBS523239424 359800832 163438592 31.235909 12RBS1.610E+09 1.606E+09 3104768 .19289495 8 RBSJF 3.220E+09 2.716E+09 504356864 15.662396 7 SFGLTS 2.146E+09 1.228E+09 918159360 42.776014 6 SFSJTS 2.146E+09 1.526E+09 620093440 28.889457 1 SYSTEM 523239424 59924480 463314944 88.547407 3 TEMP 523239424294912 522944512 99.943637 4 TOOLS 15728640 12582912 314572820 5 USERS 7340032 81927331840 99.888393 12 rows selected.

It can be seen that only 0.19% of the allocated space in the tablespace RBS with 12 FileNo is not used. This proportion is too small, and in the tablespace such as SYSTEM and TEMP, up to 80% of the space is not used. For production databases, the tablespace settings are somewhat high.

For free space management, we have the following suggestions: using the Export and Import commands to unload and load tablespaces can release a large amount of space, so as to alleviate the need for additional data files. If the proportion of free space in the tablespace that contains tables with high insert and update activities falls below 15%, more space is required for the tablespace. For a tablespace that is basically a static table data, if there is more than 20% free space, you can consider reducing the amount of file space allocated to it. It is difficult to reduce the space of the SYSTEM tablespace because it is necessary to recreate the database.

Ii. Expansion of tables and Indexes

A. In order to prevent tables or indexes from being excessively expanded and timely adjust the database, users should observe related objects frequently. We can think that tables or indexes with more than five extended areas are overextended ). See the following statement:

SQL > select substr(segment_name，1，15) Segment_name，segment_type， 2 substr(tablespace_name，1，10) Tablepace_name，extents，Max_extents 3from dba_segments 4where extents >5 and owner='JFCL' 5order by segment_name; SEGMENT_NAMESEGMENT TABLEPACE_ EXTENTS MAX_EXTENTS _TYPE -------------- --------- ---------- CHHDFYB TABLE JFSJTS 11121 CHHDFYB_DHHMINDEX JFSJTS9121 DJHZFYB_BF TABLE JFSJTS 17500 DJHZFYB_DJHMINDEX IDX_JF6500 DJHZFYB_JZHMINDEX IDX_JF7500 GSMFYB TABLE JFSJTS 11121 JFDHTABLE JFSJTS 14500 JFDH_DHHM INDEX IDX_JF 61500 JFDH_JZHM INDEX IDX_JF 64500 XYKFYB TABLE JFSJTS7121 YHDATABLE JFSJTS6500 YHDA_BAKTABLE JFSJTS6500 YHHZFYB_12 TABLE JFSJTS 10500 13 rows selected.

Through observation, DBAs can promptly discover and handle problems. We can use export to unload the table, delete the table, and then use the import command to load the table. In this way, we can combine discontinuous areas into a continuous space.

B. If you want to optimize the table space settings, for example, you need to change the initial parameter of the table EMP, you can use the following method:

1. run the indexfile parameter: imp userid = scott/tiger file = EMP. dmp indexfile = emp. SQL Oracle writes the table and index creation information to the specified file instead of writing data back.

2. Open the emp. SQL file:

REM CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4， 0)， "ENAME" REM VARCHAR2(10)， "JOB" VARCHAR2(9)， "MGR" NUMBER(4， 0)， "HIREDATE" DATE， REM "SAL" NUMBER(7， 2)， "COMM" NUMBER (7， 2)， "DEPTNO" NUMBER(2， 0)) REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL REM 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS REM 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER_DATA" ; REM ... 14 rows

Edit it, remove information such as "REM", find the Initial parameter, and change it as needed.