Discussion :

[cmako]

Bonjour,

Je n'arrive pas à exécuter ma query SQL qui a cette tête:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH params AS (
	SELECT  
--		'D476' fund_num, 
--		'EUR' fund_cur, 
--		to_date('20110630', 'YYYYMMDD') report_date1, 
--		to_date('20110930', 'YYYYMMDD') report_date2
 $P{fundNumb} fund_num
, $P{fundCurrency} fund_cur
, $P{reportDate1} report_date1
, $P{reportDate2} report_date2 
	FROM DUAL
)
SELECT ...
FROM ...

pendant l'exécution iReport remplace tous les $P{} par les valeurs de paramètres indiquées.
La requête ci-dessus ne se termine jamais.
Si je commente la partie avec les $P et je décommente la partie avec les vraies valeurs la requête s'exécute.
Je n'arrive pas à trouver pourquoi cela se produit

Petite précision :
Lorsque iReport exécute la requête, elle replace tous les $P{} par des ?. Ensuite il précompile la requête puis il l'exécute en passant les paramètres. Je soupçonne le process de précompilation qui lorsque les paramètres sont placés en dur il arrive à trouver le bon plan pour exécuter la requête, alors qu'avec des paramètre un mauvais plan (non optimisé) est exécuté. J'aimerais savoir s'il est possible de spécifier dans la requête une option qui va rechercher un plan optimal en live plutôt que le plan déjà précalculé.
Existe-t-il une OPTION RECOMPILE comme chez SQL Server équivalent en Oracle?

[cmako]

En faisant ce petit bout de code en Java j'ai le même problème

