Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    6

    Unanswered: Query slow on dba_free_space

    I am running the following query and It is very slow. The server is peforming optimally. Only this query is veru slow it takes almost 10 min+

    select '$ORA_SID'||':'|| a.tablespace_name || ':' || a.avail || ':' || nvl(b.free,0) || ':' || nvl(round(((free/avail)*100),2),0) || ':$THRESHOLD'
    from (select tablespace_name,
    round(sum(bytes/(1024*1024)),3) avail
    from sys.dba_data_files
    group by
    tablespace_name ) a,
    (select tablespace_name,
    round(sum(bytes/(1024*1024)),3) free
    from sys.dba_free_space
    group by
    tablespace_name) b
    where a.tablespace_name = b.tablespace_name (+)
    and nvl(round((free/avail)*100,2),0) < $THRESHOLD
    and a.tablespace_name = '$TBLSP_NM'
    order by a.tablespace_name

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    login as the sys user and view the execution plan on the underlying tables. If it looks like it is using a suboptimal execution plan i.e. it is joining things in the wrong order etc, then try using hints to get it to use a better execution plan. If all else fails you could create an index BUT be very careful as it may upset other processes which use those tables (dont analyze the index or table if possible as doing this on sys tables can be dangerous).

    Alan

  3. #3
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    There is an implicit select into statement and it can be killing....but do runstats on it.
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

  4. #4
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    But do check your query as well..i think it's a copy and paste...try this then...

    Code:
    SELECT      '$ORA_SID'
             || ':'
             || a.tablespace_name
             || ':'
             || a.avail
             || ':'
             || NVL (b.free, 0)
             || ':'
             || NVL (ROUND (((free / avail) * 100), 2), 0)
             || ':$THRESHOLD'
        FROM (SELECT   tablespace_name,
                       ROUND (SUM (BYTES / (1024 * 1024)), 3) avail
                  FROM SYS.dba_data_files
              GROUP BY tablespace_name) a,
             (SELECT   tablespace_name,
                       ROUND (SUM (BYTES / (1024 * 1024)), 3) free
                  FROM SYS.dba_free_space
              GROUP BY tablespace_name) b
       WHERE a.tablespace_name = b.tablespace_name(+)
         AND NVL (ROUND ((free / avail) * 100, 2), 0) < '$threshold'
         AND a.tablespace_name = '$TBLSP_NM'
    ORDER BY a.tablespace_name
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •