‘Zamong Web’ – update history

May 04, 2018

Some contents were added :
Top query (SQL_ID)
Top oracle Wait Event
Slow queries and sessions in the selected period

April 22, 2018

Some contents were added :
active session history chart
cpu usage stacked area chart



April 09, 2018

Solved : poor performance of query for dashboard chart  in Mysql 5.5 or Mariadb 5.5

Zamong clues description

We can use Zamong clues  for analysis.


Date”  and  “DB Date”  in zamong clues

The time may not be synchronized between the Oracle db server and the zamong server.  So data may not be displayed on the chart at midnight when the dates change. In this case, you can use “DB Date”  in addition to the “Date”.

Date :  Time when data was inserted into mariadb(mysql) on zamong server. 
DB Date : Time when zamong agent gathered data on oracle db server.

<How to Use>

1.  Specify “DB Alias” and “Date”.
     Click or type the time in “From” and “To”  item.
     Click the button(4).
     ( “Time  Format :  “mi:ss” )

You can see the charts and active session details.

Make the ‘DB Date’ the same as the ‘Date’ and Click the button(4).
If data of around midnight is not shown in the chart, change the DB date.


2.  Click on the line to get a time value.
     Click the “RUN” button to redraw one minute chart.

Double click that point  to clear the selection.



3.  Edit (1) and Click the “RUN” button(2) to redraw the one-minute chart for the specified time.
     Click the shift-button(3) to redraw the one-minute chart for the increased time.

You can see the chart of the active session count  over the 60 seconds of the time entered



4.  Click a second(3) to see  all active sessions at that point.
     (4) in the figure below represents the time when zamong agent gathered data on oracle db server.



5.  Drag  and click the “ZOOM” button for drill down analysis.



6.  Minutes to Seconds conversion.

     Set time interval to less than 30 minutes and click the button.

    < x-axis unit  :  one minute >

    < x-axis unit  : one second >



7.  Top queries , wait events and sessions on the selected period.

     Drag a range (1).
     Click the ‘Go summarize..’ button (2) to redraw chart for the specified range.
     (3) in the figure below represents the time range to summarize.



8.  Drill down analysis example using ‘Analyze’ Tap.

     Drag a range (1).
     Click the ‘ZOOM’ button (3).
     Chart is zoomed in (4).


     To apply new selected range, drag the range and click the ‘Go summarize ..’ button again.
     Double click a sqlid or session for details.

Zamong dashboard description

X-axis unit : one second,     mark -> ‘mi:ss’
The metrics : oracle instance statistics or wait events

    • Session count  (total session count or active session count)
      y-axis unit  :  one


    • CPU usage
      y-axis unit : %


    • DB Wait Time
      y-axis unit :  one hundredth of a second


    • User calls
      y-axis unit : one  ( count )


    • Execute Count
      y-axis unit : one


    • Active Session History  ( active session & wait class )
      y-axis unit : one  ( active session count)


    • Session Logical Reads
      y-axis unit :   one thousand  ( count )

      50 in the chart below actually represents  50000 ‘Session Logical Reads counts’
      Session logical read size  =  a  y-axis value  * 1000  * 8kb   ( oracle block size : 8kb )


    • Physical Reads
      y-axis unit :  one   (  count )

      Physical read size =  a  y-axis value  * 8kb


    • Parse Count ( total  or  hard )
      y-axis unit :   one


    • Full Scan
      y-axis unit :  one hundredth of a second


    • Log File Sync
      y-axis unit :  one hundredth of a second


    • Active Session History by Wait Type
      y-axis unit :  one ( active session  count )


  • Active Sessions that are running now
    You can get session details by double-clicking on a row.

How to view zamong web pages on first access

1. Open Chrome Web browser and enter the URL : http://http_server_ip_address/zamong

  •  Zamong Web Dashboard only supports the Chrome browser.
  • To view sql text with multibyte language character sets such as Korean,Chinese or Japanese,   you need to add ‘Chrome Set Character Encoding extension’ on your chrome browser.  See this link in your Chrome browser.
  • If your oracle database have multibyte language characters, Change encoding option with ‘Chrome Set Character Encoding extension’ in chrome browser when you start zamong web dashboard.


