Điều chỉnh SQL với Optim Query Tuner, Phần 2: Điều chỉnh các truy vấn riêng lẻ

Tìm hiểu cách giám sát và điều chỉnh các truy vấn và các tải công việc để cải thiện hiệu năng ứng dụng

Bài đầu tiên trong loạt bài này đã giới thiệu khái niệm về các đường dẫn truy cập và đã dạy bạn cách đọc một biểu đồ đường dẫn truy cập trong Optim Query Tuner (Trình điều chỉnh truy vấn Optim). Trong bài này sẽ giới thiệu một phương pháp luận để điều chỉnh các truy vấn riêng. Khi sử dụng một truy vấn mẫu, bạn được dạy cách có thể sử dụng Optim™ Query Tuner để thực hiện quá trình này. Quá trình này bao gồm việc sử dụng chú thích và định dạng truy vấn và phân tích kế hoạch truy cập, số liệu thống kê, các biến vị ngữ, và các chỉ mục. Mục đích là để đảm bảo cung cấp cho trình tối ưu hóa DB2 của IBM (IBM® DB2® optimizer) các thông tin cần thiết để đưa ra các quyết định dựa trên hiệu năng tốt nhất cho các truy vấn DB2 của bạn, và để cung cấp cho bạn lời khuyên về những điều mà bạn có thể làm để trợ giúp trình tối ưu hóa DB2 có nhiều tùy chọn hơn nữa để cải thiện truy cập, như tạo ra các chỉ mục cần thiết.

Gene Fuh, Kỹ sư cao cấp, IBM

Gene Fuh photoGene Fuh vẫn đang làm làm về các công nghệ cơ sở dữ liệu cho IBM từ năm 1994. Ông gia nhập tổ chức DB2 cho z/OS vào năm 2000 sau sáu năm rưỡi làm việc trong tổ chức DB2 LUW. Vào năm 2004, Gene bắt đầu lập một nhóm vì sự phát triển của DB2 OE/OSC (Optimization Expert and Optimization Service Center - Trung tâm dịch vụ tối ưu hóa và chuyên gia tối ưu hóa DB2). Ông là kiến trúc sư trưởng và là giám thị của dự án này cho đến năm 2007, khi sản phẩm này đã trở nên có sẵn đồng thời với DB2 9 cho z/OS. Trong năm 2008, Gene bắt đầu quá trình chuyển đổi công nghệ DB2 OE/OSC sang các giải pháp điều chỉnh Optim, mà bây giờ được gọi là Optim Query Tuner và Optim Query Workload Tuner. Trong suốt 17 năm làm việc tại IBM, Gene đã đưa ra 48 ứng dụng có bằng sáng chế và công bố hơn 20 tài liệu kỹ thuật trong cả các hội nghị IBM và các hội nghị học thuật khác.



Kendrick Ren, Kỹ sư , IBM

Kendrick Ren photoKendrick Ren là người lãnh đạo kỹ thuật về các sản phẩm IBM Optim Query Tuner và Optim Query Workload Tuner, ông đang làm việc trong phòng thí nghiệm IBM Toronto. Ông đã làm về các sản phẩm này, trong các thể hiện trước của chúng như là các sản phẩm DB2 OE/OSC kể từ khi nhóm được thành lập vào năm 2004. Kendrick cộng tác chặt chẽ với các khách hàng và các đối tác kinh doanh, những người đang sử dụng các sản phẩm này, giúp họ trong lĩnh vực tối ưu hóa truy vấn. Trước khi gia nhập đội ngũ Optimization Expert (Chuyên gia tối ưu hóa), Kendrick đã làm việc hai năm về sản phẩm IBM WebSphere Commerce Server (Máy chủ thương mại WebSphere của IBM).



Kathy Zeidenstein, Kỹ sư phần mềm, IBM

Author Photo: Kathy ZeidensteinKathy Zeidenstein đã làm việc tại IBM hơn một năm. Cô hiện đang làm trong nhóm hỗ trợ kỹ thuật IBM Optim Solutions và chịu trách nhiệm truyền thông và phát triển cộng đồng. Trước khi đảm nhận vào vai trò này, cô đã là một giám đốc tiếp thị sản phẩm về các công nghệ phân tích và tìm kiếm văn bản.



Qiang Song, Kỹ sư phần mềm, IBM

Photo of author Qiang SongQiang Song là người lãnh đạo kỹ thuật của các sản phẩm Optim Query Tuner và Optim Query Workload Tuner của IBM, làm việc trong phòng thí nghiệm phát triển Trung Quốc của IBM từ năm 2005. Anh cũng chỉ huy hỗ trợ cho khách hàng và công việc dịch vụ của các sản phẩm. Qiang có kinh nghiệm phong phú trong việc điều chỉnh hiệu năng SQL và phát triển Eclipse.



03 01 2012

Giới thiệu

Trong bài đầu tiên của loạt bài này, Điều chỉnh SQL với Optim Query Tuner, Phần 1: Tìm hiểu về các đường dẫn truy cập, đã giới thiệu khái niệm về một đường dẫn truy cập. Với một câu lệnh SQL cụ thể, thường có nhiều sự lựa chọn đường dẫn truy cập, và các đường dẫn truy cập khác nhau thường có đặc điểm hiệu năng khác nhau. Trước khi thực hiện SQL, trình tối ưu hóa DB2 ước tính giá của các đường dẫn truy cập ứng cử viên và chọn đường dẫn có giá ước tính thấp nhất. Quá trình này được bao gồm trong bước PREPARE (Chuẩn bị) cho một câu lệnh SQL động, hoặc trong bước BIND (Kết buộc) cho một câu lệnh SQL tĩnh.

Mặc dù trình tối ưu hóa DB2 có ích cho việc chọn đường dẫn truy cập tốt nhất, nhưng kết quả lại phụ thuộc vào dữ liệu đầu vào, mà thường trình tối ưu hóa không truy cập hoặc kiểm soát được. Nếu bạn là một người phát triển hoặc người quản trị cơ sở dữ liệu (DBA), với bạn để hiểu cách điều chỉnh các truy vấn đến mức bạn có thể cung cấp đầu vào tốt nhất cho trình tối ưu hóa DB2 rất có ích.

Trong bài này, tác giả giới thiệu cho bạn một phương pháp luận để điều chỉnh các truy vấn riêng lẻ, bao gồm lý do cơ bản để hiểu tại sao cách điều chỉnh các truy vấn lại quan trọng ngay cả khi có trình tối ưu hóa tầm cỡ thế giới tồn tại trong DB2. Sau đó bạn sử dụng một truy vấn mẫu để giải thích phương pháp luận để điều chỉnh một truy vấn, khi sử dụng các tính năng liên quan của Optim Query Tuner, có thể rất có ích trong việc giúp bạn hiểu, phân tích, và điều chỉnh các truy vấn riêng lẻ.

Lưu ý rằng bài này được thiết kế chủ yếu để điều chỉnh SQL trên DB2 cho z/OS, nhưng hầu hết các khái niệm tối ưu hóa truy vấn và phương pháp luận điều chỉnh SQL trong bài này cũng áp dụng được với DB2 cho Linux®, UNIX® và Windows®.

Nếu bạn muốn tự mình dùng thử truy vấn mẫu trong bài này, bạn có thể tải về các tệp dự án mẫu trong phần tải về của bài này, và sau đó nhập khẩu tệp dự án vào Data Studio (gói độc lập hoặc gói IDE với Fix Pack 1 hoặc mới hơn) hoặc bất kỳ các sản phẩm Optim Query Tuner nào.

Để nhập khẩu dự án mẫu hãy làm như sau:

  1. Mở IBM Query Tuning Perspective (Phối cảnh điều chỉnh truy vấn IBM) của sản phẩm Data Studio hoặc Optim Query Tuner của bạn.
  2. Chọn File > Import..
  3. Trong Import wizard (Trình hướng dẫn nhập khẩu), chuyển hướng đến Query Tuner > Projects, rồi nhấn Next.
  4. Nhấn Browse (Duyệt) và chọn thư mục có chứa tệp zip đã tải về để xem một danh sách các dự án trong cửa sổ Projects (Các dự án).
  5. Chọn samplequerytuningproject và nhấn Finish.
  6. Bây giờ dự án mẫu sẽ xuất hiện trong Project Explorer (Trình thám hiểm dự án) của bạn. Nếu bạn không thấy một Project Explorer Window (Cửa sổ Project Explorer), hãy chắc chắn bạn đang ở trong IBM Query Tuning Perspective và chọn Window > Reset Perspective. Ngoài ra, bạn có thể chọn Window > Show View > Project Explorer.

Về các giải pháp điều chỉnh truy vấn Optim

Các giải pháp điều chỉnh truy vấn Optim cung cấp một môi trường để nhận biết và điều chỉnh việc thực hiện các câu lệnh SQL không chạy với các lời khuyên và các công cụ có thể trợ giúp hướng dẫn bạn đến một giải pháp. Các khả năng điều chỉnh truy vấn được cung cấp trong các sản phẩm sau:

  • Các khả năng định dạng truy vấn và điều chỉnh truy vấn đơn lẻ, cơ bản có sẵn trong bản Data Studio 2.2.1 (cả bản độc lập lẫn bản IDE). Sản phẩm này có sẵn miễn phí cho cả hai DB2 cho z/OS và DB2 cho Linux, UNIX và Windows. Cần hiểu rõ rằng trong khi thông tin trong loạt bài này giải thích cách bạn có thể sử dụng Data Studio để giải thích các biểu đồ đường dẫn truy cập, thì không phải tất cả các khả năng được mô tả có sẵn trong Data Studio.
  • Định dạng truy vấn và điều chỉnh truy vấn đơn lẻ, cũng như tập các trình tư vấn lớn hơn, có sẵn trong Optim Query Tuner. Sản phẩm này hiện có sẵn cho cả hai DB2 cho z/OS và DB2 cho Linux, UNIX và Windows.
  • Điều chỉnh tải truy vấn, điều chỉnh truy vấn đơn lẻ và toàn bộ tập các trình tư vấn có sẵn trong Optim Query Workload Tuner (Trình điều chỉnh tải truy vấn Optim). Sản phẩm này chỉ có sẵn cho DB2 cho z/OS (tại thời điểm viết bài này).

