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