‘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”.

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