CategoryORACLE

Oracle12C SGA PGA UGA

SGA和PGA简介

1 sga组成: 2b4p1s(记忆)
database buffer cache:包括 default pool,keep pool,recycle pool;
redo log buffer
share pool:包括 library cache,dictionary cache
large pool
java pool
streams pool
fixed sga 这部分区域的大小只有几MB

2.pga组成:
1)stack space(sql work area) :sort area(排序区),hash area(构造hash表),bitmap merge area(索引区)
2)uga区: (private sql area占主要部分):
2.1) persistent 区域 :存放绑定变量的值,以及数据类型等游标信息
2.2) run-time 区域 :查询执行状态信息,X$KSMUP

Oracle为执行sql的每一个session分配一个private sql area,这个区域还存储了一个很重要的地址信息用来指向存储在library cache中的shared sql area,
里面保存了SQl的执行计划。Oracle建议程序开发过程中尽量多使用bind variable目的就是为了降低hard parse,
直接意思的理解就是让我们尽量重用library cache中的SQL执行计划,这样就可以降低share pool latch和library cache latch等闩的竞争。
其中bind variable从那里来呢?上面其实已经提到了就是在private sql area中的永久内存区域存在,这个区域只有在游标关闭的状态下才被释放。
这里最后要了解的是,永久内存或者是运行时内存的释放并不是将内存反还给OS,而是反还给了UGA

官方文档位置

  • Books → Concepts → Part V Oracle Instance Architecture → 14 Memory Architecture → 14.2 Overview of the User Global Area
  • Books → Concepts → Part V Oracle Instance Architecture → 14 Memory Architecture → 14.3 Overview of the Program Global Area (PGA)

 

PGA 概述

PGA 是特定于一个操作系统进程或线程的内存区,且不和系统上的其他进程或线程共享。由于 PGA 是特定于进程的,所以它决不会在 SGA 中分配。PGA 是包含某个专用或共享服务器进程所需的会话变量的内存堆。服务器进程在需要时会在 PGA 中分配内存结构。

PGA 好比是文员所使用的临时工作台面。在这个比喻中,文员是为客户(客户端进程) 服务的服务器进程。文员清理出台面的一部分,使用这个工作空间来存储有关客户要求的详细信息,并对顾客请求的文件夹排序,然后在完成工作时让出工作空间。

下图显示某个未配置为共享服务器的实例的 PGA(所有 PGA 的集合) 。您可以使用一个初始化参数设置实例 PGA 的目标最大大小。根据需要, 各个 PGA 可以按需增大到这个目标大小。

图 2:实例 PGA

 

Note:
后台进程也分配它们自己的 PGA。 本讨论的重点仅限于服务器进程 PGA。
See Also:

  • “Summary of Memory Management Methods”

1 PGA 的内容

PGA 被进一步细分为多个不同区域,每一个都有不同的目的。

下图显示一个专用服务器会话的 PGA 中可能包含的内容。不是所有的 PGA 区域在任何情况下都存在。

图 3:PGA 内容

1.1 Private SQL 区

私有 SQL 区保存了有关某个已解析的 SQL 语句的信息,和其他特定于会话的信息。

当服务器进程执行 SQL 或 PL/SQL 代码时,该过程使用其私有SQL 区域,来存储绑定变量值、查询执行状态信息、和查询执行工作区。

不要混淆在 UGA 中的私有 SQL 区,和在 SGA 中存储执行计划的共享 SQL区。在相同或不同的会话中的多个私有 SQL 区,可能指向 SGA 中的一个单一执行计划。 例如,在某个会话中运行“SELECT * FROM sales” 20 次,而在另一个不同的会话中运行同一查询 10 次,但它们可以共享相同的执行计划。每次运行的私有 SQL 区并不共享,因此可能包含不同的值和数据。

游标是指向某个特定的私有 SQL 区的一个名称或句柄。如下图所示,你可以将游标看成是一个从客户端指向服务器端状态信息的指针。游标与私有 SQL 区密切相关,这两个术语有时可以互换使用。

图 4:游标

私有 SQL 区又分为以下几个区域:

  • run-time 区域
    此区域包含查询执行状态信息。例如,run-time area会跟踪到目前为止在全表扫描中检索到的行数。
    Oracle 数据库将创建 run-time 区域,作为一个执行请求的第一步。对于 DML 语句,其 run-time 区域将在 SQL 语句关闭时被释放。
  • persistent 区域
    此区域包含绑定变量的值。绑定变量是执行 SQL 语句时,在运行时提供给 SQL 语句的值。仅当关闭该游标时,persistent  区域才被释放。

