ITPub博客

关于SELECT * FROM v$sga;结果的解析NAME VALUE-------------------- ---------Fixed Size 39816Variable Size 151544820Database Buffers 838860800Redo Buffers 41943040Fixed Size的大小由oracle控制,可以把这部分认为是SGA的“bootstrap”,用于获取SGA其他部分的bits和pieces的信息.compute sum of bytes on poolbreak on pool skip 1select pool, name, bytesfrom v$sgastatorder by pool, name;Variable Size的大小大致等于ava_pool_size + large_pool_size + shared_pool_size要注意每个控制文件会占用256个字节的variable size,如

Database Writer (DBWn) The server process records changes to undo and data blocks in the Database Buffer Cache. DBWn writes the dirty buffers from the Database Buffer Cache to the data files. It ensures that a sufficient number of free buffers (buffers that can be overwritten when server processes need to read in blocks from the data files) are available in the Database Buffer Cache. Database performance is improved because server processes make changes only in the Database Buffer Cache. DBWn de

update 　 ORACLE UPDATE 语句语法与性能分析的一点看法 为了方便起见,建立了以下简单模型,和构造了部分测试数据: 在某个业务受理子系统BSS中， --客户资料表 create table customers ( customer_id number(8) not null, -- 客户标示 city_name varchar2(10) not null, -- 所在城市 customer_type char(2) not null, -- 客户类型 ... ) create unique index PK_customers on customers (customer_id) 由于某些原因，客户所在城市这个信息并不什么准确，但是在 客户服务部的CRM子系统中，通过主动服务

Oracle AWR and STATSPACK Manual snapshot commandsThere are times when you want to manually collect AWR and STATSPACK snapshots, such as when you are experiencing a performance problem or doing a stress test. You can issue these commands to get a manual AWR or STATSPACK snapshot at whatever elapsed time interval you desire:AWR manual snapshot:EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;STATSPACK manual snapshot:exec statspack.snap;[@more@]

Oracle:select decode(a1,'0', 'easy', '1', 'normal', '2', 'hard') level from a;转成SQL Server为:select (case a1 when '0' then 'easy'when '1' then 'normal'when '2' then 'hard' end) levelfrom a[@more@]

OverviewIf you notice poor performance in your Oracle database Row Chaining and Migration may be one of several reasons, but we can prevent some of them by properly designing and/or diagnosing the database.Row Migration & Row Chaining are two potential problems that can be prevented. By suitably diagnosing, we can improve database performance. The main considerations are:What is Row Migration & Row Chaining ? How to identify Row Migration & Row Chaining ? How to avoid Row Migration &

