Điều quan trọng nhất để duy trị tốc độ trong plsql là gì

Tóm lại: truy vấn của bạn đang chạy dựa trên toàn bộ các BIỆN PHÁP. Nó khớp với thời gian của mỗi bản ghi MEASURES với bản ghi INTERVALS. Nếu cửa sổ thời gian được mở rộng bởi INTERVALS gần giống với cửa sổ được mở rộng bởi MEASURES thì truy vấn của bạn cũng đang chạy với tập hợp đầy đủ của INTERVALS, nếu không thì nó đang chạy với một tập hợp con.

Tại sao lại có vấn đề đó là vì nó làm giảm phạm vi điều chỉnh của bạn, vì quét toàn bộ bảng có khả năng là cách nhanh nhất để lấy tất cả các hàng. Vì vậy, trừ khi các bảng MEASURES hoặc INTERVALS thực của bạn có nhiều cột hơn bạn cung cấp cho chúng tôi, thì không chắc chỉ mục nào sẽ mang lại nhiều lợi thế.

Các chiến lược khả thi là:

• không có chỉ mục nào cả
• chỉ số trên MEASURES [TIME, MEASURE]
• chỉ mục trên MEASURES [TIME]
• không có chỉ mục nào trên MEASURES
• lập chỉ mục vào INTERVALS [ENTRY_TIME, EXIT_TIME]
• lập chỉ mục vào INTERVALS [ENTRY_TIME]
• không có chỉ mục nào trên INTERVALS
• truy vấn song song

Tôi sẽ không trình bày các trường hợp thử nghiệm cho tất cả các hoán vị, bởi vì kết quả khá nhiều như chúng ta mong đợi.

Đây là dữ liệu thử nghiệm. Như bạn có thể thấy, tôi đang sử dụng các tập dữ liệu lớn hơn một chút. Cửa sổ INTERVALS lớn hơn cửa sổ MEASURES nhưng không nhiều. Khoảng thời gian là 10000 giây và các phép đo được thực hiện sau mỗi 15 giây.

