Home » RDBMS Server » Performance Tuning » dbms_addm.real_time_addm_report (DB 12.x)
dbms_addm.real_time_addm_report [message #623158] Fri, 05 September 2014 05:18 Go to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
This function returns a report on the last five minutes, supposedly even if your database instance is all hung up. Sounds useful. But how is one supposed to display it? Googling around finds references from people implying that they know what it does, but they never show how they actually format the output.
Any ideas?
orclz>
orclz>
orclz> set long 100000
orclz>
orclz> select dbms_addm.real_time_addm_report from dual;

REAL_TIME_ADDM_REPORT
--------------------------------------------------------------------------------
<report db_version="12.1.0.1.0" cpu_cores="2" hyperthread="Y" timezone_offset="3
600" packs="2"><report_id><![CDATA[/orarep/rtaddm/main]]></report_id><sql_data><
report_metadata instance_number="0" date_format="HH24:MI:SS MM/DD/YYYY" start_ti
me="10:39:00 09/05/2014" report_time="10:44:32 09/05/2014"/><system_config><data
base dbid="2079796645" name="ORCLZ" log_mode="ARCHIVELOG" open_mode="READ WRITE"
 role="PRIMARY" force_log="NO" platform="Microsoft Windows x86 64-bit" flashback
_on="NO" unique_name="orclz" version="12.1.0.1.0"/><instances><instance number="
1" name="orclz" host="JWVAIO" version="12.1.0.1.0" startup_time="14:51:20 09/04/
2014" status="OPEN" logins="ALLOWED" shutdown_pending="NO" role="PRIMARY_INSTANC
E" active="NORMAL"/></instances></system_config><hangs><sessions/><blockers/><ha
ng_info/></hangs><metrics><metric_names><map name="User Transaction Per Sec" uni
t="Transactions Per Second" id="1"/><map name="Logons Per Sec" unit="Logons Per
Second" id="2"/><map name="User Commits Per Sec" unit="Commits Per Second" id="3
"/><map name="User Rollbacks Per Sec" unit="Rollbacks Per Second" id="4"/><map n
ame="Network Traffic Volume Per Sec" unit="Bytes Per Second" id="5"/><map name="
CPU Usage Per Sec" unit="CentiSeconds Per Second" id="6"/><map name="Physical Re
ad Total Bytes Per Sec" unit="Bytes Per Second" id="7"/><map name="GC CR Block R

Re: dbms_addm.real_time_addm_report [message #623169 is a reply to message #623158] Fri, 05 September 2014 08:18 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3131
Registered: May 2013
Location: World Wide on the Web
Senior Member
Documentation only says, Quote:

Note that the return type of a report is a CLOB, formatted to fit line size of 80.


I couldn't find anything else about formatting the report.
Re: dbms_addm.real_time_addm_report [message #623189 is a reply to message #623158] Fri, 05 September 2014 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It seems to be an xml file.
Can you post or PM me the complete result, I will try something to display it.

Re: dbms_addm.real_time_addm_report [message #623190 is a reply to message #623189] Fri, 05 September 2014 11:03 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
Thank you for replying both of you, here is the spooled result.
Re: dbms_addm.real_time_addm_report [message #623191 is a reply to message #623190] Fri, 05 September 2014 11:04 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
I just rememnered - possible problems with files. Here it is posted in line:


orclz> select dbms_addm.real_time_addm_report from dual;

REAL_TIME_ADDM_REPORT
--------------------------------------------------------------------------------
<report db_version="12.1.0.1.0" cpu_cores="2" hyperthread="Y" timezone_offset="3
600" packs="2"><report_id><![CDATA[/orarep/rtaddm/main]]></report_id><sql_data><
report_metadata instance_number="0" date_format="HH24:MI:SS MM/DD/YYYY" start_ti
me="16:56:00 09/05/2014" report_time="17:01:39 09/05/2014"/><system_config><data
base dbid="2079796645" name="ORCLZ" log_mode="ARCHIVELOG" open_mode="READ WRITE"
 role="PRIMARY" force_log="NO" platform="Microsoft Windows x86 64-bit" flashback
_on="NO" unique_name="orclz" version="12.1.0.1.0"/><instances><instance number="
1" name="orclz" host="JWVAIO" version="12.1.0.1.0" startup_time="14:10:20 09/05/
2014" status="OPEN" logins="ALLOWED" shutdown_pending="NO" role="PRIMARY_INSTANC
E" active="NORMAL"/></instances></system_config><hangs><sessions/><blockers/><ha
ng_info/></hangs><metrics><metric_names><map name="User Transaction Per Sec" uni
t="Transactions Per Second" id="1"/><map name="Logons Per Sec" unit="Logons Per
Second" id="2"/><map name="User Commits Per Sec" unit="Commits Per Second" id="3
"/><map name="User Rollbacks Per Sec" unit="Rollbacks Per Second" id="4"/><map n
ame="Network Traffic Volume Per Sec" unit="Bytes Per Second" id="5"/><map name="
CPU Usage Per Sec" unit="CentiSeconds Per Second" id="6"/><map name="Physical Re
ad Total Bytes Per Sec" unit="Bytes Per Second" id="7"/><map name="GC CR Block R
eceived Per Second" unit="Blocks Per Second" id="8"/><map name="GC Current Block
 Received Per Second" unit="Blocks Per Second" id="9"/><map name="Global Cache A
verage CR Get Time" unit="CentiSeconds Per Get" id="10"/><map name="Global Cache
 Average Current Get Time" unit="CentiSeconds Per Get" id="11"/><map name="Globa
l Cache Blocks Lost" unit="Blocks" id="12"/><map name="Current Logons Count" uni
t="Logons" id="13"/><map name="Current Open Cursors Count" unit="Cursors" id="14
"/><map name="Process Limit %" unit="% Processes/Limit" id="15"/><map name="Sess
ion Limit %" unit="% Sessions/Limit" id="16"/><map name="Executions Per Sec" uni
t="Executes Per Second" id="17"/><map name="Database Time Per Sec" unit="CentiSe
conds Per Second" id="18"/><map name="Physical Write Total Bytes Per Sec" unit="
Bytes Per Second" id="19"/><map name="Host CPU Utilization (%)" unit="% Busy/(Id
le+Busy)" id="20"/><map name="Current OS Load" unit="Number Of Processes" id="21
"/><map name="Average Synchronous Single-Block Read Latency" unit="Milliseconds"
 id="22"/><map name="Background CPU Usage Per Sec" unit="CentiSeconds Per Second
" id="23"/><map name="Total PGA Allocated" unit="bytes" id="24"/><map name="Run
Queue Per Sec" unit="Processes" id="25"/><map name="VM in bytes Per Sec" unit="b
ytes per sec" id="26"/><map name="VM out bytes Per Sec" unit="bytes per sec" id=
"27"/></metric_names><data><m i="1" v=".072" ma=".233" mi=".033" id="1"/><m i="1
" v=".086" ma=".183" mi=".033" id="2"/><m i="1" v=".072" ma=".233" mi=".033" id=
"3"/><m i="1" v="0" ma="0" mi="0" id="4"/><m i="1" v="0" ma="0" mi="0" id="5"/><
m i="1" v=".091" ma=".364" mi="0" id="6"/><m i="1" v="51154.435" ma="105162.038"
 mi="34104.913" id="7"/><m i="1" v="0" ma="0" mi="0" id="8"/><m i="1" v="0" ma="
0" mi="0" id="9"/><m i="1" v="0" ma="0" mi="0" id="10"/><m i="1" v="0" ma="0" mi
="0" id="11"/><m i="1" v="0" ma="0" mi="0" id="12"/><m i="1" v="36.5" ma="39" mi
="36" id="13"/><m i="1" v="61" ma="61" mi="61" id="14"/><m i="1" v="18.5" ma="19
.333" mi="18.333" id="15"/><m i="1" v="11.123" ma="11.653" mi="11.017" id="16"/>
<m i="1" v="33.884" ma="167.199" mi=".633" id="17"/><m i="1" v=".097" ma=".421"
mi="0" id="18"/><m i="1" v="18985.009" ma="50526.54" mi="10915.39" id="19"/><m i
="1" v="29.746" ma="32.142" mi="28.204" id="20"/><m i="1" v="0" ma="0" mi="0" id
="21"/><m i="1" v=".243" ma=".296" mi=".21" id="22"/><m i="1" v=".173" ma=".286"
 mi=".052" id="23"/><m i="1" v="193301504" ma="200827904" mi="191796224" id="24"
/><m i="1" v="0" ma="0" mi="0" id="25"/><m i="1" v="0" ma="0" mi="0" id="26"/><m
 i="1" v="0" ma="0" mi="0" id="27"/></data></metrics><ash_data sampling_ratio="1
" avg_active_sessions=".001" secs="339"><op><m i="2" n="INSERT"/><m i="3" n="SEL
ECT"/></op><sid><m v="1_250_347" i="1"/><m v="1_356_1" i="2"/><m v="1_370_23" i=
"3"/><m v="__" i="4"/></sid><evt><m c="CPU" e="CPU" i="1"/></evt><service><m h="
165959219" n="SYS$BACKGROUND" i="1"/></service><program><m v="ORACLE.EXE (M001)"
 i="1"/><m v="ORACLE.EXE (M000)" i="2"/><m v="ORACLE.EXE (PSP0)" i="3"/></progra
m><module><m v="MMON_SLAVE" i="1"/><m v="__" i="2"/></module><action><m v="Auto-
Flush Slave Action" i="1"/><m v="Auto ADDM Slave Action" i="2"/><m v="__" i="3"/
></action><sql><m f="14260733346930320590" o="3" s="bcg7084jc4um6" i="1"/><m f="
1045897954767918721" o="2" s="aznxgbvf99dax" i="2"/><m f="0" o="0" s="null" i="3
"/></sql><data><a>301,1,0,4,4,1,3,2,1,1,2,1,0</a><a>79,2,0,4,4,3,0,3,1,2,3,1,0</
a><a>81,2,0,4,4,3,0,3,1,2,3,1,0</a><a>224,2,0,4,4,3,0,3,1,2,3,1,0</a><a>300,3,0,
4,4,2,2,1,1,1,1,1,0</a></data></ash_data><osstat_pit><stat i="1" name="NUM_CPUS"
 v="4"/><stat i="1" name="NUM_CPU_CORES" v="2"/><stat i="1" name="PHYSICAL_MEMOR
Y_BYTES" v="8463335424"/></osstat_pit><memory><mem i="1" n="pga" v="200184832"/>
<mem i="1" n="shared pool" v="191675512"/><mem i="1" n="fixed_sga" v="2407584"/>
<mem i="1" n="large pool" v="20971520"/><mem i="1" n="shared_io_pool" v="3774873
6"/><mem i="1" n="SP free" v="13845384"/><mem i="1" n="buffer_cache" v="50751078
4"/><mem i="1" n="log_buffer" v="6643712"/><mem i="1" n="java pool" v="4194304"/
></memory><resize_ops><a i="1" t="GROW" m="DEFERRED" c="2" p="Y"/><a i="1" t="GR
OW" m="IMMEDIATE" c="2" p="Y"/></resize_ops><parameters><p n="active_instance_co
unt" i="1"/><p n="event" i="1"/><p n="max_dispatchers" i="1"/><p n="_column_trac
king_level" i="0">1</p><p n="cluster_database" i="0">FALSE</p><p n="cluster_data
base_instances" i="0">1</p><p n="compatible" i="0">12.1.0.0.0</p><p n="cpu_count
" i="0">4</p><p n="cursor_sharing" i="0">EXACT</p><p n="db_16k_cache_size" i="0"
>0</p><p n="db_2k_cache_size" i="0">0</p><p n="db_32k_cache_size" i="0">0</p><p
n="db_4k_cache_size" i="0">0</p><p n="db_8k_cache_size" i="0">0</p><p n="db_bloc
k_buffers" i="0">0</p><p n="db_block_checking" i="0">FALSE</p><p n="db_block_che
cksum" i="0">TYPICAL</p><p n="db_block_size" i="0">8192</p><p n="db_cache_size"
i="0">0</p><p n="db_file_multiblock_read_count" i="0">111</p><p n="db_keep_cache
_size" i="0">0</p><p n="db_recycle_cache_size" i="0">0</p><p n="db_writer_proces
ses" i="0">1</p><p n="dispatchers" i="0">(PROTOCOL=TCP) (SERVICE=orclzXDB)</p><p
 n="gcs_server_processes" i="0">0</p><p n="java_pool_size" i="0">0</p><p n="larg
e_pool_size" i="0">0</p><p n="log_buffer" i="0">6340608</p><p n="memory_max_targ
et" i="0">0</p><p n="memory_target" i="0">0</p><p n="open_cursors" i="0">300</p>
<p n="pga_aggregate_target" i="0">262144000</p><p n="processes" i="0">300</p><p
n="sessions" i="0">472</p><p n="sga_max_size" i="0">788529152</p><p n="sga_targe
t" i="0">788529152</p><p n="shared_pool_size" i="0">0</p><p n="statistics_level"
 i="0">TYPICAL</p><p n="streams_pool_size" i="0">0</p><p n="timed_os_statistics"
 i="0">0</p><p n="timed_statistics" i="0">TRUE</p><p n="undo_management" i="0">A
UTO</p></parameters></sql_data></report>

Re: dbms_addm.real_time_addm_report [message #623194 is a reply to message #623190] Fri, 05 September 2014 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is indeed a real xml file.
For the moment I didn't find a way to display it in a nice way but we can get in a hierarchical one with any browser.
For 90% of the information, it is easy to see what it then means.
First image: database and instances general information:
/forum/fa/12146/0/
"hangs" section is empty so you have no current hang.
Second image: global performances data with first section ("metric_names") giving the metric name and second section ("data") giving the values, you join the 2 sections with "id" field); now I think "i" means instance, "v" current value, "ma" max value, "mi" min value:
/forum/fa/12147/0/
Third image: sessions information from ASH, you can join each section ("sid", "op", "evt", "service", "program", "module, "action" and "sql") by their "i" field; "data" section is quite mysterious at first sight:
/forum/fa/12148/0/
Fourth image: "osstat", "memory" and "resize_opts", nothing mysterious here:
/forum/fa/12149/0/
Last image: instance parameters (with "i" instance number which is here 0 or 1 as you are not in RAC):
/forum/fa/12150/0/

I have no 12c to test so I let you investigate the mysterious parts...

  • Attachment: OraFAQ1.jpg
    (Size: 122.14KB, Downloaded 571 times)
  • Attachment: OraFAQ2.jpg
    (Size: 411.52KB, Downloaded 554 times)
  • Attachment: OraFAQ3.jpg
    (Size: 124.16KB, Downloaded 563 times)
  • Attachment: OraFAQ4.jpg
    (Size: 89.43KB, Downloaded 569 times)
  • Attachment: OraFAQ5.jpg
    (Size: 218.88KB, Downloaded 594 times)
Re: dbms_addm.real_time_addm_report [message #623210 is a reply to message #623194] Sat, 06 September 2014 03:34 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
Thank you for taking the time to look at this. Does this mean that unless I write some sort of parser that understands the tags and can display their contents nicely, it is not going to be possible to see what is going on?

It would seem likely that Larry does not want us to look at this - unless we have installed Cloud Control. Database Express does have a section named Real-Time ADDM Reports, but I can't see how to generate one. Very annoying.

I'll put a question on the OTN Forums, I dread doing that because one always gets bombarded with comments from idiots asking "why are you doing this?" or making suggestions that are totally useless.
Re: dbms_addm.real_time_addm_report [message #623217 is a reply to message #623210] Sat, 06 September 2014 05:26 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From the moment I don't see any other way than writing your own parser. Maybe Oracle will provide one sooner or later. Let us know the answers from OTN.
I think with external table and xml features (xmltable and xmlquery) it can be not so much difficult to do it.
Maybe you can ask Solomon he is an expert in these features.

Re: dbms_addm.real_time_addm_report [message #623226 is a reply to message #623217] Sat, 06 September 2014 10:11 Go to previous message
Lalit Kumar B
Messages: 3131
Registered: May 2013
Location: World Wide on the Web
Senior Member
Completely agree with Michel's suggestion. Even I had the same thing in my mind, Solomon is really awesome with his skills on XML stuff.

I would also suggest to post the question in Stack Overflow, as experts like Justin Cave are quite active. I agree that there are stupids who just ask silly questions instead of a relevant answer, and we see it in all the forums. But, a good thing in Stack Overflow is that, an answer is kept seperate from suggestions. If anybody attempts to post an irrelevant answer or a silly suggestion, it is immediately marked with down vote(s). You could have a try.
Previous Topic: Update non-token column on compressed table
Next Topic: Materialized view
Goto Forum:
  


Current Time: Sun Oct 21 22:22:55 CDT 2018