중화사전망 - 자전 검색 - Oracle 의 쿼리 속도를 향상시키는 방법
Oracle 의 쿼리 속도를 향상시키는 방법
데이터베이스 최적화에 대한 토론은 영원한 주제라고 할 수 있다. 고급 Oracle 최적기는 일반적으로 성능 문제를 묻는 사람에게 데이터베이스를 통계적으로 패키지화하고 데이터베이스 구성 게시 등을 하도록 합니다. 다른 사람들은 최적화를 위해 가장 느린 문장을 잡을 필요가 있다고 생각한다. 하지만 사실, 질문자들은 statspack 은 말할 것도 없고, 실행 계획도 전혀 이해하지 못할 수도 있다. 데이터베이스 최적화는 네트워크, 서버 하드웨어 구성, 운영 체제 구성, Oracle 서버 구성, 데이터 구조 조직, 구체적인 튜닝 등 큰 측면에서 고려해야 한다고 생각합니다. 사실 네트워크, 하드웨어 등이 있습니다. 변경할 수 없습니다. 응용 프로그램은 일반적으로 수정할 수 없습니다. 따라서 데이터베이스 구성 및 데이터 구조에 중점을 두어야 합니다. 먼저 데이터베이스에 좋은 구성을 부여하고 느린 명령문을 최적화하는 것을 고려해 보십시오. 사용자 시스템을 최적화하는 과정에서 데이터베이스를 최적화하는 기본적이고 간단한 방법을 요약했습니다. 이것이 바로 제 3 축입니다. ᄏ 그러나 이러한 방법은 보편적으로 사용되지 않을 수 있으며, 그 중 일부는 부작용이 있을 수도 있지만 일반적으로 OLTP 시스템 및 비용 기반 데이터베이스에 효과적입니다. 그들을 시험해 보세요. (참고: 첨부 파일은 Burleson 이 작성한 데이터베이스 성능 등의 정보를 보고하는 스크립트이며 이 문서에 사용됩니다. ) 을 참조하십시오
1. 적절한 SGA 를 설정합니다
사람들은 종종 서버 하드웨어가 좋다고 불평하지만 갑골문은 느리다. 메모리 할당이 불합리해서 생긴 것 같다. (1) 메모리가 5 12M 이라고 가정하면 일반적으로 작은 응용 프로그램입니다. 오라클에서는 SGA 를 약 240M 로 권장합니다. 여기서 * * * * 공유 풀 (SHARED_POOL_SIZE) 은 실제 사용자 및 질의 수에 따라 60M 에서 80M 사이로 설정할 수 있습니다. 블록 버퍼는 대략 120M- 150M 에 할당될 수 있습니다. 8i 에서 DB_BLOCK_BUFFERS 를 설정해야 합니다. DB_BLOCK_BUFFER*DB_BLOCK_SIZE 는 블록 버퍼 크기와 같습니다. 9i 이하의 데이터 버퍼는 db_cache_size 를 사용하여 직접 할당할 수 있습니다.
(2) 메모리가 1G 라고 가정하면 Oracle 의 SGA 는 500m 할당 고려: * * * * * 공유 풀은 100M 에서 150M 으로 할당
(3)2G 메모리, SGA 는 1.2G 할당을 고려하고, * * * 300M 에서 500M 까지의 메모리 풀을 공유하고 나머지 메모리를 블록 버퍼에 할당할 수 있습니다.
(4)2G 이상 메모리: * * 300M ~ 500M 의 풀을 즐기면 충분합니다. 많이 도움이 되지 않습니다. (비티 _ 비 오는 날 주제) 데이터 버퍼는 가능한 한 커야 하지만, 우리는 두 가지 문제점을 주의해야 한다. 하나는 운영 체제와 기타 어플리케이션에 충분한 메모리를 남겨 두는 것이고, 다른 하나는 32 비트 운영 체제에 대한 Oracle 의 SGA 에는 1.75G 의 제한이 있다. 일부 32 비트 운영 체제는이 제한을 초과 할 수 있습니다. 방법은 비티의 걸작을 보십시오.
2. 테이블과 색인을 분석하고 최적화 모드를 변경합니다.
Oracle 의 기본 최적화 모드는 CHOOSE 입니다. 이 경우 테이블을 분석하지 않으면 일반적으로 질의가 인덱스가 아닌 전체 테이블을 스캔합니다. 이로 인해 일반적으로 디스크 I/O 가 너무 많아 쿼리 속도가 느려질 수 있습니다. 실행 계획 안정성을 사용하지 않을 경우 테이블과 색인을 분석해야 합니다. 이렇게 하면 질의 속도가 직접 향상될 수 있습니다. 테이블 분석 명령은 인덱스 분석에 사용할 수 있고 인덱스 분석 명령은 다음 작업에 사용할 수 있습니다. 1 million 보다 작은 테이블의 경우 전체 테이블을 분석할 수 있고, 매우 큰 테이블의 경우 백분율로 분석할 수 있지만 백분율이 너무 낮아서는 안 됩니다. 그렇지 않으면 생성된 통계가 정확하지 않을 수 있습니다. DBA_TABLES 의 LAST_ANALYZED 열을 통해 분석 또는 분석 시간이 지났는지 확인할 수 있으며 인덱스는 DBA_INDEXES 의 LAST_ANALYZED 열을 통과할 수 있습니다.
다음 예는 분석 전후의 속도 비교를 보여줍니다. 표 CASE_GA_AJZLZ 에는 약 350,000 개의 데이터와 1 개의 기본 키가 있습니다. 먼저 SQLPLUS 에서 자동 질의 실행 계획 기능을 켭니다. \RDBMS\ADMIN\utlxplan.sql 을 처음 실행하여 PLAN_TABLE 테이블을 작성합니다.
SQL> 자동 추적 설정
SQL> 설정이 정기적으로 열립니다
SET AUTOTRACE ON 을 통해 명령문 실행 계획을 확인하고 SET TIMING ON 을 통해 명령문 실행 시간을 확인합니다.
Sql> select count (*) from case _ ga _ ajzlz;
개수 (*)
--
346639
시간 소모: 00: 00: 2 1.38
계획을 집행하다
0 SELECT 문 최적기 = 선택
1 0 정렬 (집계)
21'case _ ga _ ajzlz' 에 대한 테이블 액세스 (전체)
...... ...... ...... ......
위 분석에서 전체 테이블 스캔을 수행하는 명령문을 표시하는 전체 테이블 액세스 (FULL) 를 확인합니다. 질의에는 2 1.38 초가 걸렸습니다. 이 시점에서 테이블은 아직 분석되지 않았습니다. 다음 표를 분석해 보겠습니다.
SQL> 분석 테이블 CASE_GA_AJZLZ 컴퓨터 통계;
테이블이 분석되었습니다. 경과 시간: 00: 05: 357.63. 그런 다음 쿼리를 실행합니다.
Sql> select count (*) from case _ ga _ ajzlz;
개수 (*)
--
346639
경과 시간: 00: 00: 00.7 1
계획을 집행하다
0 SELECT 문 최적기 = first _ rows (cost = 351card =1)
1 0 정렬 (집계)
21'PK _ ajzlz' 의 색인 (빠른 전체 스캔) (비용 =35 1
카드 =34635 1)
...... ...... ...... ......
이 시간은 0.7 1 초밖에 걸리지 않았습니다! 인덱스 (빠른 전체 스캔) 에 감사드립니다. 분석 테이블을 통해 쿼리는 PK_AJZLZ 인덱스를 사용하여 디스크 I/O 를 크게 줄이고 속도를 크게 높입니다! 사용자가 GAXZUSR 이라고 가정하면 다음 유틸리티 명령문을 사용하여 사용자의 모든 테이블과 인덱스를 생성하고 분석할 수 있습니다.
SQL> 설정 페이지 크기 0
Sql> spool d: \ analyze _ tables.sql;
SQL> "분석 테이블" | | 소유자 | | "선택. "| | 테이블 이름 | |'
통계를 계산합니다. From dba_tables 여기서 owner =' gaxzusr';
SQL> 해제 선
Sql> spool spool d: \ analyze _ indexes.sql;
SQL>' 인덱스 분석' | | 소유자 | |' 을 선택합니다. " | | index _ name | |
통계를 계산합니다. From dba_indexes 여기서 owner =' gaxzusr';
SQL> 해제 선
SQL & gt@d:\analyze_tables.sql
SQL & gt@d:\analyze_indexes.sql
설명: 위 명령문은 각각 GAXZUSR 의 모든 테이블과 인덱스를 분석하는 두 개의 SQL 파일을 생성합니다. 테이블을 백분율로 분석해야 하는 경우 스크립트를 수정할 수 있습니다. 위의 단계를 통해 테이블 및 색인 분석을 완료하고 속도 향상을 테스트할 수 있습니다. 특히 데이터가 대량으로 업데이트되는 경우 위의 명령문을 정기적으로 실행하는 것이 좋습니다.
물론 테이블과 색인은 dbms_stats 를 통해 분석하는 것이 더 편리합니다. 그러나 나는 여전히 위의 방법에 익숙하다. 왜냐하면 성공은 직접적으로 표현될 것이기 때문이다.
또한 최적화 모드를 수정할 수 있습니다. Optimizer_mode 값은 RULE, CHOOSE, FIRST_ROWS 및 ALL_ROWS 일 수 있습니다. OLTP 시스템의 경우 FIRST_ROWS 로 변경하여 질의가 가능한 한 빨리 결과를 반환하도록 할 수 있습니다. 이렇게 하면 분석을 하지 않아도 쿼리 성능이 전반적으로 향상될 수 있습니다. 그러나 테이블과 인덱스에 대한 분석은 가장 적합한 실행 계획을 찾는 데 도움이 됩니다.
3.Set cursor_sharing=FORCE 또는 이와 유사한 내용.
이 방법은 8i 에서만 사용할 수 있으며 oracle805 에서는 지원되지 않습니다. 이 매개 변수를 설정하면 * * * 가 다른 단어를 사용하여 명령문 실행 설명 계획을 즐길 수 있습니다. 예를 들어, * * * 는 다음 두 문을 사용할 수 있습니다.
SQL & gtSELECT * FROM MYTABLE 여기서 name =' Tom'
SQL & gtSELECT * FROM MYTABLE 여기서 NAME =‘Turner‘'
이 방법을 사용하면 버퍼 활용도가 낮은 문제를 크게 줄이고 명령문을 다시 해석하지 않아도 됩니다. 이 기능을 사용하면 하드 분석으로 인한 성능 저하 문제를 크게 해결할 수 있습니다. 개인의 느낌은 시스템의 실제 상황에 따라 이 매개변수를 FORCE 로 변경할지 여부를 결정할 수 있다. 기본적으로 이 매개변수는 정확합니다. 단, 수정 전에 ORACLE 에 패치를 적용해야 합니다. 그렇지 않으면 수정 후 Oracle 이 100% 의 CPU 를 차지하여 사용할 수 없게 됩니다. ORACLE9i 의 경우 SIMILAR 로 설정할 수 있습니다. similar 는 FORCE 와 EXACT 의 장점을 결합한 것입니다. 그러나 이 기능을 사용할 때는 주의해야 합니다. 이 매개변수도 큰 부정적인 영향을 미칠 수 있습니다!
4. 못에 일반적으로 사용되는 작은 테이블과 색인은 KEEP 풀의 데이터 캐시에 있습니다.
메모리에서 데이터를 읽는 속도가 하드 디스크보다 훨씬 빠릅니다. 메모리가 하드 드라이브보다 14000 배 빠르게 데이터를 읽는다고 합니다! 자원이 충분하면 테이블 전체에서 자주 스캔되는 공통 테이블을 메모리에 고정시키는 것이 좋습니다. ORACLE8i 이후 ALTER TABLE 테이블 저장 영역 (buffer _ pool keep) 을 사용할 수 있는 alter table 테이블 이름 캐시만 사용하면 됩니다. 일반적으로 메모리 크기 등의 요소에 따라 200 개 블록 내에 있는 테이블을 keep 풀에 넣는 것을 고려해 볼 수 있습니다. 요구 사항을 충족하는 테이블 또는 인덱스를 찾는 방법에 대해 이 문서에 제공된 access.sql 및 access_report.sql 을 사용할 수 있습니다. 이 두 극본은 유명한 갑골문 전문가 벨리슨이 쓴 것이다. 또한 읽은 후 실제 상황에 맞게 스크립트를 조정할 수 있습니다. 인덱스의 경우 인덱스 이름 저장소 (buffer _ poolKeep) 를 변경하여 KEEP 풀에 고정할 수 있습니다.
KEEP 풀에서 테이블을 설정하려면 몇 가지 준비 작업이 필요합니다. ORACLE9i 에는 DB_KEEP_CACHE_SIZE 를 설정하고 8i 에는 buffer_pool_keep 를 설정해야 합니다. 8i 에서는 db_block_lru_latches 를 수정해야 합니다. 기본적으로 이 매개변수는 1 이며 buffer_pool_keep 를 사용할 수 없습니다. 이 매개 변수는 2 * 3 * CPUs 수보다 작아야 하지만 DB_KEEP_CACHE_BUFFER 를 설정하기 위해 1 보다 커야 합니다. Buffer_pool_keep 는 db_block_buffers 에서 지정되므로 db_block_buffers 보다 작습니다. 이러한 매개변수를 설정하면 메모리에 공통 오브젝트를 영구적으로 고정할 수 있습니다.
동사 (verb 의 약자) 는 최적기의 최대 권한을 설정합니다
다중 테이블 연결 쿼리의 경우 CBO (비용 기반 최적화) 를 사용하면 ORACLE 은 다양한 운영 시나리오를 계산하고 최적의 시나리오를 선택합니다. 이 매개변수는 Oracle 선택 최적 시나리오 수를 설정합니다. 설정이 너무 크면 최적 시나리오를 계산하는 데 시간이 오래 걸립니다. Oracle805 및 8i 의 기본값은 80000 이고 8 은 2000 으로 변경하는 것이 좋습니다. 9i 기본값은 이미 2000 입니다.
자동사는 분류 매개 변수를 조정하지 않습니다.
(1)SORT_AREA_SIZE: 정렬에 사용되는 기본 SORT _ AREA _ SIZE 는 32K 이며 일반적으로 약간 작습니다. 일반적으로 1M( 1048576) 으로 설정할 수 있습니다. 각 접속마다 동일한 정렬 메모리를 할당해야 하기 때문에 이 매개변수를 너무 크게 설정할 수 없습니다.
(2)SORT _ multi block _ READ _ COUNT: 임시 테이블스페이스의 정렬 성능을 향상시키려면 이 매개변수를 늘립니다. 이 매개변수의 기본값은 2 이며 32 로 변경하여 정렬 질의 시간의 변화를 비교할 수 있습니다. 이 매개변수의 최대값은 계단참에 따라 다릅니다.