ใช้ "UPDATE GLOBAL INDEXES" "UPDATE INDEXES"

เมื่อเรามี การ add/move/split ... อะไรก็ตามที่ กระทำให้ data มีการเปลี่ยน partitions indexes:
เราควรสนใจ option: "UPDATE GLOBAL INDEXES" with global "UPDATE INDEXES" with local

นี่เป็นตัวอย่าง คร่าวๆ กับ hash table + gloabal index

CREATE TABLE TEMP_PAR01

     (no NUMBER,

      data VARCHAR2 (50))

   PARTITION BY HASH (no)

   PARTITIONS 5;

CREATE INDEX temp01_global_par_idx ON temp_par01(no)

GLOBAL PARTITION BY hash(no)

PARTITIONS 5;

alter table temp_par01 add partition p6;

analyze table temp_par01 compute statistics;

ไม่พบ error ใดๆ ทั้งสิ้นเพราะยังไม่มีข้อมูล

ใส่ข้อมูลเพื่อทดสอบ:

begin

 for x in 1 .. 1000 

  loop 

   insert into TEMP_PAR01 (no) values (x);

  end loop;

end;

/

commit;

ลอง เพิ่ม partition อีกครั้ง

alter table temp_par01 add partition p7;

analyze table temp_par01 compute statistics;

analyze table temp_par01 compute statistics

*

ERROR at line 1:

ORA-01502: index 'SCOTT.TEMP01_GLOBAL_PAR_IDX' or partition of such index is in unusable state

พบว่า partitions indexes กลายเป็น unusable state

select partition_name, status  from user_ind_partitions 

where index_name='TEMP01_GLOBAL_PAR_IDX';

PARTITION_NAME                 STATUS

------------------------------ --------

SYS_P62                        UNUSABLE

SYS_P63                        UNUSABLE

SYS_P64                        UNUSABLE

SYS_P65                        UNUSABLE

SYS_P61                        UNUSABLE

ดังนั้นลองใช้ "UPDATE GLOBAL INDEXES"

Use "UPDATE GLOBAL INDEXES" with global "UPDATE INDEXES" with local 

alter table temp_par01 add partition p8 UPDATE GLOBAL INDEXES;

analyze table temp_par01 compute statistics;

ไม่เจอ error:

select partition_name, status  from user_ind_partitions where index_name='TEMP01_GLOBAL_PAR_IDX';

PARTITION_NAME                 STATUS

------------------------------ --------

SYS_P62                        USABLE

SYS_P63                        USABLE

SYS_P64                        USABLE

SYS_P65                        USABLE

SYS_P61                        USABLE

สรุปได้ว่า ขณะที่มีการ เพิ่ม partition tables ก็ได้มีการ update ที่ indexes ด้วย
ข้อดีตรงนี้ ช่วยให้เรามั่นใจ ได้ว่า indexes ที่้ใช้กลายเป็น unusable state

อย่างไร เมื่อมีการกระทำกับ partitions tables อย่าลืม check status ของ partitions indexes ด้วย 

 ถ้าพบ unusable state ก็ทำการ rebuild partitions indexes นั้นๆไป 

ALTER INDEX  &lt index_name &gt REBUILD PARTITION &lt partition_name&gt 

ตัวอย่าง variable on SQL Statement

หลายคนทราบกันดีว่า library cache นั้นเก็บข้อมูลเกี่ยวกับ sql statements + pl/sql
ดังนั้น ทุกๆ sql ที่เราเรียกใช้งานจะเข้าไปในกระบวนการ optimization แล้วจะเก็บไว้ใน library cache
แล้วจะเอาออกจาก memory ด้วย lru algorithm.

แต่ที่พูดมาทั้งนั้น และทั้งนี้ ถ้าเรา ลดภาระ การ optimization + การเก็บ sql ใน Library cache นั้นคงดี

ผมจึงนำเสนอ การใช้ variable ก็ตัวแปรนี่เอง

ตัวอย่าง 1

log user:

SQL> select * from test01 where object_id=100;

