oracle-db-examples/optimizer/db_load/example_output.txt at main · oracle-samples/oracle-db-examples · GitHub

Name: oracle-db-examples/optimizer/db_load/example_output.txt at main · oracle-samples/oracle-db-examples · GitHub
Rating: 4.8 (3642 reviews)
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
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
The client connection is transatlantic - so optimizing network roundtrips
makes a significant difference in this example run.

**** ****
**** ****
**** Always run tests multiple times to allow the database statistics ****
**** reported here to settle to nominal values. ****
**** ****
**** Remember: elapsed times can mislead ****
**** : Focus on reducing parse counts, executions, rountrips ****
**** : and transaction rates ****
**** ****
**** ****
**** Inserting 10 rows per test
**** ****
Connecting...
Connected
 Do nothing to get baseline statistics...

Elapsed time = 0 miliseconds

SELECT name,value FROM v$mystat, v$statname...

 SQL*Net roundtrips to/from client 3
 bytes received via SQL*Net from client 1417
 bytes sent via SQL*Net to client 2462
 execute count 8
 parse count (hard) 0
 parse count (total) 9
 user commits 0

Connecting...
Connected
 Insert fixed values into table using literals, parsing once, committing every row manually.
 PARSE INSERT INTO table VALUES ('XXXX')
 LOOP
 EXECUTE INSERT
 COMMIT
 END LOOP

Elapsed time = 3523 miliseconds

SELECT name,value FROM v$mystat, v$statname...

 + baseline comparison
 SQL*Net roundtrips to/from client 23 + 20
 bytes received via SQL*Net from client 2502 + 1085
 bytes sent via SQL*Net to client 3471 + 1009
 execute count 18 + 10
 parse count (hard) 0 + 0
 parse count (total) 10 + 1
 user commits 10 + 10

Connecting...
Connected
 Insert fixed values into table using literals, parsing once, auto committing every row.
 PARSE INSERT INTO table VALUES ('XXXX')
 LOOP
 EXECUTE INSERT AND COMMIT
 END LOOP

Elapsed time = 2144 miliseconds

SELECT name,value FROM v$mystat, v$statname...

 + baseline comparison
 SQL*Net roundtrips to/from client 13 + 10
 bytes received via SQL*Net from client 2480 + 1063
 bytes sent via SQL*Net to client 3332 + 870
 execute count 18 + 10
 parse count (hard) 0 + 0
 parse count (total) 10 + 1
 user commits 10 + 10

Connecting...
Connected
 Insert fixed values into table using literals, parsing once, committing once all rows have been inserted.
 PARSE INSERT INTO table VALUES ('XXXX')
 LOOP
 EXECUTE INSERT
 END LOOP
 COMMIT

Elapsed time = 1934 miliseconds

SELECT name,value FROM v$mystat, v$statname...

 + baseline comparison
 SQL*Net roundtrips to/from client 14 + 11
 bytes received via SQL*Net from client 2483 + 1066
 bytes sent via SQL*Net to client 3346 + 884
 execute count 18 + 10
 parse count (hard) 0 + 0
 parse count (total) 10 + 1
 user commits 1 + 1

Connecting...
Connected
 Insert fixed values into table using literals, parsing before each execute, committing once all rows have been inserted.
 LOOP
 PARSE INSERT INTO table VALUES ('XXXX')
 EXECUTE INSERT
 END LOOP
 COMMIT

Elapsed time = 1945 miliseconds

SELECT name,value FROM v$mystat, v$statname...

 + baseline comparison
 SQL*Net roundtrips to/from client 14 + 11
 bytes received via SQL*Net from client 3221 + 1804
 bytes sent via SQL*Net to client 3345 + 883
 execute count 18 + 10
 parse count (hard) 0 + 0
 parse count (total) 19 + 10
 user commits 1 + 1

