Database Reference
In-Depth Information
8 from (
9 select *
10 from (
11 select active,
12 name,
13 case when name like '%ga mem%' then round(value/1024/1024,1) else value end val
14 from sess_stats
15 where active < 275
16 )
17 pivot ( max(val) for name in (
18 'session pga memory' as "PGA",
19 'total: session pga memory' as "tot PGA",
20 'physical writes direct temporary tablespace' as "temp write",
21 'total: physical writes direct temporary tablespace' as "tot writes temp"
22 ) )
23 )
24 order by active
25 /
ACTIVE PGA tot PGA PGA_DIFF temp write tot writes temp WRITES_DIFF
------ ------ ------- -------- ---------- --------------- -----------
0 3.5 7.6 0 0
1 15.2 19.5 11.90 0 0 0
26 15.2 195.6 176.10 0 243,387 243,387
51 7.7 292.7 97.10 1,045 518,246 274,859
76 5.2 188.7 -104.00 3,066 941,324 423,078
101 5.2 232.6 43.90 6,323 1,834,035 892,711
126 5.2 291.8 59.20 6,351 3,021,485 1,187,450
151 5.1 345.0 53.20 6,326 4,783,879 1,762,394
177 5.0 403.3 58.30 6,321 8,603,295 3,819,416
201 5.2 453.2 49.90 6,327 12,848,568 4,245,273
226 4.8 507.5 54.30 6,333 15,225,399 2,376,831
251 5.1 562.2 54.70 6,315 17,579,502 2,354,103
12 rows selected.
Before we analyze the results, let's look at the query I used for reporting. My query uses a feature, available
starting with Oracle 11
g
Release 1, called
pivot
to pivot a result set. Here's an alternate way to write lines 11 through 22
of that SQL query that would work in 10
g
Release 2 and before:
11 select active,
12 max( decode(name,'session pga memory',val) ) pga,
13 max( decode(name,'total: session pga memory',val) ) as "tot PGA",
14 max( decode(name,
'physical writes direct temporary tablespace',
val) ) as "temp write",
15 max( decode(name,
'total: physical writes direct temporary tablespace',
val) ) as "tot writes temp"
