------------------------------------------------------------------------ -- - -- The following SQL definitions can be very helpful when using the - -- EXPLAIN command. - -- - -- These definitions add meaning to the result of the EXPLAIN command - -- by creating easy-to-understand values for the abbreviated codes that- -- EXPLAIN produces. When you run an EXPLAIN statement against an SQL - -- command or Access Module, the result is placed in an SQL table - -- called ACCESS_PLAN or another name that you choose. After you - -- create the ACCESS_CODE table and PLANVIEW view, you can easily query- -- any EXPLAIN results and readily determine what kind of access - -- strategies will be used for your SQL statements. This is especially- -- useful for finding SQL queries that will cause area sweeps or other - -- access strategies that might suggest adding new indices or other - -- tuning options to the database. - -- - -- Before running the script it is assumed that a "current" schema - -- has been set as follows - -- SET SESSION CURRENT SCHEMA <schema-name>; - -- Also this current schema must have been assigned with an - -- appropriate DEFAULT AREA to accommodate for the ACCESS_CODE and - -- ACCESS_PLAN tables. - -- - ------------------------------------------------------------------------
------------------------------------------------------------------------
-- Set up access plan code table:
create table ACCESS_CODE
(COLUMN smallint not null, -- column index
CODE_NUM smallint, -- numeric code
CODE_CHAR char, -- character code
TEXT char(16) not null) -- display text
no default index
;
create index ACCESS_CODE_IX
on ACCESS_CODE
(COLUMN, CODE_NUM, CODE_CHAR)
clustered
;
------------------------------------------------------------------------
-- These are the codes for the COMMAND column: insert into ACCESS_CODE values (1,8,NULL,'DECLARE'); insert into ACCESS_CODE values (1,9,NULL,'DELETE'); insert into ACCESS_CODE values (1,17,NULL,'INSERT'); insert into ACCESS_CODE values (1,25,NULL,'SELECT'); insert into ACCESS_CODE values (1,29,NULL,'UPDATE');
-- These are the codes for the STYPE column: insert into ACCESS_CODE values (2,0,NULL,' '); insert into ACCESS_CODE values (2,1,NULL,'Table Access'); insert into ACCESS_CODE values (2,2,NULL,'NL Join'); insert into ACCESS_CODE values (2,3,NULL,'SM Join'); insert into ACCESS_CODE values (2,4,NULL,'Sort'); insert into ACCESS_CODE values (2,5,NULL,'Merge Group'); insert into ACCESS_CODE values (2,6,NULL,'OR List'); insert into ACCESS_CODE values (2,7,NULL,'Dbk (Sorted)'); insert into ACCESS_CODE values (2,8,NULL,'Dbk (Unsorted)');
-- These are the codes for the ACMODE column: insert into ACCESS_CODE values (3,NULL,' ',' '); insert into ACCESS_CODE values (3,NULL,'A','Area Sweep'); insert into ACCESS_CODE values (3,NULL,'C','Calc'); insert into ACCESS_CODE values (3,NULL,'I','Index'); insert into ACCESS_CODE values (3,NULL,'M','Set Member'); insert into ACCESS_CODE values (3,NULL,'N','Insert'); insert into ACCESS_CODE values (3,NULL,'O','Set Owner'); insert into ACCESS_CODE values (3,NULL,'P','Procedure'); insert into ACCESS_CODE values (3,NULL,'R','Rowid Indx'); insert into ACCESS_CODE values (3,NULL,'S','Index Seql'); insert into ACCESS_CODE values (3,NULL,'T','Temp Seql');
-- These are the codes for the SORTC and SORTN columns: insert into ACCESS_CODE values (4,NULL,' ',' '); insert into ACCESS_CODE values (4,NULL,'D','Distinct'); insert into ACCESS_CODE values (4,NULL,'G','Group'); insert into ACCESS_CODE values (4,NULL,'M','Merge Join'); insert into ACCESS_CODE values (4,NULL,'O','Order By');
------------------------------------------------------------------------ -- This statement forces the definition of the ACCESS_PLAN table, -- so that the following view creation can reference it. EXPLAIN STATEMENT 'SELECT * FROM SYSTEM.TABLE' STATEMENT NUMBER 9999;
------------------------------------------------------------------------
-- This is a sample view definition. The ST instance of ACCESS_CODE
-- decodes the STYPE column of ACCESS_PLAN. The AM instance decodes the
-- ACMODE column. The S1 and S2 instances decode the SORTC and SORTN
-- columns, respectively. The COMMAND column isn't included in the
-- view, but could be decoded by introducing another ACCESS_CODE
-- INSTANCE in the FROM clause along with the matching join factors.
-- A descending sort was chosen to force the high level joins to the
-- top of the output - in sort of a top down tree format.
create view PLANVIEW(SNO, QB, PB, ST, "STEP TYPE", PST, TSCHEMA,
TABLE, "ACCESS MODE", ACNAME, LFS, OSORT,
ISORT, SQC) AS
select CAST(SECTION AS DEC(4)),
CAST(QBLOCK AS DEC(3)),
CAST(PBLOCK AS DEC(3)),
CAST(STEP AS DEC(3)),
ST.TEXT,
CAST(PSTEP AS DEC(3)),
SUBSTR(TSCHEMA, 1, 8),
SUBSTR(TABLE, 1, 10),
SUBSTR(AM.TEXT, 1, 10),
ACNAME,
LFS,
SUBSTR(S1.TEXT, 1, 10),
SUBSTR(S2.TEXT, 1, 10), SUBQC
from ACCESS_PLAN, ACCESS_CODE ST, ACCESS_CODE AM, ACCESS_CODE S1,
ACCESS_CODE S2
where ST.COLUMN = 2 and ST.CODE_NUM = STYPE
and AM.COLUMN = 3 and AM.CODE_CHAR = ACMODE
and S1.COLUMN = 4 and S1.CODE_CHAR = SORTC
and S2.COLUMN = 4 and S2.CODE_CHAR = SORTN
order by 1, 2, 4 desc;
------------------------------------------------------------------------ -- Look at the access plans select * from PLANVIEW;
|
Copyright © 2014 CA.
All rights reserved.
|
|