Hiệu năng PL/SQL trong Oracle 19c

Thông thường để lấy giá trị một SEQUENCE, chúng ta sẽ thường viết “SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL”. Bởi vậy, chúng ta thường mắc phải lỗi rằng mọi phép gán giá trị trong PL/SQL được sử dụng như một hàm trong cơ sở dữ liệu yêu cầu một câu lệnh SELECT-FROM-DUAL. Nên bởi vậy chúng ta sẽ gặp vấn đề về hiệu năng ở đây.

Một lỗi cơ bản

Một mẫu thường dùng khi thiết kế các bảng cần phải lưu lại thông tin về người tạo, người thay đổi, thời gian tạo, thời gian thay đổi, và bởi vậy, rất nhiều cột ví dụ như:

CREATED_BY
CREATED_DATE
MODIFIED_BY
MODIFIED_DATE

được tạo ra, và chúng thường được dùng trigger để fill các giá trị vào như ví dụ dưới đây:

begin
  if inserting then
    :new.created_by := user;
    :new.created_date := sysdate;
  end if;
  :new.modified_by := user;
  :new.modified_date := sysdate;
end;

Nhưng đây là một sai lầm, khi chúng ta thực hiện một loạt các câu lệnh insert vào bảng như vậy, chúng ta sẽ thấy một điều đặc biệt xảy ra:

INSERT INTO T (ID,ENAME) 
VALUES (:B1 ,:B2)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 100000      7.20       7.83        132       1048     106235      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100001      7.20       7.83        132       1048     106235      100000


SELECT USER 
FROM SYS.DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 200000      0.84       0.93          0          0          0           0
Fetch   200000      0.15       0.29          0          0          0      200000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   400001      1.00       1.22          0          0          0      200000

Tham chiếu “USER” trong trigger được sử dụng bởi câu lệnh SELECT-FROM-DUAL. Vì vậy, có lẽ chúng ta đã gây ra lỗi vì đã hiểu sai hoạt động của câu lệnh gán trong PL/SQL như trên.
Có một cách dễ dàng để sửa vấn đề trên, thay vì tham cheiesu đến USER thì chúng ta sử dụng biến context

begin
  if inserting then
    :new.created_by := SYS_CONTEXT('USERENV', 'SESSION_USER');
    :new.created_date := sysdate;
  end if;
  :new.modified_by := SYS_CONTEXT('USERENV', 'SESSION_USER');;
  :new.modified_date := sysdate;
end;

Điều này không yêu cầu một lệnh SELECT-FROM-DUAL và mang lại hiệu suất đáng kể trong việc thực thi của trigger, đặc biệt khi trigger tác động trong bảng có tần suất giao dịch lớn.

Một lỗi cơ bản

Related