Code :

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

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
	public static void main(String[] args) {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
 
		try {
			new TestSqlDedicatedStatement().execute();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
 
	private void execute() throws Exception {
 
		Connection connection = DriverManager.getConnection(CONNECTION_STR,CONNECTION_LOGIN,CONNECTION_PASSWORD);
 
 
		PreparedStatement pstmt = connection.prepareStatement(SQL);
		pstmt.setString(1, "D476");
		pstmt.setString(2, "EUR");
//		pstmt.setDate(3, new java.sql.Date(2011-1900, 6-1, 1));
//		pstmt.setDate(4, new java.sql.Date(2011-1900, 9-1, 1));
 
		ResultSet rs = pstmt.executeQuery();
 
		System.out.println("Executed");
	}

Lorsque je mets les valeurs en dur, la requête s'exécute. Si je mets des ? ça bloque.

[Rei Ichido]

Quelle version d'Oracle utilisez-vous ?

Il s'agit manifestement d'un cas de mauvais bind peeking. Sur une vieille version d'Oracle, je ne suis pas sûr qu'il y ait un moyen élégant de s'en affranchir ...

[cmako]

Par élimination, j'ai remarqué que :
- si je mets le fundNumb en dur et le reste en paramètres - ça marche !
- si je passe le fundNumb en paramètre et le reste je mets en dur - ça marche aussi !
- si je change l'ordre et je mets fundNumb en dernière position - tout marche!
Mais je n'ai pas d'explications sur ce qui se passe

[cmako]

Quelle version d'Oracle utilisez-vous ?

Il s'agit manifestement d'un cas de mauvais bind peeking. Sur une vieille version d'Oracle, je ne suis pas sûr qu'il y ait un moyen élégant de s'en affranchir ...

voila ce que me donne le : select * from v$version

Code :

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

1
2
3
4
5
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE	11.2.0.1.0	Production"
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

[Rei Ichido]

Par élimination, j'ai remarqué que :
- si je mets le fundNumb en dur et le reste en paramètres - ça marche !
- si je passe le fundNumb en paramètre et le reste je mets en dur - ça marche aussi !
- si je change l'ordre et je mets fundNumb en dernière position - tout marche!
Mais je n'ai pas d'explications sur ce qui se passe

Dès que la requête change, cela force Oracle à refaire le parse, et donc refaire un nouveau plan.
Là il a manifestement pris un plan qui ne va pas du tout avec les valeurs qui t'intéressent.
Par contre, la 11g est justement la version qui fait que le SGBD peut décider de changer de plan et d'avoir plusieurs plans selon les différentes variables utilisées.

[cmako]

Dès que la requête change, cela force Oracle à refaire le parse, et donc refaire un nouveau plan.
Là il a manifestement pris un plan qui ne va pas du tout avec les valeurs qui t'intéressent.
Par contre, la 11g est justement la version qui fait que le SGBD peut décider de changer de plan et d'avoir plusieurs plans selon les différentes variables utilisées.

D'accord, mais comment peut-on faire pour forcer à recalculer le plan ? Existe-t-il une option dans la requête qui permet de faire ça?

[cmako]

D'accord, mais comment peut-on faire pour forcer à recalculer le plan ? Existe-t-il une option dans la requête qui permet de faire ça?

Y a-t-il quelqu'un qui sait comment recalculer un plan?

[Mohamed.Houri]

Y a-t-il quelqu'un qui sait comment recalculer un plan?

Non, ça n'est pas en sachant comment provoquer un hard parse que vous allez vous en sortir.

Vous n'avez posté aucun explain plan afin de nous laisser apprecier ce qui se passe dans votre cas.

De plus, vous ne pouvez pas comparer l'execution d'une requête en lui passant des paramètes de substitution(comme vous le faites) avec la même requête exécutée en lui passant des paramètres en "bind variables''.

Regardez par exemple ceci

je commence par utiliser un paramètre en dur

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
mhouri.world> explain plan for
  2  select null from dual where 2 = nvl(2,2);
 
Explained.
 
mhouri.world> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT                                                                                                       
-------------------------------------------------------------------
Plan hash value: 1546270724                                                                                             
 
-----------------------------------------------------------------                                                       
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |                                                       
-----------------------------------------------------------------                                                       
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |                                                       
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |                                                       
-----------------------------------------------------------------                                                       
 
8 rows selected.

après je passe un paramètre en variable de substitution

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
mhouri.world> explain plan for
  2  select null from dual where 2 = nvl(&2,2);
Enter value for 2: 2
old   2: select null from dual where 2 = nvl(&2,2)
new   2: select null from dual where 2 = nvl(2,2)
 
Explained.
 
mhouri.world> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT                                                                                                       
---------------------------------------------------------------------
Plan hash value: 1546270724                                                                                             
 
-----------------------------------------------------------------                                                       
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |                                                       
-----------------------------------------------------------------                                                       
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |                                                       
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |                                                       
-----------------------------------------------------------------

C'est la même chose. N'est-ce pas?

Par contre, si je passe un paramètre en "bind variable"

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
mhouri.world> explain plan for
  2  select null from dual where 2 = nvl(:2,2);
 
Explained.
 
mhouri.world> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT                                                                                                       
-------------------------------------------------------------------
Plan hash value: 731024556                                                                                              
 
-----------------------------------------------------------------                                                       
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |                                                       
-----------------------------------------------------------------                                                       
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |                                                       
|*  1 |  FILTER          |      |       |            |          |                                                       
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |                                                       
-----------------------------------------------------------------                                                       
 
Predicate Information (identified by operation id):                                                                     
---------------------------------------------------                                                                     
 
   1 - filter(NVL(:2,2)=2)

C'est différent!!!

Ceci sans parler que dans le cas où vous passez des paramètres en bind variables(contrairement au paramètres transmis en dur) Oracle doit prévoir la possibilité que ces bind variables soient NULLs faisant apparaître des opérations CONCATENATION dans ce cas qu'il n'aurait pas utiliser dans le cas de paramètres passés en dur.

Le problème qu'il faut traiter ca n'est pas celui-ci : "lorsque je mets les paramètres en dur cela va vite alors que lorsque je les passe en paramètre ça bloque"; mais doit-être plutôt : "Comment va s'exécuter ma requête en PRODUCTION? en lui passant des paramètres en dur ou en en "bind variables" ?

Et c'est uniquement en répondant à cette question que vous allez orienter votre investigation. Comme j'ai de forts sentiments que c'est en "bind variables" que vous allez utiliser votre requête et comme je suppose que vous êtes dans un systeme OLTP où l'utilisation des bind variables est fortement conseillée, commencez donc par optimiser la requête telle qu'elle sera executée en PROD.