Discussion :

[tropiko]

Salut les Experts ,
J'essaye de comprendre un truc bizarre concernant un SELECT sur une table 10g partitionnée (partition P1 et partition P2).

Le SELECT est très complexe (2 pages) alors pour simplifier :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT ... FROM TOTO where PARTITION=P1 ;

3 millions de lignes en 30 min.

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT ... FROM TOTO where PARTITION=P2 ;

3 millions de lignes en 2h !!!
Sachant que :

• Les 2 plans d'exécution sont IDENTIQUES.

• Le nombre de lignes est quasi-identique dans chaque partition (3 millions).

• Les stats table et partition sont identiques et à jour dans chaque partition P1 et P2.

Des idées ? Dois-je vérifier l'organisation de chaque partition ? HWM ?
Si réorganisation nécessaire , quel choix à faire :
-- SHRINK PARTITION

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE  TOTO MODIFY PARTITION P2 SHRINK SPACE;

OU
-- MOVE TABLE PARTITION

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

alter table TOTO move P2 tablespace TBS1;

merci pour votre aide

[pachot]

Bonjour,
Les deux plans d'exécution récupérés avec dbms_xplan.display_cursor(null,null,'ALLSTATS LAST') après exécution (et avec statistics_level=all) permettraient de comparer.
Cordialement,
Franck.

[tropiko]

Bonjour Fanck ,

J'ai mis ton instruction juste après mon INSERT ...SELECT comme suit mais message :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
alter session set statistics=all;
INSERT INTO TOTO SELECT <SELECT COMPLEX>...;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')) ;

SQL_ID 8gu8j4g64gyu4, child number 6
NOTE: cannot fetch plan for SQL_ID: 8gu8j4g64gyu4, CHILD_NUMBER: 6
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)

Sinon en général le temps d'un INSERT ...SELECT est "causé" surtout par le SELECT mais dans mon cas 50% du temps dans le select et 50% du temps environ dans l'INSERT !!!
En effet le temps de l'insert ...select = 2 fois le temps du SELECT tout seul.
NB :

• Le plan d'exécution affiché par l'INSERT ...SELECT (partie SELECT) = PLAN d'exécution du SELECT seul.

• Le pan est affiché par la méthode classique (Touche F5 du PL/SQL Developer et non par un dbms_xplan.display_cursor...).

• Il s'agit d'un comportement(régression) 10gr2.

• Majorité des jointures du SELECT sont des Nested Loop (no justifié parfois) : Existe-t-il un moyen global pour favoriser le USE_HASH et le FULL SCAN sans utiliser de hint use_hash et full pour chaque jointure ...(10 jointures et 50 tables ...).

Merci.

[pachot]

Bonjour,

Je ne sais pas pourquoi dbms_xplan ne trouve pas le plan en shared pool...

Existe-t-il un moyen global pour favoriser le USE_HASH et le FULL SCAN

Le meilleur moyen: avoir de bonnes stats.

Nested Loop (no justifié parfois)

Il faudrait regarder le nombre de lignes estimées. Un nested loop non justifié, c'est quand la première table du nested loop ramène beaucoup de lignes alors que l'optimiseur en estimait très peu.

Une hypothèse à l'aveugle: le calcul de stats a été fait lorsque P1 était pleine mais P2 vide ou du moins pas encore pleine.

Cordialement,
Franck.

[mnitu]

...
Je ne sais pas pourquoi dbms_xplan ne trouve pas le plan en shared pool...
...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SQL> ALTER session SET statistics=ALL;

au lieu de

...
avec statistics_level=all
...

[tropiko]

Bonjour ,

C'est plutôt STATISTICS_LEVEL=ALL

http://docs.oracle.com/cd/B19306_01/...tparams211.htm