Re: Connection management Historical details
Date: Mon, 18 Oct 2021 03:00:34 -0400
Message-ID: <CAMHX9JLi0c2iB7OMOXkReFAqVjooqSJ8vqGXT==-Mm7iUXcgFA_at_mail.gmail.com>
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> _at_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_at_linux01.localdomain (TNS V1-V3)
CONNECTION_MGMT
240 .0 0% JDBC Thin Client
CONNECTION_MGMT PARSE
200 .0 0% DBSNMP perl_at_linux01.localdomain (TNS V1-V3)
CONNECTION_MGMT SQL_EXECUTION
170 .0 0% DBSNMP JDBC Thin Client
CONNECTION_MGMT
130 .0 0% SYS sqlplus_at_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_at_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_at_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_at_gmail.com>
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
>
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Oct 18 2021 - 09:00:34 CEST