analyse en fonction des rows

Version imprimable

Salut a tous
en lisant les differents forum, j'ai capté une table de corespondance entre le pourcentage qu'il faut donner a la commande "ANALYSE" et le nombre de lignes dans la table.
Voici cette correspondance:
Code:

1 2 3 4 5 Fewer than 10,000 rows: Exact calculation (COMPUTE) Fewer than 100,000 rows: ESTIMATE with 30 % of the table Fewer than 1,000,000 rows: ESTIMATE with 10 % of the table Fewer than 10,000,000 rows: ESTIMATE with 3 % of the table More than 10,000,000 rows: ESTIMATE with 1% of the table
Vous confirmez cette table? ou avez-vous une approche differente?
D?autre part, je voudrais avec SQL ou PL/SQL (surement plus facil en PL)
faire une requete qui consulte les tables du shema, et en fonction du nombre de lignes, crée dynamiquement les commande ANALYSE qui correspondent.
J'ai commencé mais je coince dans les IF, peu etre ce script existe deja...
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 DECLARE v_table_Name VARCHAR(50); v_nrow number; begin SELECT TABLE_NAME, NUM_ROWS INTO v_table_name, v_nrow FROM DBA_TABLES; IF v_nrow >0 AND v_nrow <10000 THEN DBMS_OUTPUT.PUT_LINE('ANALYZE '||TABLE_NAME||' ESTIMATE STATISTICS COMPUTE'); ELSE IF v_nrow >10001 AND v_nrow <100000 THEN DBMS_OUTPUT.PUT_LINE('ANALYZE '||TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 30 PERCENT'); IF v_nrow >100001 AND v_nrow <1000000 THEN DBMS_OUTPUT.PUT_LINE('ANALYZE '||TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 10 PERCENT'); ... end;
Ca marche ca?
D'avance merci pour le coup de main

20/07/2009, 17h28
orafrance

Bah teste le pour vérifier :mouarf:

Sinon, depuis la 9i ANALYZE est remplacé par DBMS_STATS ;)
20/07/2009, 17h30
ldiaz

analyse en fonction des rows

DBMS_STAT
oui oui ca je savais, mais ici ils veulent pas changer pour le moment .... etrange non?
Le script que j'ai fait tu crois que ca marche?
J'ai l'impression qu'il manque une boucle ou qque chose pour creer un dbms_output pour chaque table existente dans la base.
Non?

analyse en fonction des rows

Avec ce code:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DECLARE 
v_table_Name VARCHAR(50);
v_nrow NUMBER;
BEGIN
SELECT TABLE_NAME, NUM_ROWS INTO v_table_name, v_nrow
FROM DBA_TABLES
WHERE OWNER='MON_USER';
 
 IF v_nrow BETWEEN 0 AND 10000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||v_table_name||' ESTIMATE STATISTICS COMPUTE');
 END IF;
 IF v_nrow BETWEEN 10001 AND 100000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||v_table_name||' ESTIMATE STATISTICS SAMPLE 30 PERCENT');
 END IF;
 IF v_nrow BETWEEN 100001 AND 1000000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||v_table_name||' ESTIMATE STATISTICS SAMPLE 10 PERCENT');
 END IF;
END;
/

J'ai cette erreur:

Code:

1
2
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5

si le select renvoie plus de une ligne...alors dans ce cas il faut une boucle non? un curseur?? je nage un peu la..
Ciao

analyse dans script pl/sql

Je l'ai modifier comme ceci, mais ca deconne toujours:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DECLARE 
v_tbl DBA_TABLES.table_name%TYPE;
v_nrow DBA_TABLES.NUM_ROWS%TYPE;
BEGIN
SELECT TABLE_NAME, NUM_ROWS INTO v_tbl, v_nrow
FROM DBA_TABLES
WHERE OWNER='RHUTST';
 
 IF v_nrow BETWEEN 0 AND 10000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||v_tbl||' ESTIMATE STATISTICS COMPUTE');
 END IF;
 IF v_nrow BETWEEN 10001 AND 100000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||v_tbl||' ESTIMATE STATISTICS SAMPLE 30 PERCENT');
 END IF;
 IF v_nrow BETWEEN 100001 AND 1000000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||v_tbl||' ESTIMATE STATISTICS SAMPLE 10 PERCENT');
 END IF;
END;
/

Erreur:

Code:

1
2
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5