Oracle中，一个Delete操作的流程删除(DELETE)1.Oracle读Block到Buffer Cache(如果该Block在Buffer中不存在)2.在redo log buffer中记录delete操作的细节3.在相应回滚段段头的事务表中创建一个undo条目4.把将要删除的记录创建前镜像,存放到Undo Block中5.在Buffer Cache中的相应数据块上删除记录，并且标记相应的数据块为Dirty提交(COMMIT)1.Oracle产生一个SCN2.在回滚段事务表中标记该事务状态为commited3.LGWR Flush Log Buffer到日志文件3.如果此时数据块仍然在Buffer Cache中，那么SCN将被记录到Block Header上，这被称为快速提交(fast

Parsing in Oracle Amar Kumar Padhi, amar_padhi@hotmail.com <mailto:amar_padhi@hotmail.com> Whenever a statement is executed, Oracle follows a methodology to evaluate the statement in terms of syntax, validity of objects being referred and of course, privileges to the user. Apart from this, Oracle also checks for identical statements that may have been fired, with the intention of reducing processing overheads. All this takes place in a fraction of a second, even less, without the user kno

一条语句被解析parse的过程2006-11-22 00:35:42 / 个人分类：administration <http://blog.oracle.com.cn/47810/spacelist_type_blog_itemtypeid_144.html> SQL <javascript:;> Parsing Flow Diagram ========================Statement Submitted|Is it in an open cursor?--------------YES----V | |NO | | |Is SESSION_CACHED_CURSORS = Value | and cursor in

Oracle's buffer cache The buffer cache is part of the SGA <http://www.adp-gmbh.ch/ora/concepts/sga.html#db_buffer_cache> . It holds copies of data blocks <http://www.adp-gmbh.ch/ora/concepts/db_block.html> so as they can be accessed quicker by oracle than by reading them off disk. PurposeThe purpose of the buffer cache is to minimize physical io <http://www.adp-gmbh.ch/ora/tuning/logical_vs_physical_io.html#physical_io> . When a block <http://www.adp-gmbh.ch/ora/concepts/d

Latch 系列一: Introducion <http://www.oracledba.com.cn/blog/?p=26> Posted in Oracle <http://www.oracledba.com.cn/blog/?cat=1> , Brotherxiao's <http://www.oracledba.com.cn/blog/?cat=18> at 3:55 pm by bachelor什么是latch?latch是一种低级别的序列化的内存锁,用于保护SGA中的共享内存结构.(1)确保内核代码序列执行;(2)防止因缓存区崩溃导致的物理块的坏块任何内核代码在执行前,必须获取latch,执行完后释放latch.latch通常通过两种方式来获取:immediate和willing to wait.当进程以immeidate方式请求latch失

Oracle's x$ Tables See also: Speculation of X$ Table Names <http://www.stormloader.com/yonghuang/computer/x$table.html> x$ tables are the sql interface to viewing oracle's memory in the SGA <http://www.adp-gmbh.ch/ora/concepts/sga.html> . The names for the x$ tables can be queried with select <http://www.adp-gmbh.ch/ora/sql/select.html> kqftanam from x$kqfta;x$activeckptx$bhInformation on buffer headers. Contains a record (the buffer header) for each block in the buffer cach

虽然是转的,也得认真看看....statspack 必看的十项内容发表人:pentium | 发表时间: 2007年三月05日, 01:40http://xsb.itpub.net/post/419/38713 <http://xsb.itpub.net/post/419/38713> statspack 必看的十项内容、负载间档(Load profile)2、实例效率点击率(Instance efficiency hit ratios)3、首要的5个等待事件(Top 5 wait events)4、等待事件(Wait events)5、闩锁等待6、首要的SQL(Top sql)7、实例活动(Instance activity)8、文件I/O(File I/O)9、内存分配(Memory allocation)10、缓冲区等待(Buffer waits) 二、输出结果解释1、报

Notes on Data Guard / Physical Standby in 10g Primary orcl10g is running. About to create orcl10gsb as standby on the same Windows machine. Version is 10.1.0.2.0. Refer to "Creating a Physical Standby Database" at Ora10gDoc/server.101/b10823/create_ps.htm or http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10823/create_ps.htm <http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10823/create_ps.htm> unless otherwise indicated Single word database init para

作者:biti_rainy (出处 <http://blog.csdn.net/biti_rainy/archive/2004/07/06/35188.aspx> )热点块的定义数据库的热点块，从简单了讲，就是极短的时间内对少量数据块进行了过于频繁的访问。定义看起来总是很简单的，但实际在数据库中，我们要去观察或者确定热点块的问题，却不是那么简单了。要深刻地理解数据库是怎么通过一些数据特征来表示热点块的，我们需要了解一些数据库在这方面处理机制的特性。数据缓冲区的结构我们都知道，当查询开始的时候，进程首先去数据缓冲区中查找是否存在查询所需要的数据块，如果没有，就去磁盘上把

让我们看看老版本的oracle 是怎么执行动态SQL的create or replace procedure scheam1.trunc*** as source_cursor integer; rows_processed integer; native constant integer := 1; begin source_cursor := dbms_sql.open_cursor; dbms_sql.parse(source_cursor, 'truncate table ****** reuse storage', native); rows_processed := dbms_sql.execute(source_cursor); commit; dbms_sql.close_cursor(source_cursor);exception when others then

8Subject: ORA-12518 Reported When Using 10gr2 TNS Listener to Connect To a 8i Database Doc ID: Note:341482.1 Type: PROBLEM Last Revision Date: 11-JAN-2006 Status: PUBLISHED In this Document Symptoms Cause Solution _____ Applies to: Oracle Net Services - Version: 10.2.0.1.0Microsoft WindowsAll Dedicated Oracle Net connections to the database SymptomsWhen using a 10.2 TNS listener to connection to an 8i database, via dedicated connection model, the following error is reported :OR

转(需要引用, 请注明出处: http://ningoo.itpub.net) flaco ask me a question of v$logfile type, made me go through standby db admin issues, get from a network friend...本文只涉及Oracle9iR2 Data Guard的physical standby 一、三种保护模式最大性能(maximize performance):这是data guard默认的保护模式。primay上的事务commit前不需要从standby上收到反⌒畔ⅰ８媚Ｊ皆趐rimary故障时可能丢失数据，但standby对primary的性能影响最小。最大可用(maximize availability):在正常情况下，最大可用模式和最大保护模式一样；在standby

How High Can You Go? by Jonathan LewisIntroductionSomeone on the Oracle-L mailing list recently asked, “Does Oracle impose any limit on the height of a b-tree index?” A member of the list, who had been on one of the official advanced internals courses, said that his notes quoted a limit of 24. But that’s not an obvious limit, so how do you find out whether it’s right or not?If you’ve got about 50GB of free disk space, Oracle 9.2 or better, and around 10 hours of run time, then this article

Locally Managed Tablespaces Unless you have been living on another planet (one that does not run Oracle!) you will probably have heard of Locally Managed Tablespaces. For those of you that are not familiar with the term, here is a quick recap on the way tablespaces have evolved in Oracle.Tablespaces are the mechanism by which segments (tables, indexes, etc.) consume space in the database, and they are the logical representation of one or more datafiles on disk. Obviously as segments are created,