Hints en PL/SQL

Version imprimable

Hints en PL/SQL

Bonjour,

Afin de mener des tests de performances, je cherche a lancer un select en parallele.
Code:

1 2 with test$mt as (select n from cpu_test where rownum <= 4 ) SELECT /*+ PARALLEL (P 4) 40 */ ma_func_de_test FROM test$mt P;
Fonctionne parfaitement dans SQL*Plus. Je le vois avec SQLTrace et le nombre d'IO.

Ceci dit, je voudrais bien l'integrer dans mon package PL/SQL.
Mais bon, EXECUTE IMMEDIATE ne fonctionne pas (Any valid SQL command except SELECT can be run immediately.), et je peux pas l'ecrire directement sous la forme d'un select, les Hints sont pris (a juste titre) pour des commentaires.
Code:

1 2 3 4 5 6 7 8 9 10 WITH TEST$MT AS (SELECT N FROM CPU_TEST WHERE ROWNUM <= NUM_OF_THREAD ) SELECT /*+ PARALLEL (P || NUM_OF_THREAD || ') ' || G_LONGOP.SOFAR || ' */ ma_function INTO STR FROM TEST$MT P;
L'un d'entre vous aurait-il une idee?:D

Bonjour,

as-tu essayé comme ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
text:='WITH TEST$MT AS
       (SELECT N
          FROM CPU_TEST
         WHERE ROWNUM <= NUM_OF_THREAD )
      SELECT /*+ PARALLEL (P ' || NUM_OF_THREAD || ') ' ||
                                     G_LONGOP.SOFAR || ' */
       ma_function 
        INTO STR
        FROM TEST$MT P';
 
execute immediate text;

Ou ca

Code:

1
2
3
execute immediate '
SELECT /*+ PARALLEL (P 4) 40 */ ma_func_de_test FROM (SELECT n FROM cpu_test WHERE rownum <= 4 )';

Bonjour,

Merci de votre aide. :D

Malheureusement, cela ne fonctionne pas. :?
Ci-dessous le script de test que j'ai utilise. Peut-etre que j'ai rate une etape.

Afin de bien voir si ca lancait en parallele ou pas, j'ai sorti le dbms_output et l'insertion dans une autre table.

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
drop table tmp;
drop table tmp2;
--Log table
create table tmp (p1 number);
--Table pour lancer plusieurs fois la function
create table tmp2 (n) as select rownum from all_tables where rownum<500;
 
--Fonction a lancer
CREATE OR REPLACE FUNCTION PARA_TEST RETURN NUMBER IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  DBMS_OUTPUT.PUT_LINE('a');
  INSERT INTO TMP (P1) VALUES (12);
  COMMIT;
  RETURN 42;
END PARA_TEST;
/
 
CREATE OR REPLACE PROCEDURE MY_PROC1 IS
BEGIN
  EXECUTE IMMEDIATE 'SELECT /*+ PARALLEL (P 4) 40 */ PARA_TEST FROM (SELECT n FROM tmp2 WHERE rownum <= 8 )';
END MY_PROC1;
/
 
CREATE OR REPLACE PROCEDURE MY_PROC2 IS
text varchar2(1000);
BEGIN
text:= 'SELECT /*+ PARALLEL (P 4) 40 */ PARA_TEST FROM (SELECT n FROM tmp2 WHERE rownum <= 8 )';
dbms_output.put_line(text);
execute immediate text;
END MY_PROC2;
/
 
set serveroutput on
SELECT /*+ PARALLEL (P 4) 40 */ para_test FROM tmp2 P where rownum<=8;
select * from tmp;
 
delete from tmp;
prompt =============
prompt execute immediate:
exec my_proc1;
select * from tmp;
 
delete from tmp;
prompt =============
prompt avec text:=
exec my_proc2
select * from tmp;
 
delete from tmp;

En faisant un copie/colle a la main du prompt avec text cela fonctionne.:roll:

Sinon je vais rester sur DBMS_SCHEDULER, mais je me disais que ce serait plus elegant de faire ca avec un select et un hint.

21/01/2011, 16h12
LBO72

Bonjour,

Je me suis déjà heurté à ce problème. Il m'a fait arracher le peu de cheveux qui me restent :-)

Essayes d'écrite ton hint comme ceci :
--+ TonHint
et non pas comme cela :
/*+ TonHint */.

LBO72.
24/01/2011, 20h43
pachot

Bonjour,
- execute immediate permet de faire un select into
- pl/sql reconnait les hint et ne les prends pas pour des commentaires
- '40' n'est pas un hint
Cordialement,
Franck.

