Essa semana eu tive um problema grave em um cliente onde algumas aplicações estavam parando pois a tablespace temporária estava 100% cheia, em primeiro momento aumentei o espaço, algumas horas depois o espaço havia sido completamente esgotado e mais uma vez a aplicação enfrentou problemas. Comecei a analisar as queries da aplicação tentando entender onde estavam sendo alocado todo esse espaço.
Como o cliente usa aplicações web conectadas usando pool de conexões, essa situação se agravou devido ao fato de segmentos temporários alocados em uma sessão somente são desalocados quando ela se desconecta do banco de dados, então o perfil dessa aplicação era encher dezenas de gigabytes em alguns minutos. O que me chamou a atenção foi ver segmentos do tipo LOB sendo alocados constantemente (v$tempseg_usage), porém, eu estava fazendo join com a v$session e os sql_id’s estavam sendo atualizados a todo instante, e isso não me dava uma figura exata do que realmente estava acontecendo.
Verifiquei também que algumas das queries nessas sessões estavam usando a função WM_CONCAT tinham custo bem baixo, executavam em segundos e as outras, em sua grande maioria, usavam muito pouco ORDER BY, GROUP BY ou HASH JOINS que são rotinas que exigem da área temporária do Oracle.
Vou mostrar um caso de testes em uma base logado como SYS, pode fazer conectado com um usuário que tenha privégios de create table, consulta ao dicionário de dados ou acesso as views v$sql,v$tempseg_usage,v$session e dba_objects.
1) Criação de uma tabela “t” com algumas linhas
SQL> create table t as select * from dba_objects;
Table created.
2) Verificação de quantas linhas existem na tabela “t”
SQL> select count(1) from t;
COUNT(1)
———-
93701
3) A query abaixo mostra a quantidade de espaço usada por uma sessão conectada como “SYS”, inicialmente irá mostrar nada
select
s.sid
, s.serial#
, s.sql_id
, s.username
, t.segtype
, s.program
, t.blocks
, round((t.blocks*8096)/1024/1024,2) uso_mbs
from
v$session s
, v$tempseg_usage t
, v$sql sql
where
t.session_addr = s.saddr
and t.sqladdr = s.sql_address
and t.session_num=s.serial#
and s.sql_id = t.sql_id
and s.sql_id = sql.sql_id
and s.sql_child_number=sql.child_number
and s.username =’SYS’;
no rows selected
4) Usando a função WM_CONCAT para concatenar alguns dados e mostrar na mesma linha:
SQL> set long 20000
SQL> select wm_concat(owner||’ ‘||object_type||’ ‘||object_name)
from t
where rownum < 5001;
WM_CONCAT(OWNER||”||OBJECT_TYPE||”||OBJECT_NAME)
——————————————————————————–
JOB_NAME,SYS INDEX I_DIR$INSTANCE_ACTTYP,SYS INDEX I_DIR$INSTANCE_END_TIME,SYS T
ABLE DIR$RESONATE_OPERATIONS,SYS INDEX I_DIR$RESONATE_UI,SYS INDEX I_DIR$RESONAT
E_STATUS,SYS INDEX I_DIR$RESONATE_END_TIME,SYS INDEX I_DIR$RESONATE_ALERT_NAME,S
YS TABLE DIR$ALERT_HISTORY,SYS INDEX I_DIR$ALERT_HISTORY_NAME,SYS INDEX I_DIR$AL
ERT_HISTORY_ACTION_ID,SYS INDEX I_DIR$ALERT_HISTORY_REASON_ID,SYS INDEX I_DIR$AL
ERT_HISTORY_AT,SYS TABLE DIR$REASON_STRINGS,SYS INDEX I_DIR$REASON_STRINGS_UI,SY
S TABLE DIR$DATABASE_ATTRIBUTES,SYS INDEX I_DIR$DB_ATTRIBUTES_UI,SYS TABLE DIR$V
ICTIM_POLICY,SYS TABLE DIR$NODE_ATTRIBUTES,SYS INDEX I_DIR$NODE_ATTRIBUTES_ATTR,
SYS TABLE DIR$SERVICE_ATTRIBUTES,SYS INDEX I_DIR$SERVICE_ATTRIBUTES_UI,SYS INDEX
I_DIR$SERVICE_ATTRIBUTES_ATTR,SYS TABLE CACHE_STATS_1$,SYS INDEX I_CACHE_STATS_
1,SYS SEQUENCE CACHE_STATS_SEQ_1,SYS TABLE CACHE_STATS_0$,SYS INDEX I_CACHE_STAT
5) Rodei a query novamente do passo 3 e olha o resultado:
SID SERIAL# SQL_ID USERNAME SEGTYPE PROGRAM BLOCKS USO_MBS
———- ———- ————- ———— ——— —————————————- ———- ———-
1758 3047 1xk8hsn1wacp7 SYS LOB_INDEX sqlplus@oraclehomolog (TNS V1-V3) 2944 22,73
1758 3047 1xk8hsn1wacp7 SYS LOB_DATA sqlplus@oraclehomolog (TNS V1-V3) 41984 324,16
6) Vamos checar que sql_id é esse que tanto usa área temporária do Oracle
SQL> select sql_fulltext from v$sql where sql_id=’1xk8hsn1wacp7′;
SQL_FULLTEXT
——————————————————————————–
select wm_concat(owner||’ ‘||object_type||’ ‘||object_name)
from t
where rownum < 5001
Checando no Metalink eu achei a nota abaixo, que nem precisa abrir para entender o recado.
WMSYS.WM_Concat Should not be used for customer applications, it is an internal function [ID 1336219.1]