no rows selected

SQL> select * from test01 where object_id=101;

no rows selected

จากนั้น Login ด้วย sys เพื่อต้องการ query V$SQL

SQL> select sql_text,sql_id from v$sql where lower(sql_text) like 'select% from test01%';

SQL_TEXT SQL_ID
-------------------------------------------------- -------------
select * from test01 where object_id=100 42pxm51pbj3gq
select * from test01 where object_id=101 fw9x60q5qbr7z


เราจะพบว่าจาก sql ตอนแรกนั้นได้มีการเก็บเข้าไปใน memory ถึงสองครั้ง
และแน่นอน ว่าต้องมีการ Optimization 2 ครั้งเช่นกัน ลักษณะแบบนี้เขาเรียกกันว่า hard parse

ตัวอย่าง 2

ก่อนอื่นใช้ sys user -> flush shared_pool

SQL> alter system flush shared_pool;

System altered.

login user:


SQL> var A number
SQL> exec :A := 100

PL/SQL procedure successfully completed.

SQL> select * from test01 where object_id=:A;

no rows selected

SQL> exec :A := 101

PL/SQL procedure successfully completed.

SQL> select * from test01 where object_id=:A;

no rows selected

จากนั้นใช้ sys user เพื่อเข้าไป เช็คอีกครั้ง

SQL> select sql_text,sql_id from v$sql where lower(sql_text) like 'select% from test01%'

SQL_TEXT SQL_ID
-------------------------------------------------- -------------
select * from test01 where object_id=:A dtruvsaj9k5xp

จากผลลัพท์ที่เห็น ทำให้รู้ว่า ทั้งสอง statement นั้น จะมีการ optimization ครั้งแรกครั้งเดียว
พอครั้งที่ สอง oracle พบใน memory และนำมาใช้ได้เลย แบบนี้ statement ตัวที่สอง ทำการ soft parse

ซึ่งจะประหยัด CPU และ process ในการคิดของ database

ตัวอย่าง +ASM + Virtual Disk on linux

ใช้ "root" user (สร้าง virtual disk) ->

# mkdir /u01/asmtest
# chown oracle:dba /u01/asmtest/
# su - oracle
$ dd if=/dev/zero of=/u01/asmtest/file_disk1 bs=2k count=100000
100000+0 records in
100000+0 records out

ใช้ root user (associate a loop device with a file) ->

# losetup /dev/loop1 /u01/asmtest/file_disk1


ใช้ root user (bind raw device) ->

# raw /dev/raw/raw10 /dev/loop1
/dev/raw/raw10: bound to major 7, minor 1


Check permission :
# ls -ltr /dev/raw/raw10
crw-rw---- 1 oracle dba 162, 10 Nov 4 12:04 /dev/raw/raw10


use oracle user ->

$ export ORACLE_BASE=/u01/oracle/product
$ export ORACLE_HOME=/u01/oracle/product/10.2.0/asm
$ export ORACLE_SID=+ASM
$ export PATH=$ORACLE_HOME/bin:$PATH
$ sqlplus / as sysdba

SQL> select group_number, disk_number, mount_status, header_status, state, path from v$asm_disk;

GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STA STATE PATH
------------ ----------- ------- ---------- -------- --------------------
0 1 CLOSED CANDIDATE NORMAL /dev/raw/raw10



SQL> CREATE DISKGROUP tmp_test EXTERNAL REDUNDANCY DISK '/dev/raw/raw10';

Diskgroup created.

GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE PATH
------------ ----------- ------- ------------ -------- ---------------
3 0 CACHED MEMBER NORMAL /dev/raw/raw10



SQL> select group_number, name, total_mb, free_mb, state, type from v$asm_diskgroup;

GROUP_NUMBER NAME TOTAL_MB FREE_MB STATE TYPE
------------ ------------------------------ ---------- ---------- ----------- ------
3 TMP_TEST 195 145 MOUNTED EXTERN

ตอนนี้เราก็ ได้ diskgroup จาก virtual disk แล้ว