在网上看到的关于DIAGNOSTIC的资料，目前TD中还没有专门的文档讲DIAGNOSTIC
PARSER DIAGNOSTIC FLAGS
=======================
To obtain detailed optimizer plan information,
you can use the "diagnostic flag" utility.
The DIAGNOSTIC statement preceeds the EXPLAIN
sql statement.
Note that this information can be quite large,
so you will want to do a screen capture with
either Procomm Plus or the UNIX Script command.
The general format is:
DIAGNOSTIC [NOT] ON FOR
< REQUEST | SESSION | IFP | SYSTEM> ;
Where:
Diagnostic Command ::= parser diagnostic command
NOT ON ::= disables the diagnostic
ON ::= enables the diagnostic
REQUEST ::= Diagnostic enabled or disabled for the
request only
SESSION::= Diagnostic enabled or disabled for this
session only
IFP::= Diagnostic enabled or disabled for all sessions
logged on via this IFP
SYSTEM::= Diagnostic enabled or disabled for all sessions
on the system (only for "set costs")
Example:
BTEQ -- Enter your DBC/SQL request or BTEQ command:
diagnostic psteps on for session;
diagnostic psteps on for session;
*** Null statement accepted.
*** Total elapsed time was 1 second.
amps=xxx - simulate a given number of amps
nosattc - transitive closure (DR81503)
1,'SYNTREE','Syntaxer displays its output tree'
2,'RESTREE','Resolver displays its output tree'
3,'DSTEPS','PARDFSTEPS,GncApply displays resulting steps to disp'
4,'PHASE','Major phases give one-liner when started'
5,'PSTEPS','Plastic Steps Segment dumped'
6,'DUMAPPLY','Do dummy GncApply'
7,'WHITE','Display White forest'
8,'EVLTREE','Display EVL tree'
9,'ACCESSR','Access rights needed'
10,'REQUEST','Display request text' request- Request sends the
request text for each request from the session to screen5. This is
very handy when you have a bunch of diagnostics enabled. This get
a bit confusing. But when the request test shows up, you know it's
the start of a new request.
11,'PATH','Display single table access path analysis'- Path shows
the information used by the path analyzer (opt1axp). It gives you
info about the parser's size (cardinality) estimates.
12,'JOINPLAN','PARDFJOIN, Display join decomposition processing'
13,'NOCACHE','Turns off the Steps cache'
14,'"BYPASS ACCESSRIGHTS"','PARDFBYPASSACR, ByPass accessright check
for DBC'
15,'PRUNE','Display dup spool opt information'
16,'NOPRUNE','Dont optimize duplicate spools'
17,'EXPL','PARDFEXPLAIN, Give explanation of your query'
18,'SQLCHECK','Check for non-SQL features'
19,'NOPARALLEL','Turn off parallel steps'
20,'CMRGL','Trace OptCMrgl routines'
21,'PREPINFO','Prepare Info Parcel'
22,'MGCTSKINFO','MgcGlobal info for tasks and drek'
23,'USESTATS','Force optimizer to use statistics'
24,'CONVERT','3.x to 4.0 Conversion Flag' - don't change revision #
25,'MINLTSIZE','change the minimum size of the table that can be
considered as the large table of a LT/ST join'
26,'MINESTTIME','change minimum value of estimated
response time for a new join plan to be generated'
27,'SJOINSIZE','change size of structure used to save join plan
information when the new join plan is generated'
28,'NOLTSTJ','indicate whether LTST join should be generated'
29,'LTSTJ','indicate LTST join should be used if existed'
30,'NEWJOIN','indicate that diagnostic information should be displayed
while finding a new join plan'
31,'J1ACARDRATIO','indicate that the minimum cardinality ratio is
specified for LTST-J1a class'
32,'J2ACARDRATIO','indicate that the minimum cardinality ratio is
specified for LTST-J2a class'
33,'DMP2PCTSEG','Dump pertinent 2pc Tseg stuff'
34,'DMPCON','Dump pertinent Contsk stuff'
35,'OUTERJOIN','PARDFOUTERJ, Dump outer join information'
36,'SETREL',''
37,'OU2IN',''
38,'OJCND',''
39,'JTABC',''
40,'NORELEASESUB',''
41,'EVLINTERP','Dont generate executable EVL code'
42,'NOCSE','No Common Subexpression Elimination'
43,'ASCODE','Display EVL code in assembly language'
44,'HSJOIN',''
45,'ORGJOIN',''
46,'LOOKAHEAD','User defined number of look ahead'
47,'HSJFEATURE',''
48,'CASCADE','This flag affects dropping base temporary table. When
the flag is on, a drop base temporary table request will drop the base
temporary table and all its materialized instances in all sessions.'
49,'USEOLDUPDATES','PARDFOLDUPDATES'
50,'NOJIND','PARDFNOJINDEX, Force a query not to use join index
51,'ENBJINDSEL','Enable select from join index table'
52,'"BYPASS NAME VALIDATION"','PARDFBYPASSNAMCHK, DR42721- mkw-01
Bypass object name check'
53,'RTSDEBUG','DR43887-MK6-03 RTS Debug information'
54,'STEPPRINT',''
55,'COSTPRINT','DR48180-msirek-01 Display cost constants that are
currently in use by the optimizer'
56,'INSCAPTURE',''
57,'DMPCAPTURE',''
58,'SPLDEBUG','DR43887-MK6-03 SPL Debug information DR51552-sc7-01->>'
59,'UPSCHECKKEYS','When this flag is set, AMP code to compare the
UPSERT primary key values for update and insert will be invoked'
60,'UPSCHECKFIELDS','When this flag is set, AMP code to compare the
UPSERT field values (including primary key values) for update and
insert will be invoked'
61,'PPITEST','DR53763-msirek-02 Default implicit PI Partitioning'
62,'NODUPEDGEOG','DR53763-msirek-04 Favor join plans not involving
duped geography.'
63,'JOINTERMS','DR53763-msirek-05 Print unused join terms before
each join is permanently committed. <<-DR51552-sc7-01'
64,'NOSATTC','DR54182-ag4-01' 65,'NOJE','DR54182-ag4-01' - can reduce
amount of memory used by parser (transitive closure)
66,'NODT','DR54182-ag4-01'nodc - causes us to spool derived tables,
can eliminate 3710 failures
67,'PGBOFF','DR40247-tkp-01- PGB wont be applied'
68,'PPILISTS','DR53763-xl151000-01 Dump PPI Partition Elimination
IN/EX lists'
69,'JOINPARTITIONS','DR53763-huffman-01'
70,'PPITEST2','DR53763-msirek-08 Default implicit range_n PI
Partitioning'
71,'INDEXANALYSIS','PARDFIDXVALID, This flag indicate the index
validation
mode'
72,'DMPIA','When this flag is set, the contents of workload cache is
dumped,
depending on the index analysis context under which dump routine is
invoked.'
73,'MERGEUPDOFF','DR55821-PB210003-01 - This flags diabales Merge
Update
Optimization. In this case we get Merge Delete + Merge Insert steps.'
74,'PPINOSAT','DR53763-bessc V2R5 PPI->>For "alter table modify primary
index
partition by ", this flag when set true will
disable
the SAT step (abort when table not empty) so that we can allow altering
Partition expression on a non-empty table. The flag is used for "alter
table
revalidate primary index" testing -- the rows inserted before the
partition
expression change should be "revalidated". <<-DR53763-bessc V2R5 PPI'
75,'NOPCDC','pcdc-ag4-01- Do not create PCDC DR60117-fsk-01->'
76,'LOBREC','Allows LOB to be retrieved in record/indicator mode.
<-DR60117-fsk-01'
77,'COLLECTSTATS','DR62350-lb180002 collect stats diag'
78,'NONESTEDJOIN','Flag on nested join */ /* DR61104-bp210009-01'
79,'TREENODEUSAGE','DR63508-al151002-01 shows tree node usage'
80,'NOINNER','DR56914-nxk-01'
81,'PPIJOINCONTEXTS','PARDFPPICONTEXT, dr63974-sheldon-01 show number of
contexts
82,'NOTRMCYLCK','dr64355-ag4-01. turn off DR64355 deadlock detection'
83,'NOPRODJOIN','dr66131-kc8-01 no prod join'
84,'USEONLYSTATEMENTCACHE','PARDFUSEONLYSTMTCACHE,DR66126-greene-01
force use of statement cache'
85,'PPITEST3','DR66488-msirek Default implicit range_n non-PI
Partitioning'
86,'NOSRE','DR61945-sc180001-01 Selectivity-Redund-Elim
dr82739-hsf-01->'
87,'GROUPAMP','This flag, when set, tells that group-amp spool check
should
be ignored. The check includes (estimated total number of spool rows
<= total #Amps in system) and optimizer confidence level on the
estimate
dr82739-hsf-01<-'
88,'NOMERGEJOIN','dr82706-kc8-01 no merge join'
89,'VERBOSEEXPLAIN','PARDFVERBEXPLAIN, dr83841-rk200012-01 verbose
explain'
90,'FIPSFLAGGER','PARDFSQLCHECK, but different than SQLCHECK'
EXAMPLE:
diagnostic "usestats,amps=64" on for session;
diagnostic joinplan on for session;
diagnostic request on for session;
diagnostic path on for session;
diagnostic newjoin on for session;
diagnostic "usestats,mipspercpu=268" on for session;
diagnostic "usestats,vampspercpu=4.00" on for session;
diagnostic "usestats,pmasperdiskarray=0.5" on for session;
EXPLAIN
SELECT A.Col1,
B.Col2,
C.Col3,
D.Col4
FROM
( TAB1 A JOIN TAB2 B ON ( A.ThePI = B.ThePI ))
LEFT OUTER JOIN
( TAB3 C JOIN TAB4 D ON ( C.TheNUSI = D.TheNUSI ));
Remember, the diagnostics output goes to Screen 5.
REQUESTS WHICH PERFORM A SPECIFIC FUNCTION:
===========================================
dumpcache
fds
spoil - spoil the dictionary cache
stats - tells hash collisions (may be broken)
dbcrestart - this will cause a restart!
pardump - DBS dump
FORMAT:
DIAGNOSTIC xxxxx;
