Browse Prior Art Database

Method of detecting the most expensive statements within a stored procedure execution in database system/data warehousing system

IP.com Disclosure Number: IPCOM000234671D
Publication Date: 2014-Jan-28
Document File: 5 page(s) / 47K

Publishing Venue

The IP.com Prior Art Database

Abstract

When executing a stored procedure, one can see an overall execution time. There is, however, crucial to understand which nested statements introduce most of the overhead, i.e. which are the most expensive. The idea is to combine information from database/data warehouse system catalog, monitoring views and log files to detect stored procedure executions and list nested statements executions, sorted by its expensiveness.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 15% of the total text.

Page 01 of 5

Method of detecting the most expensive statements within a stored procedure execution in database system/data warehousing system

Before calling a stored procedure in a database system or a data warehousing system, the prerequisite is, obviously, to have one. Create [or replace] command creates a SP, i.e. it also inserts information about a stored procedure into a system catalog. This information, including the procedure body, can be extracted with show procedure

verbose command. In order to run a stored procedure, one need to use Call, Exec, Execute, Execute Procedure or just Select command followed by the procedure name and arguments.

Once a procedure is called, a corresponding entry is written into postgres log file: /nz/kit/log/postgres/pg.log, preceded by PID identifier. The log file contains also nested statements and they are prefixed with "STORED PROCEDURE EXEC:". That PID can be found also in dbos log file: /nz/kit/log/dbos/dbos.log, adding also information about session id (sid), user id (uid), and what's most important, identifiers of nested statement executions (identified by planid). This set of identifiers is what should be a filter for 2 Netezza monitoring views: _v_qryhist and _v_plan_resources. The first one provides a collection of recently executed plans, including information like statement text, database, user, start and end timestamps (i.e. execution time as well), number of snippets, resulting number of rows and bytes, estimates of cost, disk and memory. The second view is filled with data once a plan is finished and a set of attributes contains metrics about CPU, Fabric, Disk Read and Write on Host and SPU (avg and max), metrics related to SQB, GRA and more. All of that can be used to define query expensiveness, starting with as simple definition as elapse_time (_v_qryhist.TEND - _v_qryhist.TSUBMIT).

Example:

Create stored procedure :
create or replace procedure SP_A () returns bigint language nzplsql as begin_proc
declare
v_var varchar(100);

begin

for i in 0 .. 5 LOOP

select a.i || b.i into v_var from T1 a cross join T1 b order by a.i; end loop;

delete from T1 where i > (select avg(col1) from (select avg(i)*sum(i2.idx)/max(i3.idx) from T1 cross

join _V_VECTOR_IDX i2 cross join _V_VECTOR_IDX i3 cross join _v_vector_idx i4 group by i2.idx) as tmp(col1));

return row_count;

end; end_proc;

Call stored procedure: call sp_a();

 Monitor: 1) Get PID of sp_a() stored procedure invocation :
grep "
sp_a()" -i /nz/kit/log/postgres/pg.log
2013-10-01 16:15:23.653060 CES [1348] DEBUG: QUERY: call sp_a();

 2) Get a list of nested statements :
grep "
\[1348\]" /nz/kit/log/postgres/pg.log | grep "STORED PROCEDURE EXEC:"
2013-10-01 16:15:23.657297 CES [1348] DEBUG: STORED PROCEDURE EXEC: SELECT a.i ||


b.i from T1 a cross join T1 b order by a.i
2013-10-01 16:15:23.668334 CES [1348] DEBUG: STORED PROCEDURE EXEC: SELECT a.i ||

1


Page 02 of 5


b.i from T1 a cross join T1 b order by a.i
2013-10-01 16:15:23.676687 CES [1348] DEBUG: STORED PROCE...