Discussion:
Capturing sql statement
Arun Chugh
2018-09-25 17:01:23 UTC
Permalink
All,

I have one scenario related to capturing of sql statement in history
table.. Like dba_hist_sqltext capture the queries that ran for 10 sec or
more.. How do I get the sql stmt which took less time say in
millisecond.. Any idea pleae share.

Regards,
Arun Chugh
Chris Taylor
2018-09-25 17:34:33 UTC
Permalink
There's gv$sqlstats, and dba_hist_sqlstat (for historical sqls).

On gv$sqlstats there's ELAPSED_TIME which is micro seconds so you can
convert that to milliseconds and divide ELAPSED_TIME / EXECUTIONS to get
time per execution. For example, I use (ELAPSED_TIME/1000000)/EXECUTIONS
to get seconds_per_execution.

On dba_hist_sqlstat, there EXECUTIONS_DELTA and ELAPSED_TIME_DELTA. Elap
Time Delta is again microseconds , so divide to get your conversion. Then
divide that by executions_delta to get your time per execution.

Thanks,
Chris Taylor
Post by Arun Chugh
All,
I have one scenario related to capturing of sql statement in history
table.. Like dba_hist_sqltext capture the queries that ran for 10 sec or
more.. How do I get the sql stmt which took less time say in
millisecond.. Any idea pleae share.
Regards,
Arun Chugh
sachin pawar
2018-09-25 17:42:15 UTC
Permalink
You can use v$sql to write custom pl/sql job
Post by Arun Chugh
All,
I have one scenario related to capturing of sql statement in history
table.. Like dba_hist_sqltext capture the queries that ran for 10 sec or
more.. How do I get the sql stmt which took less time say in
millisecond.. Any idea pleae share.
Regards,
Arun Chugh
--
Rgds,
Sachin Pawar
https://twitter.com/sach_pwr
Mladen Gogala
2018-09-26 02:13:59 UTC
Permalink
Or use fine grained auditing. That's what it is meant for.
Post by sachin pawar
You can use v$sql to write custom pl/sql job
All,
I have one scenario related to capturing of sql statement in
history table..  Like dba_hist_sqltext capture the queries that
ran for 10 sec or more..  How do I get the sql stmt which took
less time say in  millisecond..  Any idea pleae share.
Regards,
Arun Chugh
--
Rgds,
Sachin Pawar
https://twitter.com/sach_pwr
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
Jonathan Lewis
2018-09-25 18:07:18 UTC
Permalink
dbms_workload_repository.add_colored_sql()

Supply the sql_id and the SQL will be recorded in the AWR even if it doesn't meet any of the limits set for normal "SQL ordered by ... "


There's a limit of 100 sql_ids that can be "colored" - then you get error:

ORA-13534: Current SQL count(100) reached maximum allowed (100)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 751

dbms_workload_repository.remove_colored_sql() to remove them from the hit list


Regards
Jonathan Lewis




________________________________________
From: oracle-l-***@freelists.org <oracle-l-***@freelists.org> on behalf of Arun Chugh <***@gmail.com>
Sent: 25 September 2018 18:01
To: Oracle Mailing List
Subject: Capturing sql statement


All,

I have one scenario related to capturing of sql statement in history table.. Like dba_hist_sqltext capture the queries that ran for 10 sec or more.. How do I get the sql stmt which took less time say in millisecond.. Any idea pleae share.

Regards,
Arun Chugh
--
http://www.freelists.org/webpage/oracle-l
Karl Arao
2018-09-25 20:00:51 UTC
Permalink
You can also get this from ASH

See example here
https://gist.github.com/karlarao/3ba0ead11336e2401a552588b9941e24

You can filter <1sec or <10sec queries using @ash_elap2 on the example
--
Karl Arao
Wiki: karlarao.wiki
Twitter: @karlarao <http://twitter.com/karlarao>
Loading...