SQL> select min[entry_time], max[exit_time], count[*] from intervals; MIN[ENTRY MAX[EXIT_ COUNT[*] --------- --------- ---------- 01-JAN-09 20-AUG-09 2001 SQL> select min[ts], max[ts], count[*] from measures; MIN[TS] MAX[TS] COUNT[*] --------- --------- ---------- 02-JAN-09 17-JUN-09 1200001 SQL>

NB Trong dữ liệu thử nghiệm của tôi, tôi đã cho rằng các bản ghi INTERVAL không chồng chéo. Điều này có một corrolary quan trọng: một bản ghi MEASURES chỉ kết hợp với một INTERVAL.

Điểm chuẩn

Đây là điểm chuẩn không có chỉ số.

SQL> exec dbms_stats.gather_table_stats[user, 'MEASURES', cascade=>true] PL/SQL procedure successfully completed. SQL> exec dbms_stats.gather_table_stats[user, 'INTERVALS', cascade=>true] PL/SQL procedure successfully completed. SQL> set timing on SQL> SQL> select m.measure 2 , m.ts as "TIME" 3 , i.entry_time 4 , i.exit_time 5 from 6 intervals i 7 inner join 8 measures m 9 on [ m.ts between i.entry_time and i.exit_time ] 10 order by m.ts asc 11 / 1200001 rows selected. Elapsed: 00:05:37.03 SQL>

Các bài kiểm tra MEASURES

Bây giờ, hãy tạo một chỉ mục duy nhất trên INTERVALS [ENTRY_TIME, EXIT_TIME] và thử các chiến lược lập chỉ mục khác nhau cho MEASURES. Đầu tiên, chỉ một cột MEASURES TIME.

SQL> create index meas_idx on measures [ts] 2 / Index created. SQL> exec dbms_stats.gather_table_stats[user, 'MEASURES', cascade=>true] PL/SQL procedure successfully completed. SQL> SQL> set autotrace traceonly exp SQL> SQL> set timing on SQL> SQL> select m.measure 2 , m.ts as "TIME" 3 , i.entry_time 4 , i.exit_time 5 from 6 intervals i 7 inner join 8 measures m 9 on [ m.ts between i.entry_time and i.exit_time ] 10 order by m.ts asc 11 / 1200001 rows selected. Elapsed: 00:05:20.21 SQL>

Bây giờ, hãy lập chỉ mục các cột MEASURES.TIME và MEASURE

SQL> drop index meas_idx 2 / Index dropped. SQL> create index meas_idx on measures [ts, measure] 2 / Index created. SQL> exec dbms_stats.gather_table_stats[user, 'MEASURES', cascade=>true] PL/SQL procedure successfully completed. SQL> select m.measure 2 , m.ts as "TIME" 3 , i.entry_time 4 , i.exit_time 5 from 6 intervals i 7 inner join 8 measures m 9 on [ m.ts between i.entry_time and i.exit_time ] 10 order by m.ts asc 11 / 1200001 rows selected. Elapsed: 00:05:28.54 SQL>

Hiện không có chỉ mục trên MEASURES [nhưng vẫn là chỉ mục trên INTERVALS]

SQL> drop index meas_idx 2 / Index dropped. SQL> exec dbms_stats.gather_table_stats[user, 'MEASURES', cascade=>true] PL/SQL procedure successfully completed. SQL> select m.measure 2 , m.ts as "TIME" 3 , i.entry_time 4 , i.exit_time 5 from 6 intervals i 7 inner join 8 measures m 9 on [ m.ts between i.entry_time and i.exit_time ] 10 order by m.ts asc 11 / 1200001 rows selected. Elapsed: 00:05:24.81 SQL>

Vậy truy vấn song song tạo ra sự khác biệt nào?

SQL> select /*+ parallel [4] */ 2 m.measure 3 , m.ts as "TIME" 4 , i.entry_time 5 , i.exit_time 6 from 7 intervals i 8 inner join 9 measures m 10 on [ m.ts between i.entry_time and i.exit_time ] 11 order by m.ts asc 12 / 1200001 rows selected. Elapsed: 00:02:33.82 SQL>

BIỆN PHÁP Kết luận

Không có nhiều sự khác biệt trong thời gian trôi qua cho các chỉ mục khác nhau. Tôi hơi ngạc nhiên khi xây dựng chỉ mục trên MEASURES [TS, MEASURE] dẫn đến việc quét toàn bộ bảng và thời gian thực thi hơi chậm hơn. Mặt khác, không có gì ngạc nhiên khi chạy truy vấn song song nhanh hơn nhiều. Vì vậy, nếu bạn có Enterprise Edition và bạn có CPU để phụ tùng, sử dụng PQ chắc chắn sẽ làm giảm thời gian trôi qua, mặc dù nó sẽ không thay đổi chi phí tài nguyên hơn nhiều [và thực sự không một nhiều sắp xếp nhiều hơn].

Kiểm tra INTERVALS

Vậy các chỉ mục khác nhau trên INTERVALS có thể tạo ra sự khác biệt gì? Trong các bài kiểm tra sau, chúng tôi sẽ giữ lại một chỉ số về ĐO LƯỜNG [TS]. Trước hết, chúng tôi sẽ bỏ khóa chính trên cả hai cột INTERVALS và thay thế nó bằng một ràng buộc chỉ đối với INTERVALS [ENTRY_TIME].

SQL> alter table intervals drop constraint ivl_pk drop index 2 / Table altered. SQL> alter table intervals add constraint ivl_pk primary key [entry_time] using index 2 / Table altered. SQL> exec dbms_stats.gather_table_stats[user, 'INTERVALS', cascade=>true] PL/SQL procedure successfully completed. SQL> select m.measure 2 , m.ts as "TIME" 3 , i.entry_time 4 , i.exit_time 5 from 6 intervals i 7 inner join 8 measures m 9 on [ m.ts between i.entry_time and i.exit_time ] 10 order by m.ts asc 11 / 1200001 rows selected. Elapsed: 00:05:38.39 SQL>

Cuối cùng không có chỉ mục nào trên INTERVALS

SQL> alter table intervals drop constraint ivl_pk drop index 2 / Table altered. SQL> exec dbms_stats.gather_table_stats[user, 'INTERVALS', cascade=>true] PL/SQL procedure successfully completed. SQL> select m.measure 2 , m.ts as "TIME" 3 , i.entry_time 4 , i.exit_time 5 from 6 intervals i 7 inner join 8 measures m 9 on [ m.ts between i.entry_time and i.exit_time ] 10 order by m.ts asc 11 / 1200001 rows selected. Elapsed: 00:05:29.15 SQL>

Kết luận của INTERVALS

Chỉ số trên INTERVALS tạo ra một sự khác biệt nhỏ. Tức là, lập chỉ mục [ENTRY_TIME, EXIT_TIME] dẫn đến việc thực thi nhanh hơn. Điều này là do nó hoán vị quá trình quét chỉ mục đầy đủ nhanh hơn là quét toàn bộ bảng. Điều này sẽ có ý nghĩa hơn nếu khoảng thời gian được phân định bởi INTERVALS rộng hơn đáng kể so với của MEASURES.

Kết luận tổng thể

Bởi vì chúng tôi đang thực hiện các truy vấn bảng đầy đủ, không có chỉ mục nào thay đổi đáng kể thời gian thực thi. Vì vậy, nếu bạn có Enterprise Edition và nhiều CPU Truy vấn song song sẽ cho bạn kết quả tốt nhất. Nếu không, các chỉ mục tốt nhất sẽ là INTERVALS [ENTRY_TIME, EXIT_TIME] và MEASURES [TS]. Giải pháp Vòng lặp lồng nhau chắc chắn nhanh hơn Truy vấn song song - xem Chỉnh sửa 4 bên dưới.

Nếu bạn đang chống lại một tập hợp con của các BIỆN PHÁP [giả sử giá trị của một tuần] thì sự hiện diện của các chỉ mục sẽ có tác động lớn hơn, Có khả năng là hai chỉ số tôi đã đề xuất trong đoạn trước sẽ vẫn hiệu quả nhất

Quan sát cuối cùng: Tôi chạy điều này trên một máy tính xách tay lõi kép tiêu chuẩn sag với SGA chỉ 512M. Tuy nhiên, tất cả các truy vấn của tôi chỉ mất chưa đầy sáu phút. Nếu truy vấn của bạn thực sự mất một giờ thì cơ sở dữ liệu của bạn có một số vấn đề nghiêm trọng. Mặc dù thời gian hoạt động dài này có thể là một đồ tạo tác của các INTERVALS chồng chéo, điều này có thể dẫn đến một sản phẩm cacte.

**Biên tập **

Ban đầu tôi bao gồm đầu ra từ

SQL> set autotrace traceonly stat exp

Nhưng than ôi SO đã cắt ngắn bài đăng của tôi. Vì vậy, tôi đã viết lại nó nhưng không có thực thi hoặc số liệu thống kê. Những người muốn xác thực phát hiện của tôi sẽ phải chạy các truy vấn themselevs.

Chỉnh sửa 4 [chỉnh sửa trước đã bị xóa vì lý do hết dung lượng]

Ở lần thử thứ ba, tôi đã có thể tái tạo cải tiến hiệu suất teh cho giải pháp của Quassnoi.

SQL> set autotrace traceonly stat exp SQL> SQL> set timing on SQL> SQL> select 2 /*+ LEADING [i] USE_NL[i, m] */ 3 m.measure 4 , m.ts as "TIME" 5 , i.entry_time 6 , i.exit_time 7 from 8 intervals i 9 inner join 10 measures m 11 on [ m.ts between i.entry_time and i.exit_time ] 12 order by m.ts asc 13 / 1200001 rows selected. Elapsed: 00:00:18.39 Execution Plan ---------------------------------------------------------- Plan hash value: 974071908 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost [%CPU]| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6003K| 257M| | 973K [1]| 03:14:46 | | 1 | SORT ORDER BY | | 6003K| 257M| 646M| 973K [1]| 03:14:46 | | 2 | NESTED LOOPS | | | | | | | | 3 | NESTED LOOPS | | 6003K| 257M| | 905K [1]| 03:01:06 | | 4 | TABLE ACCESS FULL | INTERVALS | 2001 | 32016 | | 2739 [1]| 00:00:33 | |* 5 | INDEX RANGE SCAN | MEAS_IDX | 60000 | | | 161 [1]| 00:00:02 | | 6 | TABLE ACCESS BY INDEX ROWID| MEASURES | 3000 | 87000 | | 451 [1]| 00:00:06 | --------------------------------------------------------------------------------------------------- Predicate Information [identified by operation id]: --------------------------------------------------- 5 - access["M"."TS">="I"."ENTRY_TIME" AND "M"."TS"

Vì vậy, Vòng lặp lồng nhau chắc chắn là cách để đi.

Bài học bổ ích từ bài tập

1. Chạy các xét nghiệm chẩn đoán có giá trị hơn nhiều so với việc phỏng đoán và đưa ra lý thuyết
2. Hiểu dữ liệu là rất quan trọng
3. Ngay cả với 11g, đôi khi chúng ta vẫn cần sử dụng các gợi ý để thúc đẩy trình tối ưu hóa trong một số trường hợp nhất định