Dünyada 10 çeşit insan vardır, binary bilenler ve bilmeyenler.

Salı, Ocak 16, 2007

 

Taşındık

erturkdiriksoy.wordpress.com
* eğer bir yerde blog adresim geçiyor ise güncellemenizi rica ediyorum.

Pazar, Ocak 14, 2007

 

CBO - Chapter 2 - calc_mbrc.sql

rem
rem Script: calc_mbrc.sql
rem Author: Jonathan Lewis
rem Dated: March 2002
rem Purpose: Calculate cost of tablescan for changing values of mbrc
rem
rem Versions tested
rem 10.1.0.2
rem 9.2.0.4
rem 8.1.7.4
rem
rem Notes
rem Cost with cpu_costing is higher than cost with nocpu_costing
rem Your system may stop changing before it gets to 128 blocks,
rem it could go higher - the limit is usually dependent on the
rem operating system and the current block size, and may top out
rem when 'scan size * block_size'reaches 1 MB.
rem
rem Note:
rem The script requires you to create a plan_table as it cycles
rem through the values for db_file_multiblock_read_count, explaining
rem a tablescan into the plan_table to find the cost.
rem
rem This script creates a fairly small table (1,000 blocks), but we then
rem use dbms_stats() to tell Oracle that it was a very big table (say 10M
rem blocks)
rem
rem The number of blocks to fake is the input parameter
rem
rem The code caters for CPU Costing on and off for 9i and above,
rem so is more complex than it needs to be for Oracle 8
rem

start setenv
set timing off

define m_blocks=128000
rem define m_blocks = &1

alter session set optimizer_mode = all_rows;

drop table t1;

create table t1
nologging
pctfree 90
pctused 10
storage (initial 40M)
as
select
rownum id,
to_char(rownum) vc_small,
rpad('x',1000) padding
from
all_objects
where
rownum <= 1000 ; analyze table t1 compute statistics; rem rem Over-ridden by: rem begin dbms_stats.set_table_stats( ownname => null,
tabname =>'T1',
numrows => &m_blocks,
numblks => &m_blocks,
avgrlen => 3500,
flags => 0
);
end;
/


delete from plan_table;
commit;

begin
for r in 1..128 loop

execute immediate
'alter session set db_file_multiblock_read_count = ' || r;

execute immediate
'explain plan set statement_id = ''' ||
to_char(r,'fm000') || 'N'' for ' ||
' select /*+ nocpu_costing */ count(*) from t1';

execute immediate
'explain plan set statement_id = ''' ||
to_char(r,'fm000') || 'C'' for ' ||
' select /*+ cpu_costing */ count(*) from t1';

end loop;
end;
.
/

spool calc_mbrc

set linesize 100
set pagesize 90

rem
rem We use the 'cost - 1' for Oracle 9.2 because it has
rem _tablescan_cost_plus_one = true;
rem whereas Oracle 8.1.7 has
rem _tablescan_cost_plus_one = false;
rem

select
to_number(substr(statement_id,1,3)) id,
cost act_cost,
round(&m_blocks/to_number(substr(statement_id,1,3)),0) old_cost,
round(&m_blocks/cost,3) eff_mbrc
-- round(&m_blocks/(cost-1),3) eff_mbrc
from
plan_table
where
id = 0
and statement_id like '%N%'
order by
statement_id
;

spool off



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

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.1.0

1 row selected.


SQL> @@ch_02_tablescans/calc_mbrc.sql; -- (for cpu_costing hint)




1 row selected.


Session altered.


Session altered.


Table dropped.


Table created.


Table analyzed.


PL/SQL procedure successfully completed.


768 rows deleted.


Commit complete.


PL/SQL procedure successfully completed.