Tóm lại, loạt bài này sử dụng tên Optim Query Tuner (OQT-Trình điều chỉnh truy vấn Optim) để nói đến tập các trình tư vấn và các công cụ mà các giải pháp điều chỉnh truy vấn Optim cung cấp. Ở đây các tên sản phẩm cụ thể, thích hợp được cung cấp khi mô tả các khả năng có thể không có sẵn trong tất cả các sản phẩm được liệt kê ở trên.

Lưu ý rằng bài này chủ yếu tập trung vào phương pháp luận điều chỉnh truy vấn và sử dụng các ảnh chụp màn hình từ Optim Query Tuner để minh họa cho các ý kiến này. Bài này không nhằm mục đích cung cấp thông tin "hướng dẫn" bằng cách sử dụng Query Tuner. Để có thêm thông tin về chuyển hướng đến các tính năng sản phẩm khác nhau, và để xem một giới thiệu chi tiết về cách khởi chạy các hàm khác nhau trong OQT, hãy tham khảo phần Tài nguyên.

Tổng quan về tối ưu hóa truy vấn

Như cho thấy trong Hình 1, trình tối ưu hóa DB2 chọn đường dẫn truy cập tốt nhất.

Hình 1. Tổng quan về trình tối ưu hóa DB2
Tổng quan về trình tối ưu hóa DB2. Văn bản dưới hình trong bài cung cấp mô tả chi tiết.

Trình tối ưu hóa so sánh giá của mỗi đường dẫn truy cập ứng cử viên dựa trên thông tin từ nhiều đầu vào, ví dụ, hãy xem dưới đây:

  • Số liệu thống kê danh mục
    Trình tối ưu hóa DB2 là một tối ưu hóa dựa trên giá. Nền tảng của sự tối ưu hóa dựa trên giá là một tập số liệu thống kê cho phép trình tối ưu hóa đánh giá chính xác giá của tất cả các đường dẫn truy cập ứng cử viên và phân biệt các đường dẫn truy cập hiệu quả với các đường dẫn truy cập không hiệu quả. Số liệu thống kê trong các bảng danh mục DB2 được sử dụng để ước tính giá của đường dẫn truy cập. Ví dụ, thông tin trong bảng danh mục SYSTABLES và SYSTABLESPACE cho bạn biết có bao nhiêu hàng và trang chứa dữ liệu trong bảng của bạn.
  • Thiết kế cơ sở dữ liệu vật lý
    Thiết kế cơ sở dữ liệu vật lý bao gồm thiết kế bảng, thiết kế chỉ mục, thiết kế bảng truy vấn được cụ thể hóa và thiết kế của các đối tượng cơ sở dữ liệu vật lý khác. Thiết kế chỉ mục có một tác động quan trọng đến việc lựa chọn đường dẫn truy cập. Như đã được đề cập trong bài trước, với truy cập bảng riêng lẻ, có hai kiểu phương thức truy cập: quét vùng bảng (TBSCAN) và quét chỉ mục (IXSCAN). Các quá trình quét chỉ mục thường là cách hiệu quả nhất để truy cập dữ liệu, đặc biệt là khi bảng lớn, nhưng số các hàng đủ điều kiện lại nhỏ.
  • Câu lệnh SQL
    Chính câu lệnh SQL cũng ảnh hưởng đến việc lựa chọn đường dẫn truy cập. Ví dụ, các biến vị ngữ được mã hóa không đúng có thể ngăn không cho trình tối ưu hóa sử dụng quét chỉ mục ngay cả khi chỉ mục có sẵn. Ngoài ra, trước khi chọn đường dẫn truy cập, trước tiên trình tối ưu hóa thực hiện một loạt các chuyển đổi truy vấn để tăng số các đường dẫn truy cập có sẵn. Nếu câu lệnh SQL bị mã hóa sai, thật khó chuyển đổi các truy vấn với trình tối ưu hóa, có ít tùy chọn có sẵn hơn để chọn một đường dẫn truy cập tối ưu.
  • Các xem xét khác để chọn đường dẫn truy cập
    Ngoài việc tự xem xét số liệu thống kê danh mục, thiết kế cơ sở dữ liệu vật lý và câu lệnh SQL, trình tối ưu hóa DB2 cũng xem xét mô hình bộ xử lý trung tâm, số lượng các bộ xử lý trung tâm, kích thước nhóm bộ đệm, kích thước nhóm RID và các thiết lập tài nguyên hệ thống khác. Ví dụ, đường dẫn truy cập có thể thay đổi từ một hệ thống này sang một hệ thống khác nếu chúng có các kích thước nhóm bộ đệm khác nhau, ngay cả khi tất cả số liệu thống kê danh mục giống hệt nhau.

Trình tối ưu hóa DB2 là toàn diện và khá mạnh. Nếu trình tối ưu hóa DB2 đang hoạt động, thì tại sao cần điều chỉnh truy vấn? Có hai lý do trả lời cho câu hỏi này:

  • Trình tối ưu hóa DB2 không biết tất cả.
    Mặc dù trình tối ưu hóa DB2 có rất nhiều thông tin nhờ đó để bố trí kế hoạch của nó, nó không thể biết những gì không tồn tại. Ví dụ, trình tối ưu hóa không biết các đặc điểm của dữ liệu trừ khi bạn đã chạy RUNSTATS để điền số liệu thống kê có liên quan vào danh mục đó. Ngoài ra, không thể biết được một số mục cho đến thời gian chạy. Ví dụ, trình tối ưu hóa không biết được các giá trị của các biến hoặc các dấu tham số chủ (nếu chúng được chứa trong truy vấn) cho đến khi thực hiện truy vấn.
  • Trình tối ưu hóa DB2 không kiểm soát tất cả.
    Như đã đề cập ở trên, thiết kế cơ sở dữ liệu vật lý, câu lệnh SQL và các giá trị thiết lập tài nguyên hệ thống tác động đến cách trình tối ưu hóa lựa chọn đường dẫn truy cập tốt nhất, nhưng cả hai cơ sở dữ liệu lẫn thiết kế truy vấn đều là các nhiệm vụ đang nằm ngoài sự kiểm soát của trình tối ưu hóa DB2. Đây là nơi mà các DBA và những người phát triển đóng một vai trò quan trọng trong việc trợ giúp hoặc gây thiệt hại cho hiệu năng SQL.

Mục đích của việc điều chỉnh truy vấn là cung cấp đầu vào có thể tốt nhất cho trình tối ưu hóa sao cho trình tối ưu hóa có thể chọn đường dẫn truy cập tốt nhất. Điều này liên quan đến nỗ lực từ cả hai những người phát triển ứng dụng và các DBA.

Đối với những người phát triển ứng dụng:

  • Làm theo các hướng dẫn và các tiêu chuẩn mã hóa SQL.
    Bạn cần tuân theo các hướng dẫn và các tiêu chuẩn mã hóa SQL khi bạn viết các câu lệnh SQL của mình. Ví dụ, viết các biến vị ngữ chỉ mục có khả năng hoặc các biến vị ngữ giai đoạn 1 và tránh viết các truy vấn không có các biến vị ngữ nối (còn được gọi là phép nối Đề-các).
  • Khai thác các tùy chọn kết buộc REOPT một cách đúng đắn.
    Đối với các câu lệnh SQL có các biến, trình tối ưu hóa sử dụng một hệ số bộ lọc mặc định để xác định đường dẫn truy cập tốt nhất tại thời điểm kết buộc. Trong một số trường hợp, đường dẫn truy cập không thực hiện tốt trong thời gian chạy nếu câu lệnh đó có chứa các biến máy chủ, các dấu tham số, hoặc các đăng ký đặc biệt. Bạn có thể sử dụng các tùy chọn kết buộc REOPT để tối ưu hóa lại đường dẫn truy cập hoặc tại thời điểm kết buộc hoặc trong thời gian chạy.

Đối với các nhà quản trị cơ sở dữ liệu (DBA):

  • Thu thập số liệu thống kê đầy đủ và chính xác.
    Số liệu thống kê không đầy đủ hoặc không chính xác dẫn đến các ước tính giá không chính xác cho các đường dẫn truy cập ứng cử viên và là lý do phổ biến nhất làm cho việc lựa chọn các đường dẫn truy cập không hiệu quả. Trong khi đó, việc thu thập và làm mới tất cả số liệu thống kê sẽ tiêu tốn quá nhiều tài nguyên không cần thiết. Căn cứ vào số lượng các hoạt động INSERT, UPDATE và DELETE và các thay đổi trong các bản phân phối dữ liệu, bạn cần thu thập số liệu thống kê thường xuyên và với việc tiêu thụ tài nguyên tối thiểu.
  • Tối ưu hóa thiết kế chỉ mục.
    Bạn cần thiết kế các chỉ mục để hỗ trợ truy cập hiệu quả với các biến vị ngữ cục bộ và các biến vị ngữ nối. Bạn cũng có thể cần thiết kế các chỉ mục để tránh sắp xếp dữ liệu và cung cấp chỉ mục chỉ để truy cập.
  • Điều chỉnh toàn bộ ứng dụng.
    Để đảm bảo hiệu năng tốt của ứng dụng, điều cần thiết là điều chỉnh toàn bộ ứng dụng này. Nỗ lực cần thiết để điều chỉnh toàn bộ ứng dụng, bằng cách đánh giá tất cả các câu lệnh riêng lẻ, có ưu thế hơn. Ngoài ra, việc cải thiện hiệu năng trên một câu lệnh có thể đi ngược lại hiệu năng của các câu lệnh khác trong ứng dụng. Vì vậy, điều rất quan trọng là điều chỉnh toàn bộ ứng dụng, còn được gọi là điều chỉnh tải công việc. Bài này sẽ tập trung vào điều chỉnh một truy vấn đơn, phần tiếp theo của loạt bài này sẽ mở rộng phương pháp luận trong bài này để giới thiệu điều chỉnh tải công việc một cách chi tiết.

Bài này mô tả một phương pháp luận để hiểu các vấn đề về hiệu năng truy vấn tiềm năng và cách giải quyết những vấn đề tiềm năng đó. Việc sử dụng Optim Query Tuner làm cho quá trình này đơn giản hơn.

