ORACLE 데이터베이스 성능의 인스턴스 최적화

이 글은 주로 ORACLE 데이터베이스 성능 최적화 문제 분석과 해결 방법을 사례를 통해 소개하고 있으니, 도움이 필요한 친구들이 참고하시면 좋겠습니다. ORACLE 데이터베이스의 최적화 방법은 MYSQL의 최적화 방법과 매우 다릅니다. 오늘은 ORACLE 데이터베이스 인스턴스를 통해 테이블, 데이터 등 다양한 측면에서 ORACLE 데이터베이스를 최적화하는 방법을 분석해 보겠습니다.

tsfree.sql view

이 SQL 문은 각 테이블스페이스의 총 공간 크기와 각 테이블스페이스의 사용 가능한 총 공간 크기를 빠르게 비교합니다.

테이블 공간은 데이터베이스의 논리적 분할, 즉 A 테이블스페이스입니다. 하나의 데이터베이스에만 속할 수 있습니다. 모든 데이터베이스 개체는 지정된 테이블스페이스에 저장됩니다. 하지만 주로 테이블을 저장하므로 테이블스페이스라고 부른다.

SELECT FS.TABLESPACE_NAME "Talbspace",
(DF.TOTALSPACE - FS.FREESPACE) "Userd MB",
FS.FREESPACE "Free MB",
DF.TOTALSPACE "Total MB",
ROUND(100 * (FS.FREESPACE / DF.TOTALSPACE)) "Pct Free" FROM
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1048576) TOTALSPACE FROM
DBA_DATA_FILES GROUP BY TABLESPACE_NAME) DF,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1048576) FREESPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) FS WHERE DF.TABLESPACE_NAME = FS.TABLESPACE_NAME;

varray 테이블 사용법

CREATE OR REPLACE TYPE EMPLOYER_NAME AS OBJECT (E_NAME VARCHAR(40));

CREATE OR REPLACE TYPE PRIOR_EMPLOYER_NAME_ARR AS VARRAY(10) OF 
EMPLOYER_NAME;

CREATE OR REPLACE TYPE FULL_MAILLING_ADRESS_TYPE AS OBJECT(STREET 
VARCHAR2(80), CITY VARCHAR2(80), STATE CHAR(2), ZIP VARCHAR2(10));

CREATE OR REPLACE TYPE EMPLOYEE AS OBJECT(LAST_NAME VARCHAR(40), 
FULL_ADDRESS FULL_MAILLING_ADRESS_TYPE, PRIOR_EMPLOYERS 
PRIOR_EMPLOYER_NAME_ARR);

CREATE TABLE EMP OF EMPLOYEE;

INSERT INTO EMP VALUES('Jim', FULL_MAILLING_ADRESS_TYPE('Airplan Ave', 'Rocky', 'NC', '2343'), PRIOR_EMPLOYER_NAME_ARR(EMPLOYER_NAME('IBM'), EMPLOYER_NAME('APPLE'), EMPLOYER_NAME('CNN')));

--Rollback

DROP TYPE PRIOR_EMPLOYER_NAME_ARR FORCE;
DROP TYPE FULL_MAILLING_ADRESS_TYPE FORCE ;
DROP TYPE EMPLOYEE FORCE;
DROP TYPE EMPLOYER_NAME FORCE;
DROP TABLE EMP ;
COMMIT;
SELECT P.LAST_NAME, PRIOR_EMPLOYERS.*
FROM EMP P, TABLE(P.PRIOR_EMPLOYERS) PRIOR_EMPLOYERS
WHERE P.LAST_NAME = 'Jim';

SQL 실행 프로세스

1, 보안을 확인하고 sql 데이터 실행자에는
2 실행 권한이 있고
3 sql 구문 확인
4 발생할 수 있는 쿼리 다시 작성

创建执行计划
生产器接受经过解析的sql
捆绑执行计划 执行执行计划 读取结果记录 排序结果集

실행

1，全表扫描 db_file_multiblock_read_count = 128
一次性最大读取block的数量
Oracle开启并行： Alter table employee parallel degree 35;
 顺序读取，直到结尾
1，当表中不存在索引
2，查询中不包含where字句
3，内置函数中的索引无效
4，like操作 %开头
5，使用基于成本优化器 数据量少时
6，当初始化文件中存在optimizer_mode = all_rows
7，负向条件查询不能使用索引 例如 status != 0, not in, not exists 可以优化为 in (2,3);

SQL 문을 실행하는 권한이 있습니다. 상황 전체 테이블 스캔이 발생합니다:

1，使用null条件查询导致全表扫，因为索引不能为空
为了绕过全表扫这个问题，可以采取这样的方法 
update emp set name = 'N/A' where name is null; 
select name from emp where name = 'N/A';
2，对没有索引的字段查询，找到where条件后面的查询不带索引的字段，加索引可以
大大提高查询性能。
3，带有like条件的查询 like '%x%' 全表扫描，like 'x%' 不会全表扫，因为like
以字符开始。
4，内置的函数使索引无效，对于Date类型的数据来说非常的严重
内置函数 （to_date，to_char）
如果没有创建与内置函数匹配的基于函数的索引，那么这些函数通常会导致sql优化器全表扫描
select name from emp where date < sysdate -8;
检查where子句脚本是否含有 substr to_char decode
SELECT SQL_TEXT, DISK_READS, EXECUTIONS, PARSE_CALLS
FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE &#39;%substr%&#39;
OR LOWER(SQL_TEXT) LIKE &#39;%to_char%&#39;
OR LOWER(SQL_TEXT) LIKE &#39;%decode%&#39;
ORDER BY DISK_READS DESC;
使用函数索引解决这个问题

