Oracle optimization scheme in small memory

1. Current Situation 1.1 table space is not properly planned, causing all tables under all users to be created under the default table space

Oracle does not use a specific table space plan for a particular data table, resulting in the default tablespace file (dbf) stored in all database tables in the current instance, as seen from performance monitoring at peak time, where Oracle data is on disk utilization of 100%, The rest of the disk is empty and there is no IO request, resulting in wasted resources. (floods and floods, droughts and deaths)

1.2 Queries with large IO requirements are performed at peak times

The statistical analysis of the performance monitoring program from the Oracke comes from 7 to 10 in the morning at peak query times, while some SQL queries bring a large amount of IO reads. For example, select * does not add any paging functionality (guess is data synchronization), so you need to discharge large IO queries against Oracle's performance analysis and try to modify them to off-peak execution.

2. Optimize scenario 2.1 Increase disk Utilization 2.1.1 RAID/LVM

With raid or LVM scenarios, multiple disks can be combined into one disk for use, and the associated IO requests will eventually be dispersed across multiple disks depending on the specific logic to improve overall IO.
Http://www.itpub.net/thread-787910-2-1.html This post said that LVM almost does not affect IO, but because of the addition of multiple disks so that IO can improve.

2.1.2 ORACLE comes with table space expansion

This blog lists four ways to increase the size of the table space, one of:

ALTER TABLESPACE app_data ADD DATAFILE‘put dbf file path here‘ SIZE 500GAUTOEXTEND ON NEXT 1G MAXSIZE 2048G;

The above-mentioned situation is to create a DBF file on another disk to directly expand the current tablespace size, but it is also necessary to refer to the official documentation for details such as whether Oracle will automatically balance the data after expansion:
Oracle DBA Doc

2.2 Resolving Current disk hotspot phenomena

If you can solve the problem in 2.1, it is possible that the current issue will be solved by the way. The current state is that the existing data are all on a disk, all the query IO or go to this disk, Oracle's Table space Auto-expansion can automatically balance the data? If not, how to solve the current query pressure is on the disk above the problem.

2.2.1 Partition Migration

Oracle Partition Migration This blog describes how to migrate a specified Oracle partition to another table space, and refer to this article to try to create a corresponding tablespace on another disk and then migrate the partitions to that tablespace to scatter the data. Approximate operation:

ALTER TABLE [tableName] MOVE PARTITION [partitionId] TABLESPACE [tableSpaceName];

oracle:moving Table Partitions

2.2.2 Direct Full Table migration

If you want to migrate the entire table to the past, you can do so
-- 迁移 alter table [tableName] move tablespace [spaceName] -- 重建索引 alter index [index_name] rebuild tablespace [tablespace_name]

3. In summary

Decide to use Oracle's own move table to carry out data migration.

3.1 Specific operations 3.1.1 using LVM or RAID to consolidate multiple free disks as a single disk

Lvm:

pvcreate /dev/sda{1,2,3,4}vgcreate ORCL /dev/sda{1}vgextend ORCL /dev/sda{2,3,4}lvcreate ...

3.1.2 Creating table Spaces

Grammar

-- 创建create tablespace test_user datafile ‘f:\test_user\zzg_data.dbf‘ size 200M;-- 授权grant create session,create table,create view,create sequence,unlimited tablespace to test_user;  

3.1.3 Migrating database tables (full table migrations) [Non-direct migration with partitioning, need to be migrated in partitioned units]

Move table

-- 迁移alter table [tableName] move tablespace [spaceName]-- 重建索引alter index [index_name] rebuild tablespace [tablespace_name];

3.1.4 Migrating in partitioned units

oracle:moving Table Partitions

Batch generation of migration statements and execution

select ‘alter table traffic_cloud.‘||table_name||‘ move partition ‘||partition_name||‘ tablespace {spaceName};‘ from user_tab_partitions where table_name = ‘ILLEGAL_DATA‘;

• Do not forget to change the dynamic increase the partition of the command, so that the next time you add the partition automatically added to the new table space *

Oracle optimization scheme in small memory