【免费】expertone-on-oneoracle10gchap4资源-CSDN文库

需积分: 0 14 浏览量 2009-10-13 09:22:12 上传评论收藏 689KB PDF 举报

资源详情

资源评论

资源推荐

This chapter is provided on an "as is" basis as part of the Apress Beta Book Program.

Please note that content is liable to change before publication of the final book, and that

neither the author(s) nor Apress will accept liability for any loss or damage caused by

information contained.

electronic or mechanical, including photocopying, recording, or by any information

storage or retrieval system, without the prior written permission of the copyright owner

and the publisher.

CHAPTER 4

The Memory Structures

Now we are ready to look at Oracle’s major memory structures. There are three of them to be

considered:

* SGA, System Global Area – This is a large, shared memory segment that virtually all

Oracle processes will access at one point or another.

* PGA, Process Global Area – This is memory that is private to a single process or

thread, and is not accessible from other processes/threads.

* UGA, User Global Area – This is memory associated with your session. It will be

found either in the SGA or the PGA depending on whether you are connected to the

database using shared server (then it’ll be in the SGA), or dedicated server (it’ll be in

the PGA, in the process memory).

We will briefly discuss the PGA and UGA, and then move onto the really big structure,

the SGA.

The PGA and UGA

The PGA is a process piece of memory. In other words, it is memory specific to a single

operating system process or thread. This memory is not accessible by any other

process/thread in the system. It is typically allocated via either of the C run-time calls, malloc()

or memmap(), and may grow (and shrink even) at run-time. The PGA is never allocated in

Oracle’s SGA – it is always allocated locally by the process or thread.

The UGA is, in effect, your session’s state. It is memory that your session must always be

able to get to. The location of the UGA is wholly dependent on how you connected to Oracle.

If you have connected via a shared server, then the UGA must be stored in a memory

structure that everyone has access to – and that would be the SGA. In this way, your session

can use any one of the shared servers, since any one of them can read and write your sessions

data. On the other hand, if you are using a dedicated server connection, this need for

universal access to your session state goes away, and the UGA becomes virtually

synonymous with the PGA – it will in fact be contained in the PGA of your dedicated server.

When you look at the system statistics, you’ll find the UGA reported in the PGA in dedicated

server mode (the PGA will be greater than, or equal to, the UGA memory used; the PGA

memory size will include the UGA size as well).

So, the PGA contains process memory and may include the UGA. The other areas of

PGA memory are generally used for in-memory sorting and hashing. It would be safe to say

that, besides the UGA memory, these are the largest contributors by far to the PGA.

Starting with Oracle 9i Release 1 and above, there are two ways to manage this other

non-UGA memory in the PGA

* Manual PGA memory management, where you tell Oracle how much ram is it

allowed to use to sort and hash anytime it needs to sort or hash

* Automatic PGA memory management, whereby you tell Oracle how much memory

system wide it should attempt to use.

The manner in which memory is allocated and used differs greatly in each case and, as

such, we’ll discuss each in turn. It should be noted that in Oracle 9i, when using a shared

server connection, you can only utilize manual PGA memory management. This restriction

was lifted with Oracle 10g R1 and above – in that release, you can use either automatic or

manual PGA memory management with shared server connections.

PGA memory management is controlled by the database initialization parameter

WORKAREA_SIZE_POLICY and may be altered at the session level. This initialization

parameter defaults to AUTO, for automatic PGA memory management when possible.

Now, we’ll take a look at both approaches.

Manual PGA Memory Management

In manual PGA memory management, the parameters that will have the largest impact on the

size of your PGA, outside of the UGA, will be:

* SORT_AREA_SIZE: The total amount of RAM that will be used to sort information before

swapping out to disk.

* SORT_AREA_RETAINED_SIZE: The amount of memory that will be used to hold sorted

data after the sort is complete. That if, if SORT_AREA_SIZE was 512k and

SORT_AREA_RETAINED_SIZE was 256k, then your server process would use up to

512k of memory to sort data during the initial processing of the query. When the sort

was complete, the sorting area would be “shrunk” down to 256k and any sorted data

that did not fit in that 256k would be written out to temporary segments.