2. Add db alias (oracle database alias)

Refer to this link for “db alias”.

ORA-01440 : column to be modified must be empty to decrease precision or scale

oracle number data type format :  number( precision, scale)

Precision 4, scale 2   : 99.99
Precision 10, scale 0 : 9999999999
Precision 8, scale 3   : 99999.999
Precision 5, scale -3  : 99999000

How to modify  column number(10)  to number(10,2)

Column number(10,2) has  2 decimal places of precision.
The 2 digits of precision being added to the column would presumably come at the cost of precision to the left of the decimal place.
Hence, ORA-01440 error  occurs

you have to increase the precision (10)  by 2
” alter table test_table1  modify (  a(12,2)  ) ;  ”

To modify  column number(6,2)  to number( 6,3 )

you have to increase the precision (6)  by 1
” alter table test_table2  modify ( 7,3)  ) ;  “

Table Description for using zamong

1) dbalias_ins_yyyymmdd

This table is daily created in  a database (mysql or mariadb) used by zamong server.
Zamong inserts delta value of instance statistics every second  into dbalias_ins_yyyymmdd.
If dbalias is “salesdb”, Table name will be salesdb_ins_yyyymmdd ( e.g. 20180101 )
dbalias_ins_yyyymmdd table has following columns.

Column a10 ~ a190 : oracle statistics
Column b1 ~ a70 : oracle wait event

intime Time when data was inserted into mariadb(mysql) on zamong server
sdt Time when zamong agent gathered data on oracle db server.
a1 Number of oracle database sessions ( background + user )
a2 Number of active sessions ( not include background process session)
a3 “User” category on cpu resource usage stats. (%)
a4 “Sys” category on cpu resource usage stats. (%)
a5 “Wait-io” category on cpu resource usage stats. (%)
Total CPU Utilization is a3 + a4 + a5.
a6 Memory resource usage stats that didn’t include size of file cache. (%)
a7 Memory resource usage stats  that is used in file cache. (%)
Total Memory Utilization is a6 + a7.
a8 reserved column
a9 reserved column
a10 reserved column
a11 opened cursors current
a12 user commits
a13 user rollbacks
a14 user calls
a15 recursive calls
a16 session logical reads
a17 CPU used when call started
a18 CPU used by this session
a19 DB time
a20 cluster wait time
a21 concurrency wait time
a22 application wait time
a23 user I/O wait time
a24 session pga memory
a25 enqueue timeouts
a26 enqueue waits
a27 enqueue requests
a28 enqueue releases
a29 global enqueue gets sync
a30 global enqueue gets async
a31 global enqueue get time
a32 global enqueue releases
a33 physical read total bytes
a34 physical write total bytes
a35 global enqueue CPU used by this session
a36 db block gets
a37 db block gets from cache
a38 db block gets direct
a39 consistent gets
a40 consistent gets from cache
a41 consistent gets – examination
a42 consistent gets direct
a43 physical reads
a44 physical reads cache
a45 physical reads direct
a46 physical read IO requests
a47 physical read bytes
a48 db block changes
a49 consistent changes
a50 physical writes
a51 physical writes direct
a52 physical writes from cache
a53 physical write IO requests
a54 physical write bytes
a55 DBWR checkpoint buffers written
a56 DBWR thread checkpoint buffers written
a57 DBWR tablespace checkpoint buffers written
a58 DBWR parallel query checkpoint buffers written
a59 DBWR object drop buffers written
a60 DBWR transaction table writes
a61 DBWR undo block writes
a62 DBWR revisited being-written buffer
a63 DBWR lru scans
a64 DBWR checkpoints
a65 redo synch writes
a66 redo synch time
a67 free buffer requested
a68 dirty buffers inspected
a69 pinned buffers inspected
a70 free buffer inspected
a71 commit cleanout failures: write disabled
a72 commit cleanout failures: block lost
a73 commit cleanout failures: cannot pin
a74 commit cleanout failures: hot backup in progress
a75 commit cleanout failures: buffer being written
a76 commit cleanouts
a77 CR blocks created
a78 current blocks converted for CR
a79 switch current to new buffer
a80 physical reads direct (lob)
a81 physical reads direct temporary tablespace
a82 physical writes direct (lob)
a83 physical writes direct temporary tablespace
a84 physical reads for flashback new
a85 redo blocks read for recovery
a86 redo entries
a87 redo size
a88 redo buffer allocation retries
a89 redo writes
a90 redo blocks written
a91 redo write time
a92 redo log space requests
a93 redo log space wait time
a94 redo ordering marks
a95 gc cr blocks served
a96 gc current blocks served
a97 gc cr blocks received
a98 gc current blocks received
a99 gc blocks lost
a100 gc claim blocks lost
a101 gc blocks corrupt
a102 gc CPU used by this session
a103 background checkpoints started
a104 background checkpoints completed
a105 flashback log writes
a106 undo change vector size
a107 transaction tables consistent reads – undo records applied
a108 transaction tables consistent read rollbacks
a109 data blocks consistent reads – undo records applied
a110 cleanouts only – consistent read gets
a111 rollbacks only – consistent read gets
a112 cleanouts and rollbacks – consistent read gets
a113 rollback changes – undo records applied
a114 transaction rollbacks
a115 immediate (CURRENT) block cleanout applications
a116 immediate (CR) block cleanout applications
a117 deferred (CURRENT) block cleanout applications
a118 cleanout – number of ktugct calls
a119 immediate CR cleanouts (index blocks)
a120 deferred CUR cleanouts (index blocks)
a121 auto extends on undo tablespace
a122 drop segment calls in space pressure
a123 total number of undo segments dropped
a124 doubling up with imu segment
a125 tune down retentions in space pressure
a126 steps of tune down ret. in space pressure
a127 space was found by tune down
a128 space was not found by tune down
a129 commit batch/immediate requested
a130 commit batch requested
a131 commit immediate requested
a132 commit batch/immediate performed
a133 commit batch performed
a134 commit immediate performed
a135 commit wait/nowait requested
a136 commit nowait requested
a137 commit wait requested
a138 commit wait/nowait performed
a139 commit nowait performed
a140 commit wait performed
a141 undo segment header was pinned
a142 total number of times SMON posted
a143 SMON posted for undo segment recovery
a144 SMON posted for txn recovery for other instances
a145 SMON posted for instance recovery
a146 SMON posted for undo segment shrink
a147 SMON posted for dropping temp segment
a148 IMU commits
a149 IMU Flushes
a150 IMU contention
a151 IMU recursive-transaction flush
a152 IMU undo retention flush
a153 IMU ktichg flush
a154 IMU bind flushes
a155 IMU mbu flush
a156 IMU pool not allocated
a157 IMU CR rollbacks
a158 IMU undo allocation size
a159 IMU Redo allocation size
a160 IMU- failed to get a private strand
a161 index crx upgrade (prefetch)
a162 index crx upgrade (found)
a163 index crx upgrade (positioned)
a164 leaf node splits
a165 leaf node 90-10 splits
a166 branch node splits
a167 failed probes on index block reclamation
a168 recursive aborts on index block reclamation
a169 lob reads
a170 lob writes
a171 index fast full scans (full)
a172 index fast full scans (rowid ranges)
a173 index fast full scans (direct read)
a174 queries parallelized
a175 workarea memory allocated
a176 workarea executions – optimal
a177 workarea executions – onepass
a178 workarea executions – multipass
a179 parse time cpu
a180 parse time elapsed
a181 parse count (total)
a182 parse count (hard)
a183 parse count (failures)
a184 execute count
a185 bytes sent via SQL*Net to dblink
a186 bytes received via SQL*Net from dblink
a187 sorts (memory)
a188 sorts (disk)
a189 reserved column
a190 reserved column
b1 latch: cache buffers chains
b2 buffer busy waits
b3 gc buffer busy acquire
b4 gc buffer busy release
b5 read by other session
b6 enq: RO – fast object reuse
b7 enq: KO – fast object checkpoint
b8 log file sequential read
b9 log file single write
b10 log file parallel write
b11 log buffer space
b12 log file switch (checkpoint incomplete)
b13 log file switch (private strand flush incomplete)
b14 log file switch (archiving needed)
b15 log file switch completion
b16 log file sync
b17 db file sequential read
b18 db file scattered read
b19 db file single write
b20 db file parallel write
b21 db file async I/O submit
b22 db file parallel read
b23 gc current request
b24 gc cr request
b25 gc cr multi block request
b26 gc current multi block request
b27 gc cr block 2-way
b28 gc cr block busy
b29 gc current block 2-way
b30 gc current block busy
b31 gc remaster
b32 direct path read
b33 direct path read temp
b34 direct path write
b35 direct path write temp
b36 enq: TM – contention
b37 undo segment extension
b38 undo segment tx slot
b39 enq: TX – row lock contention
b40 enq: TX – allocate ITL entry
b41 enq: TX – index contention
b42 latch: In memory undo latch
b43 enq: HW – contention
b44 sort segment request
b45 enq: SQ – contention
b46 latch: row cache objects
b47 row cache lock
b48 cursor: mutex X
b49 cursor: mutex S
b50 cursor: pin X
b51 cursor: pin S
b52 cursor: pin S wait on X
b53 latch: shared pool
b54 library cache pin
b55 library cache lock
b56 library cache load lock
b57 library cache: mutex X
b58 library cache: mutex S
b59 SQL*Net message to dblink
b60 SQL*Net more data to dblink
b61 SQL*Net message from dblink
b62 SQL*Net more data from dblink
b63 latch free
b64 free buffer waits
b65 local write wait
b66 checkpoint completed
b67 write complete waits
b68 reserved column
b69 reserved column
b70 reserved column

Zamong Dashboard View use intime column to draw  charts. But Zamong Clues View use sdt colum to  draw  charts.

The units of the columns below used by zamong are differ from oracle manual.

a24 : session pga memory  (kb)
a33 : physical read total bytes (kb)
a34 : physical write total bytes  (kb)
a47 : physical read bytes  (kb)
a54 : physical write bytes  (kb)
a87 : redo size  (kb)
a106 : undo change vector size  (kb)
a158 : IMU undo allocation size  (kb)
a159 : IMU Redo allocation size  (kb)
a175 : workarea memory allocated  (kb)
a185 : bytes sent via SQL*Net to dblink  (kb)
a186 : bytes received via SQL*Net from dblink  (kb)a19 : DB time (  hundredths of a second )
a20 : cluster wait time ( hundredths of a second )
a21 : concurrency wait time ( hundredths of a second )
a22 : application wait time ( hundredths of a second )
a23 : user I/O wait time ( hundredths of a second )b1 ~ b70 : TIME_WAITED in v$system_event, v$session_event ( hundredths of a second )The units of other columns is as shown in the oracle manual.


2) dbalias_as_yyyymmdd

This table is created daily.
Zamong inserts oracle active session details every second into dbalias_as_yyyymmdd.
If dbalias is “salesdb”, Table name will be salesdb_as_yyyymmdd ( e.g. 20180101 )
dbalias_as_yyyymmdd table has following columns.

