[DBA] Version count de V$sqlarea
Bonjour,
Je n'arrive pas à comprendre à quoi sert la colonne VERSION_COUNT dans la vue v$sqlarea.
Et j'ai des problèmes de requêtes identiques arrivant plusieurs fois dans ma zone sql. A mon avis, les deux sont liées, mais un truc m'echappe...
si quelqu'un peux m'expliquer et aussi me dire ce que je dois faire pour diminuer toutes ces requetes....
exemple1: requete bindée:
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
|
select v.SQL_TEXT, v.VERSION_COUNT, v.OPEN_VERSIONS, v.FETCHES, v.EXECUTIONS, v.PARSE_CALLS, v.LOADS
from v$sqlarea v
where sql_text='call tec.log.send_message()'
SQL_TEXT VERSION_COUNT OPEN_VERSIONS FETCHES EXECUTIONS PARSE_CALLS LOADS
1 call tec.log.send_message() 425 1 0 371939 185970 429
select v.SQL_TEXT, v.PERSISTENT_MEM,v.RUNTIME_MEM,v.LOADED_VERSIONS,v.OPEN_VERSIONS,v.USERS_OPENING,v.EXECUTIONS,v.USERS_EXECUTING,v.LOADS,v.PARSE_CALLS,v.OPTIMIZER_MODE,v.CHILD_NUMBER
from v$sql v
where sql_text='call tec.log.send_message()'
order by 1,LAST_LOAD_TIME
SQL_TEXT PERSISTENT_MEM RUNTIME_MEM LOADED_VERSIONS OPEN_VERSIONS USERS_OPENING EXECUTIONS USERS_EXECUTING LOADS PARSE_CALLS OPTIMIZER_MODE CHILD_NUMBER
call tec.log.send_message() 828 4280 1 1 1 371995 1 5 185998 CHOOSE 0
call tec.log.send_message() 0 0 0 0 0 0 0 1 0 NONE 1
call tec.log.send_message() 0 0 0 0 0 0 0 1 0 NONE 2
call tec.log.send_message() 0 0 0 0 0 0 0 1 0 NONE 3
call tec.log.send_message() 0 0 0 0 0 0 0 1 0 NONE 4
.
.
.
.
.
.
425 lignes |
deuwieme exemple; requete non bindée:
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
|
select v.SQL_TEXT, v.VERSION_COUNT, v.OPEN_VERSIONS, v.FETCHES, v.EXECUTIONS, v.PARSE_CALLS, v.LOADS
from v$sqlarea v
where sql_text='UPDATE message_log@prod t SET t.confirmed = :"SYS_B_0", t.order_id = :"SYS_B_1" Where t.order_id = :"SYS_B_2" and t.exec_id = :"SYS_B_3"'
SQL_TEXT VERSION_COUNT OPEN_VERSIONS FETCHES EXECUTIONS PARSE_CALLS LOADS
1 UPDATE message_log@prod t.. 349 0 0 0 0 349
2 UPDATE message_log@prod t.. 669 0 0 669 669 669
select v.SQL_TEXT, v.PERSISTENT_MEM,v.RUNTIME_MEM,v.LOADED_VERSIONS,v.OPEN_VERSIONS,v.USERS_OPENING,v.EXECUTIONS,v.USERS_EXECUTING,v.LOADS,v.PARSE_CALLS,v.OPTIMIZER_MODE,v.CHILD_NUMBER
from v$sql v
where sql_text='UPDATE message_log@prod t SET t.confirmed = :"SYS_B_0", t.order_id = :"SYS_B_1" Where t.order_id = :"SYS_B_2" and t.exec_id = :"SYS_B_3"'
order by 1,LAST_LOAD_TIME
UPDATE message_log@prod t.. 0 0 0 0 0 0 0 1 0 NONE 0
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 0
UPDATE message_log@prod t.. 0 0 0 0 0 0 0 1 0 NONE 1
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 1
.
.
.
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 590
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 590
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 591
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 591
.
.
.
.
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 1012
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 1013
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 1014
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 1015
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 1016
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 1017
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 1018
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 1019
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 1020
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 1021
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 1022
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 1023
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 1024
UPDATE message_log@prod t.. 580 0 1 0 0 1 0 1 1 CHOOSE 1025
2205 lignes |
