• Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

Comments (3)

1 localhost commented Permalink

Why the database name in onstat -g his output is not in human-readable format?

 
example:
 
...
 
Database: 0x100003 Statement text: Select count(*) nrows from informix.syssqltrace
 
...

2 localhost commented Permalink

If the SQLTRACE onconfig parameter is set to level=low or left unset (in which case it defaults to low) then a faster tracing which does not do as many string copies is used. This level=low tracing only saves the database systables partnum. If a trace level of medium or higher is used then the string name of the database server is stored and printed.

3 localhost commented Permalink

You wrote: "The table syssqltrace_iter provides information in the form of an iterator tree for each sql."

 
This is true. But there is no indication which part of the tree is related to which table or join operation or which index . For example:
 
Query:SELECT I.IDXNAME, T.TABNAME FROM SYSINDEXES I, SYSTABLES T WHERE T.TABID = I.TABID AND T.TABID > 99 AND I.IDXTYPE = 'U' AND I.IDXNAME NOT IN ( SELECT IDXNAME FROM SYSCONSTRAINTS WHERE CONSTRTYPE = 'P' ) UNION SELECT CONSTRNAME, TABNAME FROM SYSCONSTRAINTS C, SYSTABLES T WHERE C.TABID = T.TABID AND T.TABID > 99 AND C.CONSTRTYPE = 'P'
 
Iterator/Explain ================ ID Left Right Est Cost Est Rows Num Rows Type 4 0 0 5386 770 6823 Index Scan 5 0 0 1 7336 1 Index Scan 3 4 5 6056 763 6823 Nested Join 7 0 0 3185 5508 1 Index Scan 8 0 0 1 7336 1 Index Scan 6 7 8 6402 5460 1 Nested Join 2 3 6 1 1 6824 Merge 1 2 0 1 6223 6824 Sort
 
Which row of the iterator table is which part of the execution plan and which index is used?
 
Another bug/feature:in "high" level the SQL trace collects the values of he host variables. "onstat -g his" prints them. But there is no system table syssqltrace_vars or what ever that allows for SQL queries like syssqltrace_iter does for (well, part of) the execution plan.
 
Best regardsChristian

Add a Comment Add a Comment