Bonjour a tous,

la questio est de savoir si pour certaines requetes passer a un block size de 16k est plus interessant que le par default 8k. (ORACLE 9i - HP UX)

Pour demarrer j'ai fait ceci:

1 alouer espace: db_16k_cache_size big integer 25165824
2 creer un tbs test_16k_ts de 50M avec blocksize TEST_16K_TS 16384
3 creer un user test avec default tbs le nouveau...
4 creation des memes tables meme structure, meme données meme index etc sur le nouveua schema.
5 EXEC DBMS_STATS.gather_schema_stats('test');
6 Executer la meme query sur les 2 environnements:

Environement a 16k

Code : Sélectionner tout - Visualiser dans une fenêtre à part
------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 562 | 27538 | 80 | | 1 | SORT ORDER BY | | 562 | 27538 | 80 | |* 2 | FILTER | | | | | |* 3 | HASH JOIN | | 562 | 27538 | 73 | |* 4 | TABLE ACCESS FULL| RHUPTSOLICITUD | 562 | 10116 | 37 | | 5 | TABLE ACCESS FULL| RHUDIRECCION | 47883 | 1449K| 33 | ------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 -
J'ai activé l'event 10046 pour tracer la session, voici les données:

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
filter(TO_DATE('03/03/2009')<=TO_DATE('04/03/2009')) 3 - access("A"."IDP"="B"."IDP") 4 - filter("A"."FCHUPDATE">='03/03/2009' AND "A"."FCHUPDATE"<='04/ 03/2009')
 
Note: cpu costing is off END OF STMT PARSE #1:c=0,e=1499,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=18446744071645767046 EXEC #1:c=0,e=104,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=18446744071645986652 WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1111838976 p2=1 p3=0 WAIT #1: nam='db file sequential read' ela= 250 p1=11 p2=5 p3=1 WAIT #1: nam='db file scattered read' ela= 495 p1=11 p2=6 p3=3 WAIT #1: nam='db file scattered read' ela= 575 p1=11 p2=9 p3=4 WAIT #1: nam='db file scattered read' ela= 1764 p1=11 p2=13 p3=4 WAIT #1: nam='db file scattered read' ela= 523 p1=11 p2=17 p3=4 WAIT #1: nam='db file scattered read' ela= 891 p1=11 p2=21 p3=4 WAIT #1: nam='db file scattered read' ela= 484 p1=11 p2=25 p3=4 WAIT #1: nam='db file scattered read' ela= 529 p1=11 p2=29 p3=4 WAIT #1: nam='db file scattered read' ela= 10721 p1=11 p2=33 p3=4 WAIT #1: nam='db file scattered read' ela= 498 p1=11 p2=37 p3=4 WAIT #1: nam='db file scattered read' ela= 486 p1=11 p2=41 p3=4 WAIT #1: nam='db file scattered read' ela= 567 p1=11 p2=45 p3=4 WAIT #1: nam='db file scattered read' ela= 9532 p1=11 p2=49 p3=4 WAIT #1: nam='db file scattered read' ela= 538 p1=11 p2=53 p3=4 WAIT #1: nam='db file scattered read' ela= 501 p1=11 p2=57 p3=4 WAIT #1: nam='db file scattered read' ela= 629 p1=11 p2=61 p3=4
environement a 8k
Code : Sélectionner tout - Visualiser dans une fenêtre à part
------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 562 | 27538 | 102 | | 1 | SORT ORDER BY | | 562 | 27538 | 102 | |* 2 | FILTER | | | | | |* 3 | HASH JOIN | | 562 | 27538 | 95 | |* 4 | TABLE ACCESS FULL| RHUPTSOLICITUD | 562 | 10116 | 49 | | 5 | TABLE ACCESS FULL| RHUDIRECCION | 47883 | 1449K| 43 | ------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 -
filter(TO_DATE('03/03/2009')<=TO_DATE('04/03/2009')) 3 -

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
access("A"."IDP"="B"."IDP") 4 - filter("A"."FCHUPDATE">='03/03/2009' AND "A"."FCHUPDATE"<='04/ 03/2009')
Note: cpu costing is off END OF STMT PARSE #1:c=0,e=1433,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=18446744072667060037 EXEC #1:c=0,e=82,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=18446744072667246339 WAIT #1: nam='SQL*Net message to client' ela= 4 p1=1111838976 p2=1 p3=0 WAIT #1: nam='db file sequential read' ela= 3536 p1=9 p2=11755 p3=1 WAIT #1: nam='db file scattered read' ela= 418 p1=9 p2=11756 p3=5 WAIT #1: nam='db file scattered read' ela= 566 p1=9 p2=11761 p3=8 WAIT #1: nam='db file scattered read' ela= 20332 p1=9 p2=11770 p3=7 WAIT #1: nam='db file scattered read' ela= 523 p1=9 p2=11777 p3=8 WAIT #1: nam='db file scattered read' ela= 14027 p1=9 p2=11786 p3=7 WAIT #1: nam='db file scattered read' ela= 523 p1=9 p2=11793 p3=8 WAIT #1: nam='db file scattered read' ela= 472 p1=9 p2=11802 p3=7 WAIT #1: nam='db file scattered read' ela= 494 p1=9 p2=11809 p3=8 WAIT #1: nam='db file scattered read' ela= 12242 p1=9 p2=11818 p3=7 WAIT #1: nam='db file scattered read' ela= 553 p1=9 p2=11825 p3=8 WAIT #1: nam='db file scattered read' ela= 18561 p1=9 p2=11834 p3=7 WAIT #1: nam='db file scattered read' ela= 16248 p1=9 p2=11841 p3=8 WAIT #1: nam='db file scattered read' ela= 440 p1=9 p2=11850 p3=7 WAIT #1: nam='db file scattered read' ela= 504 p1=9 p2=11857 p3=8 WAIT #1: nam='db file scattered read' ela= 491 p1=9 p2=11866 p3=7
ca a l'air d'etre mieux quand meme non?

Sur un autre forim, on a parlé de la contiguité des clefs...j'ai pas tres bien compris ce concept...Ici quel est le numero qui nous dit ca? c'est p2?
D'avance merci