* HASH_AREA_SIZE: The amount of memory your server process would use to store hash

tables in memory. These structures are used during a hash join, typically when joining

a large set with another set. The smaller of the two sets would be hashed into memory

(hopefully, anything that doesn’t fit in the hash area region of memory would be stored

in the temporary tablespace) by the join key. .

These parameters control the amount of space Oracle will use to sort data before writing

(swapping) it to disk, and how much of that memory segment will be retained after the sort is

done. The SORT_AREA_SIZE is generally allocated out of your PGA and the

SORT_AREA_RETAINED_SIZE will be in your UGA. We can discover our current usage of

PGA and UGA memory and monitor its size by querying special Oracle V$ tables, also

referred to as a dynamic performance table.

NOTE We’ll find out more about these V$ tables in Chapter X, Tuning Strategies and Tools.

For example, I’ll run a small test whereby in one session we will sort lots of data and,

from a second session, we monitor the UGA/PGA memory usage in that first session. In

order to do this in a predicable manner, we’ll make a copy of the ALL_OBJECTS table without

any indexes (so we know a sort has to happen):

ops$tkyte@ORA10G> drop table t;

Table dropped.

ops$tkyte@ORA10G> create table t as select * from all_objects;

Table created.

ops$tkyte@ORA10G> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

Now, in order to remove any side effects from the initial hard parsing of queries we will

run the following script, but for now ignore it’s output. We will be running it again, in a

fresh session so as to see the effects on memory usage in a controlled environment. We will

use the sort area sizes of 64 KB, 1 MB, and 1 GB in turn:

alter session set sort_area_size = 65536;

set autotrace traceonly statistics;

select * from t order by 1, 2, 3, 4;

set autotrace off

alter session set sort_area_size=1048576;

set autotrace traceonly statistics;

select * from t order by 1, 2, 3, 4;

set autotrace off

alter session set sort_area_size=1048576000;

set autotrace traceonly statistics;

select * from t order by 1, 2, 3, 4;

set autotrace off

NOTE: We will discuss parsing in detail in Chapter X, Tuning Strategies and Tools. When we process SQL in the

database, we must first “parse” the SQL statement. There are two types of parses available, a ‘hard’ parse which

is what happens the first time a query is parsed by the database instance and includes query plan generation and

optimization. There is also a soft parse, which can skip many of the steps a hard parse must do. We are hard

parsing the queries above so as to not measure the work performed by that operation in the following section.

Now, I would suggest logging out of that SQL*Plus session and logging back in before

continuing, in order to get a consistent environment, one in which no work has been done yet.

In order to ensure we are using manual memory management, we’ll set it on specifically and

specify our rather small sort area size of 64k, also, we’ll identify our SID (session id) so we

can monitor the memory usage for that session:

ops$tkyte@ORA10G> alter session set workarea_size_policy=manual;

Session altered.

ops$tkyte@ORA10G> select sid from v$mystat where rownum = 1;

SID

----------

151

Now, we need to measure SID 151’s memory from a second separate session. If we used

the same session then our query to see how much memory we are using for sorting might

itself influence the very numbers we are looking at! To measure the memory from this

second session, I will be using a small SQL*Plus script I developed for this. It is actually a

pair of scripts, one to reset a small table and set a SQL*Plus variable to the SID we want to

watch, I call this reset_stat.sql:

drop table sess_stats;

create table sess_stats

( name varchar2(64), value number, diff number );

variable sid number

exec :sid := &1

NOTE: Before using this or in fact any script, make sure you understand what it does. I am dropping and

recreating a table called SESS_STATS. If your schema already has such a table, well, you would probably

want to use a different name!

The other script I call watch_stat.sql, and for this case study, it looks like this:

merge into sess_stats

using

(

select a.name, b.value

from v$statname a, v$sesstat b

where a.statistic# = b.statistic#

and b.sid = :sid

and (a.name like '%ga %'

or a.name like '%direct temp%')

) curr_stats

on (sess_stats.name = curr_stats.name)

when matched then

update set diff = curr_stats.value - sess_stats.value,

value = curr_stats.value

when not matched then

insert ( name, value, diff )

values