Phương pháp luận điều chỉnh truy vấn

Tổng quan về phương pháp luận điều chỉnh truy vấn

Để thực hiện điều chỉnh truy vấn, trước tiên bạn cần hiểu những gì bạn muốn điều chỉnh, trong trường hợp này đó là chính truy vấn đó và trình tối ưu hóa lựa chọn kế hoạch truy cập hiện tại của truy vấn đó, rồi tìm ra cách để điều chỉnh truy vấn đó.

Dựa trên ý tưởng này, bạn sẽ thực hiện các nhiệm vụ sau để điều chỉnh truy vấn đầy đủ, bạn có thể thực hiện truy vấn đó từ bên trong Query Tuner:

  • Định dạng truy vấn vấn đề để làm cho việc đọc và hiểu logic truy vấn dễ dàng hơn.
  • Chú thích truy vấn vấn đề với số liệu thống kê có liên quan để hiểu rõ hơn những gì trình tối ưu hóa DB2 đang sử dụng cho các đánh giá của nó.
  • Phân tích kế hoạch truy cập truy vấn để hiển thị trực quan các lựa chọn mà trình tối ưu hóa thực hiện khi truy cập dữ liệu.
  • Thực hiện phân tích số liệu thống kê để đảm bảo rằng trình tối ưu hóa DB2 luôn có số liệu thống kê phổ biến nhất và số liệu thống kê cần thiết nhất.
  • Thực hiện phân tích biến vị ngữ để xem liệu các biến vị ngữ có khả năng chọn lọc không.
  • Thực hiện phân tích chỉ mục để đảm bảo rằng các chỉ mục thích hợp tồn tại để giúp tránh các lần quét bảng không cần thiết.

Trong các phần tiếp theo, sử dụng câu lệnh SQL trong Liệt kê 1 làm một mẫu để giải thích từng nhiệm vụ điều chỉnh truy vấn riêng một cách chi tiết. Như bạn có thể tưởng tượng, có một ít sự phụ thuộc lẫn nhau giữa các nhiệm vụ này. Ví dụ, việc thay đổi số liệu thống kê, được thu thập, có thể có nhiều khả năng ảnh hưởng đến các kết quả phân tích biến vị ngữ. Ngoài ra, bạn có thể cần lặp lại thông qua một hoặc nhiều nhiệm vụ này vài lần cho đến khi giải quyết được một vấn đề hiệu năng cụ thể.

Liệt kê 1. Truy vấn mẫu được sử dụng trong bài này
SELECT CCUS.CUST_FIRST_NAME
     , CCUS.CUST_LAST_NAME
     , CINT.CUST_INTEREST_RANK
     , CILO.CUST_INTERST
FROM CUST_CUSTOMER AS CCUS
     , CUST_INTEREST_LOOKUP AS CILO
     , CUST_INTEREST AS CINT
WHERE ( CCUS.CUST_CITY = 'Singapore' 
        AND CCUS.CUST_PROV_STATE = 'Singapore'            
        AND CCUS.CUST_CODE IN ( 
                        SELECT COHE.CUST_CODE
                        FROM CUST_ORDER_HEADER AS COHE
                             , CUST_ORDER_STATUS AS COST
                        WHERE ( COHE.CUST_ORDER_DATE 
                                    >='2009-01-01 00:00:00.001'            
                                AND COST.CUST_ORDER_STATUS IN ( 'Shipped', 
                                    'Back-ordered', 'In-process' ) 
                                AND COHE.CUST_ORDER_STATUS_CODE 
                                   = COST.CUST_ORDER_STATUS_CODE
                              )
                      )
        AND CCUS.CUST_CODE = CINT.CUST_CODE
        AND CINT.CUST_INTEREST_CODE = CILO.CUST_INTEREST_CODE
      )
ORDER BY CCUS.CUST_LAST_NAME ASC 
         , CCUS.CUST_FIRST_NAME ASC 
         , CINT.CUST_INTEREST_RANK ASC

Định dạng truy vấn vấn đề

Trước khi điều chỉnh một truy vấn, bạn cần hiểu các khía cạnh sau về truy vấn vấn đề.

  • Các ngữ nghĩa của truy vấn: Cần truy cập những bảng nào trong truy vấn đó? Sử dụng các loại biến vị ngữ nào trên mỗi bảng được tham khảo? Sử dụng các loại biến vị ngữ nào để nối các bảng được tham khảo?
  • Đường dẫn truy cập của các truy vấn: Cách truy cập các bảng? Toàn bộ bảng có được quét hoặc có được truy cập bằng một chỉ mục không? Nếu có một chỉ mục, thì đó là chỉ mục hay các chỉ mục nào? Chuỗi nối và phương pháp nối là gì?

Như bạn có thể thấy trong Hình 2, truy vấn vấn đề chưa được định dạng ban đầu khó đọc và hiểu.

Hình 2. Truy vấn chưa được định dạng
Truy vấn chưa được định dạng.

Optim Query Tuner có thể định dạng truy vấn vấn đề, cung cấp một điểm khởi đầu tốt để phân tích. Trong truy vấn đã định dạng, mỗi tham khảo bảng, mỗi tham khảo cột trong mệnh đề SELECT và mỗi biến vị ngữ được hiển thị trên một dòng riêng của nó. Đối với truy vấn mẫu trong bài này, truy vấn đã định dạng được hiển thị trong Hình 3.

Hình 3. Truy vấn đã định dạng
Truy vấn đã định dạng. Văn bản dưới hình trong bài cung cấp mô tả chi tiết.

Như bạn có thể tưởng tượng, với SQL phức tạp dài dòng, việc định dạng đơn giản truy vấn có thể tiết kiệm hàng giờ đồng hồ cho DBA. Bây giờ rất dễ tìm ra các bảng nào được truy cập, có bao nhiêu bảng trong truy vấn, và các bảng này được nối như thế nào. Các truy vấn đã định dạng cung cấp cho bạn khả năng sau:

  • Tìm hiểu các bộ phận của truy vấn một cách chi tiết hơn, như các khung nhìn và các truy vấn con được tham khảo, bằng cách mở rộng và thay đổi các phần của một SQL phức tạp.
  • Dễ dàng thấy cách truy cập một bảng cụ thể trong SQL. Khi bạn nhấn vào bất kỳ dòng nào trong truy vấn đã định dạng, các dòng khác của truy vấn đó có chứa các tham khảo cột hoặc bảng của cùng một bảng cũng được đánh dấu.
  • Tùy chỉnh thứ tự định dạng của các biến vị ngữ theo các tiêu chí khác nhau như các biến vị ngữ cục bộ hoặc các biến vị ngữ nối, và các tham khảo bảng.

Trở lại với truy vấn đã định dạng được hiển thị trong Hình 3, bạn có thể thấy như sau:

  • Truy vấn truy cập vào ba bảng sau: CUST_CUSTOMER, CUST_INTEREST và CUST_INTEREST_LOOKUP để nhận được tên khách hàng đủ điều kiện và thông tin quan tâm.
  • Ba bảng được nối với các biến vị ngữ bằng nhau.
  • Có ba biến vị ngữ trên bảng CUST_CUSTOMER. Hai biến vị ngữ đầu tiên là các biến vị ngữ bằng nhau đơn giản (CCUS.CUST_CITY = 'Singapore', CCUS.CUST_PROV_STATE = 'Singapore'). Biến vị ngữ thứ ba là một biến vị ngữ IN-list, trong đó có một truy vấn con không tương quan:
    • Truy vấn con truy cập các bảng CUST_ORDER_HEADER và CUST_ORDER_STATUS để nhận được mã khách hàng đủ điều kiện, và hai bảng đó được nối với một biến vị ngữ nối bằng nhau.
    • Có một biến vị ngữ cục bộ phạm vi trên CUST_ORDER_HEADER và một biến vị ngữ cục bộ IN-list trên CUST_ORDER_STATUS.
  • Không có biến vị ngữ cục bộ trên hai bảng khác (CUST_INTEREST và CUST_INTEREST_LOOKUP).
  • Kết quả được sắp xếp theo tên và sự quan tâm của khách hàng.

Query Tuner cũng làm cho việc phát hiện ra nơi trình tối ưu hóa DB2 đã chuyển đổi một truy vấn trở nên dễ dàng. Xin nhắc lại, các phép chuyển đổi là các điều chỉnh mà trình tối ưu hóa DB2 thực hiện với một truy vấn để cố gắng cải thiện hiệu năng của truy vấn, ví dụ nó có thể thêm một biến vị ngữ cho closure bắc cầu (closure là một hàm hạng nhất có các biến tự do bị kết buột trong một môi trường từ vựng của một ngôn ngữ) để làm cho việc đánh giá chuỗi nối dễ dàng.

Để minh họa, dựa vào một biến vị ngữ ví dụ như A.CUSTNO BETWEEN ? AND ? AND C.CUSTNO = A.CUSTNO, DB2 có thể tìm thấy rằng biến vị ngữ C.CUSTNO BETWEEN ? AND ? cũng phải là đúng, vì vậy phép chuyển đổi truy vấn DB2 có thể thêm biến vị ngữ đó để cho phép nó xem xét chỉ mục khác.

Đối với ví dụ mẫu trong bài này, truy vấn đã chuyển đổi như trong Hình 4.

Hình 4. Truy vấn đã chuyển đổi
Truy vấn đã chuyển đổi. Văn bản ở trên cung cấp mô tả chi tiết.

Như bạn thấy, trình tối ưu hóa tạo một bảng ảo để xử lý truy vấn con IN-list. Ngoài ra, truy vấn con không tương quan đã được chuyển đổi thành một truy vấn con tương quan. Đây là một sự tối ưu hóa đã được giới thiệu trong DB2 cho z/OS V9.1, cho phép DB2 tối ưu hóa toàn bộ một truy vấn chứ không chỉ là một số khối truy vấn độc lập. Khi tối ưu hóa toàn bộ một truy vấn, DB2 có thể xem xét hiệu quả của một khối truy vấn trên một khối truy vấn khác, và có thể xem xét các khối truy vấn sắp xếp lại để xác định một đường dẫn truy vấn tối ưu.

Chú thích truy vấn vấn đề