ID ACT_COST OLD_COST EFF_MBRC
---------- ---------- ---------- ----------
1 128245 128000 ,998
2 74912 64000 1,709
3 57135 42667 2,24
4 48245 32000 2,653
5 42912 25600 2,983
6 39357 21333 3,252
7 36817 18286 3,477
8 34912 16000 3,666
9 33431 14222 3,829
10 32245 12800 3,97
11 31276 11636 4,093
12 30468 10667 4,201
13 29784 9846 4,298
14 29198 9143 4,384
15 28690 8533 4,461
16 28245 8000 4,532
17 27854 7529 4,595
18 27505 7111 4,654
19 27193 6737 4,707
20 26912 6400 4,756
21 26658 6095 4,802
22 26428 5818 4,843
23 26217 5565 4,882
24 26023 5333 4,919
25 25845 5120 4,953
26 25682 4923 4,984
27 25530 4741 5,014
28 25389 4571 5,042
29 25257 4414 5,068
30 25135 4267 5,093
31 25020 4129 5,116
32 24912 4000 5,138
33 24811 3879 5,159
34 24716 3765 5,179
35 24627 3657 5,198
36 24542 3556 5,216
37 24462 3459 5,233
38 24386 3368 5,249
39 24314 3282 5,264
40 24245 3200 5,279
41 24181 3122 5,293
42 24119 3048 5,307
43 24060 2977 5,32
44 24003 2909 5,333
45 23949 2844 5,345
46 23898 2783 5,356
47 23849 2723 5,367
48 23801 2667 5,378
49 23756 2612 5,388
50 23712 2560 5,398
51 23671 2510 5,407
52 23630 2462 5,417
53 23592 2415 5,426
54 23554 2370 5,434
55 23518 2327 5,443
56 23484 2286 5,451
57 23450 2246 5,458
58 23418 2207 5,466
59 23387 2169 5,473
60 23357 2133 5,48
61 23328 2098 5,487
62 23299 2065 5,494
63 23272 2032 5,5
64 23245 2000 5,507
65 23220 1969 5,512
66 23195 1939 5,518
67 23171 1910 5,524
68 23148 1882 5,53
69 23125 1855 5,535
70 23103 1829 5,54
71 23081 1803 5,546
72 23060 1778 5,551
73 23040 1753 5,556
74 23020 1730 5,56
75 23001 1707 5,565
76 22983 1684 5,569
77 22964 1662 5,574
78 22947 1641 5,578
79 22929 1620 5,582
80 22912 1600 5,587
81 22896 1580 5,59
82 22880 1561 5,594
83 22864 1542 5,598
84 22849 1524 5,602
85 22834 1506 5,606
86 22819 1488 5,609
87 22805 1471 5,613

ID ACT_COST OLD_COST EFF_MBRC
---------- ---------- ---------- ----------
88 22791 1455 5,616
89 22778 1438 5,619
90 22764 1422 5,623
91 22751 1407 5,626
92 22738 1391 5,629
93 22726 1376 5,632
94 22714 1362 5,635
95 22702 1347 5,638
96 22690 1333 5,641
97 22679 1320 5,644
98 22667 1306 5,647
99 22656 1293 5,65
100 22645 1280 5,652
101 22635 1267 5,655
102 22625 1255 5,657
103 22615 1243 5,66
104 22605 1231 5,662
105 22595 1219 5,665
106 22585 1208 5,667
107 22576 1196 5,67
108 22567 1185 5,672
109 22558 1174 5,674
110 22549 1164 5,677
111 22540 1153 5,679
112 22531 1143 5,681
113 22523 1133 5,683
114 22515 1123 5,685
115 22507 1113 5,687
116 22499 1103 5,689
117 22491 1094 5,691
118 22483 1085 5,693
119 22475 1076 5,695
120 22468 1067 5,697
121 22461 1058 5,699
122 22453 1049 5,701
123 22446 1041 5,703
124 22439 1032 5,704
125 22432 1024 5,706
126 22426 1016 5,708
127 22419 1008 5,709
128 22412 1000 5,711


SQL> / --( for nocpu_costing)

