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子系统中，通过主动服务

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

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

转(需要引用, 请注明出处: 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,

All relational databases require unique keys to identify primary table rows.A key is required whenever we need to join tables together with SQL. In most cases, the key will be a unique value (e.g. social security number), but we may sometimes see non-unique key values. Sometimes the table will not have a unique value, and your table will have a sequence to uniquely identify each table row.We need these matching keys to use SQL to join tables, and we do this inside our SQL WHERE clause. However

转利用DBMS_METADATA包获取权限信息 =========================================================== 作者: yangtingkun(http://yangtingkun.itpub.net)发表于: 2007.02.10 23:26分类: ORACLE 出处: http://yangtingkun.itpub.net/post/468/262772--------------------------------------------------------------- Oracle的9i增加了DBMS_METADATA包，从此在需要得到对象的创建脚本时，不再需要通过查询多张系统视图去自己拼凑结果了。只需要调用这个包中的GET_DDL过程就可以轻松的获取对象的创建脚步。不过很多人对DBMS_METADATA包的了解仅

http://asktom.oracle.com/pls/asktom/f?p=100:11:974743640809258::::P11_QUESTION_ID:442029737684http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:953229842074Tom, I am presently working on a Data Warehouse project which is using Informatica for mapping and loading data into an Oracle database (8.1.7 on HP UX 11.0). The data is in flat files and some of the hundreds of tables in the Warehouse are HUGE (300-500 million records). The designers have not partioned the base

Another possible idea : the following script was adapted from the original idea & research from Steve Adams (www.ixora.com.au), I tested in Personal 8ir3 and it worked as desired :step a) in a quiet database, run the script :sys@PO8IR3:SQL>@v8_running_sqls**** Currently Executing Packages ****no rows selectedsys@PO8IR3:SQL>get v8_running_sqls1 prompt **** Currently Executing Packages ****2 SELECT3 substr(DECODE(o.kglobtyp,4 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 12, 'TRIGGER', 13

---------------- showsql.sql --------------------------column status format a10set feedback offset serveroutput onselect username, sid, serial#, process, statusfrom v$sessionwhere username is not null/column username format a20column sql_text format a55 word_wrappedset serveroutput on size 1000000declarex number;beginfor x in( select username||'('||sid||','||serial#||') ospid = ' || process ||' program = ' || program username,to_char(LOGON_TIME,' Day HH24:MI') logon_time,to_char(sysdate,' Day HH