intime Time when data was inserted into
mariadb(mysql) on zamong server
 sdt Time when zamong agent gathered data on oracle db server.
 sid v$session : sid
 qstime .
 seqnum .
 serial v$session : serial#
 status v$session : status
 stype v$session : type
 duration v$session : last_call_et
 logontime v$session : logon_time
 ospid v$process :  spid
 dbuser v$session : username
 command v$session : command
 event v$session : event
 eventclass v$event_name : wail_class
 eventgb .
 p1txt v$session : p1text
 p1 v$session : p1
 p2txt v$session : p2text
 p2 v$session : p2
 p3txt v$session : p3text
 p3 v$session : p3text
 p1hex v$session : p1raw
 p2hex v$session : p2raw
 p3hex v$session : p2raw
 sqlid v$session : sql_id
 sql_hash v$session : sql_hash_value
 sql_child_num v$session : sql_child_number
 psqlid v$session : prev_sql_id
 psql_hash v$session : prev_hash_value
 psql_child_num v$session : prev_child_number
 osuser v$session : osuser
 machine v$session : machine
 terminal v$session : termianl
 program v$session : program
 module v$session : module
 action v$session : action
 w_file_num v$session : row_wait_file#
 w_block_num v$session : row_wait_block#
 w_row_num v$session : row_wait_row#
 w_obj_num v$session : row_wait_obj#
 failover_type v$session : failover_type
 failover_method v$session : failover_method
 failed_over v$session : failed_over
 b_yn .
 b_sid .
 s1 .
 s2 .
 s3 .
 s4 .
 s5 .
 s6 .
 s7 .
 s8 .
 s9 .
 s10 .
 s11 .
 s12 .
 s13 .
 s14 .
 s15 .
 s16 .
 s17 .
 s18 .
 s19 .
 s20 .
 sqltext sql query text that is currently running
 psqltext sql query that was just executed


Zamong is a free tool for real time oracle monitoring and failure analysis


Zamong is a tool for real time oracle monitoring using direct sga memory access method. So zamong does not connect to db for db monitoring. It collects Oracle DB internal status(v$sysstat, v$system_event) and active session details(v$session, sql-text .. ) using direct memory access method without accessing Oracle DB.

The advantage of this software is that there is no load on the server at all. Direct memory access is really fast and light enough to be done more than 100 times per second.

Query based DB monitoring method is performed by executing many recursive queries internally in the DB in order to execute queries for db monitoring. These resursive queries can cause latch contention in the shared pool.

Query based DB monitoring method can be used if these indicators are only normal, such as the server’s query response time, cpu usage, active session count, etc.
However, as the db response time increases, the number of active sessions increases, using a query based DB monitoring method is dangerous because it can further increase the latch contention in the shared pool.

Running queries for db monitoring every second will increase the latch, In more serious cases, you can cause a db instance hang.

So DMA method for oracle db monitoring is the best solution.

Database instance statistics are displayed as several real time charts in zamong dashboard and details of real time active sessions are also displayed in the dashboard.
these active sessions are refreshed every 2 seconds and you can see the session details by double clicking a active session in the dashboard.
zamong is also used to analyze the cause of the failure at a point in time.
you specify a time range for analyzing failure, and then you can check details of active sessions on every second in the range.

The DMA method is very powerful. Even if a oracle database is in hang state, zamong can store all information of each session in sga.
So you can use zamong for failure analysis or performance degradation analysis.



Refer to this link for a use case



linux : Unlock SSH Failed Login Attempts and disable account expiration

1) unlock oracle user:
   centos 5.x
faillog -u oracle -r o

   centos 6.x
pam_tally2 -u oracle -r

pam_tally2 module is used to lock user accounts after certain number of failed ssh login attempts made to the system. This module keeps the count of attempted accesses and too many failed attempts.

2) disable account password expiration:
chage -d 0 oracle

Zamong is free oracle monitoring software with DMA method.

Zamong은 SGA 메모리를 직접 액세스 하는 방법을 사용하는 무료의 실시간 오라클 모니터링 솔루션입니다.

Zamong is free oracle monitoring software with DMA method.


이 솔루션은 쿼리를 사용하지 않고, 모니터링 할 정보를 OS 메모리에서 직접 읽어오기 때문에 오라클 장애 순간에도,
심지어 ORACLE이 HANG 상태에 있어도, 오라클 instance 의 상태와 지표값, 실행되고 있는 sql 쿼리를 비롯하여  SGA에 올라와 있는 각 세션의 모든 정보를 os 메모리에서 읽어 저장할 수 있습니다.

메모리에서 직접 읽는 방법( DMA )은 매우 빠르고 부하가 거의 없는게 특징입니다.
실제로 테스트해보면 1초에 100 회 이상 OS 메모리 읽으면서 오라클 관련 중요 정보를 추출할 수 있을 정도입니다.
하지만 성능분석이나 장애 원인 분석을 위한 데이터 추출은 1초에 1번 추출하는것으로도 충분합니다.