Cùng với các biến vị ngữ SQL đã định dạng và các tham khảo bảng, Query Tuner (Trình điều chỉnh truy vấn) gồm các chú thích về số liệu thống kê danh mục và các thông tin ước tính giá có liên quan, ví dụ như cardinality (số các yếu tố trong một tập) và các hàng đủ điều kiện đã đánh giá, như thể hiện trong Hình 5. Việc có sẵn thông tin này một cách dễ dàng có thể giúp các DBA tăng tốc độ phân tích và giảm thời gian chết với các tình huống khẩn cấp.

Hình 5. Truy vấn vấn đề có chú thích
Truy vấn vấn đề có chú thích. Văn bản dưới hình trong bài cung cấp mô tả chi tiết.

Đối với mỗi tham khảo bảng trong truy vấn, Query Tuner thêm các chú thích sau:

  • CARDF (xem số 1 trong Hình 5): cardinality bảng, biểu thị tổng số hàng trong bảng. CUST_CUSTOMER là bảng lớn nhất (31.284), trong khi CUST_INTEREST_LOOKUP là bảng nhỏ nhất (338).
  • QUALIFIED_ROWS (số 2 trong Hình 5): Số các hàng đủ điều kiện sau khi áp dụng các biến vị ngữ cục bộ trên bảng đó. Mặc dù bảng CUST_CUSTOMER và CUST_INTEREST hầu như có cùng một cardinality bảng, các hàng đủ điều kiện với bảng CUST_CUSTOMER chỉ hơn 1, có nghĩa là có các biến vị ngữ cục bộ có khả năng chọn lọc cao trên bảng đó. Trái ngược lại, với bảng CUST_INTEREST số các hàng đủ điều kiện là giống như cardinality (31.255). Điều này cho biết rằng không có quá trình lọc nào trên bảng đó. Điều này có thể giải thích được vì trên bảng CUST_CUSTOMER có hai biến vị ngữ cục bộ trong khi trên bảng CUST_INTEREST không có các biến vị ngữ cục bộ nào.
  • NPAGESF (số 3 trong Hình 5): Tổng số các trang trên đó các hàng của bảng này xuất hiện.

Với mỗi biến vị ngữ trong truy vấn, Query Tuner thêm các chú thích số liệu thống kê cho các cột được tham khảo trong biến vị ngữ đó và cũng thêm ước tính giá cho biến vị ngữ đó:

  • COLCARDF (xem số 4 trong Hình 5): cardinality cột, biểu thị số lượng ước tính của các giá trị khác nhau trong cột. Nếu biến vị ngữ đó chứa nhiều hơn một cột, thì tách rời cardinality cột cho từng cột đã tham khảo bằng dấu gạch chéo ngược ("/") theo đúng thứ tự xuất hiện các cột trong biến vị ngữ.
  • MAX_FREQ (số 5 trong Hình 5): tần số tối đa của tất cả các giá trị cột có thể. Tần số cho một giá trị cột cụ thể là tỷ lệ phần trăm của các hàng trong bảng có chứa các giá trị cụ thể cho một cột. Ví dụ, nếu có năm giá trị khác nhau cho một cột (COLCARDF = 5), và nếu dữ liệu được phân bố đồng đều, MAX_FREQ bằng khoảng 20% vì mỗi giá trị cột khác nhau điền vào 20% các hàng của bảng. Nếu cardinality cột là 5 và MAX_FREQ vẫn còn cách xa hơn 20%, có nghĩa là dữ liệu trong bảng phân bố không đều trên cột đó. Tóm lại, có sự không đối xứng dữ liệu trên cột.
  • FF (xem số 6 trong Hình 5): Hệ số lọc với biến vị ngữ. Hệ số lọc là một số giữa 0 và 1 đánh giá tỷ lệ phần trăm của các hàng trong một bảng có biến vị ngữ là đúng. Hệ số lọc cho biết biến vị ngữ có khả năng chọn lựa như thế nào. Biến vị ngữ càng có khả năng chọn lọc nhiều hơn, thì biến vị ngữ sẽ càng được áp dụng sớm hơn.

Phân tích kế hoạch truy cập truy vấn

Query Tuner cung cấp một sự hiển thị trực quan về quá trình xử lý mà máy chủ dữ liệu của bạn sử dụng để chạy một truy vấn. Sự hiển thị trực quan này được gọi là biểu đồ kế hoạch truy cập. Từ biểu đồ kế hoạch truy cập, bạn có thể thấy những lựa chọn nào mà trình tối ưu đã thực hiện đối với cách sẽ xử lý truy vấn và lý do cơ bản cho những lựa chọn đó. Biểu đồ gồm các nút biểu diễn các bảng, các chỉ mục, các hoạt động và dữ liệu được trả về. Các nút được bố trí và được kết nối bởi các liên kết cho biết dòng chảy của quá trình này. Biểu đồ được đọc từ trái sang phải, từ dưới lên trên. Và mỗi nút được chú thích bằng số liệu thống kê, các giá ước tính, thông tin về độ chọn lọc và v.v.. được sử dụng để xác định dòng chảy của kế hoạch truy cập.

Việc hiểu biết về kế hoạch truy cập là quan trọng để hiểu biết và tác động đến hiệu năng, cũng như để ổn định hiệu năng. Hãy tham khảo bài trước trong loạt bài này để biết thêm thông tin về đọc và hiểu các đường dẫn truy cập.

Hình 6 là biểu đồ đường dẫn truy cập được Query Tuner tạo cho truy vấn mẫu trong bài này.

Hình 6. Biểu đồ kế hoạch truy cập
Biểu đồ kế hoạch truy cập. Văn bản dưới hình trong bài cung cấp mô tả chi tiết.

(Xem ảnh lớn hơn của Hình 6.)

Từ biểu đồ kế hoạch truy cập trong Hình 6, bạn có thể thấy như sau:

  • Truy vấn này chứa hai khối truy vấn, QB1 (xem số 1 trong Hình 6) và QB2 (xem số 2 trong Hình 6). QB2 biểu diễn truy vấn con IN-list trong khi QB1 là truy vấn con chính.
  • QB2 được nối với bảng CUST_CUSTOMER trong khối truy vấn bên ngoài QB1, có nghĩa là truy vấn con IN-list đã được chuyển đổi làm truy vấn con tương quan, mặc dù nó là một truy vấn con không tương quan trong truy vấn ban đầu.
  • Có thể tóm tắt kế hoạch truy cập với QB1 như sau: TBSCAN(CUST_CUSTOMER) NLJ ISCAN(CUST_INTEREST) NLJ ISCAN(CUST_INTEREST_LOOKUP)
    • 3 bảng trong QB1 được nối bằng phép nối vòng lặp lồng nhau (NLJ)
    • 3 bảng trong QB1 được nối trong chuỗi nối sau: CUST_CUSTOMER -> CUST_INTEREST -> CUST_INTEREST_LOOKUP
    • Truy cập bảng CUST_CUSTOMER bằng cách quét bảng trong khi truy cập bảng CUST_INTEREST và CUST_INTEREST_LOOKUP bằng quét chỉ mục.
  • Có thể tóm tắt kế hoạch truy cập với QB2 như sau: TBSCAN(CUST_ORDER_HEADER) NLJ ISCAN(CUST_ORDER_STATUS).
    • 2 bảng QB2 được nối bằng phép nối vòng lặp lồng nhau (NLJ)
    • 2 bảng QB2 được nối trong chuỗi nối sau: CUST_ORDER_HEADER -> CUST_ORDER_STATUS
    • Truy cập bảng CUST_ORDER_HEADER bằng quét bảng trong khi truy cập CUST_ORDER_STATUS bằng quét chỉ mục.

Từ biểu đồ kế hoạch truy cập trong Hình 6, bạn có thể thực hiện một số phân tích hiệu năng ban đầu:

  • Việc truy cập vào các bảng bên trong (CUST_INTEREST và CUST_INTEREST_LOOKUP) bằng quét chỉ mục. Đó là một kế hoạch truy cập hiệu quả hợp lý.
  • Việc truy cập vào bảng hàng đầu của truy vấn con bên ngoài (CUST_CUSTOMER) và truy vấn con bên trong (CUST_ORDER_HEADER) bằng quét bảng, có thể có một vấn đề tiềm năng:
    • Cardinality bảng với bảng CUST_CUSTOMER bằng khoảng 30000. Tuy nhiên, vì bảng này là một bảng hàng đầu và sẽ được truy cập bằng quét bảng chỉ một lần, bảng này có thể gây ra một số vấn đề hiệu năng, nhưng bảng này không phải là một thảm họa.
    • Truy vấn con bên trong là một truy vấn con tương quan. Tùy thuộc vào có bao nhiêu hàng đủ điều kiện được trả về từ bảng bên ngoài (CUST_CUSTOMER), truy vấn con này có thể được truy cập nhiều lần. Từ hoặc chú thích trong Hình 5 hoặc biểu đồ kế hoạch truy cập trong Hình 6, bạn có thể thấy rằng các hàng đủ điều kiện được đánh giá cho bảng CUST_CUSTOMER là 1; nói cách khác, trình tối ưu hóa cho rằng sẽ chỉ quét bảng CUST_ORDER_HEADER một lần. Dựa vào cardinality bảng xấp xỉ là 50000; bảng này sẽ không phải là một thảm họa về hiệu năng.

Như vậy đến nay, bạn chắc chắn rằng bảng CUST_CUSTOMER sẽ được quét chỉ một lần vì nó là một bảng hàng đầu trong truy vấn con bên ngoài. Tuy nhiên bạn không chắc chắn liệu bảng CUST_ORDER_HEADER sẽ được quét chỉ một lần không, do các hàng đủ điều kiện từ bảng CUST_CUSTOMER được tính toán với số liệu thống kê và các biến vị ngữ hiện có:

  • Cardinality bảng với bảng CUST_CUSTOMER là 31284 (xem số 7 trong Hình 5).
  • Có hai biến vị ngữ cục bộ trên bảng này có khả năng chọn lọc cao:
    • CCUS.CUST_CITY = 'Singapore', FF=0.00727 (xem số 8 trong Hình 5)
    • CCUS.CUST_PROV_STATE = 'Singapore', FF=0.004 (xem số 9 trong Hình 5)
  • Vì vậy các hàng đủ điều kiện được trình tối ưu hóa đánh giá xấp xỉ bằng 31284*0,00727*0,004 = 1.