Bonjour,

Je pense que c'est le INTO qui résout tout. :D
Merci Franck.

L’un dans l’autre c’est vrai que faire un select sans la clause INTO, il n’y avait pas de gros intérêt à l’exécuter dans le cas général. Je ne pense pas que j’y aurai pensé de si tôt.

Concernant le 40, il est là pour parser une nouvelle requête à chaque fois. Dans le code il est ajouté dynamiquement par concaténation.

Merci aussi pour les différentes réponses plus ou moins au hasard. :?

Je note en dessous le bout de script SQL utilisé pour tester les différentes solutions. La solution avec INTO (myproc 4 et 5) fonctionne.

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
 
DROP TABLE tmp;
DROP TABLE tmp2;
--Log table
CREATE TABLE tmp (p1 number);
--Table pour lancer plusieurs fois la function
CREATE TABLE tmp2 (n) AS SELECT rownum FROM all_tables WHERE rownum<500;
 
--Fonction a lancer
CREATE OR REPLACE FUNCTION PARA_TEST RETURN NUMBER IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  DBMS_OUTPUT.PUT_LINE('a');
  INSERT INTO TMP (P1) VALUES (12);
  COMMIT;
  RETURN 42;
END PARA_TEST;
/
 
CREATE OR REPLACE PROCEDURE MY_PROC1 IS
BEGIN
  EXECUTE IMMEDIATE 'SELECT /*+ PARALLEL (P 4) */ PARA_TEST FROM (SELECT n FROM tmp2 WHERE rownum <= 8 )';
END MY_PROC1;
/
 
CREATE OR REPLACE PROCEDURE MY_PROC2 IS
text varchar2(1000);
BEGIN
text:= 'SELECT /*+ PARALLEL (P 4) */ PARA_TEST FROM (SELECT n FROM tmp2 WHERE rownum <= 8 )';
dbms_output.put_line(text);
execute immediate text;
END MY_PROC2;
/
 
 
CREATE OR REPLACE PROCEDURE MY_PROC3 IS
text varchar2(1000);
BEGIN
text:= 'SELECT --+ PARALLEL (P 4) 
PARA_TEST FROM (SELECT n FROM tmp2 WHERE rownum <= 8 )';
dbms_output.put_line(text);
execute immediate text;
END MY_PROC3;
/
 
 
CREATE OR REPLACE PROCEDURE MY_PROC4 IS
text varchar2(1000);
type t_num is table of number;
N t_num;
BEGIN
text:= 'SELECT --+ PARALLEL (P 4) 
PARA_TEST FROM (SELECT n FROM tmp2 WHERE rownum <= 8 )';
dbms_output.put_line(text);
execute immediate text BULK COLLECT INTO N;
END MY_PROC4;
/
 
CREATE OR REPLACE PROCEDURE MY_PROC5 IS
text varchar2(1000);
type t_num is table of number;
N t_num;
BEGIN
text:= 'SELECT /*+ PARALLEL (P 4) 40*/ PARA_TEST FROM (SELECT n FROM tmp2 WHERE rownum <= 8 )';
dbms_output.put_line(text);
execute immediate text BULK COLLECT INTO N;
END MY_PROC5;
/
 
SET serveroutput ON
SELECT /*+ PARALLEL (P 4) 40 */ para_test FROM tmp2 P WHERE rownum<=8;
SELECT * FROM tmp;
 
DELETE FROM tmp;
prompt =============
prompt execute immediate:
exec my_proc1;
SELECT * FROM tmp;
 
DELETE FROM tmp;
prompt =============
prompt avec text:=
exec my_proc2
SELECT * FROM tmp;
 
DELETE FROM tmp;
 
prompt =============
prompt avec text:= et hint --+
exec my_proc3
SELECT * FROM tmp;
 
DELETE FROM tmp;
 
prompt =============
prompt avec text:= et hint --+ avec into
exec my_proc4
SELECT * FROM tmp;
 
DELETE FROM tmp;
 
prompt =============
prompt avec text:= et hint /*+ avec into */
exec my_proc5
SELECT * FROM tmp;
 
DELETE FROM tmp;

31/03/2011, 15h56
vlapoulle

petit détail ?

Bonjour, sans vouloir trop m'avancer, je constate que dans l'execute immediate, il n'y a pas d'alias P (à l'inverse du second test), donc le P du hint ne veut rien dire pour Oracle.
D'où peut-être les problèmes?

Bien cordialement,
vlapoulle