Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:01,370 --> 00:00:04,520
In this lesson, we're going
to look at the Program Global
2
00:00:04,520 --> 00:00:06,800
Area, or PGA.
3
00:00:06,800 --> 00:00:09,260
Again, the PGA is
the second type
4
00:00:09,260 --> 00:00:12,360
of memory cache available
in the Oracle Instance.
5
00:00:12,360 --> 00:00:14,900
So, again, an instance
is composed of background
6
00:00:14,900 --> 00:00:17,250
processes and memory caches.
7
00:00:17,250 --> 00:00:20,270
And this is the second
type of memory cache.
8
00:00:20,270 --> 00:00:23,150
The important thing to
remember about the PGA
9
00:00:23,150 --> 00:00:25,160
is that it's for private memory.
10
00:00:25,160 --> 00:00:29,960
So whereas the SGA is for
shared memory between users,
11
00:00:29,960 --> 00:00:33,650
the PGA is for private
memory that's not shared--
12
00:00:33,650 --> 00:00:36,890
things like sorting
or session variables.
13
00:00:36,890 --> 00:00:39,980
Sorting can be one of the most
expensive operations that's
14
00:00:39,980 --> 00:00:42,350
done in a database
because it involves
15
00:00:42,350 --> 00:00:47,160
reading a large amount of
memory, large amount of data,
16
00:00:47,160 --> 00:00:50,030
and then moving through
all of that data
17
00:00:50,030 --> 00:00:52,970
and putting it into some
kind of ordered set.
18
00:00:52,970 --> 00:00:57,110
So a sort is done when we use
a query that does something
19
00:00:57,110 --> 00:00:59,150
like an order-by clause.
20
00:00:59,150 --> 00:01:02,420
We may order by
first name in a table
21
00:01:02,420 --> 00:01:05,900
so we see all of the employees
and the employee table,
22
00:01:05,900 --> 00:01:07,670
for instance, alphabetically.
23
00:01:07,670 --> 00:01:10,760
So, sorting can be a
very expensive operation.
24
00:01:10,760 --> 00:01:14,960
For that reason, it was decided
when Oracle was being created,
25
00:01:14,960 --> 00:01:19,190
to not have those type of
operations occur in the SGA.
26
00:01:19,190 --> 00:01:21,890
Rather, that we needed
some kind of private area
27
00:01:21,890 --> 00:01:25,700
for users to be able to do
those type of operations.
28
00:01:25,700 --> 00:01:27,830
And it also holds
session variables
29
00:01:27,830 --> 00:01:30,710
for statements where you
have some variable that
30
00:01:30,710 --> 00:01:32,840
gets passed to a piece of code.
31
00:01:32,840 --> 00:01:35,090
So there's two ways
that this has been done.
32
00:01:35,090 --> 00:01:37,640
The old way, which is
going to be limited
33
00:01:37,640 --> 00:01:40,640
by the number of users that
we actually have connecting
34
00:01:40,640 --> 00:01:44,600
to the database, or the new way
that's done, and has been done
35
00:01:44,600 --> 00:01:48,500
since Oracle 9i, that uses
an actual pool of memory
36
00:01:48,500 --> 00:01:51,180
to more efficiently
manage how this is done.
37
00:01:51,180 --> 00:01:54,290
So the easiest way to
see how this operates
38
00:01:54,290 --> 00:01:56,870
is to talk a little bit about
the way it used to be done.
39
00:01:56,870 --> 00:02:01,130
So in the old way of private
management of memory,
40
00:02:01,130 --> 00:02:05,300
Oracle would establish
a certain amount or size
41
00:02:05,300 --> 00:02:08,690
of a slice of memory
that each user would get.
42
00:02:08,690 --> 00:02:12,950
So a user would connect in and
say the database parameters
43
00:02:12,950 --> 00:02:15,620
indicated that one
megabyte of memory
44
00:02:15,620 --> 00:02:19,160
would be given to a user for
their private operations.
45
00:02:19,160 --> 00:02:21,740
So any sorting,
session variables,
46
00:02:21,740 --> 00:02:24,080
hash values in a join,
any of those things
47
00:02:24,080 --> 00:02:26,810
would get one
megabyte of memory.
48
00:02:26,810 --> 00:02:29,030
And then another
user would connect in
49
00:02:29,030 --> 00:02:31,520
and they would get a
megabyte of memory.
50
00:02:31,520 --> 00:02:33,660
And so on and so forth.
51
00:02:33,660 --> 00:02:35,730
So we can see some
of the problems here.
52
00:02:35,730 --> 00:02:40,190
The first is, not every user
that connects into a database
53
00:02:40,190 --> 00:02:44,000
necessarily is going to
do any sorting operations.
54
00:02:44,000 --> 00:02:47,960
So one megabyte of memory might
actually be too much for them,
55
00:02:47,960 --> 00:02:50,240
so we're essentially
wasting memory.
56
00:02:50,240 --> 00:02:53,720
The next thing is, what
about a user who needs
57
00:02:53,720 --> 00:02:55,310
more than a meg of memory?
58
00:02:55,310 --> 00:02:57,670
So, they're going to
do a lot of sorting.
59
00:02:57,670 --> 00:02:59,750
Well, what occurs
in that situation
60
00:02:59,750 --> 00:03:02,930
is that Oracle can't
contain all of the data
61
00:03:02,930 --> 00:03:05,600
in the one megabyte
of memory and so it
62
00:03:05,600 --> 00:03:11,150
has to write it out to disk,
into temporary files on disk.
63
00:03:11,150 --> 00:03:13,760
And that's a very,
very slow operation
64
00:03:13,760 --> 00:03:16,550
because all of those
one megabyte pieces
65
00:03:16,550 --> 00:03:20,000
are written down to disk,
then they're all reassembled
66
00:03:20,000 --> 00:03:21,840
and presented back to the user.
67
00:03:21,840 --> 00:03:25,820
So not being able to do the
entire operation in memory
68
00:03:25,820 --> 00:03:28,500
can be a true
performance problem.
69
00:03:28,500 --> 00:03:30,350
And the last
problem with this is
70
00:03:30,350 --> 00:03:32,420
that we're completely
constrained
71
00:03:32,420 --> 00:03:34,400
by the number of
users that we have.
72
00:03:34,400 --> 00:03:37,070
So we say, how much
memory should we
73
00:03:37,070 --> 00:03:40,250
give for private
use in our database?
74
00:03:40,250 --> 00:03:42,980
We say that we have
x amount of memory,
75
00:03:42,980 --> 00:03:45,740
the operating system
uses a portion of that,
76
00:03:45,740 --> 00:03:48,470
the SGA uses a portion
of that, and then
77
00:03:48,470 --> 00:03:51,290
we have to define the
amount for the PGA--
78
00:03:51,290 --> 00:03:52,760
the private area.
79
00:03:52,760 --> 00:03:55,280
Well, in this case,
the amount of size
80
00:03:55,280 --> 00:03:56,860
that we have available
to us is going
81
00:03:56,860 --> 00:04:00,030
to be completely dependent on
the number of users we have.
82
00:04:00,030 --> 00:04:02,180
So let's say we
have one megabyte
83
00:04:02,180 --> 00:04:05,960
slices that are given to
each user for private memory.
84
00:04:05,960 --> 00:04:10,130
If we have 500 megabytes
total available to us
85
00:04:10,130 --> 00:04:14,510
for private memory, that means
we can only have 500 users.
86
00:04:14,510 --> 00:04:18,620
500 users, one meg,
equals 500 meg.
87
00:04:18,620 --> 00:04:21,410
But what if we have
more than 500 users?
88
00:04:21,410 --> 00:04:24,980
Well, then we actually have to
make the slice size smaller,
89
00:04:24,980 --> 00:04:28,250
and that gives everybody
less private memory.
90
00:04:28,250 --> 00:04:31,100
The thing that really
compounds this problem is we
91
00:04:31,100 --> 00:04:35,180
don't know that all 500
of those people or more
92
00:04:35,180 --> 00:04:37,560
need a megabyte for sorting.
93
00:04:37,560 --> 00:04:40,490
So the entire process
of doing private memory,
94
00:04:40,490 --> 00:04:44,550
prior to Oracle 9i,
was very inefficient.
95
00:04:44,550 --> 00:04:48,200
That's why Oracle came up
with the new way in Oracle 9i
96
00:04:48,200 --> 00:04:51,380
called the PGA, the
Program Global Area.
97
00:04:51,380 --> 00:04:54,480
So the PGA is actually
a pool of memory.
98
00:04:54,480 --> 00:04:58,730
Rather than a slice of memory,
we establish an overall pool.
99
00:04:58,730 --> 00:05:02,970
So we say we have 500
meg available to us
100
00:05:02,970 --> 00:05:04,410
for private memory.
101
00:05:04,410 --> 00:05:07,860
So we've established
1,000 meg for the SGA
102
00:05:07,860 --> 00:05:10,320
and we have 500 meg for the PGA.
103
00:05:10,320 --> 00:05:14,880
Now, when a user connects
in, it's Oracle in the PGA
104
00:05:14,880 --> 00:05:18,900
that decides how big of a
slice of memory to give.
105
00:05:18,900 --> 00:05:21,450
Then, if the user needs more--
106
00:05:21,450 --> 00:05:24,390
that is to say the sort
requires more memory--
107
00:05:24,390 --> 00:05:29,580
Oracle can dynamically add more
memory to their private memory,
108
00:05:29,580 --> 00:05:31,920
and even more if needed.
109
00:05:31,920 --> 00:05:34,260
When another user comes
into the database,
110
00:05:34,260 --> 00:05:36,670
they're given a piece
of memory, as well.
111
00:05:36,670 --> 00:05:39,930
But let's say they don't
require very much private memory
112
00:05:39,930 --> 00:05:43,380
for sorting because they're not
going to do any sort operation.
113
00:05:43,380 --> 00:05:47,190
And then Oracle can just be
free not to allocate anymore
114
00:05:47,190 --> 00:05:48,310
memory to that.
115
00:05:48,310 --> 00:05:53,580
So on the whole, the PGA is
able to more efficiently manage
116
00:05:53,580 --> 00:05:57,600
the memory for Oracle users
in the private sphere.
9670
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.