Oracle: PL/SQL package SQL_STMT for management and execution of SQL statements
Package specifications
create or replace package sql_stmt_mgmt
as
subtype sql_id_t is varchar2(13);
--
-- Information about the execution
-- of an SQL statement
--
type exec_res_t is record (
exec_id integer,
row_cnt integer,
err_nr integer := 0,
err_msg varchar2(500) := ''
);
function add(
stmt clob
)
return sql_id_t;
function stmt_text(sql_id varchar2) return clob;
--
-- ins_exec and upd_exec need to run as owner of the the table sql_stmt and sql_stmt_exec
-- hence, they're defined in sql_stmt_exec.
-- These functions are used from sql_stmt_exec_mgmt (with authid current_user)
--
function ins_exec(sql_id sql_id_t) return integer;
procedure upd_exec(
exec_id integer,
ts timestamp := null,
row_cnt integer := null,
err varchar2 := null);
--
-- The final underscore indicates that fill_stmt_plan_ is not
-- supposed to be executed by 'the public'.
--
function fill_stmt_plan_(
sql_id sql_id_t,
plan_exec exec_res_t,
usr varchar2
) return integer;
end sql_stmt_mgmt;
/
create or replace package sql_stmt_exec_mgmt
authid current_user
as
function exec_immediate( stmt clob)
return sql_stmt_mgmt.exec_res_t;
function exec(sql_id sql_stmt_mgmt.sql_id_t)
return sql_stmt_mgmt.exec_res_t;
function explain_plan (sql_id sql_stmt_mgmt.sql_id_t)
return integer;
end sql_stmt_exec_mgmt;
/
create or replace package sql_stmt_txt as
--
-- Remove text up to first first occurence of 'select' or 'with' so
-- as to get a select statement from an 'insert into ... ( ...) select ... from'
-- statement.
--
function extract_select_part(stmt clob) return clob;
end sql_stmt_txt;
/
create or replace package body sql_stmt_mgmt as
function add( -- {
stmt clob
) return sql_id_t
is pragma autonomous_transaction;
sql_id varchar2(13);
begin
if stmt is null then
log_mgmt.msg('stmt is null, returning null');
return null;
end if;
sql_id := dbms_sql_translator.sql_id(stmt);
begin
insert into sql_stmt (text, sql_id, force_matching_signature)
values (stmt, sql_id, dbms_sqltune.sqltext_to_signature(stmt, force_match => 1));
commit;
exception when dup_val_on_index then
rollback;
end;
return sql_id;
exception when others then
log_mgmt.exc('stmt = ' || stmt);
end add; -- }
function stmt_text(sql_id varchar2) return clob is -- {
text clob;
begin
select
s.text into stmt_text.text
from
sql_stmt s
where
s.sql_id = stmt_text.sql_id;
return text;
exception when no_data_found then
log_mgmt.msg('No data found for sql_id = ' || sql_id);
end stmt_text; -- }
function ins_exec(sql_id sql_id_t) return integer is pragma autonomous_transaction; -- {
exec_id integer;
begin
insert into sql_stmt_exec (sql_id, start_, task_exec_id) values (sql_id, systimestamp, task_mgmt.cur_task)
returning id into exec_id;
commit;
return exec_id;
end ins_exec; -- }
procedure upd_exec( -- {
exec_id integer,
ts timestamp := null,
row_cnt integer := null,
err varchar2 := null) is pragma autonomous_transaction;
begin
update sql_stmt_exec
set
end_ = upd_exec.ts ,
row_cnt = upd_exec.row_cnt,
error = upd_exec.err
where
id = exec_id;
commit;
end upd_exec; -- }
function fill_stmt_plan_( -- {
sql_id sql_id_t,
plan_exec exec_res_t,
usr varchar2
)
return integer
is
plan_id_ integer;
sqlerrm_ varchar2(512);
begin
declare -- {
op sql_stmt_plan.stmt_type%type;
begin
if plan_exec.err_nr = 0 then -- {
select operation into op from plan_table where id = 0;
else
op := 'error';
end if; -- }
insert into sql_stmt_plan ( -- {
stmt_type,
usr,
exec_id,
sql_id
)
values (
op,
usr,
plan_exec.exec_id,
sql_id
)
returning
id into plan_id_; -- }
if plan_exec.err_nr != 0 then -- {
log_mgmt.msg('returning -plan_id_');
return -plan_id_;
end if; -- }
insert into sql_stmt_plan_step ( -- {
step_id ,
parent_step_id,
operation ,
options ,
object_owner ,
object_name ,
object_alias ,
qblock_name ,
depth ,
plan_id
)
select
id ,
parent_id ,
operation ,
options ,
object_owner,
object_name ,
object_alias,
qblock_name ,
depth -1 , -- subtract 1 because depth seems to be 0 only for root-step which is not stored in sql_stmt_plan_step, but in sql_stmt_plan (where depth does not play any role)
plan_id_
from
plan_table
where
id > 0; -- }
end; -- }
log_mgmt.msg('returning +plan_id_');
return plan_id_;
exception when others then
log_mgmt.exc;
end fill_stmt_plan_; -- }
end sql_stmt_mgmt;
/
create or replace package body sql_stmt_txt as
function extract_select_part(stmt clob) return clob is -- {
--
-- Remove text up to first first occurence of 'select' or 'with' so
-- as to get a select statement from an 'insert into ... ( ...) select ... from'
-- statement.
--
begin
return regexp_replace(stmt, '^.*?((\W|\A)(select|with)\W)', '\1', 1, 0, 'in');
end extract_select_part; -- }
end sql_stmt_txt;
/
create or replace view sql_stmt_plan_v as
select
plan.stmt_type,
plan.usr usr_plan,
stmt.text stmt_text,
exec.error exec_error,
plan_insert.object_owner inserted_table_owner,
plan_insert.object_name inserted_table_name ,
exec.start_ exec_start,
exec.end_ exec_end,
stmt.sql_id sql_id,
exec.force_matching_signature,
plan.id,
row_number() over (partition by stmt.sql_id, plan.usr order by exec.end_ desc nulls last) r
from
sql_stmt stmt left join
sql_stmt_plan plan on stmt.sql_id = plan.sql_id left join
sql_stmt_exec_v exec on plan.exec_id = exec.exec_id left join
sql_stmt_plan_step plan_insert on plan.id = plan_insert.plan_id and
(
plan_insert.operation in ('LOAD TABLE CONVENTIONAL', 'LOAD AS SELECT')
and not (plan_insert.operation = 'LOAD AS SELECT' and
plan_insert.options = '(CURSOR DURATION MEMORY)'
)
)
;
create or replace view sql_stmt_plan_step_v as
select
--
-- Left join sql_stmt with sql_stmt_plan
-- to have the execution plan for every statement.
-- Additionally, indent the operation for easier
-- visual inspection.
--
plan.stmt_type,
plan.exec_error,
lpad(' ', step.depth*2) || step.operation || case when step.options is not null then ' (' || step.options || ')' end operation_indented,
case when object_name is not null then object_owner || '.' || object_name else object_alias end object,
step.operation,
step.options,
step.object_owner,
step.object_name,
-- plan.object_alias,
step.qblock_name,
step.step_id plan_step_id, -- Use this to 'order by' result
step.depth plan_depth, -- Use this to indent operation
plan.usr_plan ,
plan.id plan_id,
plan.sql_id,
plan.r plan_r
from
sql_stmt_plan_v plan left join
sql_stmt_plan_step step on plan.id = step.plan_id
;
create or replace view sql_stmt_selects_from_v_ as
select
--
-- The final underscore in the view name indicates that
-- another view will select from this view:
--
object_owner,
object_name,
sql_id,
usr_plan,
plan_id,
plan_r
from
sql_stmt_plan_step_v
where
operation in ('INDEX', 'TABLE ACCESS')
group by
object_owner,
object_name,
sql_id,
usr_plan,
plan_id,
plan_r
;
create or replace view sql_stmt_selects_from_v as
select
--
-- Because data can be selected via an index, but we want the
-- table that the index operates on, we need to collapse views
-- and tables into one object:
--
coalesce(i.table_owner, v.object_owner) table_owner,
coalesce(i.table_name , v.object_name ) table_name,
v.sql_id,
v.usr_plan,
v.plan_id,
v.plan_r
from
sql_stmt_selects_from_v_ v left join
all_indexes i on v.object_owner = i.owner and
v.object_name = i.index_name
group by
coalesce(i.table_owner, v.object_owner),
coalesce(i.table_name , v.object_name ),
v.sql_id,
v.usr_plan,
v.plan_id,
v.plan_r
;
create or replace view sql_stmt_from_to_v as
select
--
-- Combine
-- sql_stmt_v and
-- sql_stmt_selects_from_v
-- to see which table an sql statement inserts to
-- and where it gets the data from.
--
-- stmt.name,
plan.inserted_table_owner inserted_table_owner,
plan.inserted_table_name inserted_table_name,
from_.table_owner selected_object_owner,
from_.table_name selected_object_name,
plan.usr_plan,
plan.r plan_r,
plan.id plan_id,
plan.sql_id
from
sql_stmt_plan_v plan join
sql_stmt_selects_from_v from_ on plan.sql_id = from_.sql_id and
plan.r = from_.plan_r
;
declare
id_create_table sql_stmt_mgmt.sql_id_t;
id_fill sql_stmt_mgmt.sql_id_t;
id_drop_table sql_stmt_mgmt.sql_id_t;
res sql_stmt_mgmt.exec_res_t;
begin
task_mgmt.begin_('test sql_stmt');
id_drop_table := sql_stmt_mgmt.add('drop table tq84_test_sql_stmt');
id_create_table := sql_stmt_mgmt.add('create table tq84_test_sql_stmt(ts timestamp, object_name varchar2(128))');
id_fill := sql_stmt_mgmt.add('insert into tq84_test_sql_stmt select systimestamp, object_name from all_objects');
dbms_output.put_line('id_create_table = ' || id_create_table);
dbms_output.put_line('id_fill = ' || id_fill );
res := sql_stmt_exec_mgmt.exec(id_drop_table );
res := sql_stmt_exec_mgmt.exec(id_create_table);
res := sql_stmt_exec_mgmt.exec(id_fill );
res := sql_stmt_exec_mgmt.exec(id_fill );
res := sql_stmt_exec_mgmt.exec(id_drop_table );
task_mgmt.done;
end;
/
select * from log_v where cur_ses_r = 1 order by id desc;
select stmt_text, duration_s, row_cnt, error, sql_stmt_exec_v.* from sql_stmt_exec_v where task_exec_id = (select max(task_exec_id) from log_v where cur_ses_r = 1) order by exec_id;
select * from task_exec_v where cur_ses_r = 1;
create table tq84_explain_plan_test_1 (
a number,
b number
);
create table tq84_explain_plan_test_2 (
c number,
d number
);
create view tq84_explain_plan_test_v as
select
one.a,
one.b,
two.*
from
tq84_explain_plan_test_1 one join
tq84_explain_plan_test_2 two on one.a = two.c;
declare
procedure do_stmt(x varchar2, stmt clob) is
stmt_id sql_stmt_mgmt.sql_id_t;
begin
dbms_output.put_line(x);
log_mgmt.msg(x);
stmt_id := sql_stmt_mgmt.add(stmt);
dbms_output.put_line(' stmt id = ' || stmt_id);
dbms_output.put_line(' plan id = ' || sql_stmt_exec_mgmt.explain_plan(stmt_id));
dbms_output.put_line(' exec id = ' || sql_stmt_exec_mgmt.exec (stmt_id).exec_id);
end do_stmt;
begin
task_mgmt.begin_('tests for sql_stmt: explain plan functionality');
do_stmt('create table', q'[create table tq84_test_sql_stmt_table as select * from tq84_explain_plan_test_v where a in (1,2,3)]');
do_stmt('insert into ', q'[insert into tq84_test_sql_stmt_table select * from tq84_explain_plan_test_v where b in (4,5,6)]');
do_stmt('delete from ', q'[delete from tq84_test_sql_stmt_table where c in (7,8,9)]');
do_stmt('update ', q'[update tq84_test_sql_stmt_table set d = null where d = 10 ]');
--
-- TODO: Statment drop table ... cannot be explained
do_stmt('drop table ', q'[drop table tq84_test_sql_stmt_table ]');
task_mgmt.done;
exception when others then
task_mgmt.exc;
end;
/
select * from log_v where cur_ses_r = 1 order by id desc;
select * from sql_stmt_plan;
-- CREATE TABLE STATEMENT
select * from sql_stmt_plan where sql_id = '4w20j43upbfmm';
select * from sql_stmt_plan_v where sql_id = '4w20j43upbfmm';
select * from sql_stmt_plan_step where plan_id = (select id from sql_stmt_plan where sql_id = '4w20j43upbfmm');
select * from sql_stmt_plan_step_v where sql_id = '4w20j43upbfmm';
-- INSERT STATEMENT
select * from sql_stmt_plan where sql_id = '4tnbqnts21v6c';
select * from sql_stmt_plan_v where sql_id = '4tnbqnts21v6c';
select * from sql_stmt_plan_step where plan_id = (select id from sql_stmt_plan where sql_id = '4tnbqnts21v6c');
select * from sql_stmt_plan_step_v where sql_id = '4tnbqnts21v6c';
-- DELETE STATEMENT
select * from sql_stmt_plan where sql_id = 'fjyn2s0qkac6m';
select * from sql_stmt_plan_v where sql_id = 'fjyn2s0qkac6m';
select * from sql_stmt_plan_step where plan_id = (select id from sql_stmt_plan where sql_id = 'fjyn2s0qkac6m');
select * from sql_stmt_plan_step_v where sql_id = 'fjyn2s0qkac6m';
-- UPDATE STATEMENT
select * from sql_stmt_plan where sql_id = '3gvz3s6vmgadh';
select * from sql_stmt_plan_v where sql_id = '3gvz3s6vmgadh';
select * from sql_stmt_plan_step where plan_id = (select id from sql_stmt_plan where sql_id = '3gvz3s6vmgadh');
select * from sql_stmt_plan_step_v where sql_id = '3gvz3s6vmgadh';
-- error STATEMENT
select * from sql_stmt_plan where sql_id = 'c1gp8bnyuxa20';
select * from sql_stmt_plan_v where sql_id = 'c1gp8bnyuxa20';
select * from sql_stmt_plan_step where plan_id = (select id from sql_stmt_plan where sql_id = 'c1gp8bnyuxa20');
select * from sql_stmt_plan_step_v where sql_id = 'c1gp8bnyuxa20';
create table tq84_test_sql_stmt_tab_a (id number, val number);
create table tq84_test_sql_stmt_tab_b (id number, val number);
create table tq84_test_sql_stmt_tab_c (id number, val number);
create table tq84_test_sql_stmt_tab_d (id number, val number);
declare
stmt_id sql_stmt_mgmt.sql_id_t;
begin
task_mgmt.begin_('test sql_stmt: add insert statement');
stmt_id := sql_stmt_mgmt.add(q'[
insert into tq84_test_sql_stmt_tab_d
select
sum(x + c.val),
q_1.id
from (
select
a.id,
a.val + b.val as x
from
tq84_test_sql_stmt_tab_a a join
tq84_test_sql_stmt_tab_b b on a.id = b.id
) q_1 join
tq84_test_sql_stmt_tab_c c on q_1.id = c.id
group
by q_1.id]');
dbms_output.put_line('stmt_id = ' || stmt_id);
dbms_output.put_line(' plan id = ' || sql_stmt_exec_mgmt.explain_plan(stmt_id));
task_mgmt.done;
exception when others then
task_mgmt.exc;
end;
/
-- select * from log_v where cur_ses_r = 1 order by id desc;
select * from sql_stmt_plan_v where sql_id = 'cc4v1gvzgm7pr' /* id = 1 */ and r = 1;
select * from sql_stmt_selects_from_v where sql_id = 'cc4v1gvzgm7pr' and plan_r = 1;
select * from sql_stmt_from_to_v where sql_id = 'cc4v1gvzgm7pr' and plan_r = 1;