split PMAX partition with records inside

--my evn 10.2.0.2
Few things to take note during re-distribute records to new partitions:
1. Archived log gnerated , as rowmovement occurs. Rowid changed is the evidance. Here is my observation.
--rowid before split

select rowid, BILL_REF_NO, BILL_REF_RESETS from arbor.bill_invoice_detail partition(P01560_1) where BILL_REF_NO=155000001;

ROWID BILL_REF_NO BILL_REF_RESETS
------------------ ----------- ---------------
AAHsyTADUAABdzkAAD 155000001 1
AAHsyTADUAABdzkAAC 155000001 1
AAHsyTADUAABdzkAAB 155000001 1
AAHsyTADUAABdzkAAA 155000001 1

--split partition
10:55:50 SQL> alter table bill_invoice_detail split partition pmax
10:55:50 2 at (156000000,2) into (partition p01560_1 tablespace cust_bill_inv_det, partition pmax tablespace cust_bill_inv_det);

Table altered.

Elapsed: 00:09:44.28
11:05:34 SQL>
11:05:34 SQL> alter table bill_invoice_detail split partition pmax
11:05:34 2 at (157000000,2) into (partition p01570_1 tablespace cust_bill_inv_det, partition pmax tablespace cust_bill_inv_det);

Table altered.

Elapsed: 00:00:20.77
11:05:55 SQL>
11:05:55 SQL>
11:05:55 SQL> alter table bill_invoice_detail split partition pmax
11:05:55 2 at (158000000,2) into (partition p01580_1 tablespace cust_bill_inv_det, partition pmax tablespace cust_bill_inv_det);

Table altered.

Elapsed: 00:00:00.10
11:05:55 SQL>
11:05:55 SQL> alter table bill_invoice_detail split partition pmax
11:05:55 2 at (159000000,2) into (partition p01590_1 tablespace cust_bill_inv_det, partition pmax tablespace cust_bill_inv_det);

Table altered.

--rowid after split

select rowid, BILL_REF_NO, BILL_REF_RESETS from arbor.bill_invoice_detail partition(P01560_1) where BILL_REF_NO=155000001;

ROWID BILL_REF_NO BILL_REF_RESETS
------------------ ----------- ---------------
AAJM0sADXAABdzkAAG 155000001 1
AAJM0sADXAABdzkAAH 155000001 1
AAJM0sADXAABdzkAAI 155000001 1
AAJM0sADXAABdzkAAJ 155000001 1

2. Local index with records became unusable. Need to manually rebuild it.


11:20:07 SQL> exec dbms_stats.gather_table_stats(ownname=>'ARBOR', tabname=>'BILL_INVOICE_DETAIL');
BEGIN dbms_stats.gather_table_stats(ownname=>'ARBOR', tabname=>'BILL_INVOICE_DETAIL'); END;

*
ERROR at line 1:
ORA-20000: index "ARBOR"."BILL_INVOICE_DETAIL_PK" or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 13159
ORA-06512: at "SYS.DBMS_STATS", line 13179
ORA-06512: at line 1

13:24:09 SQL> select PARTITION_NAME, status from dba_ind_partitions where index_name='BILL_INVOICE_DETAIL_PK';

PARTITION_NAME STATUS
------------------------------ --------
P01470_1 USABLE
P01480_1 USABLE
P01490_1 USABLE
P01500_1 USABLE
P01510_1 USABLE
P01520_1 USABLE
P01530_1 USABLE
P01540_1 USABLE
P01550_1 USABLE
P01560_1 UNUSABLE
P01570_1 UNUSABLE
P01580_1 USABLE
P01590_1 USABLE
PMAX USABLE

14 rows selected.

13:28:27 SQL> alter index arbor.BILL_INVOICE_DETAIL_PK rebuild partition P01560_1 ;

Index altered.

Elapsed: 00:04:27.71
13:33:26 SQL> alter index arbor.BILL_INVOICE_DETAIL_PK rebuild partition P01570_1 ;

Index altered.

Elapsed: 00:01:47.79
13:36:04 SQL> select PARTITION_NAME, status from dba_ind_partitions where index_name='BILL_INVOICE_DETAIL_PK';

PARTITION_NAME STATUS
------------------------------ --------
P01470_1 USABLE
P01480_1 USABLE
P01490_1 USABLE
P01500_1 USABLE
P01510_1 USABLE
P01520_1 USABLE
P01530_1 USABLE
P01540_1 USABLE
P01550_1 USABLE
P01560_1 USABLE
P01570_1 USABLE
P01580_1 USABLE
P01590_1 USABLE
PMAX USABLE