客户端进程负责管理私有 SQL 区。虽然客户端进程可以分配的私有 SQL 区数量由初始化参数 OPEN_CURSORS 限制,但私有 SQL 区的分配和释放主要取决于应用程序。

尽管大多数用户依赖于数据库实用程序的自动游标处理机制,但 Oracle 数据库编程接口仍为开发人员提供了对游标更多的控制。一般情况下,应用程序应关闭所有打开的且不再使用的游标,以释放持久区域, 并最小化应用程序用户的内存需求。

See Also:

  • “Shared SQL Areas”
  • 《Oracle Database Development Guide》和《Oracle Database PL/SQL Language Reference》了解如何使用游标

 

1.2 SQL Work 区

工作区是在 PGA 中为内存密集型操作分配的私有内存区。

例如, 排序操作使用排序区来对一组行进行排序。同样,哈希联接操作将其左侧数据为输入,并使用哈希区来创建一个哈希表,而位图合并操作则使用位图合并区来合并从扫描多个位图索引检索到的数据。

如下示例显示了 employees 表和 departments 表相联接的查询计划。

SQL> SELECT * 
  2  FROM   employees e JOIN departments d 
  3  ON     e.department_id=d.department_id 
  4  ORDER BY last_name;
--------------------------------------------------------------------------------
| Id| Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |             |   106 |  9328 |    7  (29)| 00:00:01 |
| 1 |  SORT ORDER BY      |             |   106 |  9328 |    7  (29)| 00:00:01 |
|*2 |   HASH JOIN         |             |   106 |  9328 |    6  (17)| 00:00:01 |
| 3 |    TABLE ACCESS FULL| DEPARTMENTS |    27 |   540 |    2   (0)| 00:00:01 |
| 4 |    TABLE ACCESS FULL| EMPLOYEES   |   107 |  7276 |    3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

在示例 1 中,run-time 区域跟踪全表扫描的进度。此会话在哈希区中执行一个哈希联接,以匹配两个表中的行。而 ORDER BY 排序操作在排序区中进行。

如果要由该运算符处理的数据量太大,不能在SQL Work 区中完成, 则 Oracle 数据库会将输入数据分成更小的片断。这样一来,数据库先在内存中处理一些数据片断,而将其余数据写入到临时磁盘存储,以待后续处理。

当启用了自动 PGA 内存管理时,数据库自动优化工作区大小。你也可以手动控制和优化工作区大小。更多的信息,请参阅“Memory Management”。

通常,更大的工作区可以显著提高操作性能,但这是以更多的内存消耗为代价的。理想的情况是,工作区域的大小足以容纳由其关联的SQL操作符分配的输入数据和辅助内存结构。否则,响应时间会增加,因为输入数据的一部分必须被缓存到磁盘上。在极端情况下,如果工作区的大小相比输入数据的大小显得过小,则数据库必须来回多次倒腾这些数据片断,极大地增大了响应时间。

See Also:

  • 《Oracle Database Administrator’s Guide》了解如何使用自动 PGA 管理
  • 《Oracle Database Performance Tuning Guide》 了解如何调整 PGA 内存

2 在专用和共享服务器模式中使用 PGA

PGA 内存分配取决于数据库是使用专用的还是共享的服务器连接。

表 1 显示了差异之处。

 

内存区 专用服务器 共享服务器
会话内存的性质 私有的 共享的
persistent 区域的位置 PGA SGA
DML/DDL 语句的run-time区域的位置 PGA SGA

表 14-1 专用和共享服务器之间的内存分配差异

See Also:

 

    • 《Oracle Database Administrator’s Guide》了解如何将数据库配置为共享服务器

UGA 概述

UGA 是为会话变量分配的会话内存, 如登录信息和数据库会话所需的其他信息。 实际上,UGA 存储了会话状态。

图 1 描述了 UGA

图 1:用户全局区 (UGA)

如果某个会话将 PL/SQL 包加载到内存, 则在 UGA 中包含包状态,即是所有包变量在某个特定的时刻所存储的值集。当包的子程序更改变量的值时,包状态也将更改。默认情况下,包变量在会话的存活期间是唯一且持久的。

OLAP 页面缓冲池也存储在 UGA 中。该池管理相当于数据块的 OLAP 数据页。页缓冲池在启动一个 OLAP 会话时分配,并在该会话结束时释放。每当用户查询一个多维对象(如立方体)时,就会自动打开一个 OLAP 会话。

