Clean Oracle SYSAUX + Remove & reinstall XDB = Performance tuning

Monday, 22.12.2008 – Dejan

Prije nekoliko mjeseci porastao nama SYSAUX Tablespace u JEDNOM danu sa 3 GB na 30 GB. Uzrok je (pretpostavljam) bilo sakupljanje histograma za nekoliko velikih tabela, sa puno kolona i sa puno podataka. Nakon određenog vremena, ti histogrami su opet obrisani, jer je dolazilo do generisanja lošeg execution plana u određenim situacijama (neki Cost Based Optimizer bug je bio u pitanju).

Međutim, … nakon brisanja, datafile nisam mogao smanjiti nazad na 3 GB, zato što su se pri kraju datafile-a nalazili blokovi od AWR (Automatic Workload Repository) i od XDB (XML DB komponenta) tabela. Odradim ja fino ALTER TABLE WR*$blabla MOVE i time oslobodim zauzete blokove od WRH$ i WRI$ tabela.

Ostale su još XDB tabele. Pošto sam negdje pročitao, da ukoliko dosta koristite XML u Oracle bazi, performanse se mogu poboljšati ukoliko XDB komponentu prebacimo u vlastiti tablespace. “Taman da iskoristim priliku i usput očistim i SYSAUX tablespace” – pomislih u sebi.

Najprije da pronađem, kojom procedurom se XDB komponenta prebacuje u drugi tablespace:

select occupant_name, occupant_desc, move_procedure
from V$SYSAUX_OCCUPANTS
where occupant_name = 'XDB';

OCCUPANT_NAME  OCCUPANT_DESC  MOVE_PROCEDURE
-------------- -------------- -------------------------------
XDB            XDB            XDB.DBMS_XDB.MOVEXDB_TABLESPACE

Ok, našao sam proceduru. Ajd sad da ja to sve fino isprobam u testnoj bazi. Prvo moram kreirati poseban tablespace za XDB:

create bigfile tablespace XDB_TBS
datafile '+DG_DATA' size 100m autoextend on next 10m
extent management local
segment space management auto;

Pokrećem proceduru:

begin;
  XDB.DBMS_XDB.MOVEXDB_TABLESPACE('XDB_TBS');
end;

Nakon par minuta je gotovo. Bez greške. E sad još samo da smanjim SYSAUX datafile:

select file_name
  from dba_data_files
 where tablespace_name = 'SYSAUX';

FILE_NAME
---------------------------------------------------------
+DG_DATA/testdb/datafile/sysaux.746.673584569

alter database datafile '+DG_DATA/testdb/datafile/sysaux.746.673584569' resize 3G;

Gotovo. Zar je moguće da je ovo ovako lako i jednostavno?

Naravno da nije…

Odlučim ja istu proceduru odraditi i na production bazi:
– Očistim SYSAUX datafile od WRH$ i WRI$ tabela bez problema
– Kreiram novi tablespace za XDB
– Pokrenem proceduru za premještanje XDB komponente
– i čekam 5 minuta… 10 minuta … sat vremena… sve stoji

Zovu korisnici i žale se, kako im se sve usporilo, da se ne mogu čak ni spojiti na bazu.
Auuu, šta sad bi!?” – uhvatila me panika odmah…
Hmmm, odlučih da prekinem premještanje XDB komponente (CTRL + C), jer je vjerovatno to uzrok. Eeee, koja je to tek greška bila…

Ubio sam svoj sqlplus proces, ali je proces na serveru ostao da visi. Morao sam da ga ubijem sa orakill. Nedugo nakon toga primijetih čudno ponašanje baze…