Điều gì sẽ xảy ra nếu các hàng đủ điều kiện được đánh giá từ bảng CUST_CUSTOMER là sai? Ví dụ, điều gì sẽ xảy ra nếu hai biến vị ngữ cục bộ không được trình tối ưu hóa đánh giá bằng khả năng chọn lựa? Điều này có thể gây ra các vấn đề hiệu năng nghiêm trọng do đã có thể truy cập bảng CUST_ORDER_HEADER nhiều lần.

Một cách để xác nhận hợp lệ nghẽn cổ chai hiệu năng đáng ngờ là nhờ xem xét số liệu thống kê thời gian chạy của truy vấn đó, có thể thu được bằng cách bật chức năng theo vết hiệu năng trên IFCID 318. Một lựa chọn khác là sử dụng Query Tuner để bắt giữ các câu lệnh từ bộ nhớ truy cập nhanh (cache) câu lệnh và để xem thông tin thời gian chạy của câu lệnh đó, như trong Hình 7.

Hình 7. Số liệu thống kê thời gian chạy
Số liệu thống kê thời gian chạy. Văn bản dưới hình trong bài cung cấp mô tả chi tiết.

(Xem ảnh lớn hơn của Hình 7.)

Dòng được đánh dấu (kết thúc với một chữ "B" trong Hình 7) cho thấy thông tin thời gian chạy với truy vấn mẫu trong bài này. Như bạn có thể thấy, truy vấn này được thực hiện ba lần, và thời gian trôi qua trung bình khoảng 307 giây, rất chậm. Tổng số lần quét bảng (STAT_RSCN) khoảng 1764, tức là, nhiều hơn 580 lần quét bảng (1764/3) cho mỗi lần thực hiện. Điều này còn cách xa hơn so với những gì có thể được đánh giá từ biểu đồ kế hoạch truy cập với khoảng 2 làn quét (một với bảng CUST_CUSTOMER, và một với bảng CUST_ORDER_HEADER). Điều này khẳng định thêm nghi ngờ của chúng ta là các hàng đủ điều kiện theo số đã đánh giá từ bảng CUST_CUSTOMER còn cách xa thực tế.

Một cách khác để xác nhận hợp lệ điều này là ban hành một truy vấn như sau để tính giá trị thực của các hàng đủ điều kiện.

Liệt kê 2. Truy vấn đếm
SELECT COUNT(*)
FROM CUST_CUSTOMER AS CCUS
WHERE CCUS.CUST_CITY = 'Singapore' AND CCUS.CUST_PROV_STATE = 'Singapore'

Kết quả của truy vấn count (đếm) nói trên cho thấy rằng có khoảng 588 hàng đủ điều kiện từ bảng CUST_CUSTOMER. Nói cách khác, trình tối ưu hóa đánh giá quá cao độ chọn lọc của các biến vị ngữ cục bộ trên bảng này. Trong các phần sau của bài này, bạn sẽ phân tích truy vấn vấn đề theo các quan điểm về số liệu thống kê, biến vị ngữ và chỉ mục, sau đó chỉ ra tại sao lại xảy ra đánh giá quá cao, cũng như làm thế nào để giải quyết nó.

Thực hiện phân tích số liệu thống kê

Từ thông tin chú thích truy vấn, bạn có thể dễ dàng xem các loại số liệu thống kê nào có sẵn, và các loại số liệu thống kê nào còn thiếu. Với truy vấn trong bài này, số liệu thống kê cơ bản của các bảng, các cột và các chỉ mục tham khảo được thu thập, ví dụ như cardinality bảng, cardinality cột, v.v..

Mặt khác, một số số liệu thống kê phân phối, như tần số cột, chưa bao giờ được thu thập. Ví dụ, như trong Hình 8, MAX_FREQ với cột CUST_CITY trong biến vị ngữ CCUS.CUST_CITY = 'Singapore' được thể hiện là còn thiếu. Số liệu thống kê còn thiếu có thể làm cho trình tối ưu hóa đánh giá quá cao hoặc đánh giá quá thấp độ chọn lọc của một biến vị ngữ, và cuối cùng chọn một đường dẫn truy cập không hiệu quả.

Hình 8. Phân tích số liệu thống kê
Phân tích số liệu thống kê. Văn bản dưới hình trong bài cung cấp mô tả chi tiết.

Một vấn đề tương tự có thể xảy ra ngay cả khi số liệu thống kê được thu thập từ trước nhưng đã lâu không được làm mới đến mức bây giờ chúng đã quá hạn. Điều này đặc biệt đúng nếu dữ liệu đã thay đổi đáng kể từ lần thu thập số liệu thống kê gần nhất. Có một thuộc tính RUNSTATS TIMESTAMP trên nút bảng hoặc nút chỉ mục của biểu đồ kế hoạch truy cập cho biết thời gian cuối cùng đã thu thập số liệu thống kê. Lưu ý, bạn có thể quy định một ngưỡng theo các mức ưu tiên của Query Tuner để định nghĩa xem số liệu thống kê cũ thế nào trước khi chúng được coi là quá hạn. Theo mặc định, nếu số liệu thống kê cũ quá 1 năm thì sẽ được xác định là quá hạn.

Nếu thu thập số liệu thống kê khác nhau tại các thời điểm khác nhau, thì có thể dẫn đến một tình huống ở đó số liệu thống kê không phù hợp với nhau trên DB2 cho z/OS. Ví dụ, nếu thu thập số liệu thống kê mức bảng trên một bảng cụ thể, rồi chèn một số các hàng đáng kể vào bảng này, thì việc thu thập số liệu thống kê với các chỉ mục này chỉ có thể kết thúc với các cột chỉ mục có cardinality cột lớn hơn cardinality bảng. Số liệu thống kê không phù hợp cũng có thể đánh lừa trình tối ưu hóa khiến cho trình này chọn một đường dẫn truy cập xấu.

Thực hiện phân tích biến vị ngữ

Trong khi đó, bạn cũng có thể thực hiện phân tích biến vị ngữ để tìm xem có bất kỳ vấn đề tiềm năng nào không trong khung nhìn chú thích, như thể hiện trong Hình 9.

Hình 9. Phân tích biến vị ngữ
Phân tích biến vị ngữ. Văn bản dưới hình trong bài cung cấp mô tả chi tiết.

(Xem ảnh lớn hơn của Hình 9.)

Theo thông tin chú thích trong hình này, tham khảo bảng CUST_CUSTOMER (CCUS) có hai biến vị ngữ cục bộ có khả năng chọn lọc cao, với hệ số lọc tương ứng là 0,00727 và 0,004.

Vì không thu thập số liệu thống kê tần số nào (MAX_FREQ=(missing)), nên hệ số lọc của mỗi biến vị ngữ được tính toán dựa trên giả định rằng dữ liệu được phân bố đồng đều trên các cột. Ví dụ, cardinality cột của CUST_CITY là 1376, do đó hệ số lọc của biến vị CCUS.CUST_CITY='Singapore' được tính là 1/1376=0,00727.

Người ta có thể đặt bao nhiêu lòng tin vào đánh giá về độ chọn lọc của biến vị ngữ? Nếu dữ liệu không đối xứng trên các cột, ví dụ nếu phần lớn dữ liệu có một tên thành phố là Singapore, thì độ chọn lọc hiện tại có thể được đánh giá quá cao. Để có được một đánh giá chính xác hơn, bạn cần phải thu thập số liệu thống kê tần số trên các cột CUST_CITY và CUST_PROV_STATE của bảng CUST_CUSTOMER.

Vấn đề tiềm năng khác là biến vị ngữ cục bộ trên tham khảo bảng CUST_ORDER_HEADER (COHE) trong truy vấn con bên trong (xem trong Hình 9). Vì nó là một biến vị ngữ phạm vi và không có sẵn số liệu thống kê tần số và hoành đồ, nên tính toán hệ số lọc bằng thông tin của số liệu thống kê cơ bản là HIGH2KEY và LOW2KEY. Việc đánh giá có thể không chính xác nếu có dữ liệu không đối xứng trên cột. Để nhận được một đánh giá chính xác hơn, bạn cần thu thập số liệu thống kê hoành đồ trên cột CUST_ORDER_DATE của bảng CUST_ORDER_STATUS_CODE.

Bây giờ bạn đã phân tích độ chọn lọc của các biến vị ngữ cục bộ, chúng ta hãy xem độ chọn lọc bảng.

Với ba bảng trong truy vấn con bên ngoài, không có biến vị ngữ cục bộ nào trên bảng CUST_INTEREST_LOOKUP (CILO) và CUST_INTEREST (CInt); do đó nếu cả hai bảng đó đã được xử lý như bảng hàng đầu, thì độ chọn lọc bảng bằng không, rất xấu.

Nếu xử lý bảng CUST_CUSTOMER (CCUS) như là bảng hàng đầu, dựa vào hai biến vị ngữ cục bộ, độ chọn lọc bảng của CUST_CUSTOMER là 0,00727 * 0,004, và các hàng đủ điều kiện của bảng CUST_CUSTOMER xấp xỉ bằng 1 (31284 * 0,00727 * 0,004). Điều này có vẻ có độ chọn lọc cao, nhưng giá trị này được tính với giả định rằng hai cột (CUST_CITY, CUST_PROV_STATE) không tương quan với nhau, là không đúng sự thật. Vì Singapore là một trong những thành phố trực thuộc nước Singapore, nên để nhận được một đánh giá chính xác về độ chọn lọc bảng, bạn cần phải thu thập số liệu thống kê về cardinality và tần số của nhóm cột (CUST_CITY, CUST_PROV_STATE).

Sau khi xử lý bảng CUST_CUSTOMER, có thể coi biến vị ngữ nối CCUS.CUST_CODE = CINT.CUST_CODE là một biến vị ngữ cục bộ trên bảng CUST_INTEREST (CINT). Căn cứ vào cardinality bảng CUST_INTEREST xấp xỉ bằng 31255, bạn có thể thấy rằng với mỗi hàng đủ điều kiện từ bảng CUST_CUSTOMER, chỉ có một bản ghi phù hợp trong CUST_INTEREST. Nghĩa là, bảng CUST_INTEREST có thể có độ chọn lọc cao thông qua biến vị ngữ nối.

