Bug in Define Session Queries > Details?

[William Robertson]

I can't work out what the pattern is, but I've just defined the following as a new tab in my Session browser window (Sessions > Define Session Queries > Details tab):

SELECT ash.started
     , ash.elapsed
     , s.sql_fulltext
     , ash.sql_id
     , ash.sql_child_number
     , ash.sql_plan_hash_value
FROM   ( SELECT MIN(sample_time) AS started
              , CAST
                ( MAX(sample_time) - MIN(sample_time)
                  AS INTERVAL DAY(0) TO SECOND(0) ) AS elapsed
              , sql_id
              , sql_child_number
              , sql_plan_hash_value
         FROM   v$active_session_history
         WHERE  session_id = :sid
         AND    session_serial# = :serial#
         GROUP BY sql_id, sql_child_number, sql_plan_hash_value ) ash
       JOIN v$sqlstats s
       ON s.sql_id = ash.sql_id AND s.plan_hash_value = ash.sql_plan_hash_value
ORDER BY 1 DESC

When I view the tab in the session window the first column is not displayed. I tried using various different names, changing the column order and adding dummy columns around it, but nothing works. Can anyone else reproduce this or is it just me?

Another example:

SELECT sql_fulltext
     , 'there seems to be a bug here' AS "SQL text"
FROM   v$sqlstats s
WHERE s.sql_id = :sql_id

This seems to be the only way to display sql_fulltext as "SQL text". Without the dummy column it displays nothing, and "SELECT *" skips the column contents and shifts all of the headings by one place.

(PL/SQL Developer 8.0.3.1510, Oracle 10.2.0.4 via 11g client, XP Pro 5.1 SP3).

 

[Theod]

Hi,

for me it's the same, whith the column-heading, but I see data.

It seems to be the CLOB-Type of sql_fulltext to cause this. At the Position of this colum, the columnheader shifts by one place too.

I tried then "to_char(sql_fulltext) as sql_fulltext" and it works.

 

[Theod]

Hi,

but it works only for sql_text up to 4000 length. The "ORA-22835: Puffer zu klein f

 

[Marco Kalter]

It is indeed a CLOB issue. We'll fix it.

 

[William Robertson]

Aha, good spot - thanks!

The simple fix in my case is to use sql_text instead of sql_fulltext. I can look up the full text elsewhere if I need it.

EDIT: Just realised it still didn't show the "started" column, but now I'm looking at datatypes I cast it to DATE and it's now OK.

My revised version is now:

SELECT CAST(ash.started AS DATE) AS started
     , ash.elapsed
     , s.sql_text
     , s.executions
     , ROUND(s.parse_calls/NULLIF(s.executions,0),2) parses_per_exec
     , ROUND(s.fetches/NULLIF(s.executions,0),2) fetches_per_exec
     , ROUND(s.rows_processed/NULLIF(s.executions,0),2) rows_processed_per_exec
     , s.module, s.action
     , ash.sql_id
     , ash.sql_child_number
     , ash.sql_plan_hash_value
FROM   ( SELECT MIN(sample_time) AS started
              , CAST(MAX(sample_time) - MIN(sample_time) AS INTERVAL DAY(0) TO SECOND(0)) AS elapsed
              , sql_id
              , sql_child_number
              , sql_plan_hash_value
         FROM   v$active_session_history
         WHERE  session_id = :sid
         AND    session_serial# = :serial#
         GROUP BY sql_id, sql_child_number, sql_plan_hash_value
       ) ash
       JOIN v$sql s
       ON  s.sql_id = ash.sql_id
       AND s.plan_hash_value = ash.sql_plan_hash_value
ORDER BY 1 DESC;

I switched to v$sql to get module and action, but the sql_fulltext/sql_text columns are the same. (Note that the stats about executions and rows processed etc in v$sql are global rather than per-session, which may be misleading if you're not expecting it.)