Trigger bug

There has a section “CALL Statements in Triggers” in the “Lesson 10: Creating Compound, DDL, and Event Database Triggers” in the Student Guide “Program with PL/SQL – Student Guide II” in this course “Program with PL/SQL” of ODDLS (Oracle Database Deveoper Learning Subscription) subscribed by me.

As you can see from the first screenshot about procedure and trigger code, actually there exists an invoking procedure “log_execution” clause when creating the trigger “log_employee”. And you should also notice that adding an interesting comment “– no semicolon needed” after that invoking statement in order to remind reader not to add an extra semicolon (there is also a conspicuous Note in the second screenshot).

For practicing that trigger code, I separately tested it on oracle 11.2, 12.1, 12.2, 18.3, 19.3 and LiveSQL (current 19.5). Unfortunately PL/SQL compiler alwayws reports the very weird error of PLS-00103 on all the oracle versions I mentioned previously. So yesterday morning I emailed to Steven Feuerstein (Oracle PL/SQL guru all over the world) to ask for him to help me, as a result, he replied to me it looks like a bug if removing the comment “– no semicolon needed” and then it works. He was going to report the issue. The following are three pictures about our emails back and forth.

After his help, today I wrote the following test SQL code snippet on Github.

REM
REM     Script:        invoke_procedure_in_trigger.sql
REM     Author:        Quanwen Zhao
REM     Dated:         Jul 31, 2020
REM
REM     Last tested:
REM             11.2.0.4
REM             12.1.0.2
REM             12.2.0.1
REM             18.3.0.0
REM             19.3.0.0
REM             19.5.0.0 -- LiveSQL
REM
REM     Purpose:
REM       If adding a comment after the call procedure clause in a trigger, PL/SQL compiler
REM       will report the very weird error of PLS-00103. So I emailed to Steven Feuerstein,
REM       he suggests me remove that comment and then it will normally work, which seems to
REM       be a bug.
REM

CREATE TABLE emp (empno NUMBER, ename VARCHAR2(10));

INSERT INTO emp VALUES(7839, 'King');
INSERT INTO emp VALUES(7698, 'Blake');
INSERT INTO emp VALUES(7782, 'Clark');

COMMIT;

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE log_execution IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('log_execution: Emp table has inserted 1 row.');
END;
/

CREATE OR REPLACE TRIGGER log_emp
BEFORE INSERT ON emp
FOR EACH ROW
CALL log_execution -- no semicolon needed
/

Warning: Trigger created with compilation errors.