5，all_rows 优化器目标是提高吞吐量而且倾向于使用全表扫描，因此 对于任何一
个要求sql快速查询返回部分结果集而言，optimizer_mode 
应该设置为first_rows

6,经验上，能过滤80%数据时就可以使用索引，对于订单状态，如果状态很少，不宜
使用索引，如果状态值很多可以使用索引。

7，如果查询字段大部分是单条数据查询，使用Hash索引性能更好
原因:B-TREE 索引的时间复杂度是O(log(n))
Hash 索引的时间复杂度是O(1)
   
8,符合索引最左前缀，例如建立符合索引(passWord,userName)
select * from user u where u.pass_word = ? and u.user_name = ? 可以命中索引
select * from user u where u.user_name = ? and u.pass_word= ? 可以命中索引
select * from user u where u.pass_word = ? 可以命中索引
select * from user u where u.user_name = ? 不可以命中索引

매우 영향력 있는 SQL 문을 찾는 방법

 视图 v$sqlarea ,下列参数按照重要性从高到低排序
 executions ：越经常执行的sql语句就应当越早的调整，因为它会对整体的性能产生巨大的影响。
 disk_reads: 磁盘读取，高的磁盘读取量可能表明查询导致过多的输入输出量。
 rows_processed:记录处理，处理大量的记录的查询会导致较大的输入输出量，同时在排序的时候对TEMP表空间产生影响。
 buffer_gets:缓冲区读取，高的缓冲读取量可能表明了一个高资源使用量的查询。
 sorts:排序会导致速度的明显减低，尤其是在TEMP表空间中进行的排序。

2. 열

Oracle对单表簇和多表簇进行散列存储，用来在连接操作中减低输入 输出

3, ROWID 액세스

을 가져옵니다.

Rowid 단일 데이터에 접근하는 가장 빠른 방법은 먼저 인덱스에서 ROWID를 수집한 후 ROWID

Index 접근 방식

索引都可以看做一组符合主键和ROWID的组合，索引访问的目的是收集对目标快速读取时所需要的ROWID

B-tree 인덱스를 통해 데이터를 읽어오는 것입니다. 함수 인덱스를 기반으로 한 비트맵 인덱스입니다.

인덱스 범위 스캔: 하나 이상의 ROWID 인덱스 값을 오름차순으로 읽습니다.

eg：select * from table where a = 'a';

빠른 전체 인덱스 스캔

eg: 고유한 색상 선택, 카운트(*) 색상별로 테이블 그룹화;

단일 인덱스 스캔: 단일 ROWID 읽기

내림차순 인덱스 범위 스캔: 하나 이상의 ROWID 인덱스 값을 내림차순으로 읽기

AND - EQUALS: a = '인 테이블에서 * 선택 a' 및 b > 34; where 절에서 여러 ROWID 수집

Join 작업

중첩 루프 조인

Hash 조인

Hash 조인은 일반적으로 특히 구동 테이블 및 필터에서 중첩 루프 조인보다 빠릅니다. 쿼리의 where 절에서 소수의 레코드만 남을 경우

정렬 병합 연결

연결 프롬프트:

表反向连接提示，例如，NOT IN, NOT EXISTS
尽量避免使用 NOT IN 子句（它将调用子查询），而应该使用NOT EXISTS 子句(它将调用相关联的子查询)，
因为如果子查询返回的任何一条记录中包含空值，那么该查询将不会返回记录,如果允许NOT IN 子句查询为空，那么
这种查询的性能非常的低，子查询会在外层查询块中对每一条记录重新执行一次。

sort_area_size_init.ora 매개변수, 콘솔에서 sort_area_size 보기

쿼리문: show 매개변수 sort_area_size;

디스크 정렬의 실행 속도는 메모리 정렬의 실행 속도보다 14,000배 느립니다.

디스크 정렬이 비용이 많이 드는 이유는 다음과 같습니다.

1, 동일한 메모리 정렬 비교가 너무 느립니다
2. 디스크 정렬은 임시 테이블 공간의 리소스를 소비합니다

데이터베이스는 2개의 임시 테이블 공간을 할당합니다:

사용자 이름='SYS';

인 dba_users에서 DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE를 선택합니다. select * from dba_temp_free_space;

Oracle 임시 테이블 공간은 주로 임시 테이블 데이터 세그먼트 할당과 정렬 요약 오버플로 세그먼트라는 두 가지 주요 역할을 수행합니다.

정렬된 요약 오버플로의 범위는 상대적으로 넓습니다. SQL 문에서 order by/group by 및 기타 작업을 수행하는 첫 번째 단계는 PGA의 메모리 정렬 영역, 해시 영역 및 비트맵 영역을 선택하는 것입니다.

