Re: SHARED_POOL_SIZE Increase Makes a Bstat Little Worse - Why?
Date: 1997/07/17
Message-ID: <33ce3c0d.0_at_news1.ibm.net>
Hi (to Jonathan Lewis),
>
If the version_count is high, it would be a particular giveaway that
the OCI is making you apparently identical statements be treated as
different statements, and causing real re-parsing to take place.
<
There is only one stmt that has a version_count > 1 (it is 2)
and it not a stmt I did. It looks like some oracle thing.
>
The original though was basically prompted by checking the number of
parse calls (360K) against the parse time (6,900 seconds) which
indicates a lot of real parsing as opposed to shared-pool hits.
Basically, your batch doesn't seem to be doing a huge amount of work
(for the machine size), but is spending far too much time parsing.
<
How do you tell if 'parse count' or 'parse time cpu' is a lot?
What do you think of these numbers below (from what you said to try)? Could it be that I am doing so well on our sql stmt.s that increasing the shared pool size does not help? Could this be true? or can you burst my bubble?
I guess the next thing I wonder about is: Is there a way to tell what the 'size' of this v$sqlarea or shared sql area is that is being used so I can just use that to create the proper shared_pool_size?
Thanks for your help.
SQL> select count(*) from v$sqlarea;
COUNT(*)
9464
1 select executions, count(*) from v$sqlarea
2* group by executions
SQL> /
EXECUTIONS COUNT(*)
---------- ---------
1 6714 <--- looked bad...until I saw the bottom
2 537
3 1055
4 319
5 101
6 114
7 59
8 49
9 22
10 11
11 17
12 12
13 7
14 12
15 5
16 4
17 5
18 3
19 5
20 8
21 9
EXECUTIONS COUNT(*)
---------- ---------
22 7
23 7
24 4
25 1
26 6
27 6
28 6
29 1
31 8
32 3
33 1
34 6
35 3
36 3
37 2
38 2
39 2
40 3
41 8
42 2
43 5
EXECUTIONS COUNT(*)
---------- ---------
44 9
45 1
46 3
47 4
49 5
50 1
51 3
54 1
55 3
57 2
58 4
60 1
61 2
63 1
67 1
70 2
73 1
74 3
75 1
76 1
77 2
EXECUTIONS COUNT(*)
---------- ---------
78 1
80 1
81 2
82 1
84 3
85 1
94 1
95 1
96 1
99 1
100 1
102 1
103 1
105 1
108 2
109 2
111 1
114 1
115 1
117 1
119 3
EXECUTIONS COUNT(*)
---------- ---------
122 2
125 1
126 1
130 4
131 1
136 2
137 1
138 2
147 5
148 3
150 1
151 2
154 1
157 1
160 1
161 2
162 1
165 1
168 2
169 1
170 1
EXECUTIONS COUNT(*)
---------- ---------
174 1
175 1
176 1
179 1
182 1
190 1
197 2
200 1
203 1
208 1
210 1
211 4
215 1
219 3
222 1
229 1
230 2
234 1
235 2
239 1
240 15
EXECUTIONS COUNT(*)
---------- ---------
255 1
262 1
287 1
319 1
326 1
365 1
369 1
379 1
410 2
428 1
431 1
440 1
456 1
468 5
481 1
513 1
519 4
540 1
569 1
603 1
647 1
EXECUTIONS COUNT(*)
---------- ---------
679 2
716 1
735 2
746 2
752 1
755 2
765 1
776 1
806 1
827 1
900 1
914 1
938 1
1012 1
1029 1
1101 1
1104 1
1171 1
1176 1
1189 11
1225 1
EXECUTIONS COUNT(*)
---------- ---------
1252 1
1267 1
1287 2
1302 1
1319 1
1348 1
1355 1
1366 1
1397 1
1428 1
1887 1
2120 1
2123 1
2124 2
2125 1
2327 1
5738 1
5974 1
23179 1
25812 1
43717 1
EXECUTIONS COUNT(*)
---------- ---------
150671 1
178528 1
191 rows selected.
SQL> select sql_text from v$sqlarea
2 where executions = '178528';
SQL_TEXT
SELECT * from dol.pjc_tbl WHERE ROWID = :1
SQL> select sql_text, version_count, executions, loads, parse_calls,
buffer_gets
2 from v$sqlarea
3 where version_count > 1;
SQL_TEXT
--- VERSION_COUNT EXECUTIONS LOADS PARSE_CALLS BUFFER_GETSReceived on Thu Jul 17 1997 - 00:00:00 CEST
------------- ---------- --------- ----------- -----------
begin DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); end; 2 4 2 3 127 SQL> select executions, sql_text from v$sqlarea 2 where executions > '1500' order by executions desc; EXECUTIONS
----------
SQL_TEXT
------------------------------------------------------------------------------
----------------------
355464 SELECT * from dol.pjc_tbl WHERE ROWID = :1 150903 SELECT * from fac.bom_tbl WHERE ROWID = :1 43717 SELECT * from fac.est_tbl WHERE ROWID = :1 FOR UPDATE NOWAIT 25812 SELECT * from fac.por_tbl WHERE ROWID = :1 23179 SELECT * from fac.tra_tbl WHERE ROWID = :1 13153 SELECT * from fac.pij_tbl WHERE ROWID = :1 5876 EXECUTIONS
----------
SQL_TEXT
------------------------------------------------------------------------------
----------------------
SELECT * from dol.pin_tbl WHERE ROWID = :1 3304 SELECT --+ INDEX_ASC( fac.pij_tbl fac.pij_trans_key ) ROWID FROM fac.pij_tbl WHERE (trans_typ e = :1 AND job_level_0 = :2 AND job_level_1 = :3 AND job_level_2 = :4 AND job_level_3 = :5 AND job_l evel_4 = :6 AND job_level_5 = :7 AND budget_cat = :8 AND traveler_no = :9 AND traveler_oper_no = :10 AND po_no_1 = :11 AND po_item_no_1 = :12) ORDER BY trans_type, job_level_0, job_level_1, job_level _2, job_level_3, job_level_4, job_level_5, budget_cat, traveler_no, traveler_oper_no, po_no_1, po_it em_no_1 ... the rest are like the last one. stantow_at_ibm.net