Sau khi xử lý bảng CUST_CUSTOMER và CUST_INTEREST, có thể coi biến vị ngữ nối CINT.CUST_INTEREST_CODE = CILO.CUST_INTEREST_CODE là một biến vị ngữ cục bộ trên bảng CUST_INTEREST_LOOKUP (CILO). Dựa vào cardinality bảng của bảng CUST_INTEREST_LOOKUP xấp xỉ bằng 338, bạn có thể thấy rằng với mỗi hàng đủ điều kiện của bảng CUST_INTEREST, có khoảng 13 (338/26) bản ghi phù hợp trong bảng CUST_INTEREST_LOOKUP, đó là một mức chọn lọc khá tốt.

Với số liệu thống kê và phân tích biến vị ngữ ở trên, bạn có một ý niệm rõ ràng về cần thu thập số liệu thống kê nào. Sau khi thu thập số liệu thống kê đó, bạn sẽ nhận được biểu đồ kế hoạch truy cập mới được thể hiện trong Hình 10.

Hình 10. Biểu đồ kế hoạch truy cập sau khi chạy RUNSTATS
Biểu đồ kế hoạch truy cập sau khi chạy RUNSTATS. Văn bản dưới hình trong bài cung cấp mô tả chi tiết.

Từ biểu đồ kế hoạch truy cập mới này, bạn có thể thấy như sau:

  • Do đã thu thập số liệu thống kê trên nhóm cột (CUST_CITY và CUST_PROV_STATE) trên bảng CUST_CUSTOMER, bây giờ trình tối ưu hóa DB2 biết số hàng chính xác đủ điều kiện sau khi lọc cục bộ là 590 thay vì 1.
  • Chuỗi nối của truy vấn con bên trong được thay đổi, chuỗi ban đầu là CUST_ORDER_HEADER-> CUST_ORDER_STATUS, now it is CUST_ORDER_STATUS -> CUST_ORDER_HEADER; vì số liệu thống kê hoành đồ trên cột CUST_ORDER_DATE của bảng CUST_ORDER_HEADER đã được thu thập.
  • Với các hàng đủ điều kiện chính xác từ CUST_CUSTOMER, truy vấn con bên trong được xử lý như là truy vấn con không tương quan, được cụ thể hóa vào một tệp làm việc và sau đó được truy cập bằng quét chỉ mục rời rạc trong bộ nhớ.

Dòng được kết thúc bằng một chữ "S" trong Hình 11 cho thấy thông tin thời gian chạy của truy vấn mẫu sau khi chạy RUNSTATS. Thời gian trôi qua trung bình xấp xỉ 2,8 giây, một sự cải tiến hiệu năng đáng kể so với thời gian đó trước khi chạy RUNSTATS (khoảng 307 giây).

Hình 11. Số liệu thống kê thời gian chạy sau RUNSTATS
Số liệu thống kê thời gian chạy sau khi chạy RUNSTATS. Văn bản dưới hình trong bài cung cấp mô tả chi tiết.

(Xem ảnh lớn hơn của Hình 11.)

Cũng như trong Hình 11, bạn có thể thấy rằng vẫn còn có hai lần quét bảng cho mỗi lần thực hiện truy vấn này. Từ biểu đồ kế hoạch truy cập trong Hình 10, bạn có thể dễ dàng tìm ra hai lần quét bảng đến từ bảng CUST_CUSTOMER và CUST_ORDER_HEADER tương ứng. Bạn sẽ thấy cách có thể sửa chữa việc này nhờ việc phân tích chỉ mục trong phần tiếp theo.

Thực hiện phân tích chỉ mục

Optim Query Tuner cung cấp các báo cáo theo định dạng HTML và định dạng văn bản có chứa thông tin về các bảng, các chỉ mục, và các biến vị ngữ có liên quan với một câu lệnh SQL cụ thể. Với truy vấn mẫu trong bài này, Hình 12 cho thấy báo cáo chỉ mục, mô tả các chỉ mục hiện có của các bảng tham khảo.

Hình 12. Bản ghi chỉ mục
Bản ghi chỉ mục. Văn bản dưới hình trong bài cung cấp mô tả chi tiết.

Để thuận tiện cho việc thảo luận, các chỉ mục hiện có được tóm tắt trong Bảng 1.

Bảng 1. Các chỉ mục hiện có
Tên bảngTên chỉ mụcCột chỉ mục
CUST_CUSTOMERBQT01_CUST_CUSTOMERCUST_AGE
BQT02_CUST_CUSTOMERCUST_PROV_STATE_CODE
BQT03_CUST_CUSTOMER

GENDER_CODE,

MARITAL_STATUS_CODE,

CUST_AGE

IDX_CUST_CUSTOMERCUST_CODE
CUST_INTERESTCUST_INTEREST_PK

CUST_CODE,

CUST_INTEREST_CODE

CUST_INTEREST_LOOKUPCUST_INT_LOOKUP_PK

CUST_INTEREST_CODE,

CUST_INTEREST_LANGUAGE

CUST_ORDER_HEADERBQT01_CUST_ORDER_HEADERCRDT_METHOD_CODE
CUST_ORDER_HEADER_PKCUST_ORDER_NUMBER
CUST_ORDER_STATUSBQT01_CUST_ORDER_STATUS

CUST_ORDER_STATUS,

CUST_ORDER_STATUS_LANGUAGE

CUST_ORDER_STAT_PK

CUST_ORDER_STATUS_CODE,

CUST_ORDER_STATUS_LANGUAGE

Bây giờ chúng ta hãy xem xét các biến vị ngữ với bảng CUST_CUSTOMER khi được trích xuất từ truy vấn đã định dạng trong Hình 3:

  • CCUS.CUST_CITY = 'Singapore'
  • CCUS.CUST_PROV_STATE = 'Singapore'
  • CCUS.CUST_CODE = CINT.CUST_CODE

Dựa vào các chỉ mục hiện có trong Bảng 1 và biến vị ngữ ở trên, chỉ có biến vị ngữ nối (CCUST.CUST_CODE = CINT.CUST_CODE) có thể được lợi từ chỉ mục hiện có IDX_CUST_CUSTOMER. Hai biến vị ngữ cục bộ không thể được lợi từ việc phối hợp hoặc kiểm tra các lần quét chỉ mục từ bất kỳ các chỉ mục hiện có nào.

Để hỗ trợ cả hai biến vị ngữ cục bộ và các biến vị ngữ nối với các lần quét chỉ mục, bạn cần tạo một chỉ mục trên các cột CUST_CITY, CUST_PROV_STATE và CUST_CODE.

Đối với bảng CUST_INTEREST và CUST_INTEREST_LOOKUP, mỗi bảng có một biến vị ngữ nối, và chỉ mục hiện tại có thể hỗ trợ một lần quét chỉ mục phù hợp trên các biến vị ngữ nối như được hiển thị dưới đây.

  • CCUS.CUST_CODE = CINT.CUST_CODE
  • CINT.CUST_INTEREST_CODE = CILO.CUST_INTEREST_CODE

Đối với bảng CUST_ORDER_HEADER, hai trong số các chỉ mục hiện có không hỗ trợ các biến vị ngữ cục bộ và nối trên bảng, như được cho thấy dưới đây.

  • COHE.CUST_ORDER_DATE >= '2009-01-01 00:00:00.001'
  • COHE.CUST_ORDER_STATUS_CODE = COST.CUST_ORDER_STATUS_CODE

Để hỗ trợ cả hai biến vị ngữ cục bộ và biến vị ngữ nối với một lần quét chỉ mục, bạn cần tạo một chỉ mục trên các cột CUST_ORDER_DATE và CUST_ORDER_STATUS_CODE. Ngoài ra, để có được hiệu năng tốt hơn với chỉ mục chỉ truy cập, bạn cũng có thể thêm CUST_CODE làm một cột chính. Khi cho rằng bảng này là một phần của truy vấn con IN-list, nếu CUST_CODE là cột chính đầu tiên, thì trình tối ưu hóa DB2 có thể xem xét việc tối ưu hóa truy vấn chung để truy cập truy vấn con như là truy vấn con đã tương quan.

Đối với bảng CUST_ORDER_STATUS, chỉ mục đầu tiên là BQT01_CUST_ORDER_STATUS hỗ trợ truy cập chỉ mục cho biến vị ngữ cục bộ, nhưng không thể hỗ trợ cho biến vị ngữ nối. Chỉ mục thứ hai CUST_ORDER_STAT_PK có thể hỗ trợ truy cập chỉ mục cho biến vị ngữ nối nhưng lại không thể hỗ trợ cho biến vị ngữ cục bộ:

  • COST.CUST_ORDER_STATUS IN ( 'Shipped', 'Back-ordered', 'In-process' )
  • COHE.CUST_ORDER_STATUS_CODE = COST.CUST_ORDER_STATUS_CODE

Để đạt được hiệu năng tốt hơn trên bảng CUST_ORDER_STATUS, bạn cần tạo ra một chỉ mục với cả hai cột CUST_ORDER_STATUS_CODE và CUST_ORDER_STATUS.

Dựa vào phân tích chỉ mục ở trên, chúng ta đề xuất ba chỉ mục sau để đạt được hiệu năng tốt hơn:

Bảng 2. Các chỉ mục mới
Tên bảngTên chỉ mụcCột chỉ mục
CUST_CUSTOMERCUST_CUSTOMER_NEW_INDEX

CUST_CITY, CUST_PROV_STATE,

CUST_CODE

CUST_ORDER_HEADERCUST_ORDER_HEADER_NEW_IDX

CUST_CODE, CUST_ORDER_DATE,

CUST_ORDER_STATUS_CODE

CUST_ORDER_STATUSCUST_ORDER_STATUS_NEW_IDX

CUST_ORDER_STATUS_CODE,

CUST_ORDER_STATUS

Sau khi tạo ba chỉ mục mới, bạn sẽ nhận được biểu đồ kế hoạch truy cập mới được thể hiện trong Hình 13.

Hình 13. Biểu đồ kế hoạch truy cập sau khi tạo các chỉ mục
Biểu đồ kế hoạch truy cập sau khi tạo các chỉ mục. Văn bản dưới hình trong bài cung cấp mô tả chi tiết.

