physbuff and logbuff utilization

Does anyone know if I can get the physbuff and logbuff utilization percent ((pages/io / bufsize) * 100) from sysmaster or can this only be retrieved via 'onstat -l'? Thanks, Andrew

On Apr 9, 9:51 am, "Andrew Ford" <a...@networkip.net> wrote: > Does anyone know if I can get the physbuff and logbuff utilization percent > ((pages/io / bufsize) * 100) from sysmaster or can this only be retrieved > via 'onstat -l'? > > Thanks, > > Andrew Well, you can get the values that onstat uses to do the computation from sysmaster, so you can probably write some stored procedure or something to do it. But the values you would be looking for would be from the sysprofile table. Specifically select * from sysprofile where name in ("plgpagewrites", "plgwrites", "llgrecs", "llgpagewrites", "llgwrites"); so from onstat -l the "pages/io" for the physlog is plgpagewrites/ plgwrites and for the logical log buffer "recs/pages" is llgrecs/ llgpagewrites and "pages/io" is llgpagewrites/llgwrites. Jacques

On Apr 9, 2:32 pm, jpren...@yahoo.com wrote: > On Apr 9, 9:51 am, "Andrew Ford" <a...@networkip.net> wrote: > > > Does anyone know if I can get the physbuff and logbuff utilization percent > > ((pages/io / bufsize) * 100) from sysmaster or can this only be retrieved > > via 'onstat -l'? > > > Thanks, > > > Andrew > > Well, you can get the values that onstat uses to do the computation > from sysmaster, so you can probably write some stored procedure or > something to do it. But the values you would be looking for would be > from the sysprofile table. > > Specifically select * from sysprofile where name in ("plgpagewrites", > "plgwrites", "llgrecs", "llgpagewrites", "llgwrites"); > > so from onstat -l the "pages/io" for the physlog is plgpagewrites/ > plgwrites and for the logical log buffer "recs/pages" is llgrecs/ > llgpagewrites and "pages/io" is llgpagewrites/llgwrites. > > Jacques Oh, I forgot the size (in pages) of the plog buffer would be: select pl_bufsize from sysplog; However, it doesn't appear that the size of the logical log buffer is readily available from the sysmaster database.

On Apr 9, 2:47 pm, jpren...@yahoo.com wrote: > On Apr 9, 2:32 pm, jpren...@yahoo.com wrote: > > > > > On Apr 9, 9:51 am, "Andrew Ford" <a...@networkip.net> wrote: > > > > Does anyone know if I can get the physbuff and logbuff utilization percent > > > ((pages/io / bufsize) * 100) from sysmaster or can this only be retrieved > > > via 'onstat -l'? > > > > Thanks, > > > > Andrew > > > Well, you can get the values that onstat uses to do the computation > > from sysmaster, so you can probably write some stored procedure or > > something to do it. But the values you would be looking for would be > > from the sysprofile table. > > > Specifically select * from sysprofile where name in ("plgpagewrites", > > "plgwrites", "llgrecs", "llgpagewrites", "llgwrites"); > > > so from onstat -l the "pages/io" for the physlog is plgpagewrites/ > > plgwrites and for the logical log buffer "recs/pages" is llgrecs/ > > llgpagewrites and "pages/io" is llgpagewrites/llgwrites. > > > Jacques > > Oh, I forgot the size (in pages) of the plog buffer would be: > > select pl_bufsize from sysplog;> > However, it doesn't appear that the size of the logical log buffer is > readily available from the sysmaster database. Eh, I found a place to get it...I guess I should be more patient. You can do select cf_effective from sysconfig where cf_name = "LOGBUFF"; However, the output for that is in kbytes just like from the onconfig file. So you'd have to then convert it to pages based on your page size. That could also be used to get the physical log buff size, but use "PHYSBUFF" instead for the cf_name. That would also be in kbytes. But if you use the previous mentioned sysplog query it returns the value in pages already.