일반적인 쿼리 방식의 모니터링 솔루션은 주기적으로 쿼리를 실행시켜 DB 인스턴스 나 Active Session의 상세 정보를 추출하는 방법을 사용하는데, 이 방법은 매우 위험할 수 있습니다.
쿼리 수도 많고, 또 쿼리를 수행하기위해 DB에서 내부적으로 많은 recursive 쿼리가 실행되고, 이러한 recursive 쿼리는 shared pool에서 latch 경합을 일으킬 수 있습니다. 더구나 cpu 사용률이 높고, 실시간 트랜잭션이 많은 계정계 db나 쇼핑몰 db, PG VAN사의 결제 db 등 민감한 db에서는 이런 쿼리 방식의 모니터링은 매우 위험합니다.
예전 얘기지만 Oracle 9i 때는 v$session를 반복적으로 조회하는 것만으로도 shared pool 내의 latch를 유발시켜 instance hang 까지 발생했었습니다.

Zamong 을 사용하면 대시보드에서 DB instance의 중요 통계(stat) 항목을 실시간으로 차트로 보여주고, 또 Active 세션들의 상세 정보도 2초 단위로 refresh 해줍니다. 이렇게 실시간적으로 주요 statistics 변화 추이를 차트로 보여주기 때문에 db에 어떤 성능 지연이 나타나면 바로 인지할 수 있고, 하단에 보여주는 active session 정보까지 같이 확인하면, 어떤 세션들로 인해 db에 부하가 가고 있는지 직관적으로 확인 할 수 있습니다. 무엇보다도 1초 간격으로 모니터링 된 정보들이 수집서버로 전송되고 저장되고 있어, 과거 장애 난 시점의 각 엑티브 세션들의 Wait Event와 쿼리, 프로그램 등 상세정보들을 분석 뷰에서 1초 간격으로 시간을 이동시키면서 분석할 수 있습니다. 그래서 세션들의 전/후 움직임을 직접 확인 할 수 있습니다. 간단하게 설명드리면 어느 서버의 어떤 프로그램에서 db에 접속했고 어떤 쿼리를 실행시켰고 그 쿼리가 어떤 wait event를 발생시키고 있고, 어느 데이터 파일의 어느 블럭을 엑세스 하고 있는지 체크가 가능하며, 그 시점의 백그라운드 프로세스의 엑티브한 wait event까지 확인이 가능합니다.
바로 빠른 장애 원인 분석과 성능 저하 분석을 위한 툴입니다.

이 툴의 최고 장점은 응답지연이나 장애 원인의 명확한 증거를 찾아낼 때 입니다.
지금까지는 과거 시점에 장애 원인이나 성능저하를 분석하기위해서 고가의 모니터링 솔루션을 이용하거나, 그런 모니터링 솔루션이 없다면 ASH( active session history) 뷰를 조회하는 방법을 사용하곤 했습니다.
ASH 뷰 하나면 믿고 DB를 운영하는 DBA는 없을 겁니다. 정작 중요시점의 정보는 조회해도 없을 때가 많다는 것을 대부분의 DBA는 알고 있기 때문입니다.
그리고 ASH( active session history) 기능을 사용하려면 Enterprise edition 라이선스 외에 Diagnostics pack 이라는 추가적인 라이선스가 필요합니다.  이 부분은 DBA에게 매우 중요한 포인트인데,  만일 Standard edition일 경우에 ASH( active session history) 기능을 enable 해서 사용할 경우,  요즘 핫하게 이슈되고 있는 LMS의 타겟이 될 수 있습니다. 이 경우는 diagnostics pack 뿐만이 아니라 enterprise edition 라이선스 위반이 되기 때문에 standard edition 의 구매 금액대와는 차원이 다르게 심각해질 수 있게 됩니다.   이 부분은 여기를 참조해보세요 https://docs.oracle.com/database/121/DBLIC/options.htm#DBLIC165

간단한 사용 방법 링크.