Najprije pojedine XML aplikacije nisu radile kako treba. Potom su se počeli pojavljivati “čudni” procesi. Zašto čudni? Zato što su se spajali u SHARED modu, iako su baza i svi servisi u njoj definisani kao DEDICATED. Procesi su imali oznaku Sxxx (npr. S000, S001 i tako dalje), a pregledom v$session i v$process nisam mogao naći nikakve dodatne informacije. Ti shared procesi su samo otvarali sesije u bazi, ali nisu “ništa” radili, osim što su zauzimali resurse i opterećivali bazu. U Grid control centru mi kao trenutnu aktivnost procesa prikazuje “null event“. “Šta je sad ovo u p**** m******!?!?” – naslonih se nazad i zagonetno gledah u ovu misteriju…

Kontam u sebi “hm, shared server procesi, koji se spajaju preko SYS$USERS servisa… to sigurno ima veze sa dispatcherom, a jedini, koji postoji je onaj za XDB… sigurno se zbog onog prekida premještanja XDB komponente nešto poremetilo u bazi…

select name, value from v$parameter where name = 'dispatchers';

NAME            VALUE
--------------- ----------------------------------------
dispatchers     (PROTOCOL=TCP) (SERVICE=productiondbXDB)

Garant je XDB komponenta “oštećena” (corrupted)… Ne preostaje mi ništa drugo, nego “popraviti” XDB.

Nabrzaka pronađem na MetaLinku odličan tekst, kako da uklonim i ponovo instaliram XDB (MetaLink Doc ID: 243554.1 , How to Deinstall and Reinstall XML Database (XDB) ). Prvo pokušam sa xdbrelod.sql, ali je opet stalo sve i nije se pomjeralo ni nakon pola sata. Ubih i taj proces, pa nastavih sa drugim dijelom tog teksta (Removal & Install steps).

Odradih sve kako tamo piše uz malu izmjenu – mi koristimo RAC, pa sam morao stopirati kompletnu bazu, pokrenuti samo jedan node kao non-RAC (alter system set cluster_database=false scope=spfile sid='*';) i odraditi sve korake navedene u tom tekstu. Nakon vraćanja u normalu (nazad u RAC, cluster_database=true), pokrenuo sam još utlrp.sql i sve je funkcionisalo kako treba! Ah kako sam bio neizmjerno srećan…

Navešću korake, koje sam obavio na osnovu gorenavedenog teksta (nadam se da nisam zaboravio nešto prilikom copy/pasteanja :) ):

C:\oracle\product\10.2.0\crs\bin\> srvctl stop instance -d productiondb -i productiondb2
C:\oracle\product\10.2.0\db\bin\> sqlplus / as sysdba
SQL> alter system set cluster_database=false scope=spfile sid='*';
SQL> ALTER SYSTEM RESET dispatchers scope=spfile sid='*';
SQL> shutdown transactional;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 5.2429E+10 bytes
Fixed Size                  2360024 bytes
Variable Size            3130653992 bytes
Database Buffers         4.9291E+10 bytes
Redo Buffers                4325376 bytes
Database mounted.
Database opened.

SQL> @?\rdbms\admin\catnoqm.sql

PL/SQL procedure successfully completed.

User dropped.

PL/SQL procedure successfully completed.

Table dropped.

SQL> shutdown transactional;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
SQL> @?\rdbms\admin\catqm.sql XDB XDB_TBS TEMP;
SQL> alter system set cluster_database=true scope=spfile sid='*';
SQL> ALTER SYSTEM SET dispatchers="(PROTOCOL=TCP) (SERVICE=productiondbXDB)" scope=spfile sid='*';
System altered.
SQL> shutdown transactional;
SQL> startup;
SQL> @?\rdbms\admin\utlrp.sql
SQL> exit;

C:\oracle\product\10.2.0\crs\bin\> srvctl start instance -d productiondb -i productiondb2

I koji je rezultat svega ovoga? Osim što SYSAUX tablespace sada zauzima manje prostora, primijetili smo već nakon par dana da su XML aplikacije za nijansu brže i da nemamo read/write konflikte u SYSAUX tablespaceu.

Izgubio sam dosta živaca, ali se isplatilo.

Post a Comment