I am having a very difficult time troubleshooting issues with poor performance for global temporary tables.
All the usual sources of information - table statistics, dba_segments, dba_seg_stats, AWR data, etc. seem to be unavailable when the issue points to a temporary table.
For example, when I want to review whether an index being used for a query in a global temporary table has appropriate cardinality. I cannot do a "select count(column) from <gtt_table_name>" to find out the true cardinality since the object is only visible within the session that is updating the global temp table.
Another example: if I want to review whether a temporary table is fragmented (space) or has a lot of chained rows, there is no way to do this outside of the context of the vendor supplied process that created the temporary table. I recently noticed that the AWR statistic "chain_row_excess_delta" from dba_hist_seg_stat was reporting huge numbers of chained row reads for global temporary tables created by an application by a well known vendor.
Does anybody have a framework to analyze the details of global temporary tables - number rows, row size, cardinality, etc.? The segment organization of blocks of a temporary table has to be different than a normal heap oriented table simply because each session inserting into the table can only see the records from that session. Are there any internal tables that reveal data about temporary tables in a way that somebody with sysdba privilege can review it (outside of the session populating the table)?
Any help would be appreciated.