Re: Connection management Historical details

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_at_tanelpoder.com> 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> _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