ID ACT_COST OLD_COST EFF_MBRC
---------- ---------- ---------- ----------
1 76353 128000 1,676
2 48383 64000 2,646
3 37051 42667 3,455
4 30660 32000 4,175
5 26472 25600 4,835
6 23479 21333 5,452
7 21213 18286 6,034
8 19429 16000 6,588
9 17980 14222 7,119
10 16775 12800 7,63
11 15755 11636 8,124
12 14878 10667 8,603
13 14115 9846 9,068
14 13443 9143 9,522
15 12846 8533 9,964
16 12312 8000 10,396
17 11830 7529 10,82
18 11394 7111 11,234
19 10995 6737 11,642
20 10630 6400 12,041
21 10294 6095 12,434
22 9984 5818 12,821
23 9696 5565 13,201
24 9428 5333 13,577
25 9178 5120 13,946
26 8945 4923 14,31
27 8725 4741 14,67
28 8519 4571 15,025
29 8324 4414 15,377
30 8141 4267 15,723
31 7967 4129 16,066
32 7802 4000 16,406
33 7646 3879 16,741
34 7497 3765 17,073
35 7355 3657 17,403
36 7220 3556 17,729
37 7091 3459 18,051
38 6968 3368 18,37
39 6850 3282 18,686
40 6737 3200 19
41 6628 3122 19,312
42 6524 3048 19,62
43 6424 2977 19,925
44 6327 2909 20,231
45 6234 2844 20,533
46 6145 2783 20,83
47 6058 2723 21,129
48 5975 2667 21,423
49 5895 2612 21,713
50 5817 2560 22,004
51 5741 2510 22,296
52 5668 2462 22,583
53 5598 2415 22,865
54 5529 2370 23,151
55 5463 2327 23,43
56 5399 2286 23,708
57 5336 2246 23,988
58 5275 2207 24,265
59 5216 2169 24,54
60 5159 2133 24,811
61 5103 2098 25,083
62 5049 2065 25,352
63 4996 2032 25,62
64 4945 2000 25,885
65 4894 1969 26,154
66 4845 1939 26,419
67 4798 1910 26,678
68 4751 1882 26,942
69 4706 1855 27,199
70 4661 1829 27,462
71 4618 1803 27,718
72 4576 1778 27,972
73 4534 1753 28,231
74 4494 1730 28,482
75 4455 1707 28,732
76 4416 1684 28,986
77 4378 1662 29,237
78 4341 1641 29,486
79 4305 1620 29,733
80 4269 1600 29,984
81 4235 1580 30,224
82 4201 1561 30,469
83 4167 1542 30,718
84 4134 1524 30,963
85 4102 1506 31,204
86 4071 1488 31,442
87 4040 1471 31,683

ID ACT_COST OLD_COST EFF_MBRC
---------- ---------- ---------- ----------
88 4010 1455 31,92
89 3980 1438 32,161
90 3951 1422 32,397
91 3922 1407 32,636
92 3894 1391 32,871
93 3867 1376 33,101
94 3840 1362 33,333
95 3813 1347 33,569
96 3787 1333 33,8
97 3761 1320 34,034
98 3736 1306 34,261
99 3711 1293 34,492
100 3686 1280 34,726
101 3662 1267 34,954
102 3639 1255 35,174
103 3615 1243 35,408
104 3592 1231 35,635
105 3570 1219 35,854
106 3548 1208 36,077
107 3526 1196 36,302
108 3504 1185 36,53
109 3483 1174 36,75
110 3462 1164 36,973
111 3442 1153 37,188
112 3422 1143 37,405
113 3402 1133 37,625
114 3382 1123 37,847
115 3363 1113 38,061
116 3343 1103 38,289
117 3325 1094 38,496
118 3306 1085 38,717
119 3288 1076 38,929
120 3270 1067 39,144
121 3252 1058 39,36
122 3234 1049 39,579
123 3217 1041 39,789
124 3200 1032 40
125 3183 1024 40,214
126 3166 1016 40,43
127 3150 1008 40,635
128 3134 1000 40,842

128 rows selected.

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


As seen above, as mbrc goes up cost did not fall as mbrc's rise. Because operating system's block size is a limit for oracle multiple block reads.
There is difference between nocpu_costing and cpu_costing. cpu_costing is allways, higher because when calculating cost, CBO estimates cost from a formula has a parameter for cpu_cost. This means when nocpu_costing hint given CBO will put 0 into this formula for cpu cost.