Connecting...
Connected
 Insert different values into table using literals, parsing before each execute, committing once all rows have been inserted.
 LOOP
 STRING_VALUE = RANDOM_STRING()
 PARSE INSERT INTO table VALUES (' || STRING_VALUE || ')
 EXECUTE INSERT
 END LOOP
 COMMIT

Elapsed time = 1950 miliseconds

SELECT name,value FROM v$mystat, v$statname...

 + baseline comparison
 SQL*Net roundtrips to/from client 14 + 11
 bytes received via SQL*Net from client 3221 + 1804
 bytes sent via SQL*Net to client 3356 + 894
 execute count 28 + 20
 parse count (hard) 10 + 10
 parse count (total) 29 + 20
 user commits 1 + 1

Connecting...
Connected
 Insert different values into table using bind variables (parameters), parsing before each execute, committing once all rows have been inserted.
 LOOP
 STRING_VALUE = RANDOM_STRING()
 PARSE INSERT INTO table VALUES (?)
 SET PARAMETER = STRING_VALUE
 EXECUTE INSERT
 END LOOP
 COMMIT

Elapsed time = 1947 miliseconds

SELECT name,value FROM v$mystat, v$statname...

 + baseline comparison
 SQL*Net roundtrips to/from client 14 + 11
 bytes received via SQL*Net from client 3581 + 2164
 bytes sent via SQL*Net to client 3346 + 884
 execute count 18 + 10
 parse count (hard) 0 + 0
 parse count (total) 19 + 10
 user commits 1 + 1

Connecting...
Connected
 Insert different values into table using bind variables (parameters), parsing before each execute but with statement cache, committing once all rows have been inserted.
 LOOP
 STRING_VALUE = RANDOM_STRING()
 PARSE INSERT INTO table VALUES (?)
 SET PARAMETER = STRING_VALUE
 EXECUTE INSERT
 END LOOP
 COMMIT

Elapsed time = 1954 miliseconds

SELECT name,value FROM v$mystat, v$statname...

 + baseline comparison
 SQL*Net roundtrips to/from client 14 + 11
 bytes received via SQL*Net from client 2906 + 1489
 bytes sent via SQL*Net to client 3345 + 883
 execute count 18 + 10
 parse count (hard) 0 + 0
 parse count (total) 10 + 1
 user commits 1 + 1

Connecting...
Connected
 Insert different values into table using bind variables (parameters), parsing once, committing once all rows have been inserted.
 PARSE INSERT INTO table VALUES (?)
 LOOP
 STRING_VALUE = RANDOM_STRING()
 SET PARAMETER = STRING_VALUE
 EXECUTE INSERT
 END LOOP
 COMMIT

Elapsed time = 1950 miliseconds

SELECT name,value FROM v$mystat, v$statname...

 + baseline comparison
 SQL*Net roundtrips to/from client 14 + 11
 bytes received via SQL*Net from client 2906 + 1489
 bytes sent via SQL*Net to client 3346 + 884
 execute count 18 + 10
 parse count (hard) 0 + 0
 parse count (total) 10 + 1
 user commits 1 + 1

Connecting...
Connected
 Insert different values into table using bind variables (parameters), parsing once, inserting rows in batches of 50, committing in batches of 100000.
 PARSE INSERT INTO table VALUES (?)
 LOOP
 LOOP BATCH SIZE
 STRING_VALUE = RANDOM_STRING()
 SET PARAMETER[N] = STRING_VALUE
 END LOOP
 EXECUTE BATCH INSERT N
 COMMIT EVENT Mth ROW
 END LOOP

Elapsed time = 354 miliseconds

SELECT name,value FROM v$mystat, v$statname...

 + baseline comparison
 SQL*Net roundtrips to/from client 5 + 2
 bytes received via SQL*Net from client 2024 + 607
 bytes sent via SQL*Net to client 2581 + 119
 execute count 9 + 1
 parse count (hard) 0 + 0
 parse count (total) 10 + 1
 user commits 1 + 1

Process exited.
Debugger disconnected from local process.