该 UGA 必须在数据库会话的整个存活期间是可用的。 由于这个原因, 当使用共享服务器的连接时,UGA 不能存储在 PGA 中,因为 PGA 是特定于单个进程的。因此,当使用共享服务器的连接时,UGA 被存储在 SGA 中,以使任何共享服务器进程都能访问它。在使用专用服务器的连接时, UGA 存储在 PGA 中。

 

pga和uga比较

uga:user global area ,是会话含义的内存区 为了保证数据可以被会话访问到,所以mts模式属于sga中的大池,专有模式属于pga,属于用户的内存区。
uga保存当前会话相关的信息,比如会话登录信息、pl/sql包的参数信息,绑定变量的值。

pga:program global area,是操作系统含义上的内存区,
可以理解为操作系统在一个进程启动时,为他分配的内存空间
查询使用 show pga;

sga和pga比较

sga:共享数据块,所有进程可以访问,数据并发访问
涉及lock,latch,锁定和队列
是数据库最主要优化区域,一些重要的指标:data buffer hit,library hit(hard/soft parse),hot blocks
pga:为专有进程服务,进程间无法数据共享,数据独占
无需锁定机制
性能优化只需要考虑它的大小。

oracle的约束隐式创建索引和先索引后约束的区别

两种情况:
1.对于创建约束时隐式创建的索引,在做删除操作的时候: 9i~11g都会连带删除该索引

2.对于先创建索引,再创建约束(使用到此索引)这种情况:
9i版本:需要区分索引是否唯一:
如果索引是唯一的,则删除约束的时候,会连带删除索引;如果非唯一的,则不会删除索引。
10g以后版本,包括11g:无论索引是否唯一,都只是删除约束,索引不会删除。

参考metalink文档:309821.1

实验验证下
$ ss

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 1 18:29:31 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 889389056 bytes
Fixed Size 2233480 bytes
Variable Size 830475128 bytes
Database Buffers 50331648 bytes
Redo Buffers 6348800 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> conn hr/hr
Connected.

先创建的索引,无论是否是unique索引,都不会随约束删除而被删除
SQL> create table test(a number );

Table created.

SQL> create index ind on test ( a );

Index created.

SQL> alter table test add constraint c1_pk primary key(a) using index;

Table altered.

SQL> select index_name from user_indexes where table_name=’TEST’;

INDEX_NAME
——————————
IND

SQL> alter table test drop constraint c1_pk;

Table altered.

SQL> select index_name from user_indexes where table_name=’TEST’;

INDEX_NAME
——————————
IND

SQL> drop index IND ;

Index dropped.

 
SQL> create unique index ind2 on test ( a );

Index created.

SQL> alter table test add constraint c2_pk primary key(a) using index;

Table altered.

SQL> alter table test drop constraint c2_pk;

Table altered.

SQL> select index_name from user_indexes where table_name=’TEST’;

INDEX_NAME
——————————
IND2

 

清理一下环境,删除索引,然后直接建约束,隐式创建索引,索引会因为约束被删除,而同时被删除
SQL> drop index ind2 ;

Index dropped.

SQL>
SQL>
SQL> alter table test add constraint c2_pk primary key(a) using index;

Table altered.

SQL> select index_name from user_indexes where table_name=’TEST’;

INDEX_NAME
——————————
C2_PK

SQL> alter table test drop constraint c2_pk;

Table altered.

SQL> select index_name from user_indexes where table_name=’TEST’;

no rows selected

impdp报错ORA-39083 ORA-02304 Object type TYPE failed to create

impdp报错ORA-39083 ORA-02304 Object type TYPE failed to create

环境
Red Hat Enterprise Linux Server release 5.8 (Tikanga)
ORACLE Release 11.2.0.3.0 Production

我用expdp,impdp复制一个shema,在impdp导入的时候报错
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE “GENIDCDEV2”.”ARRAY_TYPE2″ OID ‘0367306C82464BF1E0534E092D0A4AB5′ is array(48) of varchar2(255)

查看官方文档的impdp章节,搜索OID,发现了如下有用的内容
查看impdp help=y 可以使用一个参数:transform

TRANSFORM
Enables you to alter object creation DDL for objects being imported.

OID – If the value is specified as n, the assignment of the exported OID during the
creation of object tables and types is inhibited. Instead, a new OID is assigned.
This can be useful for cloning schemas, but does not affect referenced objects. The
default value is y.

