DBAPack/getplan10.sql at master · flvani/DBAPack · GitHub

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
108
109
110
111
112
113
114
-- Bug que gera desconexão quando consulta é realizada contra V$SQL_PLAN. Workaround abaixo:
--alter session set "_cursor_plan_unparse_enabled"=false;

DELETE PLAN_TABLE
WHERE STATEMENT_ID = '&1.'
/

INSERT INTO sys.PLAN_TABLE$
(
  BYTES
 ,ACCESS_PREDICATES
 ,CARDINALITY
 ,COST
 ,CPU_COST
 ,DEPTH
 ,DISTRIBUTION
 ,FILTER_PREDICATES
 ,ID
 ,IO_COST
 ,OBJECT_ALIAS
 ,OBJECT_NAME
 ,OBJECT_NODE
 ,OBJECT_OWNER
 ,OBJECT_TYPE
 ,OPERATION
 ,OPTIMIZER
 ,OPTIONS
 ,OTHER
 ,OTHER_TAG
 --,OTHER_XML
 ,PARENT_ID
 ,PARTITION_ID
 ,PARTITION_START
 ,PARTITION_STOP
 ,POSITION
 ,PROJECTION
 ,QBLOCK_NAME
 ,REMARKS
 ,SEARCH_COLUMNS
 ,TEMP_SPACE
 ,TIME
 ,TIMESTAMP
 ,STATEMENT_ID
 ,PLAN_ID
)
SELECT
  BYTES
 ,ACCESS_PREDICATES
 ,CARDINALITY
 ,COST
 ,CPU_COST
 ,DEPTH
 ,DISTRIBUTION
 ,FILTER_PREDICATES
 ,ID
 ,IO_COST
 ,OBJECT_ALIAS
 ,OBJECT_NAME
 ,OBJECT_NODE
 ,OBJECT_OWNER
 ,OBJECT_TYPE
 ,OPERATION
 ,OPTIMIZER
 ,OPTIONS
 ,OTHER
 ,OTHER_TAG
 --,OTHER_XML
 ,PARENT_ID
 ,PARTITION_ID
 ,PARTITION_START
 ,PARTITION_STOP
 ,POSITION
 ,PROJECTION
 ,QBLOCK_NAME
 ,REMARKS
 ,SEARCH_COLUMNS
 ,TEMP_SPACE
 ,TIME
 ,SYSTIMESTAMP, '&1.'
 , 1
FROM GV$SQL_PLAN
WHERE HASH_VALUE = &1. AND ADDRESS = '&2.'
AND CHILD_NUMBER =
 ( SELECT MAX( CHILD_NUMBER ) FROM GV$SQL_PLAN WHERE HASH_VALUE = &1. AND ADDRESS = '&2.' )
/

@do.expplan.sql &1.

SELECT DISTINCT
  P.SQL_ID, P.HASH_VALUE, P.CHILD_NUMBER VERSION#, P.PLAN_HASH_VALUE
FROM GV$SQL_PLAN P
WHERE P.HASH_VALUE = '&1.' AND P.ADDRESS = '&2.'
ORDER BY 1, 2, 3
/
/*

SELECT DISTINCT
  P.SQL_ID, P.HASH_VALUE, P.CHILD_NUMBER VERSION#, P.PLAN_HASH_VALUE,
  S.USERS_EXECUTING, S.LOADS, S.FIRST_LOAD_TIME
FROM V$SQL_PLAN P, V$SQL S
WHERE P.HASH_VALUE = '&1.' AND P.ADDRESS = '&2.'
AND P.CHILD_ADDRESS = S.CHILD_ADDRESS
ORDER BY 1, 2, 3


S.OPEN_VERSIONS
S.USERS_OPENING
S.FETCHES
S.EXECUTIONS
S.USERS_EXECUTING
S.LOADS
S.FIRST_LOAD_TIME
S.INVALIDATIONS
*/