Taking the Oracle7.33 database system as an example, when developing a large Oracle database system, based on the characteristics of the project, we conducted some physical design on the database in line with the principles of security and efficiency, this greatly improves the speed of database query statistics. Summarized as follows:
1) expand the data table space to 500 MB, which is used to store the data of the system;
2) The initial size of the segment disk is 10 KB, And the increase is 10 KB. The increase is 1;
3) The temporary user space is increased by 40 MB;
4) The system temporary tablespace and rollback segment tablespace increase by 40 MB, and four new rollback segments are created;
5) databases, directory tables, and receiving tables with large data volumes need to be joined frequently;
6) provides regular backup and stores the backup file on another machine.
The SQL statement used to set the data table space is as follows:
CREATE TABLESPACE WXGL_DATA1 DATAFILE 'WXGL_DATA1.ORA' SIZE 500M ONLINE;
|
The SQL statement for adding a system temporary tablespace and a rollback segment tablespace is as follows:
ALTER TABLESPACE TEMPORARY_DATA ADD DATAFILE 'TMP2ORCL.ORA' SIZE 40M;
ALTER TABLESPACE ROLLBACK_DATA ADD DATAFILE 'RBS2ORCL.ORA' SIZE 40M;
|
The SQL statement for setting a data space in a specified data file is as follows:
CREATE USER ZBGL IDENTIFIED BY ZBGL;
GRANT DBA TO ZBGL;
ALTER USER ZBGL DEFAULT TABLESPACE WXGL_DATA1 TEMPORARY TABLESPACE TEMPORARY_DATA;
|
The SQL statement for setting five rollback segments is as follows:
SELECT SEGMENT_NAME FROM DBA_ROLLBACK_SEGS WHERE INITIAL_EXTENT < 512000 AND
UPPPER(OWNER) = 'PUBLIC';
SELECT UPPER(STATUS) FROM DBA_ROLLBACK_SEGS WHERE UPPER(SEGMENT_NAME) = ''
ALTER ROLLBACK SEGMENT RB1 OFFLINE;
ALTER ROLLBACK SEGMENT RB2 OFFLINE;
ALTER ROLLBACK SEGMENT RB3 OFFLINE;
ALTER ROLLBACK SEGMENT RB4 OFFLINE;
ALTER ROLLBACK SEGMENT RB5 OFFLINE;
DROP ROLLBACK SEGMENT RB1;
DROP ROLLBACK SEGMENT RB2;
DROP ROLLBACK SEGMENT RB3;
DROP ROLLBACK SEGMENT RB4;
DROP ROLLBACK SEGMENT RB5;
CREATE PUBLIC ROLLBACK SEGMENT RB1 TABLESPACE ROLLBACK_DATA
STORAGE (INITIAL 512000 NEXT 512000 MAXEXTENTS 121);
CREATE PUBLIC ROLLBACK SEGMENT RB2 TABLESPACE ROLLBACK_DATA
STORAGE (INITIAL 512000 NEXT 512000 MAXEXTENTS 121);
CREATE PUBLIC ROLLBACK SEGMENT RB3 TABLESPACE ROLLBACK_DATA
STORAGE (INITIAL 512000 NEXT 512000 MAXEXTENTS 121);
CREATE PUBLIC ROLLBACK SEGMENT RB4 TABLESPACE ROLLBACK_DATA
STORAGE (INITIAL 512000 NEXT 512000 MAXEXTENTS 121);
CREATE PUBLIC ROLLBACK SEGMENT RB5 TABLESPACE ROLLBACK_DATA
STORAGE (INITIAL 512000 NEXT 512000 MAXEXTENTS 121);
ALTER ROLLBACK SEGMENT RB1 ONLINE;
ALTER ROLLBACK SEGMENT RB2 ONLINE;
ALTER ROLLBACK SEGMENT RB3 ONLINE;
ALTER ROLLBACK SEGMENT RB4 ONLINE;
ALTER ROLLBACK SEGMENT RB5 ONLINE;
COMMIT;
|
The SQL statements that place large databases, tables, and so on in a cluster are as follows:
KCB = 'create TABLE QC_KCB ('
+ 'Cknm NUMBER (8 ),'
+ 'Qcnm NUMBER (10 ),'
+ 'Ckkc NUMBER (12, 2 ),'
+ 'Snckkc NUMBER (12, 2 ),'
+ 'Ldj NUMBER (12, 2 ),'
+ 'Bz VARCHAR (100 ),'
+ 'Primary KEY (CKNM, QCNM ))'
+ 'Tablespace WXGL_DATA1 '; stores large data volumes in WXGL_DATA1)
QCFL = 'create TABLE QC_QCFL'
+ '(Flbh number (2) primary key ,'
+ 'Flmc VARCHAR (20 )'
+ ')'
+ 'Tablespace WXGL_DATA2 '; (other tables are stored in WXGL_DATA2)
|
The basic data warehouse tables and directory tables of the system have about 80 mb of data. One Unit generally sends and receives 300 million data records each year, and the receiving and receiving orders have about 50 MB of data. The system has 100 MB of redundant data, the system supports 10 MB of auxiliary data. Therefore, the total space required by the system is about 240 mb. Currently, the system opens up 500 mb of data space, which fully meets the storage requirements. The system uses redundant data to avoid multi-table join queries when querying common data. In this way, although more storage space is used, the query efficiency is greatly improved; at the same time, the system puts the data that needs to be frequently joined for query in a cluster, and the space for storing the data is physically adjacent, which greatly improves the query speed.
In addition, there is an Initorcl. ora file under the Database directory of oracle7.33. Changing the settings can also increase the query statistics speed. The content of this file is as follows:
#
# $Header: init.ora 1.2 94/10/18 16:12:36 gdudey Osd
$ init.ora Copyr (c) 1991 Oracle
# ##########################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation to help you customize
# your RDBMS installation for your site. Important system parameters
# are discussed, and example settings given.
#
# Some parameter settings are generic to any size installation.
# For parameters that require different values in different size
# installations, three scenarios have been provided: SMALL, MEDIUM
# and LARGE. Any parameter that needs to be tuned according to
# installation size will have three settings, each one commented
# according to installation size.
#
# Use the following table to approximate the SGA size needed for the
# three scenarious provided in this file:
#
# -------Installation/Database Size------
# SMALL MEDIUM LARGE
# Block 2K 4500K 6800K 17000K
# Size 4K 5500K 8800K 21000K
#
# To set up a database that multiple instances will be using, place
# all instance-specific parameters in one file, and then have all
# of these files point to a master file using the IFILE command.
# This way, when you change a public
# parameter, it will automatically change on all instances. This is
# necessary, since all instances must run with the same value for many
# parameters. For example, if you choose to use private rollback segments,
# these must be specified in different files, but since all gc_*
# parameters must be the same on all instances, they should be in one file.
#
# INSTRUCTIONS: Edit this file and the other INIT files it calls for
# your site, either by using the values provided here or by providing
# your own. Then place an IFILE= line into each instance-specific
# INIT file that points at this file. #################################################################
db_name = oracle
db_files = 20
control_files = C:\ORAWIN95\DATABASE\ctl1orcl.ora
compatible = 7.3.0.0.0
db_file_multiblock_read_count = 8 # INITIAL
# db_file_multiblock_read_count = 8 # SMALL
# db_file_multiblock_read_count = 16 # MEDIUM
# db_file_multiblock_read_count = 32 # LARGE
db_block_buffers = 200 # INITIAL
# db_block_buffers = 200# SMALL
# db_block_buffers = 550 # MEDIUM
# db_block_buffers = 3200 # LARGE
shared_pool_size = 3500000 # INITIAL
# shared_pool_size = 3500000 # SMALL
# shared_pool_size = 6000000 # MEDIUM
# shared_pool_size = 9000000 # LARGE
log_checkpoint_interval = 10000
processes = 50 # INITIAL
# processes = 50 # SMALL
# processes = 100 # MEDIUM
# processes = 200 # LARGE
dml_locks = 100 # INITIAL
# dml_locks = 100 # SMALL
# dml_locks = 200 # MEDIUM
# dml_locks = 500 # LARGE
log_buffer = 8192 # INITIAL
# log_buffer = 8192 # SMALL
# log_buffer = 32768 # MEDIUM
# log_buffer = 163840 # LARGE
sequence_cache_entries = 10 # INITIAL
# sequence_cache_entries = 10 # SMALL
# sequence_cache_entries = 30 # MEDIUM
# sequence_cache_entries = 100# LARGE
sequence_cache_hash_buckets = 10 # INITIAL
# sequence_cache_hash_buckets = 10 # SMALL
# sequence_cache_hash_buckets = 23 # MEDIUM
# sequence_cache_hash_buckets = 89 # LARGE
# audit_trail = true # if you want auditing
# timed_statistics = true # if you want timed statistics
max_dump_file_size = 10240 # limit trace file size to 5 Meg each
# log_archive_start = true # if you want automatic archiving
log_archive_dest=%ORACLE_HOME%\DATABASE\ARCHIVE
# define directories to store trace and alert files
background_dump_dest=%RDBMS73%\trace
user_dump_dest=%RDBMS73%\trace
db_block_size = 2048
snapshot_refresh_processes = 1
remote_login_passwordfile = shared
|
You can modify the db_file_multiblock_read_count, db_block_buffers, shared_pool_size, processes, log_buffer, sequence_cache_entries, sequence_cache_hash_buckets, and other items in this file ), you can increase the query statistics speed by modifying the value based on your needs and the features of the database servers used by the system. You must change log_archive_start = true to False to make the setting take effect.
The Oracle Database setup method described in this article has been strictly tested in actual use and proved to be effective and practical.