Il faut boucler sur un curseur puisqu'il y a plus d'une table dans ton schema.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
FOR c in (SELECT TABLE_NAME, NUM_ROWS
FROM DBA_TABLES
WHERE OWNER='RHUTST')
LOOP
 
 IF c.NUM_ROWS BETWEEN 0 AND 10000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||c.TABLE_NAME||' ESTIMATE STATISTICS COMPUTE');
 END IF;
 IF c.NUM_ROWS BETWEEN 10001 AND 100000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||c.TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 30 PERCENT');
 END IF;
 IF c.NUM_ROWS BETWEEN 100001 AND 1000000 THEN
 DBMS_OUTPUT.PUT_LINE('ANALYZE '||c.TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 10 PERCENT');
 END IF;
END LOOP;

C'est dommage parce qu'avec DBMS_STATS Oracle fourni une estimation automatique du sample à utiliser :
Code:

1 2 3 4 5 exec dbms_stats.gather_table_stats ( ownname => '<user>' , tabname => '<table>' , estimate_percent => dbms_stats.auto_sample_size, cascade => TRUE );
Vous pouvez même récupérer toutes les statistiques d'un schéma en une seule commande avec dbms_stats.gather_schema_stats.

C'est un serpent qui se morde la queue!
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 mni@DIANA> create table t_stat as select * from user_objects; Table crÚÚe. mni@DIANA> select table_name, num_rows 2 from dba_tables 3 where table_name = 'T_STAT'; TABLE_NAME NUM_ROWS ------------------------------ ---------- T_STAT mni@DIANA>
Sans analyse NUM_ROWS ne contient pas l'information et à la prochaine analyse l'information est périmée pour prendre des décisions.

21/07/2009, 09h12
ldiaz

analyse dans script pl/sql

Bonjour a tous
tout d'abord merci de vos differents messages, ca aide beaucoup.
Le script marche super bien, a part un message d'overflow du cache pour le output, le reste c'est niquel. J'ai compris la boucle, mais du coup je pige pas dans quel cas utiliser un curseur en le declarant. Bref je chercherais...
.
En ce qui concernce DBMS_STAT je suis d'accord c'est dommage, mais grace a ton info, peu etre ca servira d'argument pour faire evoluer les choses.
Je vais proposer ca...

Le dernier post je pige pas trop. Tu veux dire qu'il faut analyser les tables beaucoup pour avoir toujours les dernieres mise a jour?
Merci
21/07/2009, 09h47
mnitu

Citation:

Envoyé par ldiaz

Bonjour a tous
...
Le dernier post je pige pas trop. Tu veux dire qu'il faut analyser les tables beaucoup pour avoir toujours les dernieres mise a jour?
Merci

Non. Je dis que c’est incorrect de se baser sur DBA_TABLES.NUM_ROWS pour prendre des décisions d’analyse des tables qui modifieront DBA_TABLES.NUM_ROWS. A la limite tu devrais la faire en calculant toi même le num_rows sur chaque table. Mais, c’est tellement du gaspillage par rapport au package DBMS_STAT que personnellement je ne le ferais pas.

analyse avec pl/sql

Bonjour,
ok .. oui je suis d'accord. Esperon que les arguments apportés seront sufisant.
;)
Une derniere aller c'est mardi matin:
imaginons qu'ils refusent !!!
est il possible d'ajouter DBMS_STAT dans le bloc PL/SQL dont on parlait un peu plus haut, afin d'analyser les tables EXTERNES dans la foulée?
Je vais essayer, mais j'aimerais votre avis aussi...pour si jamais.

A la final j'aurais ceci:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 BEGIN FOR c IN (SELECT TABLE_NAME, NUM_ROWS FROM DBA_TABLES WHERE OWNER='RHUPROD' AND TABLE_NAME LIKE 'RHU%') LOOP IF c.NUM_ROWS BETWEEN 0 AND 10000 THEN DBMS_OUTPUT.PUT_LINE('ANALYZE '||c.TABLE_NAME||' ESTIMATE STATISTICS COMPUTE'); END IF; IF c.NUM_ROWS BETWEEN 10000 AND 100000 THEN DBMS_OUTPUT.PUT_LINE('ANALYZE '||c.TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 30 PERCENT'); END IF; IF c.NUM_ROWS BETWEEN 100000 AND 1000000 THEN DBMS_OUTPUT.PUT_LINE('ANALYZE '||c.TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 10 PERCENT'); END IF; IF c.NUM_ROWS BETWEEN 1000000 AND 10000000 THEN DBMS_OUTPUT.PUT_LINE('ANALYZE '||c.TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 3 PERCENT'); END IF; IF c.NUM_ROWS > 10000000 THEN DBMS_OUTPUT.PUT_LINE('ANALYZE '||c.TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 1 PERCENT'); END IF; END LOOP; dbms_stats.gather_schema_stats(ownname => 'SCHEMA', tabname => '<les_table_ext>' estimate_percent => dbms_stats.auto_sample_size, method_opt=>'for all columns size auto', cascade=>true); END;
Ca marche ca? tout ensemble? SI y'a plusieurs tables ext j'ajoute juste un tag tabname en dessous de l'autres?
merci (promis apres j'arrete)

Code:

1
2
3
4
5
6
7
8
9
 
SQL> exec dbms_stats.gather_table_stats(user,'TRACE');
 
begin dbms_stats.gather_table_stats(user,'TRACE'); end;
 
ORA-20000: Unable to analyze TABLE "MNI"."TRACE", sampling on external table is not supported
ORA-06512: à "SYS.DBMS_STATS", ligne 13056
ORA-06512: à "SYS.DBMS_STATS", ligne 13076
ORA-06512: à ligne 2

sampling sur table externe

Salut
tu veux dire que l'option qui laisse oracle faire le sample auto sur une table externe ne marche pas?
Alors il faut faire un compute complet?

J'ai trouvé ca via google:
Code:

1 2 3 4 5 KUP-04088 data sampling is not supported by this access driver Cause: A query of an external table requested a sample of the data returned. The access driver for the external table does not support sampling. Action: Do not try to use sampling for this external table.
a cette adresse

cette commande fonctionne:
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'schema',tabname => 'TABLE_EXT',CASCADE=> TRUE);

Donc sans aucune information de SAMPLE, ca veut dire que cette commande fait comme un COMPUTE?

D'avance merci