Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:01,420 --> 00:00:04,120
In this lesson, we're going
to take a look at the System
2
00:00:04,120 --> 00:00:06,760
Global Area, or SGA.
3
00:00:06,760 --> 00:00:09,290
The SGA is a pod of
the Oracle instance.
4
00:00:09,290 --> 00:00:12,940
So the instance is composed of
background processes and memory
5
00:00:12,940 --> 00:00:13,930
caches.
6
00:00:13,930 --> 00:00:17,110
And the System Global
Area is the main cache
7
00:00:17,110 --> 00:00:19,690
that we think of when
we think of memory areas
8
00:00:19,690 --> 00:00:21,490
that Oracle uses.
9
00:00:21,490 --> 00:00:24,280
It's been said in Oracle
that everything is a cache,
10
00:00:24,280 --> 00:00:26,710
or everything is in cache.
11
00:00:26,710 --> 00:00:30,010
That's why the caches can be
so important to processing
12
00:00:30,010 --> 00:00:33,880
in Oracle because operations
don't occur on disk,
13
00:00:33,880 --> 00:00:35,590
they happen in memory.
14
00:00:35,590 --> 00:00:39,850
Volatile storage, such as RAM,
is so much faster, usually
15
00:00:39,850 --> 00:00:43,630
an order of magnitude faster,
than non-volatile storage
16
00:00:43,630 --> 00:00:44,710
like disk.
17
00:00:44,710 --> 00:00:47,020
So the SGA is the
System Global Area,
18
00:00:47,020 --> 00:00:50,500
or it could also be thought
of as the shared global area
19
00:00:50,500 --> 00:00:55,810
because the SGA represents the
shared memory used by Oracle.
20
00:00:55,810 --> 00:01:00,400
And shared memory is absolutely
crucial for a database system.
21
00:01:00,400 --> 00:01:03,670
The reason is that you have
many different users that
22
00:01:03,670 --> 00:01:05,440
access a database.
23
00:01:05,440 --> 00:01:07,240
If all of those
users are completely
24
00:01:07,240 --> 00:01:09,520
siloed from each
other, they don't
25
00:01:09,520 --> 00:01:13,160
get the benefit of being able
to share anything in memory.
26
00:01:13,160 --> 00:01:15,940
That's why shared memory is
important to an enterprise
27
00:01:15,940 --> 00:01:18,730
system, because it
allows one user to be
28
00:01:18,730 --> 00:01:21,250
able to benefit from the
work that was already
29
00:01:21,250 --> 00:01:23,430
done by another user.
30
00:01:23,430 --> 00:01:27,750
The first portion of the SGA
is the database buffer cache.
31
00:01:27,750 --> 00:01:30,930
In Oracle, database
blocks are read from disk
32
00:01:30,930 --> 00:01:34,530
into buffers in memory,
and those buffers
33
00:01:34,530 --> 00:01:37,050
form the database buffer cache.
34
00:01:37,050 --> 00:01:40,560
So all of the data that's
read from the database
35
00:01:40,560 --> 00:01:44,250
into memory to become accessible
to other users, all of that
36
00:01:44,250 --> 00:01:47,430
is written into the
database buffer cache.
37
00:01:47,430 --> 00:01:51,690
That makes it a very, very
crucial portion of the SGA.
38
00:01:51,690 --> 00:01:54,900
Users can require the
same data at times.
39
00:01:54,900 --> 00:01:57,660
So if a user does a
query, they perform
40
00:01:57,660 --> 00:02:02,220
a query that brings employee
information into the SGA,
41
00:02:02,220 --> 00:02:03,900
and it stays in
the database buffer
42
00:02:03,900 --> 00:02:06,270
cache, the reason it's
held in the buffer
43
00:02:06,270 --> 00:02:09,930
cache is that another user
might execute a query that
44
00:02:09,930 --> 00:02:11,610
requires the same data.
45
00:02:11,610 --> 00:02:16,140
So rather than another process
reading the data again,
46
00:02:16,140 --> 00:02:18,330
that data is already
in memory, it's
47
00:02:18,330 --> 00:02:22,440
already available for the second
user that came in as well.
48
00:02:22,440 --> 00:02:26,070
This is a crucial component
of how Oracle manages
49
00:02:26,070 --> 00:02:28,200
a high performance system.
50
00:02:28,200 --> 00:02:31,560
The database buffer cache
keeps data in the cache,
51
00:02:31,560 --> 00:02:34,980
based on what's called
the LRU algorithm.
52
00:02:34,980 --> 00:02:38,370
LRU stands for
Least Recently Used.
53
00:02:38,370 --> 00:02:42,090
So this algorithm defines
the rules or the conditions
54
00:02:42,090 --> 00:02:45,000
for which the database
writer process will
55
00:02:45,000 --> 00:02:48,900
write the buffers back out
of memory down to disk.
56
00:02:48,900 --> 00:02:52,480
And it's not the oldest
blocks in memory--
57
00:02:52,480 --> 00:02:55,710
it's the least
recently used blocks.
58
00:02:55,710 --> 00:02:59,950
The second portion of the System
Global Area is the shared pool.
59
00:02:59,950 --> 00:03:02,430
So let's look at the
concept of parsing.
60
00:03:02,430 --> 00:03:05,430
We execute this Select
statement that you see here--
61
00:03:05,430 --> 00:03:07,840
Select first name
from employees.
62
00:03:07,840 --> 00:03:09,840
Let's think about
what actually happens
63
00:03:09,840 --> 00:03:11,970
when we execute this query.
64
00:03:11,970 --> 00:03:14,430
It has to be parsed,
which means it
65
00:03:14,430 --> 00:03:17,910
has to be broken down
into a machine language
66
00:03:17,910 --> 00:03:19,980
that the computer
can understand.
67
00:03:19,980 --> 00:03:23,190
It has to be checked for
syntactical correctness.
68
00:03:23,190 --> 00:03:25,330
It has to be checked
for accuracy.
69
00:03:25,330 --> 00:03:28,290
So does the employees
table exist?
70
00:03:28,290 --> 00:03:32,160
Does the first name column
exist in the employee's table?
71
00:03:32,160 --> 00:03:35,040
Does the user have
access to read
72
00:03:35,040 --> 00:03:37,890
that table, do they have
the proper permissions.
73
00:03:37,890 --> 00:03:40,240
So all of those
things have to happen.
74
00:03:40,240 --> 00:03:41,820
But one of the most
important things
75
00:03:41,820 --> 00:03:44,610
that happens during a
parse is that the statement
76
00:03:44,610 --> 00:03:47,280
is broken down and
Oracle decides on what's
77
00:03:47,280 --> 00:03:49,260
called an execution plan.
78
00:03:49,260 --> 00:03:54,150
What's the best way for Oracle
to bring that data from disk
79
00:03:54,150 --> 00:03:57,120
into memory and make
it available to users.
80
00:03:57,120 --> 00:03:59,520
Should it read every
row in the table?
81
00:03:59,520 --> 00:04:00,990
Should it use an index?
82
00:04:00,990 --> 00:04:02,580
Should it use a partition?
83
00:04:02,580 --> 00:04:05,640
All of those things go
into the execution plan.
84
00:04:05,640 --> 00:04:08,490
So it turns out that just
executing a simple SQL
85
00:04:08,490 --> 00:04:10,950
statement can be an
expensive operation.
86
00:04:10,950 --> 00:04:12,870
There's a lot of work
that goes into it.
87
00:04:12,870 --> 00:04:14,760
And so Oracle is
designed, again,
88
00:04:14,760 --> 00:04:18,450
as a high performance system
to store the execution
89
00:04:18,450 --> 00:04:21,690
plans of SQL statements
from different users,
90
00:04:21,690 --> 00:04:24,480
and to store them in what's
called the shared pool.
91
00:04:24,480 --> 00:04:26,910
The specific portion
of the shared pool
92
00:04:26,910 --> 00:04:29,940
that stores this information
is called the library cache.
93
00:04:29,940 --> 00:04:32,520
So it will store the shared SQL.
94
00:04:32,520 --> 00:04:34,470
The second portion
of the shared pool
95
00:04:34,470 --> 00:04:36,750
is called the Data
Dictionary cache,
96
00:04:36,750 --> 00:04:39,150
which stores database metadata.
97
00:04:39,150 --> 00:04:42,480
So anytime, a SQL
statement is executed.
98
00:04:42,480 --> 00:04:45,330
Information is needed
from the data dictionary
99
00:04:45,330 --> 00:04:46,560
in the database.
100
00:04:46,560 --> 00:04:49,500
And so, again, as a
high performance system,
101
00:04:49,500 --> 00:04:53,250
rather than reread all of that
data dictionary information,
102
00:04:53,250 --> 00:04:55,650
it is stored in memory as well.
103
00:04:55,650 --> 00:04:57,930
The next memory area of
the System Global Area
104
00:04:57,930 --> 00:04:59,650
is the log buffer.
105
00:04:59,650 --> 00:05:03,120
So the log writer process
will write any changes
106
00:05:03,120 --> 00:05:08,070
that occur in the database to
memory first, then from memory
107
00:05:08,070 --> 00:05:09,150
onto disk.
108
00:05:09,150 --> 00:05:13,230
The memory area that is written
to by the log writer process
109
00:05:13,230 --> 00:05:15,060
is called the log buffer.
110
00:05:15,060 --> 00:05:17,970
So it is the small area
that holds information
111
00:05:17,970 --> 00:05:19,590
before it's written to disk.
112
00:05:19,590 --> 00:05:21,030
And there's three
conditions that
113
00:05:21,030 --> 00:05:24,810
can occur that caused the
log buffer to be flushed out
114
00:05:24,810 --> 00:05:26,070
to disk.
115
00:05:26,070 --> 00:05:32,040
Anytime a commit occurs, any
time the log buffer becomes 1/3
116
00:05:32,040 --> 00:05:36,990
full, or on a mandatory
checkpoint when Oracle forces
117
00:05:36,990 --> 00:05:40,550
data buffers out of
memory onto disk.
118
00:05:40,550 --> 00:05:44,090
The large pool is a
specialized memory area
119
00:05:44,090 --> 00:05:49,200
in the System Global Area that
was added in Oracle version 9i.
120
00:05:49,200 --> 00:05:50,900
And really it kind
of underscores
121
00:05:50,900 --> 00:05:52,760
the benefits of caching.
122
00:05:52,760 --> 00:05:55,970
The large pool was designed to
take some work from the shared
123
00:05:55,970 --> 00:05:56,900
pool.
124
00:05:56,900 --> 00:06:00,140
It's used in the shared
server architecture where,
125
00:06:00,140 --> 00:06:04,010
rather than give every user a
dedicated server process when
126
00:06:04,010 --> 00:06:07,970
they connect in as the dedicated
server model it operates,
127
00:06:07,970 --> 00:06:10,520
the shared server
model is going to spawn
128
00:06:10,520 --> 00:06:14,360
a fixed number of pipes into
the database, if you will,
129
00:06:14,360 --> 00:06:18,650
and every user that comes
in competes for those pipes.
130
00:06:18,650 --> 00:06:22,070
And it can be an efficient
use of resources in situations
131
00:06:22,070 --> 00:06:25,250
where you don't have a
lot of computing resources
132
00:06:25,250 --> 00:06:27,650
to satisfy your users.
133
00:06:27,650 --> 00:06:30,260
The large pool is
also used as caching
134
00:06:30,260 --> 00:06:31,710
for backup and recovery.
135
00:06:31,710 --> 00:06:33,830
So as backups and
recoveries occur,
136
00:06:33,830 --> 00:06:38,060
the large pool can be used
as an added memory area
137
00:06:38,060 --> 00:06:40,280
to speed that process.
138
00:06:40,280 --> 00:06:43,220
And the last of the pools
in the System Global Area
139
00:06:43,220 --> 00:06:45,770
are the Streams pool
and the Java pool.
140
00:06:45,770 --> 00:06:48,710
These are very specialized
pools that you will not
141
00:06:48,710 --> 00:06:51,990
use unless you use the
products associated with them.
142
00:06:51,990 --> 00:06:55,500
So the Streams pool will
support Oracle streams,
143
00:06:55,500 --> 00:06:58,310
which is Oracle's
latest product to do
144
00:06:58,310 --> 00:07:00,950
replication between
geographically
145
00:07:00,950 --> 00:07:02,780
separated locations.
146
00:07:02,780 --> 00:07:05,120
The Java pool is
a memory area that
147
00:07:05,120 --> 00:07:07,730
can be used to
instantiate Java objects.
148
00:07:07,730 --> 00:07:09,590
So if you use
Java-stored procedures
149
00:07:09,590 --> 00:07:13,370
in the database, stored code
of Java, those types of objects
150
00:07:13,370 --> 00:07:17,520
can benefit from the
memory of the Java pool.
12244
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.