Improve the query statistics of Oracle databases (transfer)

xiaoxiao2021-03-06 15

In large database systems, the query statistics are often used, but for systems with large data volume, users often feel very slow when performing complex query statistics, and cannot meet application requirements, which requires us to make reasonable settings when designing database systems. Increase the speed of query statistics. This article combines the author's project development experience, explains the specific setup method. Taking the Oracle7.33 database system as an example, we combine the characteristics of the project when developing a large Oracle database system, in accordance with the security and efficiency principles, the database has been physically designed to greatly improve the query statistics of the database. Summarize as follows: 1) Expand data table space to 500m, used to store data of this system; 2) The initial size of the section is 10K, the growth rate is 10K, the growth rate is 1; 3) User temporary space increase Large 40m; 4) System temporary table space and rollback segment table space increased by 40M, and newly built four backgrous; 5) Need to join inquiry, and the amount of data is large, the list, the list, the send and receive sheet In a cluster; 6) Provide timed backups, the backup file is placed on another machine. Setting the SQL statement of the data table space as follows:

CREATE TABLESPACE WXGL_DATA1 DATAFILE 'WXGL_DATA1.ORA' SIZE 500M Online;

Increase the SQL statement of the system temporary table space and rollback segment table space as follows:

Alter TableSpace Temporary_data add datafile 'tmp2orcl.ora' size 40m;

Alter TableSpace Rollback_Data Add DataFile 'rbs2orcl.ora' size 40m;

Setting the data space in the specified data file SQL statement as follows:

Create User ZBGL Identified by ZBGL;

Grant dba to zbgl;

Alter User ZBGL Default TableSpace Wxgl_data1 Temporary TableSpace Temporary_data;

Set the SQL statement of the five returning segments 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 UPER (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_DataStorage (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;

Placing the inventory inventory inventory inventory in a cluster 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'; (large data volume inventory, etc.) WXGL_DATA1)

Qcfl = 'crete Table QC_QCFL'

'(FLBH Number (2) Primary Key,'

'FLMC VARCHAR (20)'

')'

'TableSpace wxgl_data2'; (other placed in wxgl_data2)

The system's basic data inventory table, the name table is approximately data 80m; one unit generally transmits up 300 times a year, the transmission and reception list is about 50m; system redundancy data 100M, system auxiliary data 10M; therefore, the system requires approximately 240m Now the system opens up 500M of the data space and fully meets storage requirements. Since the system uses redundant data, when the common data is queried, the multi-table link query is avoided, so that although more storage space is used, the query efficiency is greatly improved; at the same time, the system will need to join inquiry. The data is placed in a cluster, so that the space to store these data is physically adjacent, which also increases the query speed. In addition, there is an initorcl.ora file in the Database directory of the Oracle7.33 database, which changes the settings can also improve the query statistics. The content of this file is as follows:

######################################################################################################################################################################################################################################################################################################## #####################

# 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 area generic to any size installation.

# For parameters That Require Different Values in Different Size

# INSTALLATIONS, Three Scenarios Have Been Province: Small, Medium

# and large. Any Parameter That Needs to be tuned accounting to

# Installation Size Will Have Three Settings, Each One Commented

# According to installation size.

# Use the folowing table to approximate the sig 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 The 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 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 value provided here or by providing

# Your owne = line @ Into Each Instance-Specific

# Init file what 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 # inTIAL

# 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 # limited 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 = 1Remote_login_passwordfile = Shared

You can modify the file db_file_multiblock_read_count, db_block_buffers, shared_pool_size, processes, log_buffer, sequence_cache_entries, sequence_cache_hash_buckets other items (both prompt file), change the appropriate large value according to the characteristics and needs of the system used by the database server can improve the speed of query statistics. It should be noted here that the log_archive_start = true item must be changed to FALSE, and the settings must take effect. The Oracle database setting method introduced in this article has been strictly tested in the actual use of users, proves to be effective and practical.

转载请注明原文地址:https://www.9cbs.com/read-47471.html

9cbs

New Post(0)