这个参数默认是Y,如果改成N,那么导入的时候会把各个对象重新赋予新的OID。
transform=oid:n
这样,再用impdp导入的时候就不会出现
ORA-39083,ORA-02304
注意:exp/imp不支持oid转换,又是一个使用数据泵的优势。

因此我想到了两种方法可以解决问题
1.用下面impdp语句重新导入
impdp system/’*****’ directory=dir_lisx REMAP_SCHEMA=WMSUAT:GENIDCDEV2 dumpfile=wmsuat20150304.dump include=TYPE transform=oid:n logfile=impdp_wmsuat20150304_2.log

Import: Release 11.2.0.3.0 – Production on Wed Mar 4 14:55:40 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: system/******** directory=dir_lisx REMAP_SCHEMA=WMSUAT:GENIDCDEV2 dumpfile=wmsuat20150304.dump include=TYPE transform=oid:n logfile=impdp_wmsuat20150304_2.log
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Job “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully completed at 14:55:45
2.手工重建刚才导入失败的type
核查type的ddl语句
SELECT dbms_metadata.get_ddl(‘TYPE’,’ARRAY_TYPE2′,’WMSUAT’) from dual ;

CREATE OR REPLACE TYPE “WMSUAT”.”ARRAY_TYPE2″ is array(48) of varchar2(255) ;

记得重新编译一下新clone的schema
EXEC DBMS_UTILITY.compile_schema(schema => ‘GENIDCDEV2’);

ORACLE11.2.0.3数据库alter table drop column慢原因分析

中午某生产数据库发布补丁,开发人员反应有语句阻塞,无法执行下面语句
alter table pyramid.pd_loanProduct drop column customerType;
这是一个只有4行的小表

我建个测试表尝试了一下,果然很慢
create table pyramid.test_alter2(id number,name varchar2(10))  ;
alter table pyramid.test_alter2 drop column name;

Continue reading

解决standby数据库standby_file_management为MANUAL引发的问题

standby数据库参数
SQL> show parameter standby

NAME TYPE VALUE
———————————— ———– ——————————
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL

在主库新建表空间后,备库alert报错,并且mrp进程终止
主库执行的命令:
create tablespace tbs_test datafile ‘/u01/app/oracle/oradata/prod/tbs_test01.dbf’ size 10m;
备库alert:
Continue reading

修改standby的日志传输模式,从ASYNC到LGWR SYNC AFFIRM

修改之前是主备库相差一个日志文件,如果switchover ,有数据丢失的风险,修改后,主库的日志可以实时传输,并且实时应用到standby,有个AFFIRM的过程,主库commit之前会确认日止在备库已经应用。

从alert看,非ADG的alert的关键词是Media Recovery Log ,ADG的alert关键词是Recovery of Online Redo Log

版本:
SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 23 07:04:35 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

修改前参数
alter system set log_archive_dest_2=’SERVICE=dsedidg ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dsedidg’ scope=both ;

修改方法:
–主库
alter system set log_archive_dest_2=’SERVICE=dsedidg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dsedidg’ scope=both ;

–备库
alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect from session;
Continue reading

oracle 11gR2 standby重建online和standby log删除不掉最后一个log

因为上了SSD,SSD对小文件连续写会有性能问题,因此ORACLE推荐把redo和control迁移到SAS盘上,因此要重建redo log和standby_log,在备库上新建了redo和standby后,想删除旧的log
发现问题,备库上新建的online和standby log都无法被ORACLE使用,因此备库上旧的日志也无法彻底删除
17:25:12 sys@DSEDI>show parameter standby

NAME TYPE VALUE
———————————— ———– ——————————
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
Continue reading

WAITEVENT: “log file sync” Reference Note (文档 ID 34592.1)

WAITEVENT: “log file sync” Reference Note (文档 ID 34592.1)

Versions:7.0 – 11.1 Documentation: 11g 10g
When a user session(foreground process) COMMITs (or rolls back), the session’s redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write all redo required from the log buffer to the redo log file. When the LGWR has finished it will post the user session. The user session waits on this wait event while waiting for LGWR to post it back to confirm all redo changes are safely on disk.

This may be described further as the time user session/foreground process spends waiting for redo to be flushed to make the commit durable. Therefore, we may think of these waits as commit latency from the foreground process (or commit client generally).

Continue reading

Troubleshooting ‘Log File Sync’ Waits

I have been contacted by one of our customers to provide reference information on troubleshooting Oracle Log File Sync waits.

I think that this information worth short blog post.

Reasons:
Log File Sync waits occur when sessions wait for redo data to be written to disk
typically this is caused by slow writes
or committing too frequently in the application
CPU overburning(very high demand => LGWR on run queue)
improper Operating System configuration(check 169706.1)
BUGs in Oracle(especially with RAC option) and 3rd Party software(like ODM/DISM)

Recommendations:
tune LGWR process to get good throughput, especially when ‘log file parallel write‘ high too:
do not put redo logs on RAID 5 without good write cache
do not put redo logs on Solid State Disk (SSD)
It looks like last recommendatin was based on old experience working with SSD disk, which is obsolete now and even Oracle recommends using SSD disks for REDO logs(1566935.1 Implementing Oracle E-Business Suite 12.1 Databases on Oracle Database Appliance):

“Move REDO log files to +REDO diskgroup on Solid State Disks (SSDs).”

if CPUs are overburned(check runqueue with vmstat):
check for non-oracle system activity, like GZIP or BZIP2 running in business hours…
lower instance’s CPU usage(for example, tune SQL for LIOs)
increase LGWR priority(renice or _high_priority_processes),
decrease COMMITs count for applications with many short transactions
use COMMIT [BATCH] NOWAIT(10g+) when possible
do some processing with NOLOGGING(or may be even with _disable_logging=TRUE if just testing performance benchmark/impact), but think about database recoverability
lower system’s CPU usage or increase LGWR priority
check if there is some 3rd party software, or utilities like RMAN, activity on the same disks as redo logs placed, like trace/systemstate dump files, e.t.c
trace LGWR as the last option for troubleshooting OS/3rd party issues 😉

References:
34592.1 WAITEVENT: “log file sync” Reference Note
34583.1 WAITEVENT: “log file parallel write” Reference Note
1376916.1 Troubleshooting: log file sync’ Waits
223117.1 Troubleshooting I/O-related waits
857576.1 How to Minimise Waits for ‘Log File Sync’
1064487.1 Script to Collect Log File Sync Diagnostic Information (lfsdiag.sql)
1318709.1 AIX: Things To Check When Seeing Long Log File Sync Time in 11.2.
1205673.1 ‘Log File Sync’ problem on a Sun Server: A Typical Source for LOGFILE SYNC Performance Problems
1523164.1 SPARC: Reducing High Waits on ‘log file sync’ on Oracle Solaris SPARC by Increasing Priority of Log Writer
13551402.8 High “log file parallel write” and “log file sync” after upgrading 11.2 with Veritas/Symantec ODM
1278149.1 Intermittent Long ‘log file sync’ Waits, LGWR Posting Long Write Times, I/O Portion of Wait Minimal
1229104.1 LOG FILE SYNC WAITS SPIKES DURING RMAN ARCHIVELOG BACKUPS
1462942.1 Adaptive Switching Between Log Write Methods can Cause ‘log file sync’ Waits
Kevin Closson: “Manly Men Only Use Solid State Disk For Redo Logging. LGWR I/O is Simple, But Not LGWR Processing”
Jeremy Schneider: “Adaptive Log File Sync: Oracle, Please Don’t Do That Again”
Riyaj Shamsudee: “Tuning ‘log file sync’ wait events”
Gwen Shapira: “De-Confusing SSD (for Oracle Databases)”
Guy Harrison: “Using Solid State Disk to optimize Oracle databases”
SSD Performance Blog

Adaptive Log File Sync

Disclaimer: Much of what follows is pure speculation on my part. It could be completely wrong, and I’m putting it out there in the hopes that it’ll eventually be proven one way or the other.

The Summary

  • Underscore parameter _use_adaptive_log_file_sync
    • Default value changed in 11.2.0.3 from FALSE to TRUE
    • Dynamic parameter
  • Enables a new method of communication for LGWR to notify foreground processes of commit
    • Old method used semaphores, LGWR had to explicitly “post” every waiting process
    • New method has the FG processes sleep and “poll” to see if commit is complete
    • Advantage is to free LGWR from CPU work required to inform lots of processes about commits
  • LGWR dynamically switches between old and new method based on load and responsiveness
    • Method can switch frequently at runtime, max frequency is 3 switches per minute (configurable)
    • Switch is logged in LGWR tracefile, we have seen several switches per day
  • Few problems in general, possible issues seem to be in RAC and/or the switching process itself
    Continue reading

© 2019 Init dba

Theme by Anders NorenUp ↑