14 rows selected.

ora-25143 can not change next extent size for locally managed tablespace with uniform size

--can not change next extent size for locally managed tablespace with uniform size.

SYS@FMSP> alter tablespace FMS_CUSTOMER_INDEX default storage (next 1048576);
alter tablespace FMS_CUSTOMER_INDEX default storage (next 1048576)
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy



fms02@/software/orafms/admin/FMSP/bdump> oerr ora 25143
25143, 00000, "default storage clause is not compatible with allocation policy"
// *Cause: default storage clause was specified for a tablespace with
// AUTOALLOCATE or UNIFORM policy
// *Action: Omit the storage clause

Temporary segments do not span tablespaces of Temporary Tablespaces Group

I took for granted that single session with one big sort can benefit from tablespace group.
However, it is wrong ! No wonder our data warehouse often hit ora-1652.
--before make change , temp1,2,3 size is 16gb,16gb,19gb respectively
--This is means available temporary range is 16gb to 19gb.

The relevant metalink doc is 245645.1 and 248712.1

Reason is quite simple , Temporary segments do not span tablespaces. This easy to understand, same as other segment.


-- change support id to use small temp3 , which is the default database temporary tablespace (check from database_properties table)

-- remove temp1, temp2,temp3 from temp_group

alter tablespace TEMP1 tablespace group '';
alter tablespace TEMP2 tablespace group '';
alter tablespace TEMP3 tablespace group '';

--drop temp2

drop tablespace temp2 including contents and datafiles;

--epxand temp1;

--shrink temp3

--after make change , temp1,3 size is 40gb,10gb respectively
--assign application id to use big temporary tablespace


Since we don't parallel DML & have limited diskspace, tablespace group does't help.

From this practice, I think tablespace group is only good if you have lots of tablespace to create tablespaces with same size for round-robin assignment fashion.
Ideally, can create them on separate disks to reduce I/O contention.


How come important things is missed out in many articles searched by Google !?

%ROWCOUNT before and after COMMIT

LIQY@XE> declare
2 begin
3 insert into t1 values (100);
4 dbms_output.put_line(SQL%ROWCOUNT);
5 COMMIT;
6 dbms_output.put_line(SQL%ROWCOUNT);
7 end;
8 /
1
0

PL/SQL procedure successfully completed.

In 8i the output is the same , however behavior in 10g & 11g is as above.

I think it is related to COMMIT.

Tuning at the meeting

Near the end of business hour, my manager called me to attend a meeting related to slowness of data warehouse project.

without any info provided, during the briefing from the Vendor's DBA, lots of figure almost make me sleep. Maybe I was tired after whole day's hard work.

Finally, I realized :
1. The slowness is in development instead of production.
2. The partition key is not used.
3. The index is chosen. The cost shown in TOAD is 130. good value .?

Gathered more info from in-house developer, almost 1/6 rows are accessed in the partition.

Then was asked PGA, hash hint, nest hint ...


The join is in between a small table and a big partition. Feeling that FULL Table Scan should help.

Advise developer to hit the partition directly . And did a test of select count(*) from t partition(part-name), the elapsed time is about 4minutes.

Force to use FULL hint, took about the same time. However the cost shown to vendors is 500k. He looks not willing to try. instead, trying other hints, however needs to google syntax . I want to leave the meeting room, then I went to toilet.

When I came back, they were trying the FULL hint.

Run 4 concurrent sessions to hit different partitions, took the same timing , with 1k+ rows processed per second.

Application manager was so happy , asked everybody "happy with this ? " Saw some smile.


When I stepped the room, leaving 7 persons there continue testing happily more sessions. 6 Thanks to me, except the vendor's DBA.

Feeling COOL, it was almost 7:30pm.

Stressful if I didn't tune it on time. everybody was looking at me... even GM attended the meeting.



moreover, I use sqlplus defeated the TOAD while checking the execution plan.

install sqldeveloper on Ubuntu

1. copy it /opt directory
2. set JDK path to avoid prompt whenever launch sqldeveloper.sh
echo "/usr/lib/jvm/java-6-sun" > ~/.sqldevolper/jdk

Internet TV on Linux

Finally can see IPTV on Linux.

Thanks sopcast-player http://www.sopcast.com/download/

1. install libstdc++5 using Synaptic Package Manager
2. download & install sp-auth_3.0.1_amd64.deb
3. download & install sopcast-player_0.3.0-0ubuntu1_amd64.deb