Oracle give use lots of performance views, not only for database only, but also some important data about the applications. As a DBA we should analyze those data, and show the data to others. For example, the SQL execute ratio of each application hosts for a database. It may be useful in application capacity plaing.
It seems DBAs cannot get any data about it, we should analyze the application log and then get the required data. But it's not ture, usually the application use connection pool, so the database connection is permanent to database, sessions are not reconnected between different calls. So we can get the SQL execute count staistics from V$SESSTAT performance view. Create a user with select catalog role and create a table to hold the data under the created user.
create table TMP_SESSION_EXECUTES
(
ID NUMBER(38),
SID NUMBER(12),
serial# number(12),
machine varchar2(64),
username varchar2(30),
execute number(38)
);
Create a sequence to generate the snap id for each data collect.
create sequence seq_temp_sesstat;
Run the following PL/SQL codes for few hours, we will get lots of data to analyze the above problem.
declare
ver number:=0;
begin
loop
select seq_temp_sesstat.nextval into ver from dual;
insert into TMP_SESSION_EXECUTES
select ver, a.sid,
a.serial#, a.machine, a.username, b.value
from v$session a, v$sesstat b
where a.sid=b.sid
and b.STATISTIC#=238;
commit;
dbms_lock.sleep(300);
end loop;
end;
/
The data we gathered is cumulative data, we need to get the changed value for each snap, and then do a summary by application machine and schema name.
SELECT MACHINE, USERNAME, SUM(EXECUTE) EXECUTE
FROM (
SELECT A.MACHINE, A.USERNAME,
A.EXECUTE - NVL(B.EXECUTE,0) EXECUTE
FROM TMP_SESSION_EXECUTES A, TMP_SESSION_EXECUTES B
WHERE A.ID=B.ID+1
AND A.SID=B.SID
AND A.SERIAL#=B.SERIAL#
AND A.MACHINE=B.MACHINE
AND A.USERNAME=B.USERNAME
AND A.EXECUTE > NVL(B.EXECUTE,0) )
GROUP BY MACHINE, USERNAME
Now we get the SQL executions of each application machine in a given duration, and calculate the ratio of each application machine, it's very useful infromation.