We've been tracking our query use and are seeing an excess of help queries that are not being executed manually. We're trying to confirm how to disable any background processes that Toad maybe executing that is eating into spool capacity which is limited to a specified number of queries before a login resets.
Help Column * From "databaseName"."tableName"
Hi,
I've opened TMB-2695 to address this. Next time please include more details like type of connection and/or screenshots.
Thanks, I'll add it to the issue.
Any ideas as to how we can control this? It continues to eat up queries and we're trying to see if it's something we're doing that we can address or it's something with TDP that continues to request HELP on columns.
Sorry, no ideas. It's not been worked on yet and we don't know when it will be.
This portion of a query surfaced that has comments that we cannot trace to anything maintained on this end. It's either coming from Tableau or Data Point.
" -- for unlnown reasons TOAD/or Client or something else formats the number ?????"
This query is not something we generate, but it's being executed by TDP or Tableau.
SELECT
ColumnId,
ColumnName AS NAME,
ColumnType,
CASE
WHEN Cols.ColumnType = 'I' THEN 'INTEGER'
WHEN Cols.ColumnType = 'D' THEN 'DECIMAL' || Coalesce('(' || DecimalTotalDigits || Coalesce(',' || DecimalFractionalDigits, '') || ')', '')
WHEN Cols.ColumnType = 'I1' THEN 'BYTEINT'
WHEN Cols.ColumnType = 'I2' THEN 'SMALLINT'
WHEN Cols.ColumnType = 'I8' THEN 'BIGINT'
WHEN Cols.ColumnType = 'F' THEN 'FLOAT'
WHEN Cols.ColumnType = 'DA' THEN 'DATE'
WHEN Cols.ColumnType = 'AT' THEN 'TIME' || Coalesce('(' || DecimalFractionalDigits || ')', '')
WHEN Cols.ColumnType = 'TS' THEN 'TIMESTAMP' || Coalesce('(' || DecimalFractionalDigits || ')', '')
WHEN Cols.ColumnType = 'TZ' THEN 'TIME' || Coalesce('(' || DecimalFractionalDigits || ')', '') || ' WITH TIME ZONE'
WHEN Cols.ColumnType = 'SZ' THEN 'TIMESTAMP' || Coalesce('(' || DecimalFractionalDigits || ')', '') || ' WITH TIME ZONE'
WHEN Cols.ColumnType = 'YR' THEN 'INERVAL YEAR' || Coalesce('(' || DecimalTotalDigits || ')', '')
WHEN Cols.ColumnType = 'YM' THEN 'INERVAL YEAR' || Coalesce('(' || DecimalTotalDigits || ')', '') || ' TO MONTH'
WHEN Cols.ColumnType = 'MO' THEN 'INERVAL MONTH' || Coalesce('(' || DecimalTotalDigits || ')', '')
WHEN Cols.ColumnType = 'DY' THEN 'INERVAL DAY' || Coalesce('(' || DecimalTotalDigits || ')', '')
WHEN Cols.ColumnType = 'DH' THEN 'INERVAL DAY' || Coalesce('(' || DecimalTotalDigits || ')', '') || ' TO HOUR'
WHEN Cols.ColumnType = 'DM' THEN 'INERVAL DAY' || Coalesce('(' || DecimalTotalDigits || ')', '') || ' TO MINUTE'
WHEN Cols.ColumnType = 'DS' THEN 'INERVAL DAY' || Coalesce('(' || DecimalTotalDigits || ')', '') || ' TO SECOND' || Coalesce('(' || DecimalFractionalDigits || ')', '')
WHEN Cols.ColumnType = 'HR' THEN 'INERVAL HOUR' || Coalesce('(' || DecimalTotalDigits || ')', '')
WHEN Cols.ColumnType = 'HM' THEN 'INERVAL HOUR' || Coalesce('(' || DecimalTotalDigits || ')', '') || ' TO MINUTE'
WHEN Cols.ColumnType = 'HS' THEN 'INERVAL HOUR' || Coalesce('(' || DecimalTotalDigits || ')', '') || ' TO SECOND' || Coalesce('(' || DecimalFractionalDigits || ')', '')
WHEN Cols.ColumnType = 'MI' THEN 'INERVAL MINUTE' || Coalesce('(' || DecimalTotalDigits || ')', '')
WHEN Cols.ColumnType = 'MS' THEN 'INERVAL MINUTE' || Coalesce('(' || DecimalTotalDigits || ')', '') || ' TO SECOND' || Coalesce('(' || DecimalFractionalDigits || ')', '')
WHEN Cols.ColumnType = 'SC' THEN 'INERVAL SECOND' || Coalesce('(' || DecimalTotalDigits || Coalesce(',' || DecimalFractionalDigits, '') || ')', '')
WHEN Cols.ColumnType = 'PD' THEN 'PERIOD(DATE)'
WHEN Cols.ColumnType = 'PT' THEN 'PERIOD(TIME' || Coalesce('(' || DecimalFractionalDigits || ')', '') || ')'
WHEN Cols.ColumnType = 'PZ' THEN 'PERIOD(TIME' || Coalesce('(' || DecimalFractionalDigits || ')', '') || ' WITH TIME ZONE)'
WHEN Cols.ColumnType = 'PS' THEN 'PERIOD(TIMESTAMP' || Coalesce('(' || DecimalFractionalDigits || ')', '') || ')'
WHEN Cols.ColumnType = 'PM' THEN 'PERIOD(TIMESTAMP' || Coalesce('(' || DecimalFractionalDigits || ')', '') || ' WITH TIME ZONE)'
WHEN Cols.ColumnType = 'CF' THEN 'CHAR' || CASE -- CAST is needed to avoid expression like CHAR(2,300 ). -- for unlnown reasons TOAD/or Client or something else formats the number ?????
WHEN CharType = 2 OR CharType = 4 THEN -- UNICODE or GRAPHIC COALESCE('(' || TRIM(CAST(ColumnLength/2 as INTEGER)) || ')', '')
ELSE Coalesce('(' || Trim(Cast(ColumnLength AS INTEGER)) || ')', '')
end
Whatever this background query is it's eating 25% of our total spool capacity, which is a significant waste of resources if we can do something about it. It's not Tableau because it has a Toad comment and this query is system generated. Would really like some input as to how to prevent this.
This may be part of the import wizard creating a wrapper. However we set the block size much higher so if that is working correctly the import wizard should not execute this many times. Seems to be two potential issues here.
Is there any way to disable the look-ups on the filters? It seems like TDP is always running queries to populate drop-down menus even when they are not in use. Is there an option to control that? 2000 queries a day is an expensive waste.
This could be happening inside a loop, but there is no explicit HELP being executed. Is there any way to tell when a HELP Column would be needed while TDP iterates through a loop? That would explain the volume since there are two queries running inside the loop over ~1100 people.
