ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
prvt_awr_viewer.adjust_realtime_input_times(
p_start_time IN OUT DATE,
p_end_time IN OUT DATE,
p_source OUT VARCHAR2,
p_duration IN NUMBER);
DECLARE
pstart DATE := SYSDATE;
pend DATE := SYSDATE+1;
psrc VARCHAR2(30);
BEGIN
prvt_awr_viewer.adjust_realtime_input_times(pstart, pend, psrc, 10);
dbms_output.put_line(psrc);
END;
/ history
prvt_awr_viewer.awr_rmmetrics_xml(
p_plan_name IN VARCHAR2,
p_awr_period IN OUT PRVT_AWRV_METADATA,
p_bucket_map IN OUT PRVT_AWRV_MAPTAB,
p_bucket_interval IN NUMBER,
p_show_sql IN NUMBER,
p_idname_map IN OUT PRVT_AWRV_INSTTAB,
output_xml IN OUT XMLTYPE);
prvt_awr_viewer.awr_sysmetrics_xml(
awr_period IN OUT PRVT_AWRV_METADATA,
bucket_map IN OUT PRVT_AWRV_MAPTAB,
p_bucket_max_count IN NUMBER,
p_bucket_interval IN NUMBER,
p_metric_type IN VARCHAR2,
p_show_sql IN NUMBER,
output_xml OUT XMLTYPE);
prvt_awr_viewer.awr_sysstat_totalstat_xml(
awr_period IN OUT PRVT_AWRV_METADATA,
bucket_map IN OUT PRVT_AWRV_MAPTAB,
p_bucket_max_count IN NUMBER,
p_bucket_interval IN NUMBER,
p_show_sql IN NUMBER,
p_stat_desc IN VARCHAR2,
output_xml OUT XMLTYPE);
prvt_awr_viewer.create_bucket_snap_map(
p_awr_period IN OUT PRVT_AWRV_METADATA,
p_bucket_map IN OUT PRVT_AWRV_MAPTAB,
p_bucket_max_count IN OUT NUMBER,
p_bucket_calc_interval OUT NUMBER);
prvt_awr_viewer.fet_topsql_xml(
start_time IN DATE,
end_time IN DATE,
inst_id IN NUMBER,
dbid IN NUMBER,
is_realtime IN NUMBER,
top_n_detail IN NUMBER,
outer_start_time IN DATE,
outer_end_time IN DATE,
compress_xml IN BINARY_INTEGER,
show_sql IN NUMBER,
db_tz IN VARCHAR2,
is_omx IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.generate_buckets_tag2(p_buckets IN VARCHAR2)
RETURN VARCHAR2;
SELECT prvt_awr_viewer.generate_buckets_tag2('Testing')
FROM dual;
PRVT_AWR_VIEWER.GENERATE_BUCKETS_TAG2('TESTING')
---------------------------------------------------------------------------------
nvl2(Testing, xmlelement("buckets", xmlattributes( :p_bucket_interval as "bucket_interval", :p_bucket_count as "bucket_count", :b_min_time as "start_time", :b_max_time as "end_time"
, :b_duration as "duration"),Testing), null)
prvt_awr_viewer.get_bucket_map(
p_start_time IN DATE,
p_end_time IN DATE,
p_inst_id IN NUMBER,
p_dbid IN NUMBER,
p_bucket_max_count IN OUT NUMBER,
p_bucket_interval OUT NUMBER,
p_awr_period OUT prvt_awrv_metadata;
p_bucket_map IN OUT prvt_awrv_maptab);
SELECT dbid
FROM v$database;
DBID
----------
2140826538
DECLARE
a DATE := SYSDATE-1;
b DATE := SYSDATE;
c NUMBER := 1;
d NUMBER := 428676178;
e NUMBER := 5;
f NUMBER;
g prvt_awrv_metadata;
h prvt_awrv_maptab;
BEGIN
prvt_awr_viewer.get_bucket_map(a,b,c,d,e,f,g,h);
prvt_awr_viewer.get_rmmetrics_xml(
is_realtime IN NUMBER,
start_time IN DATE,
end_time IN DATE,
bucket_count IN NUMBER,
bucket_interval IN NUMBER,
inst_id_low IN NUMBER,
inst_id_high IN NUMBER,
awr_period IN sys.prvt_awrv_metadata,
show_sql IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.get_sql_n_parse_time_xml(
is_realtime IN NUMBER,
start_time_UTC IN DATE,
end_time_UTC IN DATE,
bucket_count IN NUMBER,
bucket_interval IN NUMBER,
inst_id_low IN NUMBER,
inst_id_high IN NUMBER,
awr_period IN sys.prvt_awrv_metadata,
show_sql IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.get_sysmetrics_xml(
is_realtime IN NUMBER,
start_time_utc IN DATE,
end_time_utc IN DATE,
bucket_count IN NUMBER,
bucket_interval IN NUMBER,
inst_id_low IN NUMBER,
inst_id_high IN NUMBER,
awr_period IN sys.prvt_awrv_metadata,
show_sql IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.get_wait_time_xml(
is_realtime IN NUMBER,
start_time_utc IN DATE,
end_time_utc IN DATE,
bucket_count IN NUMBER,
bucket_interval IN NUMBER,
inst_id_low IN NUMBER,
inst_id_high IN NUMBER,
awr_period IN sys.prvt_awrv_metadata,
show_sql IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.report_addm_xml(
start_time IN DATE,
end_time IN DATE,
num_days IN NUMBER,
owner IN VARCHAR2,
task_name IN VARCHAR2,
section IN VARCHAR2,
spotrep IN VARCHAR2,
spotlist IN VARCHAR2,
inst_id IN NUMBER,
db_id IN NUMBER,
show_sql IN NUMBER,
top_n_detail IN NUMBER,
compress_xml IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.report_awrrep_xml(
selected_start_time IN DATE,
selected_end_time IN DATE,
inst_id IN NUMBER,
dbid IN NUMBER,
compress_xml IN BINARY_INTEGER,
report_reference IN VARCHAR2)
RETURN XMLTYPE;
prvt_awr_viewer.report_awr_xml(
start_time IN DATE,
end_time IN DATE,
instance_list IN VARCHAR2,
dbid IN NUMBER,
member_id IN NUMBER,
bucket_max_count IN NUMBER,
time_model IN VARCHAR2,
wait_class IN VARCHAR2,
wiat_event IN VARCHAR2,
event_class IN VARCHAR2,
sysstat IN VARCHAR2,
sqlstat IN VARCHAR2,
osstat IN VARCHAR2,
iostat IN VARCHAR2,
memory IN VARCHAR2,
space IN VARCHAR2,
key_statistics IN VARCHAR2,
summary IN VARCHAR2,
inst_detail IN VARCHAR2,
members IN VARCHAR2,
timepicker_start IN DATE,
timepicker_end IN DATE,
report_level IN VARCHAR2,
show_sql IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.report_containers_xml(
p_start_time IN DATE,
p_end_time IN DATE,
p_last_refresh_time IN DATE,
p_inst_id IN NUMBER,
p_dbid IN NUMBER,
p_IS_realtime IN NUMBER,
p_top_n_count IN NUMBER,
p_top_n_rankby IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.report_metrics_xml(
start_time IN DATE,
end_time IN DATE,
inst_id IN NUMBER,
dbid IN NUMBER,
bucket_max_count IN NUMBER,
bucket_interval IN NUMBER,
inst_detail IN VARCHAR2,
report_level IN VARCHAR2,
show_sql IN NUMBER)
RETURN XMLTYPE;
CREATE TABLE t (testcol XMLTYPE);
Table created.
SELECT dbid
FROM v$database;
DBID
----------
2140826538
DECLARE
a DATE := SYSDATE-1;
b DATE := SYSDATE;
c NUMBER := 1;
d NUMBER := 2140826538;
e NUMBER := 5;
f NUMBER := 1;
g VARCHAR2(30); -- inst_detail
h VARCHAR2(10) := 'TYPICAL';
i NUMBER := 1; -- show_sql
x XMLTYPE;
BEGIN
x := prvt_awr_viewer.report_metrics_xml(a,b,c,d,e,f,g,h,i);
INSERT INTO t VALUES (x);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SELECT * FROM t;
TESTCOL
----------------------------------------------------------------------------
<report db_version="21.0.0.0.0" elapsed_time="0.09" cpu_time="0.07" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" packs="2" service_type="0">
<report_id>
<![CDATA[/orarep/perfpage/main%3fdbid%3d2140826538%26end_time%
3d06%3a20%3a2021%2019%3a20%3a24%
26inst_id%3d1%26report_level%3dTYPICAL%26start_time%3d06%3a19%3a2021%2019%
3a20%3a24]]>
</report_id>
<report_parameters>
<start_time>06/19/2021 19:20:24</start_time>
<end_time>06/20/2021 19:20:24</end_time>
<inst_id>1</inst_id>
<dbid>2140826538</dbid>
<bucket_max_count>5</bucket_max_count>
<bucket_interval>1</bucket_interval>
<report_level>TYPICAL</report_level>
<show_sql>1</show_sql>
</report_parameters>
...
select nvl2(buckets,
xmlelement("stattype",
xmlattributes('allochist' as "name"),
xmltype(:l_total_mem),
nvl2(stat_id,
xmlelement("stat_info",stat_id),
null),
xmlelement("buckets",
xmlattributes(:l_bucket_interval as "bucket_interval",
:l_bucket_count as "bucket_count",
:l_min_time as "start_time",
:l_max_time as "end_time",
:l_duration as "duration"),
buckets)),
null)
from (
select xmlagg(
xmlelement("bucket",
xmlattributes(bucket_id as "bucket_id"),stats)
order by bucket_id) buckets,
xmlagg(stat_id) stat_id
from (
select bucket_id,
xmlagg(stat_id order by id) stat_id,
xmlagg(xmlelement("stat",
xmlattributes(id as "id",
round(bytes,2) as "value"))
order by id) stats
from (
select bucket_id, id, bytes,
nvl2(component,
xmlelement("stat",
xmlattributes(id as "id",
component as "name",
alloc as "alloc",
'bytes' as "unit",
case when alloc != 'paging'
then '1048576'
else null
end as "factor")),
null) stat_id
from alloc_hist)
group by bucket_id))]]></script>
</report>
prvt_awr_viewer.report_rac_xml(
start_time IN DATE,
end_time IN DATE,
last_refresh_time IN DATE,
inst_id IN NUMBER,
dbid IN NUMBER,
is_realtime IN NUMBER,
show_sql IN NUMBER)
RETURN XMLTYPE;
CREATE TABLE t (testcol XMLTYPE);
SELECT dbid
FROM v$database;
DBID
----------
2140826538
DECLARE
a DATE := SYSDATE-1;
b DATE := SYSDATE;
c DATE := SYSDATE;
d NUMBER := 1;
e NUMBER := 2140826538;
f NUMBER := 1;
g NUMBER := 1;
x XMLTYPE;
BEGIN
x := prvt_awr_viewer.report_rac_xml(a,b,c,d,e,f,g);
prvt_awr_viewer.report_timepicker_xml(
start_time IN DATE,
end_time IN DATE,
duration IN NUMBER,
inst_id IN NUMBER,
dbid IN NUMBER,
is_realtime IN NUMBER,
sql_id IN VARCHAR2,
sid IN NUMBER,
serial IN NUMBER,
show_sql IN NUMBER)
RETURN XMLTYPE;
CREATE TABLE t (testcol XMLTYPE);
SELECT sql_id, sid, serial#
FROM v$sqlarea
WHERE executions = (
SELECT MAX(executions) FROM v$sqlarea);
SELECT dbid
FROM v$database;
DBID
----------
2140826538
DECLARE
a DATE := SYSDATE-1;
b DATE := SYSDATE;
c NUMBER := 1;
d NUMBER := 1;
e NUMBER := 2140826538;
f NUMBER := 1;
g VARCHAR2(13) := '96g93hntrzjtr';
h NUMBER := NULL;
i NUMBER := NULL;
j NUMBER := 1;
x XMLTYPE;
BEGIN
x := prvt_awr_viewer.report_timepicker_xml(a,b,c,d,e,f,g,h,i,j);
prvt_awr_viewer.report_topsql_xml(
start_time IN DATE,
end_time IN DATE,
last_refresh_time IN DATE,
inst_id IN NUMBER,
dbid IN NUMBER,
is_realtime IN NUMBER,
top_n_detail IN NUMBER,
outer_start_time IN DATE,
outer_end_time IN DATE,
compress_xml IN BINARY_INTEGER,
show_sql IN NUMBER)
RETURN XMLTYPE
conn / as sysdba
CREATE TABLE t (testcol XMLTYPE);
SELECT dbid
FROM v$database;
DBID
----------
2140826538
DECLARE
a DATE := SYSDATE-1;
b DATE := SYSDATE;
c DATE := SYSDATE-1;
d NUMBER := 1;
e NUMBER := 2140826538;
f NUMBER := 1; -- is_realtime
g NUMBER := 10; -- top_n
h DATE;
i DATE;
j NUMBER := 1; -- compress
k NUMBER := 1; -- show SQL
x XMLTYPE;
BEGIN
x := prvt_awr_viewer.report_topsql_xml(a,b,c,d,e,f,g,h,i,j,k);
prvt_awr_viewer.report_workload(
service_type IN VARCHAR2,
is_realtime IN NUMBER
dbid IN NUMBER,
inst_id IN NUMBER,
start_time IN DATE,
end_time IN DATE,
report_level IN VARCHAR2,
db_tz IN VARCHAR2,
show_sql IN NUMBER)
RETURN XMLTYPE;
prvt_awr_viewer.rmmetrics_data_xml(
p_start_time IN DATE,
p_end_time IN DATE,
p_inst_id_low IN NUMBER,
p_inst_id_high IN NUMBER,
p_dbid IN NUMBER,
p_bucket_count IN NUMBER,
p_bucket_interval IN NUMBER,
p_inst_detail IN NUMBER,
p_is_rac IN NUMBER,
p_show_sql IN NUMBER,
p_plan_name IN VARCHAR2)
RETURN XMLTYPE;