Sophis Risque: some useful queries for performance check...
Mar
24
Written by:
3/24/2014 3:29 PM
Auditing some performance at one client, I needed to make some search on the database in order to see how the database is growing. This is a part of the results of this study and could be considered as a beginning to reference some other useful queries. It is using in most cases the audit tables and not the logs. Of course, the use of the Sophis logs could enhance the results made by these queries too.
One of the most important (and biggest) table in Sophis Risque is HISTOMVTS, and its linked audit table AUDIT_MVT. It is thus interesting to analyze some data during only the last days, for example the last month, and not on all data, in order to get some results quickly.
For example, getting all operations affecting the deals during the last 30 days can be done using this query:
select trunc(datemodif) as day,count(*) from audit_mvt where datemodif>trunc(sysdate)-30 group by trunc(datemodif) order by day desc;
Same query, but without the saturday, in order to forget some deals made during the last last day of the week, on multi-sites environments (for instance NYC, for databases stored in the Paris/London GMT zone).
select trunc(datemodif) as day,count(*) from audit_mvt where datemodif>trunc(sysdate)-30 and to_char(datemodif,'D')<>7 group by trunc(datemodif) order by day desc;
In order to get only the new deals day after day, applying just a filter on the version number will give the result, since all new deals will be inserted with a version equal to 1. The query is then:
select trunc(datemodif) as day,count(*) from audit_mvt where datemodif>trunc(sysdate)-30 and to_char(datemodif,'D')<>7 and version=1 group by trunc(datemodif) order by day desc;
All these queries above gives modifications on the database day after day. In order to get the aggregation of all results, the following one should be better used:
select day,count(*) from audit_mvt a,(select trunc(datemodif) as day from audit_mvt where datemodif>trunc(sysdate)-10 group by trunc(datemodif)) where a.datemodif<day group by day;
In order to get some performance of inserted deals, it is interesting to see the time elapsed between two insertions too:
select trunc(datemodif) as day,min(diff) as min,max(diff) as max,avg(diff) as avg,count(*) from (
select datemodif, lag(datemodif) over(order by datemodif) as previous_modif, datemodif - lag(datemodif) over(order by datemodif) as diff from audit_mvt
where datemodif>trunc(sysdate)-30)
group by trunc(datemodif)
order by avg desc
And finally some other useful statistics:
Getting the number of instruments created day after day:
select trunc(num_to_date(date_validite)) as day,count(*) from infos_histo where modif=1 and nom_table='titres' group by trunc(num_to_date(date_validite)) order by day desc;
Getting the number of correlations inserted:
select trunc(num_to_date(date_validite)) as day,count(*) from correl_histo group by trunc(num_to_date(date_validite)) order by day desc;