(Xem ảnh lớn hơn của Hình 13.)

Với các chỉ mục mới được tạo ra, truy cập tất cả các bảng bằng các lần quét chỉ mục. Thay đổi lại truy vấn con thành truy vấn con tương quan, và truy cập hai bảng trong truy vấn con bằng cách sử dụng chỉ mục cung cấp hiệu năng tốt nhất.

Dòng được kết thúc với một chữ "I" trong Hình 14 cho thấy thông tin thời gian chạy của truy vấn mẫu sau khi tạo các chỉ mục mới. Thời gian trôi qua trung bình là khoảng 0,246 giây, một sự cải thiện hơn nữa so với hiệu năng sau khi chạy RUNSTATS (khoảng 2,8 giây).

Hình 14. Số liệu thống kê thời gian chạy sau khi tạo các chỉ mục
Số liệu thống kê thời gian chạy sau khi tạo các chỉ mục. Văn bản dưới hình trong bài cung cấp mô tả chi tiết.

(Xem ảnh lớn hơn của Hình 14.)

Với sự phân tích từng bước một trên số liệu thống kê, biến vị ngữ, và chỉ mục, hiệu năng của truy vấn vấn đề được cải thiện đáng kể. Bảng 2 hiển thị một bản tóm tắt về các cải thiện hiệu năng. Hàng 1 cho thấy số liệu thống kê thời gian chạy trước khi điều chỉnh. Hàng 2 cho thấy số liệu thống kê sau khi thu thập số liệu thống kê có liên quan, và hàng 3 cho thấy các kết quả sau khi tạo các chỉ mục đã đề xuất.

Bảng 3. Bảng 3. So sánh hiệu năng
Nhiệm vụ điều chỉnhĐếm số lần thực hiệnSTAT_GPAGSTAT_ELAPSTAT_CPUAVG_STAT_ELAPAVG_STAT_CPU
Trước khi điều chỉnh323486129922,197815145,920307307,39927248,640102
Thu thập số liệu thống kê4837611,2001860,880022,8000470,220007
Tạo các chỉ mục460840,9833570,0477040,2458390,011926

Tăng tốc độ điều chỉnh truy vấn bằng Optim Query Tuner

Cho đến nay, bạn đã thấy cách bạn có thể phân tích và điều chỉnh một truy vấn vấn đề với sự hỗ trợ của các khả năng Optim Query Tuner, ví dụ như chú thích truy vấn, biểu đồ kế hoạch truy cập, và v.v. Mặc dù các công cụ này rất trực quan và thân thiện với người dùng, bạn vẫn cần có đủ kiến thức cơ bản về trình tối ưu hóa DB2 và đường dẫn truy cập SQL để tận dụng đầy đủ lợi thế của các hàm mạnh mẽ do các công cụ này cung cấp. Tất nhiên, bạn cũng cần dành một số thời gian cho việc phân tích.

Tuy nhiên, trong nhiều trường hợp, bạn không cần thực hiện điều chỉnh truy vấn bằng thủ công. Optim Query Tuner cung cấp một loạt các trình tư vấn để tự động hóa việc điều chỉnh truy vấn bằng cách đưa ra các khuyến cáo, mà bạn có thể xem xét và thực hiện trực tiếp từ sản phẩm này để giải quyết một vấn đề về hiệu năng.

  • Trình tư vấn số liệu thống kê: các khuyến cáo để thu thập số liệu thống kê cần thiết để làm cho việc lựa chọn đường dẫn truy cập dễ dàng.
  • Trình tư vấn truy vấn: các khuyến cáo để viết lại truy vấn có hiệu quả tốt hơn.
  • Trình tư vấn chỉ mục: các khuyến cáo về các chỉ mục có thể giúp cải thiện hiệu năng.

Khuyến cáo: Thông thường, nó đề xuất bạn chạy các trình tư vấn theo thứ tự sau:

  1. Chạy trình tư vấn thống kê và hành động ngay để tạo số liệu thống kê hiện tại.
  2. Chạy trình tư vấn truy vấn và viết lại SQL nếu cần thiết và có thể.
  3. Chạy trình tư vấn chỉ mục và tạo các chỉ mục mới hoặc điều chỉnh các chỉ mục hiện có. Số liệu thống kê chính xác và đầy đủ sẽ giúp đưa ra lời khuyên về đường dẫn truy cập tốt, và tất nhiên không nên thực hiện trình tư vấn chỉ mục mà không có số liệu thống kê tốt.

Các phần sau nghiên cứu chi tiết hơn về các trình tư vấn nói trên.

Thực hiện trình tư vấn số liệu thống kê để cải thiện chất lượng và thu thập số liệu thống kê

Như đã đề cập ở phần đầu của bài này, số liệu thống kê cơ sở dữ liệu là những bằng chứng dựa vào đó để trình tối ưu hóa đưa ra các quyết định về các kế hoạch truy cập. Do đó, nếu số liệu thống kê không chính xác, quá hạn, hay mâu thuẫn nhau, thì trình tối ưu hóa sẽ tạo ra các đánh giá không chính xác cho các giá của các bước trong một kế hoạch truy vấn, dẫn đến hiệu năng kém.

Trong DB2, lệnh RUNSTATS TABLE ALL INDEX ALL thu thập tất cả số liệu thống kê như nhau, mà nhiều số liệu thống kê đó có thể không cần thiết cho việc cải thiện hiệu năng truy vấn. Đồng thời, tiện ích này không thu thập số liệu thống kê chính nào đó, ví dụ như số liệu thống kê nhiều cột và phân tán. Thường có các tương quan dữ liệu giữa các cột. Ví dụ, có một sự tương quan mạnh mẽ giữa các cột CUST_CITY và CUST_PROV_STATE với truy vấn mẫu trong bài này. Việc thu thập số liệu thống kê của các cột riêng biệt có thể không đủ để cung cấp thông tin cần thiết, vì vậy bạn cần thu thập số liệu thống kê của nhóm cột.

Nhờ đưa ra khuyến cáo RUNSTATS nên trình tư vấn số liệu thống kê Query Tuner làm cho việc thu thập số liệu thống kê cần thiết trở nên dễ dàng hơn bằng cách xác định trạng thái của số liệu thống kê có vấn đề như sau.

  • Số liệu thống kê còn thiếu: Khi vẫn còn thiếu số liệu thống kê, trình tối ưu hóa dùng các giá trị mặc định để xác định các giá, có thể hoàn toàn không chính xác.
  • Số liệu thống kê mâu thuẫn nhau: Số liệu thống kê mâu thuẫn nhau có thể làm cho trình tối ưu hóa lấy ra các giá ước tính sai và đưa ra quyết định sai về đánh giá kế hoạch truy cập. Có thể xảy ra số liệu thống kê mâu thuẫn nhau khi những người dùng thu thập số liệu thống kê một phần, ví dụ như thu thập số liệu thống kê bảng và chỉ mục riêng biệt, tại các thời điểm khác nhau.
  • Số liệu thống kê quá hạn: Số liệu thống kê cũ có thể không còn biểu diễn trạng thái hiện tại của bảng nữa.

Trình tư vấn số liệu thống kê cũng đánh giá tầm quan trọng tương đối của lời khuyên và cung cấp hai kiểu khuyến cáo sau.

  • Sửa chữa: Các khuyến cáo của kiểu này cho biết số liệu thống kê quan trọng vẫn còn thiếu, hoặc có các mâu thuẫn tồn tại giữa số liệu thống kê. Khuyến cáo này bao gồm một lệnh RUNSTATS mà bạn có thể chạy để nắm bắt và sửa chữa số liệu thống kê có liên quan.
  • Hoàn thành: Các khuyến cáo của kiểu này bao gồm cả các khuyến cáo Sửa chữa cũng như số liệu thống kê khác, có thể được làm mới cho mục đích bảo trì. Khuyến cáo này cung cấp một công việc RUNSTATS mà bạn có thể muốn chạy theo định kỳ trong chu kỳ bảo trì của bạn để duy trì sự lành mạnh của số liệu thống kê có liên quan.

Khi bạn tìm đến lời khuyên của một khuyến cáo cụ thể, như thể hiện trong Hình 15, bạn sẽ thấy rằng trình tư vấn số liệu thống kê tạo một lệnh RUNSTATS mà bạn có thể sử dụng để thu thập hoặc sửa chữa số liệu thống kê. Bạn có thể lưu chúng để thực hiện sau, hoặc chạy chúng trực tiếp từ máy khách Query Tuner nếu bạn có ủy quyền thích hợp. Lưu ý, để thực hiện các khuyến cáo RUNSTATS từ Query Tuner, thủ tục đã lưu SYSPROC.DSNUTILU cần có sẵn để dùng và có khả năng hoạt động ở phía máy chủ.

Bạn cũng có thể thấy lời giải thích sau khuyến cáo đó. Trong Hình 15, bạn có thể thấy rằng trình tư vấn đã tìm thấy một số cá thể mâu thuẫn hoặc thiếu số liệu thống kê cũng như đưa ra lời giải thích về lý do trình tư vấn cho rằng chúng đang mâu thuẫn nhau.

Hình 15. Các khuyến cáo của trình tư vấn số liệu thống kê
Các khuyến cáo của trình tư vấn số liệu thống kê. Văn bản dưới hình trong bài cung cấp mô tả chi tiết.

Bạn đã trải qua cách cải thiện chất lượng số liệu thống kê có nghĩa là bạn đang cung cấp cho trình tối ưu hóa DB2 dữ liệu chính xác mà dựa vào đó để đưa ra các quyết định cơ bản về việc tối ưu hóa kế hoạch truy cập và do đó có khả năng cải thiện hiệu năng và giảm số lượng CPU. Tuy nhiên, có lợi ích khác nữa. Nhờ chỉ thu thập số liệu thống kê cần thiết, bạn có thể tránh được việc thu thập số liệu thống kê không liên quan đẩy chi phí hoạt động của CPU lên cao và tạo ra tải công việc không cần thiết bên trong các cửa sổ bảo trì có giới hạn. Nói cách khác, trình tư vấn số liệu thống kê có thể giúp cải thiện chất lượng số liệu thống kê cũng như nâng cao hiệu quả của việc thu thập số liệu thống kê.