What is the main difference between ACT_COST & OLD_COST columns?

Salı, Aralık 26, 2006

 

CBO - Intro

- no magic setting ( as we already know )
- never believe all you read
- in_list example:

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.1.0

1 row selected.

SQL> @@/opt/oracle/cbo_files/ch_00_preface/setenv




1 row selected.


Session altered.

SQL> @@/opt/oracle/cbo_files/ch_00_preface/in_list
SP2-0310: unable to open file "setenv.sql"

Table dropped.


PL/SQL procedure successfully completed.


Table created.


PL/SQL procedure successfully completed.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 20600 | 3 |
|* 1 | TABLE ACCESS FULL| T1 | 200 | 20600 | 3 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("N1"=1 OR "N1"=2)

Note
-----
- cpu costing is off (consider enabling it)




as seen above 10g forseen cost as 3, 9i 8 estimated cost as 4. I will learn it why.


if we set "_optimizer_cost_model"=cpu


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 20600 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 200 | 20600 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("N1"=1 OR "N1"=2)


if we set "_optimizer_cost_model"=fixed


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 20600 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 200 | 20600 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("N1"=1 OR "N1"=2)



if we set "_optimizer_cost_model"=choose


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 20600 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 200 | 20600 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("N1"=1 OR "N1"=2)



So 10g did the right cardinality as 9i. But Why cost matters


----------------------------------------------------------------------
My friends and I just started to running over Jonathan Lewis'es CBO book I will post comments for all chapters.

Cumartesi, Kasım 11, 2006

 

enable kernel module development on ubuntu 6.06

(choose ur kernel ver.)
$ sudo apt-get install linux-headers-2.6.15-27-686
$ sudo ln -s /usr/src/linux-headers-2.6.15-27-686/ build



if you have a file my_module.c, use this Makefile:

obj-m := my_module.o

KDIR := /lib/modules/$(shell uname -r)/build
PWD := $(shell pwd)

default:
$(MAKE) -C $(KDIR) SUBDIRS=$(PWD) modules

Perşembe, Kasım 09, 2006

 

Xalan da java

XML nerde transform etsek? Xalan gayet bir çözüm gibi duruyor. Özellikle Oracle XDK Java da bulunmayan XSL içinden java wrap etmesi gayet hoş :).

Elliotte Rusty Harold,makalesinde belirtildigi uzere XSL functionaldır*. OO olan java nın dikkatli çağırılması gerekir.
- oldukça deterministic olan methodlar çağırmak func. yapıyı çok sarsmaz (özellikle static)
- object instance ları üzerinde çalışırken, type overloading e dikkat etmek gerekir. Mümkün oldukça simple typelar. Ve Xalan'ın type mapping tercihleri önemlidir. Java <-> XSL type mappingler bire bir değildir.
- henüz exceptionlar desteklenmiyor

http://www-128.ibm.com/developerworks/java/library/x-xalanextensions.html?ca=drs-

Perşembe, Ekim 05, 2006

 

Kyte babadan gülleler

Removing Duplicates

Please explain how to remove duplicate records quickly in a single run from a large table containing about 5 million records. I tried it with the following query, but it takes 10 hours:

delete from test1
where rowid not in (select min(rowid)
from test1 group by rc_no);

Even after increasing the rollback segment tablespace to 7GB, we are not getting the desired results.

If you are using the rule-based optimizer (RBO), there is a very good chance that the NOT IN query is being evaluated once for each row in the outer query! So, hopefully, you are using the much smarter cost-based optimizer (CBO).

But in any case, my approach to removing duplicates is a little different. I would generate the set of rowids to delete by using analytics and then delete them, like this:

SQL> select count(*),
2 count(distinct cust_seg_nbr)
3 from t;

COUNT(*) COUNT(CUST_SEG_NBR)
--------- -------------------
1871652 756667

Here you can see that I have more than 1.8 million rows but only 756,667 unique ones. I need to delete about 1.1 million of my rows (lots). Let's see how speedy this can be:

delete from t
where rowid in
(select rid
from
(select rowid rid,
row_number() over
(partition by
cust_seg_nbr
order by rowid) rn
from t
)
where rn <> 1
)
1114985 rows deleted.
Elapsed: 00:01:46.06

SQL> select count(*),
2 count(distinct cust_seg_nbr)
3 from t;
COUNT(*) COUNT(DISTINCTCUST_SEG_NBR)
--------- ---------------------------
756667 756667

Now, that was the speed on a laptop computer (so your mileage may vary). Also, in my case, the table was not indexed. Each index is going to add more processing time. Consider disabling your indexes for a mass duplicate removal like this and then rebuilding your indexes afterward. Index maintenance can be very expensive on a large bulk operation in which you plan to delete most of the data.

As for the rollback size, it will necessarily get as big as it needs to be in order to process the delete. Every index you have will make the delete need more space, in addition to making it take more time.

Another very viable option when mass-deleting data is to create a new table that keeps just the right records and drops the old table, as in the following example:

SQL> select count(*),
2 count(distinct cust_seg_nbr)
3 from t;

COUNT(*) COUNT(CUST_SEG_NBR)
--------- -------------------
1871652 756667

create table t2
as
select cust_seg_nbr, ...
from
(select t.*,
row_number() over
(partition by cust_seg_nbr
order by rowid) rn
from t
)
where rn = 1
/
Table created.
Elapsed: 00:00:10.93

As you can see, it took 11 seconds in this example to copy out the rows to keep, versus 1 minute and 46 seconds to remove the rows you don't want, and you can do this without generating any UNDO (rollback) or REDO to boot.



ref : http://www.oracle.com/technology/oramag/oracle/04-jul/o44asktom.html

Pazartesi, Ekim 02, 2006

 

Neden medeniyeti ve kardeşliği, uzaklarda değil yaşadığımız yerde aramıyoruz?

Mevlana ve Hacı Bektaş

Bir adam kötü yoldan para kazanıp bununla kendisine bir inek alır.

Neden sonra, yaptıklarından pişman olur ve 'iyi bir şey yapmış olmak' adına bunu Hacı Bektaş Veli'nin dergahına kurban olarak bağışlamak ister.

O zamanlar dergahlar aynı zamanda aşevi işlevi görüyordu.

Durumu Hacı Bektaş Veli'ye anlatır ve Hacı Bektaş Veli 'helal değildir' diyerek bu bağışı geri çevirir.

Bunun üzerine adam Mevlevi dergahına gider ve aynı durumu Mevlana'ya anlatır. Mevlana, bu hediyeyi kabul eder.

Adam aynı şeyi Hacı Bektaş Veli'ye de anlattığını ama onun bunu kabul etmemiş olduğunu söyleyip, Mevlana'ya bunun sebebini sorar. Mevlana söyle der:

- Biz bir karga isek Hacı Bektaş Veli bir şahin gibidir. Öyle her leşe konmaz. O yüzden senin bu hediyeni biz kabul ederiz ama o kabul etmeyebilir.

Adam üşenmez kalkıp Hacı Bektaş Dergahı'na gider ve Hacı Bektaş Veli'ye gider.

Mevlana'nın kurbanı kabul ettiğini söyleyip, bunun sebebini bir de Hacı Bektaş Veli'ye sorar. Hacı Bektaş da şöyle der:

- Bizim gönlümüz bir su birikintisi ise Mevlana'nın gönlü okyanus gibidir. Bu yüzden, bir damlayla bizim gönlümüz kirlenebilir ama onun engin gönlü kirlenmez. Bu sebepten dolayı o senin hediyeni kabul etmiştir.

--> http://www.aksam.com.tr/yazar.asp?a=54329,10,11


Archives

Mayıs 2005   Haziran 2005   Temmuz 2005   Ekim 2005   Mayıs 2006   Haziran 2006   Temmuz 2006   Ağustos 2006   Eylül 2006   Ekim 2006   Kasım 2006   Aralık 2006   Ocak 2007  

This page is powered by Blogger. Isn't yours?