Tags: database, databaseiam, dear, defragmantation, friends, mysql, oracle, solaristhanks, sql, sun, sybase, tables

defragmantation information

On Database » sybase

6,410 words with 3 Comments; publish: Sat, 17 May 2008 11:37:00 GMT; (25062.50, « »)

Dear Friends,

How to get the defragmantation information of all tables in database

Iam using 12.5.0.3 on Sun solaris

Thanks in advance

lalit

All Comments

Leave a comment...

  • 3 Comments
    • What do you mean by "defragmentation information"? This seems to be a

      subjective question.

      On the other hand, since this is 12.5.0.3., one can now obtain some of the

      cluster ratio values as reported by optdiag:

      select X = derived_stat (id, indid, "dpcr" | "drcr" | "ipcr" | "lgio" |

      "sput")

      "dpcr" = data page cluster ratio, ..., "lgio" = large IO effectiveness,

      "sput" = space utilization

      Sput is actually a little bit low except for APL nonclustered indexes. The

      first time that I became aware of this new function was by a posting by Ryan

      Putnam somewhere. Note that the function results are only as good as the

      last update statistics or a few other commands.

      "Lalit" <issues_sybase.sybase.questionfor.info.hotmail.com> wrote in message

      news:7bcd3933.0309230016.bf93070.sybase.questionfor.info.posting.google.com...

      quote:

      > Dear Friends,

      > How to get the defragmantation information of all tables in database

      > Iam using 12.5.0.3 on Sun solaris

      > Thanks in advance

      > lalit


      #1; Sat, 17 May 2008 11:39:00 GMT
    • Try this query. If your table stats are up to date, the column Density will

      give you a % of space used. Use the reorg compact command if the density is

      below 0.7 or 70%

      select "Table"=convert( char(36), o.name + ' (' + user_name( uid ) + ')' ),

      "Lock Scheme" =

      convert( char(10) ,

      case

      when sysstat2 & 8192 = 8192 then 'All Pages'

      when sysstat2 & 16384 = 16384 then 'Data Pages'

      when sysstat2 & 32768 = 32768 then 'Data Rows'

      else 'AllP old-s' -- Old Style locking so its all pages

      end ),

      "Pages" = str( pagecnt, 8,0),

      "Rows" = str( rowcnt,8,0),

      "RSize" = str( s.datarowsize, 5,0),

      "Total MB" = str( (convert( numeric(28,5), s.pagecnt) *

      onvert( numeric(28,5),.sybase.questionfor.info.psize))

      /1048576,

      8,2),

      "Density" = str( ( convert( numeric(28,5), s.rowcnt) *

      onvert( numeric(28,5),s.datarowsize)

      + convert( numeric(28,5), 64*s.pagecnt) )

      / (convert( numeric(28,5),s.pagecnt) * convert( numeric(28,5),.sybase.questionfor.info.psize) ),

      7,2)

      from sysobjects o,

      systabstats s

      where o.type = 'U'

      and s.indid in ( 0,1)

      and o.id = s.id

      order by rowcnt desc

      "Lalit" <issues_sybase.sybase.questionfor.info.hotmail.com> wrote in message

      news:7bcd3933.0309230016.bf93070.sybase.questionfor.info.posting.google.com...

      quote:

      > Dear Friends,

      > How to get the defragmantation information of all tables in database

      > Iam using 12.5.0.3 on Sun solaris

      > Thanks in advance

      > lalit


      #2; Sat, 17 May 2008 11:40:00 GMT
    • Uuups, I left out some stuff from the script. Here is the corrected version.

      begin

      declare .sybase.questionfor.info.psize float

      select .sybase.questionfor.info.psize = convert(float,low)

      from master.dbo.spt_values

      where type = 'E'

      and number = 1

      select "Table"=convert( char(36), o.name + ' (' + user_name( uid ) + ')' ),

      "Lock Scheme" =

      convert( char(10) ,

      case

      when sysstat2 & 8192 = 8192 then 'All Pages'

      when sysstat2 & 16384 = 16384 then 'Data Pages'

      when sysstat2 & 32768 = 32768 then 'Data Rows'

      else 'AllP old-s' -- Old Style locking so its all pages

      end ),

      "Pages" = str( pagecnt, 8,0),

      "Rows" = str( rowcnt,8,0),

      "RSize" = str( s.datarowsize, 5,0),

      "Total MB" = str( (convert( numeric(28,5), s.pagecnt) *

      onvert( numeric(28,5),.sybase.questionfor.info.psize))

      /1048576,

      8,2),

      "Density" = str( ( convert( numeric(28,5), s.rowcnt) *

      onvert( numeric(28,5),s.datarowsize)

      + convert( numeric(28,5), 64*s.pagecnt) )

      / (convert( numeric(28,5),s.pagecnt) * convert( numeric(28,5),.sybase.questionfor.info.psize) ),

      7,2)

      from sysobjects o,

      systabstats s

      where o.type = 'U'

      and s.indid in ( 0,1)

      and o.id = s.id

      order by rowcnt desc

      end

      go

      "Karl Ritter" <karl.ritter (at) europe.com> wrote in message

      news:3f754adb$0$16267$79c14f64.sybase.questionfor.info.nan-newsreader-02.noos.net...

      quote:

      > Try this query. If your table stats are up to date, the column Density


      will

      quote:

      > give you a % of space used. Use the reorg compact command if the density


      is

      quote:

      > below 0.7 or 70%

      >

      > select "Table"=convert( char(36), o.name + ' (' + user_name( uid ) +


      ')' ),

      quote:

      > "Lock Scheme" =

      > convert( char(10) ,

      > case

      > when sysstat2 & 8192 = 8192 then 'All Pages'

      > when sysstat2 & 16384 = 16384 then 'Data Pages'

      > when sysstat2 & 32768 = 32768 then 'Data Rows'

      > else 'AllP old-s' -- Old Style locking so its all pages

      > end ),

      > "Pages" = str( pagecnt, 8,0),

      > "Rows" = str( rowcnt,8,0),

      > "RSize" = str( s.datarowsize, 5,0),

      > "Total MB" = str( (convert( numeric(28,5), s.pagecnt) *

      > onvert( numeric(28,5),.sybase.questionfor.info.psize))

      > /1048576,

      > 8,2),

      > "Density" = str( ( convert( numeric(28,5), s.rowcnt) *

      > onvert( numeric(28,5),s.datarowsize)

      > + convert( numeric(28,5), 64*s.pagecnt) )

      > / (convert( numeric(28,5),s.pagecnt) *


      convert( numeric(28,5),.sybase.questionfor.info.psize) ),

      quote:

      > 7,2)

      > from sysobjects o,

      > systabstats s

      > where o.type = 'U'

      > and s.indid in ( 0,1)

      > and o.id = s.id

      > order by rowcnt desc

      >

      >

      >

      >

      >

      >

      >

      > "Lalit" <issues_sybase.sybase.questionfor.info.hotmail.com> wrote in message

      > news:7bcd3933.0309230016.bf93070.sybase.questionfor.info.posting.google.com...

      >


      #3; Sat, 17 May 2008 11:41:00 GMT