On 9 О©╫О©╫О©╫, 17:51, "Andrew Ford" <a...@networkip.net> wrote: > Does anyone know if I can get the physbuff and logbuff utilization percent > ((pages/io / bufsize) * 100) from sysmaster or can this only be retrieved > via 'onstat -l'? You can choose what you need :) ------------------------------------------------- -- Logs (Physical and Logical) profile -- (whole instance) -- -- V.Shulzhenko DBA_Tools Last modify: 2006-04 ------------------------------------------------- set isolation to dirty read;select '===== Logs Profile ============' ______________ ,DBINFO('dbhostname') hostname ,(select cf_effective from sysconfig where cf_name='DBSERVERNAME') dbserver_name ,current year to second - EXTEND(dbinfo('utc_to_datetime',sh_pfclrtime),year to second) statistic_time ,'-------------------------------' ______________ ,'--Onconfig Effective--' __________ ,(select cf_effective from sysconfig where cf_name='PHYSBUFF') physlog_buffer_kb ,(select cf_effective from sysconfig where cf_name='PHYSDBS') physlog_dbs ,(select cf_effective from sysconfig where cf_name='PHYSFILE') physlog_size_kb ,'----' ____ ,(select cf_effective from sysconfig where cf_name='LOGBUFF') llog_buffer_kb ,(select cf_effective from sysconfig where cf_name='LOGFILES') log_files ,(select count(*) from syslogs) _Real_logs ,(select cf_effective from sysconfig where cf_name='LOGSIZE') llog_sizes_kb ,round((select sum(size) from syslogs)*sh_pagesize/1024) _Real_size_logs_kb ,'----' ____ ,(select cf_effective from sysconfig where cf_name='LOG_BACKUP_MODE') log_backup_mode ,(select cf_effective from sysconfig where cf_name='LTAPEDEV') ltapedev ,(select cf_effective from sysconfig where cf_name='LOGSMAX') logsmax ,(select cf_effective from sysconfig where cf_name='LTXHWM') ltxhwm ,(select cf_effective from sysconfig where cf_name='LTXEHWM') ltxehwm ,'-------------------------------' ______________ ,'-- Physical_log --' __________ ,round(((select value from sysprofile where name='plgpagewrites')/ (select value from sysprofile where name='plgwrites'))/ ((select cf_effective from sysconfig where cf_name='PHYSBUFF')/ (sh_pagesize/1024)),2) _phbuff_utiliz ,(select value from sysprofile where name='plgpagewrites') page_writes ,(select value from sysprofile where name='plgwrites') writes ,round((select value from sysprofile where name='plgpagewrites')/ (select value from sysprofile where name='plgwrites'),2) __pages_per_write ,round((select value from sysprofile where name='plgpagewrites')/ (sh_curtime-sh_pfclrtime)*60,2) _pages_per_min ,round((select value from sysprofile where name='plgwrites')/ (sh_curtime-sh_pfclrtime)*60,2) _writes_per_min ,'-- Logical_logs --' __________ -- Logical log buffer utilization -- (llgpagewrites/llgwrites) / (LOGBUFF/page_size_in_K) is good if > 0.75 ,round(((select value from sysprofile where name='llgpagewrites')/ (select value from sysprofile where name='llgwrites'))/ ((select cf_effective from sysconfig where cf_name='LOGBUFF')/ (sh_pagesize/1024)),2) _logbuff_utiliz ,(select value from sysprofile where name='llgrecs') records ,round((select value from sysprofile where name='llgrecs')/ (sh_curtime-sh_pfclrtime)*60,2) _records_per_min ,(select value from sysprofile where name='llgpagewrites') page_writes ,round((select value from sysprofile where name='llgrecs')/ (select value from sysprofile where name='llgpagewrites'),2) _records_per_page ,round((select value from sysprofile where name='llgpagewrites')/ (sh_curtime-sh_pfclrtime)*60,2) _pages_per_min ,(select value from sysprofile where name='llgwrites') writes ,round((select value from sysprofile where name='llgrecs')/ (select value from sysprofile where name='llgwrites'),2) _records_per_write ,round((select value from sysprofile where name='llgpagewrites')/ (select value from sysprofile where name='llgwrites'),2) __pages_per_write ,round((select value from sysprofile where name='llgwrites')/ (sh_curtime-sh_pfclrtime)*60,2) _writes_per_min ,sh_lastlogfreed last_log_freed ,'-- Transactions --' __________ ,(select value from sysprofile where name='iscommits') iscommits ,(select value from sysprofile where name='isrollbacks') isrollbacks ,round((select value from sysprofile where name='llgrecs')/ ((select value from sysprofile where name='iscommits') +(select value from sysprofile where name='isrollbacks')),2) _records_per_tranx ,sh_longtx long_trans from sysshmvals;