Hi Tanel
Thanks for sharing
I was looking for historical details with snapid , in trail mail you can
see i was trying for query it just googled and i was trying for some
custmization with historical details but no luck
If anything related with historical trend if i can achieve , would really
help me here
So if suggestion would really helpfull
Regards,
Krishna
On Mon, 18 Oct 2021, 12:30 Tanel Poder, <tanel@xxxxxxxxxxxxxx> wrote:
The other angle for drilling into the "connection management elapsed time"
is to use ASH, you'll get much better ability to slice & dice the data:
SQL> @ash/dashtop username,module,time_model_name in_connection_mgmt='Y'
sysdate-365 sysdate
Total
Seconds AAS %This USERNAME MODULE
TIME_MODEL_NAME
--------- ---- ------- ------------
---------------------------------------
----------------------------------------------
403820 .0 95% SYS JDBC Thin Client
CONNECTION_MGMT
11440 .0 3% JDBC Thin Client
CONNECTION_MGMT
6410 .0 2% JDBC Thin Client
CONNECTION_MGMT SQL_EXECUTION
770 .0 0% DBSNMP perl@linux01.localdomain (TNS V1-V3)
CONNECTION_MGMT
240 .0 0% JDBC Thin Client
CONNECTION_MGMT PARSE
200 .0 0% DBSNMP perl@linux01.localdomain (TNS V1-V3)
CONNECTION_MGMT SQL_EXECUTION
170 .0 0% DBSNMP JDBC Thin Client
CONNECTION_MGMT
130 .0 0% SYS sqlplus@mac19 (TNS V1-V3)
CONNECTION_MGMT
130 .0 0% JDBC Thin Client
CONNECTION_MGMT SQL_EXECUTION PLSQL_EXECUTION
90 .0 0% DBSNMP JDBC Thin Client
CONNECTION_MGMT SQL_EXECUTION
50 .0 0% SYS perl@linux01.localdomain (TNS V1-V3)
CONNECTION_MGMT
50 .0 0% JDBC Thin Client
CONNECTION_MGMT BIND
50 .0 0% JDBC Thin Client
CONNECTION_MGMT CURSOR_CLOSE
30 .0 0% SYS
CONNECTION_MGMT
30 .0 0% JDBC Thin Client
CONNECTION_MGMT PARSE SQL_EXECUTION
30 .0 0%
CONNECTION_MGMT
20 .0 0% SYS JDBC Thin Client
CONNECTION_MGMT PARSE
20 .0 0% SYS JDBC Thin Client
CONNECTION_MGMT PARSE HARD_PARSE SQL_EXECUTION
20 .0 0% WEBTA_VA sqlplus@airm1.localdomain (TNS V1-V3)
CONNECTION_MGMT SQL_EXECUTION
10 .0 0% DBSNMP JDBC Thin Client
CONNECTION_MGMT PARSE HARD_PARSE SQL_EXECUTION
--
Tanel Poder
https://tanelpoder.com
On Sat, Oct 16, 2021 at 5:24 AM Krishnaprasad Yadav <
chrishna0007@xxxxxxxxx> wrote:
Hi Team ,
we are trying to get the connection management historical data
from dba_hist_sys_time_model view .
We are referring one off the script to get data for connection
management historically , below is script :
select e.stat_name "Statistic Name"
, (e.value - b.value)/1000000 "Time (s)"
, decode( e.stat_name,'DB time'
, to_number(null)
, 100*(e.value - b.value)
)/
( select nvl((e1.value - b1.value),-1)
from dba_hist_sys_time_model e1
, dba_hist_sys_time_model b1
where b1.snap_id = b.snap_id
and e1.snap_id = e.snap_id
and b1.dbid = b.dbid
and e1.dbid = e.dbid
and b1.instance_number = b.instance_number
and e1.instance_number = e.instance_number
and b1.stat_name = 'DB time'
and b1.stat_id = e1.stat_id
)
"Percent of Total DB Time"
from dba_hist_sys_time_model e
, dba_hist_sys_time_model b
where b.snap_id = 2221
and e.snap_id = 2222
and b.dbid =376520799
and b.dbid =e.dbid
and b.instance_number =1
and b.instance_number =e.instance_number
and b.stat_id =e.stat_id
and e.stat_name ='connection management call elapsed time'
and e.value - b.value > 0
order by 2 desc ;
/
above script gives output but when scripts are provided in range of snap
i.e for collecting all together i am trying to provide value of snap_id in
between clauses it is giving me some weird data .
It will be great help if someone can provide way out here to get the
historical value of connection management call elapsed time for range of
month
Regards,
Krishna
