dear andes,
mungkin sample berikut ini bisa membantu??
==> buat table partisi :)
create table th (id number, modd number, padding varchar2(40), rrnum number)
tablespace ts1 partition by hash(id) partitions 4
store in (ts1,ts2);
==> isi data
kolom id = unik
kilom modd = mendekati unik
declare
r pls_integer;
strnd varchar2(12);
begin
for i in 1..2000 loop
r := round(dbms_random.value(1,99999999),0);
strnd := dbms_random.string('A',12);
insert into th values(i,r+5,strnd,r/12);
end loop;
commit;
end;
/
create index idx_th1 on th(modd) compute statistics;
==> gather statistics, buat histogram semua kolom
dgn bucket size 1 (frequency histogram).
begin
dbms_stats.gather_table_stats(
OWNNAME => 'SCOTT',
TABNAME => 'TH',
METHOD_OPT => 'for columns MODD size 1',
CASCADE => true);
end;
/
==> expected result...
select * from th where modd=900;
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 2
(0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| TH | 1 | 36 | 2
(0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | IDX_TH1 | 1 | | 1
(0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------
==> modd=1 yg asalnya ada 109 record, ganti ke 10 sehingga
modd=1 tinggal 9.
update th set modd=10 where modd=1 and rownum<=100;
begin
dbms_stats.gather_table_stats(
OWNNAME => 'SCOTT',
TABNAME => 'TH',
METHOD_OPT => 'for columns MODD size 1',
CASCADE => true);
end;
/
==> dengan bucket yg kurang tepat,
even ada index, tapi ndak kepake
karena berasumsi bahwa
1* select column_name,endpoint_number,
endpoint_value,endpoint_actual_value from user_histograms where
table_name='TH'
SQL> /
COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
---------- --------------- -------------- --------------------
MODD 0 1
MODD 1 10
==> dari informasi histogram ini,
oracle berasumsi bahwa modd=1 adalah
(2000/10)=200
200/2000 = 10%
oracle berasumsi gak efisien dgn scanning index karena >5%
select * from th where modd=1;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
Pstart| Pstop |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 7200 | 5 (0)| 00:00:01 |
| |
| 1 | PARTITION HASH ALL| | 200 | 7200 | 5 (0)| 00:00:01 |
1 | 4 |
|* 2 | TABLE ACCESS FULL| TH | 200 | 7200 | 5 (0)| 00:00:01 |
1 | 4 |
-------------------------------------------------------------------------------------------
begin
dbms_stats.gather_table_stats(
OWNNAME => 'SCOTT',
TABNAME => 'TH',
METHOD_OPT => 'for columns MODD size 10',
CASCADE => true);
end;
/
SQL> select column_name,endpoint_number,
endpoint_value,endpoint_actual_value from user_histograms where
table_name='TH';
COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
---------- --------------- -------------- --------------------
MODD 228 2
MODD 442 3
MODD 689 4
MODD 886 5
MODD 1115 6
MODD 1347 7
MODD 1572 8
MODD 1789 9
MODD 2000 10
MODD 9 1
==> disini oracle tau distribusi data
karena informasi dari histogram secara akurat tahu
bahwa modd=1 cuman berisi 9 rows saja,
makanya akan lebih baik menggunakan index.
select * from th where modd=1;
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 324 | 2
(0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| TH | 9 | 324 | 2
(0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | IDX_TH1 | 9 | | 1
(0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------
select * from th where modd=10;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
Pstart| Pstop |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 211 | 7596 | 5 (0)| 00:00:01 |
| |
| 1 | PARTITION HASH ALL| | 211 | 7596 | 5 (0)| 00:00:01 |
1 | 4 |
|* 2 | TABLE ACCESS FULL| TH | 211 | 7596 | 5 (0)| 00:00:01 |
1 | 4 |
-------------------------------------------------------------------------------------------
--
best regards
Oracle DBA
http://www.dbs247.com
http://blog.dbs247.com
2009/4/20 Andes Febrian <[email protected]>
> pak bw, gimana cara mastikan statistics dan histogram itu sudah di update ?
>
> kemudian, apa syntax untuk statistics dan histogram ini ?
>
> apa dgn update statistics dan histogram pada tabel, akan memberikan hasil
> pada execution plan tersebut ?
>
> Many Thanks
>
> 2009/4/17 Yulius Wibowo <[email protected]>
>
> >
> >
> > Penyebab lain kenapa penggunaan tablespace TEMP di 10g lebih banyak
> > dibandingkan 9i adalah karena "execution plan" yg berbeda.
> >
> > Misal:
> > Ketika SQL statement dijalankan di 9i execution plannya menggunakan
> NESTED
> > LOOP,
> > sedangkan di 10g berubah menjadi SORT MERGE atau HASH ...
> > dan parahnya lagi table yg dipakai utk sortir adalah table yg besar,
> > sehingga penggunaan tablespace TEMP membengkak.
> >
> > Ini disebabkan karena optimizer (optimizer_features_enable) yg dipakai
> > berbeda antara 9i dan 10g.
> >
> > Pastikan statistics dan juga histogram sudah diupdate setelah migrasi.
> >
> > bw
> >
> >
> > --- In [email protected] <indo-oracle%40yahoogroups.com>,
> Andrew
> > Krisna <endru...@...> wrote:
> > >
> > > aloo
> > >
> > > Mau nambahin aja nih.
> > >
> > > Seabis kamu upgrade apakah sudah di monitor penggunaan memory nya?
> Biasa
> > sorting akan lari ke TEMP tablespace jika memory untuk sorting tidak
> > cukup(bisa check "pga_aggregate_target" parameter).
> > > Nah kalo TEMP tablespace ini memang sih sebaiknya tidak dibikin
> > autoextend unlimited, tp inget juga kalo tidak cukup dan perlu space maka
> > user yang udah jalanin reporting lama2 bisa gagal gara2 space nya gak
> cukup
> > :) alhasil user telpon DBAnya trus ngamuk2 :)
> > >
> > > Untuk itu kudu dimonitor query2 yg memerlukan sorting besar, dan karena
> > ini skrg pake 10g, bisa dibilang pemakain memorynya lebih boros sedikit.
> So
> > jangan malu2 digedein sedikit2 parameternya "pga_aggregate_target" dan
> > dimonitor performance nya.
> > >
> > >
> > >
> > > cheers,
> > >
> > >
> > > Andrew
> > >
> > >
> > >
> > >
> > > ________________________________
> > > From: Martin <mar...@...>
> > > To: [email protected] <indo-oracle%40yahoogroups.com>
> > > Sent: Wednesday, 15 April 2009 3:48:56
> > > Subject: [indo-oracle] temp di 10 g
> > >
> > >
> > >
> > >
> > >
> > > suhu tanya dong
> > >
> > > kenapa setelah upgrade dari 9i ke 10 G penggunaan table space temporary
> > > meningkat tajam.
> > >
> > > di 9i --> 3 Gb di 10g sudah sampai ke 17 G
> > >
> > > ask
> > > 1. kira-kira apa yang perlu saya cek dan setup ulang yah
> > > 2. Apa sih yang menggunakan temp sampai segitu banyak
> > > - Run report dari oracle dev 6i ( analisa saya saat ini )
> > > - ........
> > >
> > > salam
> > >
> > > martin
> > >
> > >
> > >
> > >
> > >
> > > New Email names for you!
> > > Get the Email name you've always wanted on the new @ymail and
> > @rocketmail.
> > > Hurry before someone else does!
> > > http://mail.promotions.yahoo.com/newdomains/sg/
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
>
>
>
> --
> Cheers,
> ^_^ Andes ^_^
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> --
> -----------I.N.D.O - O.R.A.C.L.E---------------
> Keluar: [email protected]
> Website: http://indooracle.wordpress.com
> http://www.facebook.com/group.php?gid=51973053515
> -----------------------------------------------
>
> Bergabung dengan Indonesia Thin Client User Groups,
> Terminal Server, Citrix, New Moon Caneveral, di:
> http://indo-thin.blogspot.comYahoo! Groups Links
>
>
>
>
[Non-text portions of this message have been removed]