show errors
Errors for TRIGGER LOG_EMP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/37     PLS-00103: Encountered the symbol "end-of-file" when expecting
         one of the following:
         := . ( @ % ;
         The symbol ";" was substituted for "end-of-file" to continue.

-- if eliminating the comment '-- no semicolon needed' after the 'CALL log_execution' clause

CREATE OR REPLACE TRIGGER log_emp
BEFORE INSERT ON emp
FOR EACH ROW
CALL log_execution
/

Trigger created.

INSERT INTO emp VALUES(7566, 'Jones');
log_execution: Emp table has inserted 1 row.

1 row created.

COMMIT;

And I have to say LiveSQL is a pretty nice tool runing Oracle SQL and PL/SQL code, hence I also wrote a SQL script to test my code (in other words, report a bug). Here‘s my LiveSQL script.

Finally I replied to Steven Feuerstein to let me know the bug number that when MoS has been going to finish reporting.

Addendum

Update Aug 01, 2020: Yesterday evening Steven Feuerstein also emailed to me the bug filed number #31698004 and he reported the issue to oracle and was still able to reproduce it on 20c (outside of LiveSQL).

Displaying and setting PL/SQL Initialization Parameters

There are the following four types of initialization parameters for PL/SQL compilation.

  • PLSQL_CODE_TYPE
  • PLSQL_OPTIMIZE_LEVEL
  • PLSQL_CCFLAGS
  • PLSQL_WARNINGS

In releases before Oracle Database 10g, the PL/SQL compiler translated your code to machine code without applying many changes for performance. Oracle Database 11g onwards, PL/SQL uses an optimizing compiler that can rearrange code for better performance. You do not need to do anything to get the benefits of this new optimizer; it is enabled by default.

The following table is specially describing those four number of initialization parameter of PL/SQL compilation.

+============================+=============================================================================+
|Compiler Option             |Description                                                                  |
+============================+=============================================================================+
|PLSQL_CODE_TYPE             |Specifies the compilation mode for PL/SQL library units.                     |
+----------------------------+-----------------------------------------------------------------------------+
|PLSQL_OPTIMIZE_LEVEL        |Specifies the optimization level to be used to compile PL/SQL library units. |
+----------------------------+-----------------------------------------------------------------------------+
|PLSQL_WARNINGS              |Enables or disables the reporting of warning messages by the PL/SQL compiler.|
+----------------------------+-----------------------------------------------------------------------------+
|PLSQL_CCFLAGS               |Controls conditional compilation of each PL/SQL library unit independently.  |
+----------------------------+-----------------------------------------------------------------------------+

The new compiler improves the performance of PL/SQL code and allows it to execute approximately two times faster than an Oracle 8i database and 1.5 times to 1.75 times as fast as Oracle 9i Database Release 2.

To get the fastest performance, the compiler setting must be:

PLSQL_CODE_TYPE = NATIVE
PLSQL_OPTIMIZE_LEVEL = 2

Hence this post I focus on displaying and setting the previous two initialization parameters for PL/SQL compilation.

Taking the schema “OE” for example in Oracle Database 11.2, firstly, we use the following SQL code snippet to check the current value of both PLSQL_CODE_TYPE and PLSQL_OPTIMIZE_LEVEL based on some oracle objects.

-- running on the schema of 'OE'.

SET LINESIZE 200
SET PAGESIZE 200
COLUMN code_type FORMAT a15

SELECT name
     , type
     , plsql_code_type AS CODE_TYPE
     , plsql_optimize_level AS OPT_LVL
FROM   user_plsql_object_settings;

NAME                           TYPE         CODE_TYPE          OPT_LVL
------------------------------ ------------ --------------- ----------
ACTIONS_T                      TYPE         INTERPRETED              2
ACTION_T                       TYPE         INTERPRETED              2
ACTION_V                       TYPE         INTERPRETED              2
ADD_JOB_HISTORY                PROCEDURE    INTERPRETED              2
CATALOG_TYP                    TYPE         INTERPRETED              2
CATALOG_TYP                    TYPE BODY    INTERPRETED              2
CATEGORY_TYP                   TYPE         INTERPRETED              2
COMPOSITE_CATEGORY_TYP         TYPE         INTERPRETED              2
COMPOSITE_CATEGORY_TYP         TYPE BODY    INTERPRETED              2
CORPORATE_CUSTOMER_TYP         TYPE         INTERPRETED              2
CUSTOMER_TYP                   TYPE         INTERPRETED              2
CUST_ADDRESS_TYP               TYPE         INTERPRETED              2
CUST_DATA                      PACKAGE      INTERPRETED              2
CUST_DATA                      PACKAGE BODY INTERPRETED              2
CUST_LIST                      PROCEDURE    INTERPRETED              2
......

51 rows selected.

As you can see from the above vaule “INTERPRETED” based on PLSQL_CODE_TYPE, but we can set the compiler initialization parameter’s value using the ALTER SYSTEM or ALTER SESSION statements.

Secondly, we’ll use the ALTER SESSION statement to set PLSQL_CODE_TYPE to become “NATIVE” in order to get the fastest performance.

ALTER SESSION SET PLSQL_CODE_TYPE = 'NATIVE';

Thirdly, I create a procedure “add_job_history” on the schema “OE”.

CREATE OR REPLACE PROCEDURE add_job_history
  (p_emp_id             job_history.employee_id%TYPE,
   p_start_date         job_history.start_date%TYPE,
   p_end_date           job_history.end_date%TYPE,
   p_job_id             job_history.job_id%TYPE,
   p_department_id      job_history.department_id%TYPE
  )
  IS
  BEGIN
    INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id)
    VALUES (p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
  END add_job_history;
/

Abruptly oracle gives me the following warning message.

Warning: Procedure created with compilation errors.

Using a simple statement “SHOW ERRORS” to quickly find out which lines the compilation errors are located in.

oe@ORA11GR2> SHOW ERRORS
Errors for PROCEDURE ADD_JOB_HISTORY:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/5     PL/SQL: SQL Statement ignored
10/17    PL/SQL: ORA-01031: insufficient privileges

As you can see from the previous error ORA-01031 that has a bit of confusion. Next, I check the object_type of “job_history”.

COLUMN object_name FORMAT a12
COLUMN object_type FORMAT a12

SELECT object_name, object_type FROM user_objects WHERE object_name = 'JOB_HISTORY';

OBJECT_NAME  OBJECT_TYPE
------------ ------------
JOB_HISTORY  SYNONYM

Oh, my god! As you can see from the above object_type, it’s a synonym (not a table). A real table “JOB_HISTORY” should be from the schema “HR”. By the following SQL, you can prove my description whether it is right.

COLUMN synonym_name FORMAT a12
COLUMN table_owner  FORMAT a12
COLUMN table_name   FORMAT a12

SELECT synonym_name, table_owner, table_name FROM user_synonyms WHERE synonym_name = 'JOB_HISTORY';

SYNONYM_NAME TABLE_OWNER  TABLE_NAME
------------ ------------ ------------
JOB_HISTORY  HR           JOB_HISTORY

So, switching to the schema “HR”, rerunning all the previous code step by step.

SET LINESIZE 200
SET PAGESIZE 200
COLUMN code_type FORMAT a15

SELECT name
     , type
     , plsql_code_type AS CODE_TYPE
     , plsql_optimize_level AS OPT_LVL
FROM   user_plsql_object_settings;

NAME                           TYPE         CODE_TYPE          OPT_LVL
------------------------------ ------------ --------------- ----------
ADD_COL                        PROCEDURE    INTERPRETED              2
ADD_DEPARTMENT                 PROCEDURE    INTERPRETED              2
ADD_DEPARTMENT_NOEX            PROCEDURE    INTERPRETED              2
ADD_DEPT                       PROCEDURE    INTERPRETED              2
ADD_EMPLOYEE                   PROCEDURE    INTERPRETED              2
ADD_ROW                        PROCEDURE    INTERPRETED              2
......

81 rows selected.
ALTER SESSION SET PLSQL_CODE_TYPE = 'NATIVE';
CREATE OR REPLACE PROCEDURE add_job_history
  (p_emp_id             job_history.employee_id%TYPE,
   p_start_date         job_history.start_date%TYPE,
   p_end_date           job_history.end_date%TYPE,
   p_job_id             job_history.job_id%TYPE,
   p_department_id      job_history.department_id%TYPE
  )
  IS
  BEGIN
    INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id)
    VALUES (p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
  END add_job_history;
/

Procedure created.
SELECT name
     , type
     , plsql_code_type AS CODE_TYPE
     , plsql_optimize_level AS OPT_LVL
FROM   user_plsql_object_settings;

NAME                           TYPE         CODE_TYPE          OPT_LVL
------------------------------ ------------ --------------- ----------
ADD_COL                        PROCEDURE    INTERPRETED              2
ADD_DEPARTMENT                 PROCEDURE    INTERPRETED              2
ADD_DEPARTMENT_NOEX            PROCEDURE    INTERPRETED              2
ADD_DEPT                       PROCEDURE    INTERPRETED              2
ADD_EMPLOYEE                   PROCEDURE    INTERPRETED              2
ADD_JOB_HISTORY                PROCEDURE    NATIVE                   2
ADD_ROW                        PROCEDURE    INTERPRETED              2
......

82 rows selected.

As you can see the last SQL query result, the PLSQL_CODE_TYPE of the procedure “ADD_JOB_HISTORY” being created just now has become NATIVE.

Potential Bug on Muti-tenant DB

Middle last month I found a very strange phenomenon on oracle 19.3 of Windows platform – querying “SELECT COUNT(*) FROM all_objects” is very rapid on SYS but pretty slow on a COMMON user.

So I decided to use a 10046 event to trace the SQL statement previously mentioned so that expected to find out some valuable clues. Take a look at my operation steps as follows.

  • running on SYS schema
  • running on C##QWZ schema
  • tracking SQL via 10046 event on C##QWZ schema

SYS

16:18:35 SYS@ora19c> select count(*) from all_objects;

  COUNT(*)
----------
     72352

16:18:46 SYS@ora19c> 

C##QWZ

16:28:29 C##QWZ@ora19c> select count(*) from all_objects;

  COUNT(*)
----------
     55672

16:40:01 C##QWZ@ora19c> 

10046 event

SET LINESIZE 150
SET PAGESIZE 150
SET SERVEROUTPUT OFF

ALTER SESSION SET max_dump_file_size = 'UNLIMITED';
ALTER SESSION SET tracefile_identifier = '10046';
ALTER SESSION SET events '10046 trace name context forever,level 12';

select count(*) from all_objects;

ALTER SESSION SET events '10046 trace name context off';
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

Next generating a trc file named “…10046.trc” such as, “C:\APP\ADMINISTRATOR\VIRTUAL\diag\rdbms\ora19c\ora19c\trace\ora19c_ora_20224_10046.trc”.

Continue reading

Wget

A few of days ago Jonathan Lewis sent the following message (quoting his original stuff) on Twitter.

EARLY WARNING: at the end of May my old (unchanged since September 2012) website jlcomp.demon.co.uk will be taken off the internet. I may update and post a very small number of the articles on my blog eventually, but if there’s anything you want to keep from it, copy it now.

04:40PM 2020-04-04 Twitter Web App

and,

WARNING: by end May the DEMON domain (with my old website jlcomp.demon.co.uk) will disappear. If there’s anything you want from the website copy it now.

wget -e robots=off -r -np http://www.jlcomp.demon.co.uk -o log.txt

05:53 PM 2020-04-06 Twitter Web App

I heard of this bad news and asked him what reason it is afterwards he replied to me – the license of sub-domain DEMON was about to expire. So it’s time to copy those stuff from his old website if you keep somthing.

Continue reading

Optimizing SQL with LIKE expression – 3

So far I’ve written two number of blog notes about how to optimize sql with LIKE expression (here and here). A couple of days ago I built another funny case including two different columns in the WHERE clause of SQL statement, the former is a like expression and the latter is a constant expression. Hence this post will focus on talking about how to optimize this kind of SQL query.

Here is a demo (same as my previous two posts) creating a test table and quickly inserting some random data with 3e5 lines.

CREATE TABLE person
( id   NUMBER GENERATED ALWAYS AS IDENTITY
, name VARCHAR2(6) NOT NULL
, sex  VARCHAR2(1) NOT NULL
, flag VARCHAR2(1)
, pwd  VARCHAR2(6)
, CONSTRAINT person_pk PRIMARY KEY(id)
);

EXEC DBMS_RANDOM.seed(0);

INSERT /*+APPEND*/ INTO person (name, sex, flag, pwd)
SELECT DBMS_RANDOM.string('A', 6) AS name
     , CASE MOD(ROWNUM, 2) WHEN 0 THEN 'F'
                           WHEN 1 THEN 'M'
       END sex
     , CASE MOD(ROWNUM, 3) WHEN 0 THEN 'T'
                           WHEN 1 THEN NULL
                           WHEN 2 THEN 'F'
       END flag
     , DBMS_RANDOM.string ('p', 6) AS pwd
FROM dual
CONNECT BY level <= 3e5;

COMMIT;

EXEC DBMS_STATS.gather_table_stats(ownname => NULL, tabname => 'PERSON');

As known as wondering a running SQL query is whether more efficient or not, it’s best to check its row-source execution plan that acquired from memory. The simple SQL query is as below.

SELECT *
  FROM person
 WHERE name LIKE 'qw%'
   AND sex = 'F'
;

Now I will use the following three type of cases to optimize the preceding SQL query.

  • creating two seprarate normal indexes for two columns
  • creating a normal index and a bitmap index for two columns
  • creating a composite index for two columns
Continue reading

Optimizing SQL with LIKE expression – 2

In my previous post I introduced 4 number of cases about how to optimize SQL with LIKE expression on oracle database. Even if I thought that I didn’t completely extend some more important details to describe/discuss those optimization skills particularly in previous case 2 and 4 as follows.

  • %qw
  • q%w
Continue reading

Optimizing SQL with LIKE expression

This post is focused on talking about how to optimize several SQL statements with LIKE expression in oracle database. Now let me quote some important knowledge point about the pattern of LIKE condition on 20c.

The LIKE conditions specify a test involving pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the LIKE conditions match a portion of one character value to another by searching the first value for the pattern specified by the second.

The pattern can contain special pattern-matching characters:

An underscore (_) in the pattern matches exactly one character (as opposed to one byte in a multibyte character set) in the value.

A percent sign (%) in the pattern can match zero or more characters (as opposed to bytes in a multibyte character set) in the value. The pattern ‘%’ cannot match a null.

In general a LIKE expression has four number of situation in a SQL query statement. Take, for example, some random combinations of between ‘%’ and character strings ‘qw’.

  • qw%
  • %qw
  • %qw%
  • q%w
Continue reading

Middle-N, Pagination Query and Optimization

So far I’ve written 3 number of notes in the series of “top-n, pagination query and optimization”, they’re as follows.

A few days ago in particular Nigel Bayliss gave me some good ideas that was never in my mind (by communicating with email back and forth) so it is a reason why today I am going to write this blog note. Here’s my test demo (running on 19.3) creating table “staff”.

Continue reading

Top-N, Pagination Query and Optimization – 3

About how to optimize the query of Top-N and Pagination on oracle database I’ve written total two number of notes (here and here). Today this note is the last one in this series. From time to time (occasionally) there appears some strange bug on descending index that I once mentioned on the previous note. Thus I only can talk about several SQL queries with ascending index (default) and here’s my demo via creating test table and quickly inserting some random data into all columns of that table.

Continue reading

Top-N, Pagination Query and Optimization – 2

After sending Tweets message based on my previous blog post on Twitter, unexpectedly, Jonathan Lewis, Andrew Sayer, Patrick Jolliffe (oracle gurus all over the world) and Tiger Liu (China) all gave me so many pretty nice comments and very valuable opinions, so I have added some stuff (at that moment my confusion about whether it’s bug or not) on that blog note with [Update Mar 04, 2020: ……] and at the same time also marked those old sentences (wrong analysis process) using a conspicuous strikethrough.

According to their good suggestions I’ve done two funny test as well:

  • index full scan VS index range scan
  • descending index oddity (bug) VS ascending index (default)
Continue reading