Posted in 2015

TM contention and INSERT /*+ APPEND */ parallel sessions?

Today I did a reorganization on 1,7TB tablespace, finally reduced to 620GB. (I have to have 10% free in a tablespace to not cause alerts). There were some tables arround 30GB with smaller LOBs and two tables with one 1TB LOB and 500GB LOB. To know it will work a testcase on the standby database was necessary. What I found out is following. When you have these two table with a structure like: ID PRIMARY KEY VARCHAR2(40), DESCRIPTION VARCHAR2(255), SIZE NUMBER, DATA BLOB the ID was something like a HEX number so it’s not the real query I use here, there was a sustr and ascii function too, Description of the data, size of the blob and finally the file or something like that, it’s a doc table, I don’t know what the application stores, for a DBA it’s not necessary to know. what I did to reorganize it was:

  • a new tablespace
  • create table new as select * from current where rownum<=0;
  • 8 sessions with: insert /*+ append */ into new select * from current where mod(id,8) = 0-7 ;

Here I found out that when you start them parallel, you get an enqueue: TM contention on sessions 1-7. Funny, so I googled and discovered to solve it I need to create a foreign key. alter table new add foreign key fk_new_id referencing current(id) ; After I did this and restarted all the insert sessions I got DIRECT PATH READ on all sessions and a THROUGHPUT of arround 1,3GB/min. The longest INSERT took 1 hour 30 minutes, so in this time a reorg on 500GB LOB was possible, 20 datafiles on one filesystem. Finally it had arround 160GB the LOB. alter table new drop constraint fk_new_id; alter table new add constraint pk_id primary key id tablespace index_tbs; On the 1TB LOB I used the same procedure and finished in 4 hours and 30 minutes. So I reduced 1,1TB from 23:00 until 06:00. Well, it’s interresting that when you use parallel insert you get a contention and a foreign key helps to solve it.

Posted in 2015

Little Statspack Performance monitoring SQL

Well, you know Average Active Sessions, if you don’t have AWR than this is a way how to get some information, use Statspack. The query displays how many users were working(active) in a second.

select t.snap_time,round(t.dbtime/t.elapsed,2) as "AvgActiveSessions" from (
nvl(round((l.value-lag(l.value) over (order by l.snap_id))/1000000,2),0) as dbtime,
nvl((s.snap_time-lag(s.snap_time) over (order by s.snap_id asc))*24*60*60,0) as elapsed
from stats$snapshot s, v$sys_time_model m, stats$sys_time_model l where
m.stat_id=l.stat_id and s.snap_id=l.snap_id
and m.stat_name='DB Time'
and snap_time between &timelow and &timehigh
order by 1 asc ) t
where t.dbtime!=0

The same query can be used for the statname DB CPU, so it means it should be smaller than the AAS. It says how much CPU seconds were used in a second.

select g.snap_time,round(g.cpu/g.elapsed,2) from (
round((n.value-lag(n.value,1,n.value) over (order by n.snap_id)),0)/100 cpu,
nvl((t.snap_time-lag(t.snap_time) over (order by t.snap_id asc))*24*60*60,0) as elapsed
from stats$sysstat n,stats$snapshot t
where n.snap_id in (select snap_id from stats$snapshot where snap_time between &timelow and &timehigh )
and ='CPU used by this session' and t.snap_id=n.snap_id order by 1,2 ) g
where g.cpu!=0

This query shows the same information about the CPU usage, of course it’s not the same as the underlying tables are not refreshed in the same time (v$sysstat and v$sys_time_model – I know these are views, but I mean the underlying tables)

AAS = 1,2
DB CPU = 0,8

There were 1,2 active sessions per second and they used 0,8 seconds per Second on CPU. Great.. now if you divide this 0,8 with CPU_COUNT*100 you get the CPU usage in %