인덱스가 생성되고 dbms_stats 계산이 사용된 후 쿼리 실행 속도가 느림
저는 150만 줄이 있는 테이블을 가지고 있습니다.열에 반복되지 않는 값이 있는 레코드를 가져오는 쿼리를 실행합니다.인덱스를 만든 후 쿼리 성능이 저하되는 동작을 관찰하고 있습니다.또한 oracle 11g CBO가 쿼리 계획에 대해 더 많은 정보를 제공하는 결정을 내릴 수 있도록 100% 추정 비율(컴퓨팅 모드)이 있는 dbms_stats를 사용하여 통계를 수집했지만 쿼리 실행 시간이 향상되지 않습니다.
SQL> desc tab3;
Name Null? Type
----------------------------------------------
COL1 NUMBER(38)
COL2 VARCHAR2(100)
COL3 VARCHAR2(36)
COL4 VARCHAR2(36)
COL5 VARCHAR2(4000)
COL6 VARCHAR2(4000)
MEASURE_0 VARCHAR2(4000)
MEASURE_1 VARCHAR2(4000)
MEASURE_2 VARCHAR2(4000)
MEASURE_3 VARCHAR2(4000)
MEASURE_4 VARCHAR2(4000)
MEASURE_5 VARCHAR2(4000)
MEASURE_6 VARCHAR2(4000)
MEASURE_7 VARCHAR2(4000)
MEASURE_8 VARCHAR2(4000)
MEASURE_9 VARCHAR2(4000)
»measure_040만 개의 고유 값이 있습니다.
SQL> select count(*) from (select measure_0 from tab3 group by measure_0 having count(*) = 1) abc;
COUNT(*)
----------
403664
다음은 실행 계획에 대한 쿼리입니다. 테이블에 인덱스가 없습니다.
SQL> set autotrace traceonly;
SQL> SELECT * FROM (
2 SELECT
3 (ROWNUM -1) AS COL1,
4 ft.COL1 AS OLD_COL1,
5 ft.COL2,
6 ft.COL3,
7 ft.COL4,
8 ft.COL5,
9 ft.COL6,
10 ft.MEASURE_0,
11 ft.MEASURE_1,
12 ft.MEASURE_2,
13 ft.MEASURE_3,
14 ft.MEASURE_4,
15 ft.MEASURE_5,
16 ft.MEASURE_6,
17 ft.MEASURE_7,
18 ft.MEASURE_8,
19 ft.MEASURE_9
20 FROM tab3 ft
21 WHERE MEASURE_0 IN
22 (
23 SELECT MEASURE_0
24 FROM tab3
25 GROUP BY MEASURE_0
26 HAVING COUNT(*) = 1
27 )
28 ) ABC WHERE COL1 >= 0 AND COL1 <=449;
450 rows selected.
Elapsed: 00:00:01.90
Execution Plan
----------------------------------------------------------
Plan hash value: 3115757351
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1243 | 28M| 717K (1)| 02:23:29 |
|* 1 | VIEW | | 1243 | 28M| 717K (1)| 02:23:29 |
| 2 | COUNT | | | | | |
|* 3 | HASH JOIN | | 1243 | 30M| 717K (1)| 02:23:29 |
| 4 | VIEW | VW_NSO_1 | 1686K| 3219M| 6274 (2)| 00:01:16 |
|* 5 | FILTER | | | | | |
| 6 | HASH GROUP BY | | 1 | 3219M| 6274 (2)| 00:01:16 |
| 7 | TABLE ACCESS FULL| TAB3 | 1686K| 3219M| 6196 (1)| 00:01:15 |
| 8 | TABLE ACCESS FULL | TAB3 | 1686K| 37G| 6211 (1)| 00:01:15 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1">=0 AND "COL1"<=449)
3 - access("MEASURE_0"="MEASURE_0")
5 - filter(COUNT(*)=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
354 recursive calls
0 db block gets
46518 consistent gets
45122 physical reads
0 redo size
43972 bytes sent via SQL*Net to client
715 bytes received via SQL*Net from client
31 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
450 rows processed
쿼리 시간은 1.90초입니다.쿼리를 다시 실행하면 1.66초가 걸립니다.왜 1차 주행에서 더 많은 시간이 소요됩니까?
속도를 높이기 위해 쿼리에 사용된 두 개의 열에 인덱스를 만들었습니다.
SQL> create index ind_tab3_orgid on tab3(COL1);
Index created.
Elapsed: 00:00:01.68
SQL> create index ind_tab3_msr_0 on tab3(measure_0);
Index created.
Elapsed: 00:00:01.83
제가 이 후 처음으로 쿼리를 실행했을 때, 응답하는 데 무려 21초가 걸렸습니다.반면에 후속 실행은 2.9초로 단축되었습니다.오라클은 첫 실행에 왜 그렇게 많은 시간이 걸리죠, 워밍업인가 뭔가..당황스럽네요!
이게 2.9초가 걸릴 때의 계획입니다
450 rows selected.
Elapsed: 00:00:02.92
Execution Plan
----------------------------------------------------------
Plan hash value: 240271480
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1243 | 28M| 711K (1)| 02:22:15 |
|* 1 | VIEW | | 1243 | 28M| 711K (1)| 02:22:15 |
| 2 | COUNT | | | | | |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 1243 | 30M| 711K (1)| 02:22:15 |
| 5 | VIEW | VW_NSO_1 | 1686K| 3219M| 6274 (2)| 00:01:16 |
|* 6 | FILTER | | | | | |
| 7 | HASH GROUP BY | | 1 | 3219M| 6274 (2)| 00:01:16 |
| 8 | TABLE ACCESS FULL | TAB3 | 1686K| 3219M| 6196 (1)| 00:01:15 |
|* 9 | INDEX RANGE SCAN | IND_TAB3_MSR_0 | 1243 | | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| TAB3 | 1243 | 28M| 44 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1">=0 AND "COL1"<=449)
6 - filter(COUNT(*)=1)
9 - access("MEASURE_0"="MEASURE_0")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
660054 consistent gets
22561 physical reads
0 redo size
44358 bytes sent via SQL*Net to client
715 bytes received via SQL*Net from client
31 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
450 rows processed
저는 테이블이 색인되지 않았을 때보다 시간이 더 낮을 것으로 예상했습니다.테이블의 인덱싱된 버전이 인덱싱되지 않은 버전보다 결과를 가져오는 데 더 많은 시간이 걸리는 이유는 무엇입니까?제가 틀린 것이 아니라면 시간이 걸리는 것은 TABLE ACCESS BY INDEX ROWID입니다.TABLE ACCESS FULL을 사용하도록 오라클을 강제할 수 있습니까?
그런 다음 CBO가 컴퓨팅 옵션으로 계획을 개선할 수 있도록 표에 통계를 수집했습니다.그래서 이제 통계는 정확할 것입니다.
SQL> EXECUTE dbms_stats.gather_table_stats (ownname=>'EQUBE67DP', tabname=>'TAB3',estimate_percent=>null,cascade=>true);
PL/SQL procedure successfully completed.
Elapsed: 00:01:02.47
SQL> set autotrace off;
SQL> select COLUMN_NAME,NUM_DISTINCT,SAMPLE_SIZE,HISTOGRAM,LAST_ANALYZED from dba_tab_cols where table_name = 'TAB3' ;
COLUMN_NAME NUM_DISTINCT SAMPLE_SIZE HISTOGRAM LAST_ANALYZED
------------------------------ ------------ ----------- --------------- ---------
COL1 1502257 1502257 NONE 27-JUN-12
COL2 0 NONE 27-JUN-12
COL3 1 1502257 NONE 27-JUN-12
COL4 0 NONE 27-JUN-12
COL5 1502257 1502257 NONE 27-JUN-12
COL6 1502257 1502257 NONE 27-JUN-12
MEASURE_0 405609 1502257 HEIGHT BALANCED 27-JUN-12
MEASURE_1 128570 1502257 NONE 27-JUN-12
MEASURE_2 1502257 1502257 NONE 27-JUN-12
MEASURE_3 185657 1502257 NONE 27-JUN-12
MEASURE_4 901 1502257 NONE 27-JUN-12
MEASURE_5 17 1502257 NONE 27-JUN-12
MEASURE_6 2202 1502257 NONE 27-JUN-12
MEASURE_7 2193 1502257 NONE 27-JUN-12
MEASURE_8 21 1502257 NONE 27-JUN-12
MEASURE_9 27263 1502257 NONE 27-JUN-12
다시 쿼리를 실행했습니다.
450 rows selected.
Elapsed: 00:00:02.95
Execution Plan
----------------------------------------------------------
Plan hash value: 240271480
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31M| 718G| 8046 (2)| 00:01:37 |
|* 1 | VIEW | | 31M| 718G| 8046 (2)| 00:01:37 |
| 2 | COUNT | | | | | |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 31M| 62G| 8046 (2)| 00:01:37 |
| 5 | VIEW | VW_NSO_1 | 4057 | 7931K| 6263 (2)| 00:01:16 |
|* 6 | FILTER | | | | | |
| 7 | HASH GROUP BY | | 1 | 20285 | 6263 (2)| 00:01:16 |
| 8 | TABLE ACCESS FULL | TAB3 | 1502K| 7335K| 6193 (1)| 00:01:15 |
|* 9 | INDEX RANGE SCAN | IND_TAB3_MSR_0 | 4 | | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| TAB3 | 779K| 75M| 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL1">=0 AND "COL1"<=449)
6 - filter(COUNT(*)=1)
9 - access("MEASURE_0"="MEASURE_0")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
660054 consistent gets
22561 physical reads
0 redo size
44358 bytes sent via SQL*Net to client
715 bytes received via SQL*Net from client
31 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
450 rows processed
이번에는 쿼리가 2.9초 만에 다시 실행되었습니다(때로는 3.9초가 소요되기도 했습니다).
제 목표는 쿼리 실행 시간을 최대한 줄이는 것입니다.그러나 인덱스를 추가하거나 통계를 계산한 후 쿼리 시간이 계속 증가했습니다.이러한 현상이 발생하는 이유는 무엇이며 인덱스를 유지함으로써 개선할 수 있는 것입니까?
먼저 Tom Kyte의 말을 인용하겠습니다.
요.
, ." """ 체전검악이지않인좋다않습니지도스덱며으의"
, ." """ 체전검악이지않인좋다않습니지도스덱며으의"
, ." """ 체전검악이지않인좋다않습니지도스덱며으의"
, ." """ 체전검악이지않인좋다않습니지도스덱며으의"
, ." """ 체전검악이지않인좋다않습니지도스덱며으의"
, ." """ 체전검악이지않인좋다않습니지도스덱며으의"
인덱스가 항상 성능을 향상시키는 것은 아닙니다. 마법의 은총은 아닙니다(마치 그런 것이 존재했던 것처럼 :).
이제 당신은 왜 당신의 색인이 더 오래 걸리는지 묻고 있습니다.답은 매우 간단합니다.
- 전체 테이블 스캔: 46518 일관된 gets
- 인덱스와 함께: 660054 일관된 gets.
즉, 다음과 같습니다.Oracle은 전체 테이블 검색보다 인덱스를 사용하여 더 많은 읽기 작업을 수행합니다.이는 다음과 같은 이유로 발생합니다.
- 전체 테이블 스캔 읽기는 대량 작업(한 번에 여러 블록)이므로 많은 데이터를 효율적으로 읽을 수 있습니다.
- 때때로 인덱스에서 읽을 때 동일한 데이터 블록을 두 번 이상 읽게 됩니다.
옵티마이저가 분명히 비효율적인 이 지수를 사용하기로 선택한 이유에 대해, 이것은 심지어는esimate_percent=100 전체 (▁the▁()것"()에서 )MEASURE_0열), 일부 데이터 분포는 여전히 최적화 도구의 단순한 분석으로는 신뢰할 수 없습니다.특히, 분석기는 열 간 및 표 간 종속성을 잘 이해하지 못합니다.이는 잘못된 추정치로 이어지고, 이는 잘못된 계획 선택으로 이어집니다.
편집: CBO의 작업 가설이 이 자체 조인에 전혀 작동하지 않는 것 같습니다(마지막 쿼리는 3,100만 행으로 예상하는 반면 450개만 선택됨!).테이블에 1.5M 행만 있기 때문에 이것은 상당히 혼란스럽습니다.사용 중인 Oracle 버전은 무엇입니까?
자체 조인을 제거할 수 있으므로 분석을 통해 쿼리 성능을 향상시킬 수 있습니다.
SELECT * FROM (
SELECT (ROWNUM -1) AS COL1, ABC.*
FROM (
SELECT
ft.COL1 AS OLD_COL1,
[...],
COUNT(*) OVER (PARTITION BY MEASURE_O) nb_0
FROM tab3 ft
) ABC
WHERE nb_0 = 1
AND ROWNUM - 1 <= 449
) v
WHERE COL1 >= 0;
또한 쿼리를 처음 실행하는 데 일반적으로 더 많은 시간이 걸리는 이유를 물었습니다.회사에 캐시가 있기 때문입니다.데이터베이스 레벨에는 모든 블록을 디스크에서 먼저 복사한 다음 여러 번 읽을 수 있는 SGA가 있습니다(블록을 처음 쿼리할 때는 항상 물리적 읽기).또한 일부 시스템에는 최근에 읽은 데이터가 더 빨리 반환되는 독립적인 시스템 캐시가 있습니다.
자세한 내용은 다음을 참조하십시오.
- 잘못된 추정치로 이어질 수 있는 데이터 불일치에 대한 설명과 SQL 프로파일을 포함하는 솔루션.
이 코드는 어떻게 작동합니까?
SELECT ROWNUM - 1 AS col1
, ft.col1 AS old_col1
, ft.col2
, ft.col3
, ft.col4
, ft.col5
, ft.col6
, ft.measure_0
, ft.measure_1
, ft.measure_2
, ft.measure_3
, ft.measure_4
, ft.measure_5
, ft.measure_6
, ft.measure_7
, ft.measure_8
, ft.measure_9
FROM tab3 ft
WHERE NOT EXISTS (SELECT NULL
FROM tab3 ft_prime
WHERE ft_prime.measure_0 = ft.measure_0
AND ft_prime.ROWID <> ft.ROWID)
AND ROWNUM <= 450;
언급URL : https://stackoverflow.com/questions/11229258/query-executes-slower-after-indexes-are-created-and-dbms-stats-compute-is-used
'programing' 카테고리의 다른 글
| SQL 또는 MySQL에서 JOIN 키워드를 사용하지 않는 조인에 문제가 있습니까? (0) | 2023.07.26 |
|---|---|
| PHP에서 종료되지 않은 엔티티 참조 (0) | 2023.07.26 |
| @RequestMapping 주석은 @FeignClient 인터페이스에서 허용되지 않습니다. (0) | 2023.07.26 |
| Java 스프링:'@Value' 주석을 사용하여 'Environment' 속성을 주입하는 방법은? (0) | 2023.07.26 |
| 텐서플로 보폭 인수 (0) | 2023.07.26 |