Thực hiện trình tư vấn truy vấn để cải thiện thiết kế truy vấn

Trước khi lựa chọn đường dẫn truy cập, trình tối ưu hóa DB2 chuyển đổi câu lệnh SQL thành các dạng giống hệt nhau về ngữ nghĩa, ví dụ như bằng cách sử dụng sắp xếp đẩy xuống của biến vị ngữ hoặc closure bắc cầu của biến vị ngữ. Kết quả là, nó có thể cải thiện các đường dẫn truy cập có khả năng. Ngược lại, Query Tuner giúp người viết và người điều chỉnh các truy vấn nhận biết các lỗi và các trường hợp sơ suất bằng cách thực hiện các đề xuất ràng buộc truy vấn hơn nữa, tăng cường sử dụng chỉ mục, và giảm các lần đọc dữ liệu. Trình tư vấn truy vấn tìm kiếm các cơ hội trong một truy vấn để làm như sau.

  • Giảm thiểu số các trang chỉ mục và các hàng dữ liệu phải đọc. Ví dụ, bạn có thể giảm thiểu việc đọc các hàng bằng cách có các biến vị ngữ trong truy vấn có thể xác định các hàng cần thiết từ chỉ riêng chỉ mục ấy.
  • Giảm thiểu các hoạt động sắp xếp. Ví dụ, mệnh đề ORDER BY hay GROUP BY có cần thiết trong truy vấn hay không, hoặc có thể giải quyết chúng thông qua truy cập chỉ mục không.

Cụ thể, trình tư vấn truy vấn kiểm tra như sau.

  • Các biến vị ngữ nối còn thiếu, nhưng chỉ khi một khóa ngoài được định nghĩa.
  • Các biến vị ngữ Giai đoạn 2 có thể cải thiện hiệu năng nếu viết lại như Giai đoạn 1 hoặc có thể tạo chỉ mục. Xem phần Tài nguyên để biết một liên kết đến Trung tâm Thông tin của DB2 cho z/OS nơi bạn có thể tìm thêm thông tin về biến vị ngữ Giai đoạn 1 và Giai đoạn 2.
  • Các biến vị ngữ Giai đoạn 1 có thể cải thiện hiệu năng nếu viết lại là chỉ mục có khả năng.
  • Các biến vị ngữ cục bộ bổ sung không được DB2 cung cấp tự động do DB2 có thể cung cấp closure bắc cầu của biến vị ngữ.
  • Các biến vị ngữ được đẩy xuống đến một biểu thức bảng lồng nhau hoặc khung nhìn cụ thể hóa mà không thay đổi kết quả, và đã không thực hiện tự động bằng DB2.
  • Các biến vị ngữ đã bổ sung một mệnh đề WHERE phức tạp, có chứa OR, AND, và các dấu ngoặc đơn (). Điều này có thể cải thiện hiệu năng mà không thay đổi kết quả.
  • Việc sử dụng SELECT * có thể được thay thế bằng danh sách cột cụ thể.

Trong Hình 16, bạn có thể xem các chi tiết của một khuyến cáo của trình tư vấn truy vấn cùng với lý do cơ bản cho khuyến cáo đó. Giao diện người dùng (UI) tự động làm nổi bật phần truy vấn liên quan đến khuyến cáo đó. Theo cách này, các DBA hoặc những người phát triển tìm hiểu về điều chỉnh truy vấn khi họ xem khuyến cáo.

Hình 16. Các khuyến cáo của trình tư vấn truy vấn
Các khuyến cáo của trình tư vấn truy vấn. Văn bản dưới hình trong bài cung cấp mô tả chi tiết.

Thực hiện trình tư vấn chỉ mục để nâng cao hiệu quả truy vấn

Query Tuner cũng cung cấp lời khuyên về chỉ mục. Nó phân tích truy vấn và khuyến cáo các chỉ mục bổ sung thường mang lại lợi ích cho truy cập truy vấn. Trình tư vấn chỉ mục có thể khuyến cáo các chỉ mục vì những lý do sau.

  • Các khóa ngoài vẫn chưa định nghĩa các chỉ mục.
  • Các chỉ mục sẽ cung cấp lọc chỉ mục và/hoặc kiểm tra câu lệnh SQL.
  • Các chỉ mục sẽ cung cấp truy cập chỉ theo chỉ mục cho câu lệnh SQL.
  • Các chỉ mục có thể giúp tránh các sắp xếp.

Hình 17 cho thấy các khuyến cáo chỉ mục cho truy vấn mẫu trong bài này, cùng với sự cải thiện hiệu năng đã đánh giá và yêu cầu vùng DASD. Nó cũng tạo ra DDL cần thiết để tạo các chỉ mục, và cung cấp cho bạn khả năng để chạy chúng ngay lập tức, giả sử bạn có ủy quyền thích hợp, hoặc lưu chúng để sau này xem xét và thực hiện.

Hình 17. Các khuyến cáo của trình tư vấn chỉ mục
Các khuyến cáo của trình tư vấn chỉ mục. Văn bản dưới hình trong bài cung cấp mô tả chi tiết.

Bạn cũng có thể tùy chỉnh các khuyến cáo và tạo DDL. Ví dụ, bạn có thể quy định một số tối đa cho số các cột có thể là một phần của một khóa chỉ mục và thay đổi Creator ID (Mã định danh của trình tạo) được sử dụng khi tạo DDL chỉ mục. Bạn cũng có thể quy định các giá trị FREEPAGE, PCTFREE, và CLUSTERRATIO được thừa nhận cho các chỉ mục mới, và dù kích thước trang lá chỉ mục có thể vượt quá 4 KB hay không.

Bạn cũng có thể thử nghiệm các chỉ mục được khuyến cáo cùng với các chỉ mục mà bạn đề xuất với "phân tích what-if " trước khi triển khai. Nó cho phép bạn áp dụng các ràng buộc khác nhau cho trình tư vấn chỉ mục và xem cách các ràng buộc đó thay đổi các khuyến cáo. "Phân tích what-if " sử dụng một chỉ mục ảo trong các vỏ bọc để đánh giá hiệu quả của các chỉ mục. Nhờ đó, bạn có thể chọn tạo các chỉ mục có thể giúp cung cấp cho bạn hiệu năng mà bạn cần.

Kết luận

Bài này đã giới thiệu một phương pháp luận để điều chỉnh các truy vấn riêng rẽ, và đã sử dụng một truy vấn mẫu để giải thích từng bước của phương pháp luận này một cách chi tiết. Bài này bao gồm cách hiểu truy vấn vấn đề tốt hơn bằng cách sử dụng các phương tiện trợ giúp trực quan do định dạng truy vấn, ghi chú, các báo cáo, và biểu đồ kế hoạch truy cập cung cấp. Bạn đã học cách phân tích và điều chỉnh truy vấn vấn đề từ quan điểm của số liệu thống kê, các biến vị ngữ, và các chỉ mục. Bạn cũng đã được trải qua các cách để tăng tốc độ điều chỉnh truy vấn bằng cách sử dụng các trình tư vấn Query Tuner.

Bài tiếp theo của loạt bài này sẽ mở rộng thảo luận đến phạm vi của tải truy vấn, và giới thiệu lý do cần làm việc này, với một ví dụ để giới thiệu điều chỉnh tải công việc một cách chi tiết.


Tải về

Mô tảTênKích thước
Sample project file for this articlesamplequerytuningproject.zip338KB

Tài nguyên

Học tập

Lấy sản phẩm và công nghệ

  • Tải về phần mềm miễn phí Data Studio.
  • Xây dựng dự án phát triển tiếp theo của bạn với phần mềm dùng thử của IBM, có sẵn để tải về trực tiếp từ developerWorks.
  • Đánh giá các sản phẩm IBM theo cách phù hợp nhất với bạn: Tải về một bản dùng thử sản phẩm, hãy thử một sản phẩm trực tuyến, sử dụng một sản phẩm trong một môi trường đám mây, hoặc dành một vài giờ trong SOA Sandbox để tìm hiểu cách thực hiện Service Oriented Architecture (Kiến trúc hướng dịch vụ) một cách hiệu quả.

Thảo luận

Bình luận

developerWorks: Đăng nhập

Các trường được đánh dấu hoa thị là bắt buộc (*).


Bạn cần một ID của IBM?
Bạn quên định danh?


Bạn quên mật khẩu?
Đổi mật khẩu

Bằng việc nhấn Gửi, bạn đã đồng ý với các điều khoản sử dụng developerWorks Điều khoản sử dụng.

 


Ở lần bạn đăng nhập đầu tiên vào trang developerWorks, một hồ sơ cá nhân của bạn được tạo ra. Thông tin trong bản hồ sơ này (tên bạn, nước/vùng lãnh thổ, và tên cơ quan) sẽ được trưng ra cho mọi người và sẽ đi cùng các nội dung mà bạn đăng, trừ khi bạn chọn việc ẩn tên cơ quan của bạn. Bạn có thể cập nhật tài khoản trên trang IBM bất cứ khi nào.

Thông tin gửi đi được đảm bảo an toàn.

Chọn tên hiển thị của bạn



Lần đầu tiên bạn đăng nhập vào trang developerWorks, một bản trích ngang được tạo ra cho bạn, bạn cần phải chọn một tên để hiển thị. Tên hiển thị của bạn sẽ đi kèm theo các nội dung mà bạn đăng tải trên developerWorks.

Tên hiển thị cần có từ 3 đến 30 ký tự. Tên xuất hiện của bạn phải là duy nhất trên trang Cộng đồng developerWorks và vì lí do an ninh nó không phải là địa chỉ email của bạn.

Các trường được đánh dấu hoa thị là bắt buộc (*).

(Tên hiển thị cần có từ 3 đến 30 ký tự)

Bằng việc nhấn Gửi, bạn đã đồng ý với các điều khoản sử dụng developerWorks Điều khoản sử dụng.

 


Thông tin gửi đi được đảm bảo an toàn.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=70
Zone=Information Management
ArticleID=783807
ArticleTitle=Điều chỉnh SQL với Optim Query Tuner, Phần 2: Điều chỉnh các truy vấn riêng lẻ
publish-date=01032012