SQL에서 사용하는 정렬 공간이 매우 높고 단일 서버 프로세스에 해당하는 PGA가 정렬 요구 사항을 지원하기에 충분하지 않은 경우 임시 테이블 공간은 정렬 세그먼트의 데이터 쓰기 역할을 합니다.

그리고 디스크 정렬은 단일 작업 속도를 늦추고 Oracle 인스턴스에서 실행되는 다른 작업에도 영향을 미치며, 너무 많은 디스크 정렬은 과도한 유휴 버퍼 대기

로 이어지며 다른 작업의 데이터 블록이 버퍼에서 페이징 아웃됩니다. 수영장은 비싸요.

Oracle은 먼저 sort_area_size로 할당된 메모리 영역에서 정렬을 시도합니다. Oracle이 메모리 정렬을 할 수 없는 경우에만 디스크 정렬

을 호출하고 메모리 프레임을 TEMP 테이블스페이스로 마이그레이션하여 정렬을 계속합니다.

인덱스 범위 스캔 사용의 일반 원칙

 -- 对于原始排序的表， 仅读取少于40%的表记录查询就应该使用索引范围扫描，反之，多余40%，使用全表扫。
 -- 对于未排序的表， 仅读取少于7%的表记录查询就应该使用索引范围扫描，反之，多余7%，使用全表扫。

테이블 액세스 방법

sql 최적화 프로그램

모든 SQL 문에는 테이블 액세스를 최적화하는 고유한 방법이 있으며 이를 찾는 것이 작업입니다. 그리고 오랫동안 사용하세요.

db_file_multiblock_read_count

SQL 문에 대해 최소한의 리소스로 가장 빠른 실행 계획을 생성하는 것이 목적입니다

1, 규칙 기반 최적화

步骤 
对于在where子句中的每一个表
-- 生成一个可行的执行计划列表，这个列表中列出所有可以用来访问表的路径
-- 为每一个执行计划指定级别数值
-- 选择级别数值最低的计划
-- 对结果集的选择级别最低 连接方法进行评估
 
基于规则优化器（PBO）特征
- 总是使用索引，使用索引永远比使用全表扫描或使用排序合并连接（排序合并连接不需要索引）更加可取
- 总是从驱动表开始 在from字句的最后一个表是驱动表,在这个表中选择的记录数应该是最少(查询返回值最少)，RBO在执行嵌套循环连接
操作时，将这个驱动表作为第一个操作表。
- 只有在不可避免的情况下才使用全表扫描
-任何索引都可以
- 有时越简单越好

2, 비용 기반 최적화(CBO)

 基于规则优化提供更加复杂的优化替代方案
 ANALYZE TABLE TT_TCAS_HK_QTY COMPUTE STATISTICS;
 ANALYZE TABLE TT_TCAS_HK_QTY ESTIMATE STATISTICS SAMPLE 5000 ROWS;
 ANALYZE TABLE TT_TCAS_HK_QTY ESTIMATE STATISTICS SAMPLE 5000 ROWS FOR ALL INDEXED COLUMNS;
 
 CBO在以下情况会选择错误的全表扫描
 1，最高峰值过高
 2，错误的optimizer_mode,如果optimizer_mode设置为all_rows,choose,那么sql优化器会倾向于使用全表扫描。
 3，多表连接，存在多余3张表连接时，即使连接中存在索引，cbo仍然会对这些表进行全表扫描。
 4，不平衡的索引分布，比如 color = 'blue' color字段上有索引,但是只有1%的记录属于blue，

SQL의 SGA 통계

이름 선택, v$sysstat에서 값 선택, 이름은 'table%'

table scans(short table) -- 작은 테이블의 전체 테이블 스캔 횟수

table scans(long table) -- 큰 테이블의 전체 테이블 스캔 횟수, 큰 테이블의 스캔 횟수를 줄일지 여부를 평가합니다. 인덱스를 추가하거나 Oracle Parallel(opq)을 호출하여 쿼리 실행 속도를 향상시킵니다.

table scans Rows Gotten -- 이 숫자는 전체 테이블 스캔으로 스캔한 레코드 수를 나타냅니다.

table scans block Gotten -- 얻은 데이터베이스 스캔 수

Table fetch by rowid -- 레코드 수 여기서 인덱스는 일반적으로 중첩 루프 연결입니다

table　fetch by Continued Row -- 이 숫자는 라이브러리 캐시에서 여러 번 사용할 수 있는 다른 데이터 블록

SQL과 연결된 레코드 수를 나타냅니다

오라클이 확인 중입니다." 동일한 "SQL 문"에 문제가 있습니다

예: select from customer; Select From Customer; 문자의 대소문자가 달라도 Oracle은 두 번째 SQL 문을 다시 컴파일하여 실행합니다.

관련 권장 사항:

자세한 설명 oracle 페이징 쿼리의 기본 원칙

MySQL과 Oracle 간의 "경쟁"

oracle 확장 사용 방법 요약

위 내용은 ORACLE 데이터베이스 성능의 인스턴스 최적화의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!