Home » SQL & PL/SQL » SQL & PL/SQL » trigger executing a procedure to execute dbms_stats.gather_table_stats (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
trigger executing a procedure to execute dbms_stats.gather_table_stats [message #686504] Wed, 28 September 2022 15:28 Go to next message
wtolentino
Messages: 363
Registered: March 2005
Senior Member
i had this code that when i inserted a row on a table it will fire a trigger to execute a procedure that will eventually execute the oracle built-in package dbms_stats.gather_table_stats. though there is no commit but i know that there is implicit commit is being done thru that dbms_stats.gather_table_stats package. how do i go around about this? any advise or help will be appreciated. thank you.

SQL> create table test_load_tab
  2   (table_owner varchar2(40), table_name varchar2(40), load_status varchar2(10), comments varchar2(80));

Table created.

SQL> create or replace procedure test_load_pro (pTableOwner varchar2, pTableName varchar2) as
  2  begin
  3    dbms_stats.gather_table_stats (ownname     => pTableOwner,
  4                                   tabname     => pTableName,
  5                                   cascade     => TRUE,
  6                                   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  7                                   method_opt  => 'FOR ALL COLUMNS SIZE AUTO',
  8                                   granularity => 'ALL',
  9                                   degree      => 2);
 10
 11    update test_load_tab
 12       set load_status = 'Completed'
 13     where table_owner = pTableOwner
 14       and table_name  = pTableName
 15       and load_status = 'In-Process';
 16
 17  exception
 18    when others then
 19      dbms_output.put_line('gather stats on table partition '||pTableName||' failed ');
 20      dbms_output.put_line('error '||substr(sqlerrm, 1, 200));
 21  end;
 22  /

Procedure created.


SQL> create or replace trigger test_load_trg
  2  before insert on test_load_tab for each row
  3  begin
  4    if inserting then
  5      if (:new.table_owner is not null) and (:new.table_name is not null) and (:new.load_status = 'In-Process') then
  6        test_load_pro (:new.table_owner, :new.table_name);
  7      end if;
  8    end if;
  9  end;
 10  /

Trigger created.

SQL> create table test_stat_tab
  2   (stat_id number, attribute varchar2(40), create_date date);

Table created.

SQL>
SQL> insert into test_stat_tab values (1, 'A', sysdate);

1 row created.

SQL> insert into test_stat_tab values (2, 'B', sysdate);

1 row created.

SQL> insert into test_stat_tab values (3, 'C', sysdate);

1 row created.

SQL> insert into test_stat_tab values (4, 'D', sysdate);

1 row created.

SQL> insert into test_stat_tab values (5, 'E', sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> set serveroutput on;
SQL> insert into test_load_tab values ('WTOLENTINO','TEST_STAT_TAB','In-Process','Test 1');
gather stats on table partition TEST_STAT_TAB failed
error ORA-04092: cannot COMMIT in a trigger

1 row created.

SQL>
Re: trigger executing a procedure to execute dbms_stats.gather_table_stats [message #686505 is a reply to message #686504] Thu, 29 September 2022 05:56 Go to previous messageGo to next message
Littlefoot
Messages: 21750
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A workaround can be declaring the procedure as autonomous transaction, which also means that it must commit (or rollback); it is then "isolated" from the main transaction. Something like this (see lines #4 and #21):

SQL> CREATE OR REPLACE PROCEDURE test_load_pro (pTableOwner  VARCHAR2,
  2                                             pTableName   VARCHAR2)
  3  AS
  4     PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6     DBMS_STATS.gather_table_stats (
  7        ownname           => pTableOwner,
  8        tabname           => pTableName,
  9        cascade           => TRUE,
 10        estimate_percent  => DBMS_STATS.AUTO_SAMPLE_SIZE,
 11        method_opt        => 'FOR ALL COLUMNS SIZE AUTO',
 12        granularity       => 'ALL',
 13        degree            => 2);
 14
 15     UPDATE test_load_tab
 16        SET load_status = 'Completed'
 17      WHERE     table_owner = pTableOwner
 18            AND table_name = pTableName
 19            AND load_status = 'In-Process';
 20
 21     COMMIT;
 22  EXCEPTION
 23     WHEN OTHERS
 24     THEN
 25        DBMS_OUTPUT.put_line (
 26           'gather stats on table partition ' || pTableName || ' failed ');
 27        DBMS_OUTPUT.put_line ('error ' || SUBSTR (SQLERRM, 1, 200));
 28  END;
 29  /

Procedure created.
Insert that previously failed now works:

SQL> show user
USER is "SCOTT"
SQL> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') right_now from dual;

RIGHT_NOW
-------------------
29.09.2022 12:55:17

SQL> insert into test_load_tab values ('SCOTT','TEST_STAT_TAB','In-Process','Test 1');

1 row created.

SQL> select * from test_load_tab;

TABLE_OWNER  TABLE_NAME      LOAD_STATUS  COMMENTS
------------ --------------- ------------ --------------------
SCOTT        TEST_STAT_TAB   In-Process   Test 1

SQL> select to_char(last_analyzed, 'dd.mm.yyyy hh24:mi:ss') last_analyzed
  2  from user_tables
  3  where table_name = 'TEST_STAT_TAB';

LAST_ANALYZED
-------------------
29.09.2022 12:55:32

SQL>
Re: trigger executing a procedure to execute dbms_stats.gather_table_stats [message #686506 is a reply to message #686505] Thu, 29 September 2022 07:20 Go to previous messageGo to next message
wtolentino
Messages: 363
Registered: March 2005
Senior Member
wonderful that works thanks so much and i appreciate that.
Re: trigger executing a procedure to execute dbms_stats.gather_table_stats [message #686507 is a reply to message #686506] Thu, 29 September 2022 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68315
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Both "solutions" are not correct, mainly in the consistency of the work.

First wtolentino's one (after creating the objects):
SQL> insert into test_load_tab values ('WTOLENTINO','TEST_STAT_TAB','In-Process','Test 1');
gather stats on table partition TEST_STAT_TAB failed
error ORA-04092: cannot COMMIT in a trigger

1 row created.
Ok there's the expected error but:
SQL> select * from test_load_tab;
TABLE_OWNER  TABLE_NAME      LOAD_STATU COMMENTS
------------ --------------- ---------- -------------
WTOLENTINO   TEST_STAT_TAB   In-Process Test 1

1 row selected.
In-process really? Should be "aborted" Twisted Evil


Now Littlefoot's one:
SQL> rollback;

Rollback complete.

SQL> select * from test_load_tab;

no rows selected

SQL> CREATE OR REPLACE PROCEDURE test_load_pro (pTableOwner  VARCHAR2,
  2                                             pTableName   VARCHAR2)
  3  AS
  4     PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6     DBMS_STATS.gather_table_stats (
  7       ownname           => pTableOwner,
  8       tabname           => pTableName,
  9       cascade           => TRUE,
 10       estimate_percent  => DBMS_STATS.AUTO_SAMPLE_SIZE,
 11       method_opt        => 'FOR ALL COLUMNS SIZE AUTO',
 12       granularity       => 'ALL',
 13       degree            => 2);
 14
 15     UPDATE test_load_tab
 16       SET load_status = 'Completed'
 17      WHERE     table_owner = pTableOwner
 18            AND table_name = pTableName
 19            AND load_status = 'In-Process';
 20
 21    COMMIT;
 22  EXCEPTION
 23    WHEN OTHERS
 24     THEN
 25       DBMS_OUTPUT.put_line (
 26         'gather stats on table partition ' || pTableName || ' failed ');
 27       DBMS_OUTPUT.put_line ('error ' || SUBSTR (SQLERRM, 1, 200));
 28  END;
 29  /

Procedure created.

19:15:39 SQL> insert into test_load_tab values ('MICHEL','TEST_STAT_TAB','In-Process','Test 1');

1 row created.

19:15:47 SQL> commit;

Commit complete.

19:15:50 SQL> select to_char(last_analyzed, 'dd.mm.yyyy hh24:mi:ss') last_analyzed
19:15:54   2  from user_tables
19:15:54   3  where table_name = 'TEST_STAT_TAB';
LAST_ANALYZED
-------------------
29.09.2022 19:15:47

1 row selected.

19:15:54 SQL> select * from test_load_tab;
TABLE_OWNER  TABLE_NAME      LOAD_STATU COMMENTS
------------ --------------- ---------- ----------------------------------------------------------
MICHEL       TEST_STAT_TAB   In-Process Test 1

1 row selected.
Still "In-Process" when it is "Completed", the reason is that an AUTONOMOUS_TRANSACTION is another transaction and so can't see the new inserted line.

See slightly modifying the procedure:
SQL> select * from test_load_tab;

no rows selected

SQL> CREATE OR REPLACE PROCEDURE test_load_pro (pTableOwner  VARCHAR2,
  2                                             pTableName   VARCHAR2)
  3  AS
  4     PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6     DBMS_STATS.gather_table_stats (
  7       ownname           => pTableOwner,
  8       tabname           => pTableName,
  9       cascade           => TRUE,
 10       estimate_percent  => DBMS_STATS.AUTO_SAMPLE_SIZE,
 11       method_opt        => 'FOR ALL COLUMNS SIZE AUTO',
 12       granularity       => 'ALL',
 13       degree            => 2);
 14
 15     UPDATE test_load_tab
 16       SET load_status = 'Completed'
 17      WHERE     table_owner = pTableOwner
 18            AND table_name = pTableName
 19            AND load_status = 'In-Process';
 20
 21  dbms_output.put_line('>>> '||sql%rowcount||' rows updated');
 22
 23    COMMIT;
 24  EXCEPTION
 25    WHEN OTHERS
 26     THEN
 27       DBMS_OUTPUT.put_line (
 28         'gather stats on table partition ' || pTableName || ' failed ');
 29       DBMS_OUTPUT.put_line ('error ' || SUBSTR (SQLERRM, 1, 200));
 30  END;
 31  /

Procedure created.

SQL> insert into test_load_tab values ('MICHEL','TEST_STAT_TAB','In-Process','Test 1');
>>> 0 rows updated

1 row created.

SQL> select * from test_load_tab;
TABLE_OWNER  TABLE_NAME      LOAD_STATU COMMENTS
------------ --------------- ---------- ----------------------------------------------------
MICHEL       TEST_STAT_TAB   In-Process Test 1

1 row selected.
And still the same issue if an error occurs:
SQL> rollback;

Rollback complete.

SQL> select * from test_load_tab;

no rows selected

SQL> insert into test_load_tab values ('SCOTT','TEST_STAT_TAB','In-Process','Test 1');
gather stats on table partition TEST_STAT_TAB failed
error ORA-20000: Unable to analyze TABLE "SCOTT"."TEST_STAT_TAB", insufficient privileges or does not exist

1 row created.

SQL> select * from test_load_tab;
TABLE_OWNER  TABLE_NAME      LOAD_STATU COMMENTS
------------ --------------- ---------- ----------------------------------------------------------------------
SCOTT        TEST_STAT_TAB   In-Process Test 1

1 row selected.

Once more, read WHEN_OTHERS.
It should not be used but in very specific cases and in these cases it should always end with "RAISE;".
Re: trigger executing a procedure to execute dbms_stats.gather_table_stats [message #686508 is a reply to message #686507] Thu, 29 September 2022 12:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68315
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So one correct way to do is:
SQL> CREATE OR REPLACE PROCEDURE test_load_pro (pTableOwner  VARCHAR2,
  2                                             pTableName   VARCHAR2)
  3  AS
  4     PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6     DBMS_STATS.gather_table_stats (
  7       ownname           => pTableOwner,
  8       tabname           => pTableName,
  9       cascade           => TRUE,
 10       estimate_percent  => DBMS_STATS.AUTO_SAMPLE_SIZE,
 11       method_opt        => 'FOR ALL COLUMNS SIZE AUTO',
 12       granularity       => 'ALL',
 13       degree            => 2);
 14    COMMIT;
 15  END;
 16  /

Procedure created.

SQL> create or replace trigger test_load_trg
  2  before insert on test_load_tab for each row
  3  begin
  4    if inserting then
  5      if (:new.table_owner is not null) and (:new.table_name is not null) and (:new.load_status = 'In-Process') then
  6        test_load_pro (:new.table_owner, :new.table_name);
  7        :new.load_status := 'Completed';
  8      end if;
  9    end if;
 10  end;
 11  /

Trigger created.

SQL> select * from test_load_tab;

no rows selected

SQL> set time on
19:32:09 SQL> insert into test_load_tab values ('MICHEL','TEST_STAT_TAB','In-Process','Test 1');

1 row created.

19:32:11 SQL> select * from test_load_tab;
TABLE_OWNER  TABLE_NAME      LOAD_STATU COMMENTS
------------ --------------- ---------- ----------------------------------------------------------
MICHEL       TEST_STAT_TAB   Completed  Test 1

1 row selected.

19:32:18 SQL> select to_char(last_analyzed, 'dd.mm.yyyy hh24:mi:ss') last_analyzed
19:32:25   2  from user_tables
19:32:25   3  where table_name = 'TEST_STAT_TAB';
LAST_ANALYZED
-------------------
29.09.2022 19:32:11

1 row selected.
And you are sure that the row that is updated to "Completed" is the current one and not a previous one that was in the table as with the UPDATE statement.

And in case of error:
SQL> rollback;

Rollback complete.

SQL>  select * from test_load_tab;

no rows selected

SQL> insert into test_load_tab values ('SCOTT','TEST_STAT_TAB','In-Process','Test 1');
insert into test_load_tab values ('SCOTT','TEST_STAT_TAB','In-Process','Test 1')
            *
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SCOTT"."TEST_STAT_TAB", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 24682
ORA-06512: at "SYS.DBMS_STATS", line 24751
ORA-06512: at "MICHEL.TEST_LOAD_PRO", line 6
ORA-06512: at "MICHEL.TEST_LOAD_TRG", line 4
ORA-04088: error during execution of trigger 'MICHEL.TEST_LOAD_TRG'


SQL>  select * from test_load_tab;

no rows selected
The row is not inserted and so no false information is given on the work (no "In-Process" when nothing is in process).
No need of EXCEPTION block, you have the same information in standard Oracle error (and much more).

[Updated on: Fri, 30 September 2022 01:54]

Report message to a moderator

Re: trigger executing a procedure to execute dbms_stats.gather_table_stats [message #686525 is a reply to message #686508] Mon, 03 October 2022 08:08 Go to previous message
wtolentino
Messages: 363
Registered: March 2005
Senior Member
thanks so much.
Previous Topic: Columns replicate
Next Topic: Need a trigger or similar thing
Goto Forum:
  


Current Time: Wed Dec 07 11:21:46 CST 2022