ORACLE9i_性能调优基础二(Database Configuration and IO Issues)

最新推荐文章于 2021-04-12 21:57:16 发布

夜归人_

最新推荐文章于 2021-04-12 21:57:16 发布

阅读量439

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/night_person_/article/details/45095881

版权

oracle 专栏收录该内容

31 篇文章 1 订阅

订阅专栏

Objective

after completing this lesson, you should be able to do the following:

1.List the advantages of distributing different Oracle file types

2.Diagnose tablespace usage problesm

3.List reasons for partitioning data in tablespaces

4.Describe how checkpoints work

5.Monitor and tune checkpoints

6.Monitor and tune redo logs

Oracle I/O Architecture

Oracle Processes and Files

Performance Guidelines

Basic performance rules are as follows:

1.Keep disk I/O to a minimum

2.Spread your disk load across disk devices and controllers

3.Use temporary tablespaces where appropriate.

Distributing Files

redo log files: 要选速度最快的磁盘

Tablespace Usage

1.Reserve the system tablespace for data dictionary objects

2.Create Locally managed tablespaces to avoid space management issues

3.Split tables and indexes into separate tablespaces

4.Create rollback segments in their own tablespaces

5.Store very large objects in their own tablespace.

6.Create one or more temporary tablespaces

Locally Managed SYS TS

Tools for I/O Statistics

Using v$filestat View

The view displays the number of physical reads and writes done and the total number of single-block and multiblock I/Os done at file level.

SQL> col name format a51;                                                                                                    
SQL> select f.phyrds, f.phywrts, d.name from v$datafile d, v$filestat f  where d.file#=f.file# order by d.name;              

    PHYRDS    PHYWRTS NAME
---------- ---------- ---------------------------------------------------
        10          2 /u01/app/oracle/oradata/king/backup/app02_01.dbf
        14          2 /u01/app/oracle/oradata/king/backup/app1_01.dbf
         9          2 /u01/app/oracle/oradata/king/backup/app3_01.dbf
         0       1088 /u01/app/oracle/oradata/king/backup/perfstat_01.dbf
      1389       2140 /u01/app/oracle/oradata/king/backup/sysaux01.dbf
      6240        853 /u01/app/oracle/oradata/king/backup/system01.dbf
        45       1189 /u01/app/oracle/oradata/king/backup/undotbs01.dbf
         9          2 /u01/app/oracle/oradata/king/backup/users01.dbf

8 rows selected.

I/O Statistics

SQL>col tablespace format a10;
SQL> col FILE_NAME format a40;
SQL> select d.tablespace_name tablespace, d.file_name, f.phyrds,f.phywrts from v$filestat f, dba_data_files d where f.file#=d.file_id;
TABLESPACE FILE_NAME                                    PHYRDS    PHYWRTS
---------- ---------------------------------------- ---------- ----------
SYSTEM     /u01/app/oracle/oradata/king/backup/syst       6240        862
           em01.dbf
UNDOTBS1   /u01/app/oracle/oradata/king/backup/undo         45       1208
           tbs01.dbf
SYSAUX     /u01/app/oracle/oradata/king/backup/sysa       1389       2154
           ux01.dbf
USERS      /u01/app/oracle/oradata/king/backup/user          9          2
           s01.dbf

File Striping

1.Operating system striping:

a.Use operating system striping software of a redundant array of indexpensive disks(RAID)

b.Determine the right stripe size

2.Manual striping :Use the Create TABLE or ALTER TABLE command with the ALLOCATE clause.

Tuning Full Table Scan Operatings

1.Investigate the need for full table scans.

2.Configure the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter to:

a.Determine the number of database blocks the server reads at once.

b.Influence the execution plan of the cost-based optimizer

3.Monitor long-running full table scans with v$session_longops view

DB_FILE_MULTIBLOCK_READ_COUNT:进行全表扫描，一次读的数据库块数，受底层操作系统限制

Table Scan Statistics

SQL> select name , value from v$sysstat where name like '%table scan%';     

NAME                                                     VALUE
--------------------------------------------------- ----------
table scans (short tables)                               20580
table scans (long tables)                                    6
table scans (rowid ranges)                                   0
table scans (cache partitions)                               0
table scans (direct read)                                    0
table scan rows gotten                                 6867533
table scan blocks gotten                                335358

7 rows selected.

table scans (long tables) 如果这个值对应的 value比较大，则需要调整

Monitoring FTS Operations

1.Determine the progress of long operations using:

SQL> select sid, serial#,opname, to_char(start_time,'hh24:MI:SS') as "START" , (sofar/totalwork)*100 AS PERCENT_COMPLETE from v$session_longops;

2.Use SET_SESSION_LONGOPS to populate v$session_longops

Checkpoints

1.Incremental checkpoints

a.CKPT updates the control file.

b.During a log switch CKPT updates the control file and the data file headers

2.Full checkpoints

a.CKPT updates the control file and the data file headers

b.DBWn writes out all buffers on the checkpoint queue.

Full Checkpoints

Two categories of full checkpoints

1.Complete

2.Tablespace

SQL> show parameter log_checkpoints_to_alert;                                                                                

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert             boolean     FALSE

这个参数为true时，检查点信息记录到alert里边