( curr_stats.name, curr_stats.value, null )

select *

from sess_stats

order by name;

I said it looks like this for this case study, because of the line in bold, the names of the

statistics I’m interested in looking at change from example to example. In this particular case

we are interested in anything with ‘ga ‘ in it (pga and uga) or anything with ‘direct temp’

which in Oracle 10g will show us the direct reads and writes against temporary space (how

much IO we did reading and writing to temp).

NOTE: In Oracle 9i, direct IO to temporary space was not labeled as such, you would use a where clause that

included and (a.name like '%ga %' or a.name like '%physical % direct%') in it.

When that is run from the SQL*Plus command line , you’ll see a listing of the PGA and

UGA memory statistics for that session as well as temporary IO. Before we do anything in

session 154, using manual PGA memory management, let's use this script to find out how

much memory we are currently using and how many temporary IO’s we have performed:

ops$tkyte@ORA10G> @watch_stat

6 rows merged.

NAME VALUE DIFF

------------------------------------------- ---------- ----------

physical reads direct temporary tablespace 0

physical writes direct temporary tablespace 0

session pga memory 498252

session pga memory max 498252

session uga memory 152176

session uga memory max 152176

So, before we begin we can see that we have about 149 KB (152176/1024) of data in the

UGA and 487 KB of data in the PGA. The first question is: how much memory are we using

between the PGA and UGA, that is – are we using 149+487 Kb of memory or some other

amount? It is a trick question, and one that you cannot answer unless you know if you are

connected via a dedicated server or a shared server, and even then it might be hard to figure

out. In dedicated server mode, the UGA is totally contained within the PGA, in which case

we would be consuming 487 KB of memory in our process or thread. In shared server, the

UGA is allocated from the SGA, and the PGA is in the shared server. So, in shared server

mode, by the time we get the last row from the above query, our process may be in use by

someone else. That PGA isn’t ‘ours’ anymore, so technically we are using 149 KB of

memory (except when we are actually running the query at which point we are using 487 KB

剩余36页未读，继续阅读

评论收藏

内容反馈

jade_nju

粉丝: 0
资源: 8

expert one-on-one oracle 10g chap4

评论0

最新资源

expert one-on-one oracle 10g chap4

评论0

expert one-on-one oracle (10g) ch1

Effective Oracle by Design&Expert One-on-One Oracle&Apress expert oracle database architecture 9i and 10g(part2)

Effective Oracle by Design&Expert One-on-One Oracle&Apress expert oracle database architecture 9i and 10g(part1)

expert one-on-one oracle 10g chap5

WNS-PPT-chap07-Windows群集-V.ppt

RFC2759 RFC2759-PPP_CHAP_Version2

PPP-PAP-CHAP-封装协议-配置命令.doc

lwip_ppp_MS-CHAP V2

《Oracle9i&10g编程艺术》完整英文版PDF(apress.expert.oracle.database.architecture.9i.and.10g.programming.techniques.and.solutions.sep.2005.)

lecco sql expert for oracle v3.2 连接Oracle 10g 时，需进行的步骤

expert oracle database 10g administration 高清pdf

Macroeconomics-chap-经济增长理论.ppt

李贤平-概率论基础-Chap4

USCODE-2011-title15-chap47-sec2057c.zip

贾永红-数字图像处理--chap1.ppt

贾永红-数字图像处理--chap6.ppt

贾永红-数字图像处理--chap3.ppt

网络设置-CHAP的配置

Clementine系列视频教程－chap6-2.flv

Clementine系列视频教程－chap6-3.flv

Clementine系列视频教程－chap6-1.flv

大连理工大学网络安全与密码学--chap10-pgp.ppt

计算机网络实验-华为ensp拓扑图-用于PPP基本配置-PAP 鉴别-CHAP 鉴别-IP 地址协商

Python全套课程笔记-chap4-函数

1.进入计算机世界(预科)prev-PPT-CHAP06-V1.0.ppt

Clementine系列视频教程－chap4.flv

贾永红-数字图像处理--chap2.ppt

程序设计基础徐明星w06－chap06－数据组织一－递推思想－数组定义－字符数组－part1.ppt

最新资源