ITPub博客

Oracle8i Database introduced the AUTHID clause for procedures, functions and packages. When set to AUTHID DEFINER (the default), then your program runs under "definer rights." This means that any references to data objects (such as tables and views) are resolved at compile time, based on the directly granted privileges of the definer or owner of the program. Roles are ignored. If, on the other hand, you set the clause to AUTHID CURRENT_USER, then any references to data objects are reso

这篇文章中有个例子说明了query_rewrite_integrity参数设置为不同值时的区别。 《Expert one on one Oracle》- 物化视图 - 笔记 作者: Dreaming Bug链接: http://gooply.blogspot.com/2007/08/expert-one-on-one-oracle.html物化视图概念类似于discoverer中的summary table。在discoverer的管理端，可以创建不同的summary table。在discoverer进行查询的时候，discoverer首先对查询进行解析，判断查询是否可以使用对应的summary table，如果可以，将会改写查询去查询对应的summary table。一个例子通过如下的例子，对比统计数据需求的情况

为了方便查阅，记录在此zz from 王朝网络链接: http://www.wangchao.net.cn/bbsdetail_147873.html很多时候，对数据库进行性能诊断可以使用SQL跟踪的方法，把一些信息记录在trace文件里以后分析。一般情况下我们可以通过初始化参数SQL_TRACE=TRUE来设置SQL跟踪。　　　　我们也可以通过设置10046事件来进行SQL跟踪，并且可以设置不同的跟踪级别，比使用SQL_TRACE获得更多的信息。　　　　Level 0 停用SQL跟踪，相当于SQL_TRACE=FALSE　　Level 1 标准SQL跟踪，相当于SQL_TRACE=TRUE　　Level 4 在level 1的基础上增加绑定变量的信息　　Le

Tracing SQL in Oracle Database 10g By Kimberly Floss New tools help you better understand the performance of your applications. In a busy production environment with many active users, tracing a SQL session is time-consuming and complicated, because processing SQL statements in any multitier system that uses a connection pool can span multiple processes, or even different instances. With Oracle Database 10g, Oracle rationalizes SQL tracing through a new built-in package, DBMS_MONITOR, which enco

1. DBMS_STATS gathers only statistics needed for optimization; it does not gather other statistics. For example, the table statistics gathered by DBMS_STATS include the number of rows, number of blocks currently containing data, and average row length, but not the number of chained rows, average free space, or number of unused data blocks.2. Oracle Database automatically collects index statistics whenever an index is created or rebuilt. Exceptions are domain indexes, or indexes created while the

学习Sort Operations的时候希望能看到一些更详细的内容，去google了一把，于是又找到了wanghai的这篇文章，摘录在此。最近摘录了很多别人的文章，但是在阅读这些文章的过程中感觉到自己学到了很多东西，每天都在进步。虽然求职的道路很漫长也很艰难，但是我相信只要我持续地努力，不放下技术，总有成功的一天。oracle sort 系列一作者：wanghai链接：http://wzwanghai.spaces.live.com/blog/cns!56626E237AFBD116!156.entry相信做oracle开发和管理的朋友对sort肯定不会陌生，大家通常都遇到这样那样的排序性能问题，所以我写这一系列关于s

面试一家做数据仓库应用的公司时被问到什么是星型连接，之前只是听过这个名词，知道是数据仓库里用到的，但根本不晓得是个什么东东。今天在网上找到两篇相关文章看了一下，虽然仍然没有理解地十分透彻，但大概是个什么样子的已经有点概念了。记录在此。数据库优化中什么是星型转换(Star Transform)?作者: AnySQL.net链接: http://www.anysql.net/oracle/olap_tuning_startransformation.html在数据仓库中经常查询的SQL总带有下列特征: 几个表进行关联 只有一个数据量巨大的表, 称为事实表 其他的都是编码表, 称为维表 维表和事实表之间有主

前几天面试时的一个问题，除了内存相关参数外，还有哪些参数和性能相关。当时只说出了第二个。今天看SQL TUNING时刚好看到相关内容，因此在这里列出一些。虽然这些参数一般取默认值即可，不过还是有必要了解一下。CURSOR_SHARINGCURSOR_SHARING determines what kind of SQL statements can share the same cursors. The default value is EXACT.Values:FORCEForces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.SIMILARCaus

(The descirption is from Oracle Document: Performance Tuning)Column statistics may be stored as histograms. These histograms provide accurate estimates of the distribution of column data. Histograms provide improved selectivity estimates in the presence of data skew, resulting in optimal execution plans with nonuniform data distributions.Oracle uses two types of histograms for column statistics: height-balanced histograms and frequency histograms. The type of histogram is stored in the HISTOGRAM

今天学习了三种利用索引关联来访问数据的方法，记录在此。and_equal,index_join,index_combine这三种都是oracle利用索引关联获得数据的方法,三者的目的都是为了最大限度的利用索引,减少回表的代价.但是三者的实现方法是有区别的,下面一一来分析.and_equal:这种方式需要查询条件里面包括所有索引列,然后取得每个索引中得到的rowid列表,然后对这些列表做merge join,过滤出相同的rowid后再去表中获取数据或者直接从索引中获得数据.and_equal有一些限制,比如它只对单列索引有效,只对非唯一索引有效,使用到的索引不能超过5个,查询条件只能是”=

zz from 蓝精灵oracle中in与exists的效率比较效率比较：select * from t1 where x in ( select y from t2 )事实上可以理解为：select * from t1, ( select distinct y from t2 ) t2 where t1.x = t2.y如果你有一定的SQL优化经验，从这句很自然的可以想到t2绝对不能是个大表，因为需要对t2进行全表的“唯一排序”，如果t2很大这个排序的性能是不可忍受的。但是t1可以很大，为什么呢？最通俗的理解就是因为t1.x=t2.y可以走索引。但这并不是一个很好的解释。试想，如果t1.x和t2.y都有索引，我们知道索引是种有序的结构，因此t1和t2之间最佳的

http://www.cnoug.org/viewthread.php?tid=21969贴主： wanghaicheckpoint小议什么是checkpoint?checkpoint是一个数据库事件，它将已修改的数据从高速缓存刷新到磁盘，并更新控制文件和数据文件。什么时候发生checkpoint?我们知道了checkpoint会刷新脏数据，但什么时候会发生checkpoint呢？以下几种情况会触发checkpoint。1.当发生日志组切换的时候2.当符合LOG_CHECKPOINT_TIMEOUT，LOG_CHECKPOINT_INTERVAL，fast_start_io_target,fast_start_mttr_target参数设置的时候3.当运行ALTER SYSTEM SWITCH LOGFILE的时候4.当运行ALTER SYSTEM C

I used to consider that an unusable index may cause DML changes fail. In fact, only the index related to a constraint may lead to such situation.SQL> create table move_test(cola number);表已创建。SQL> begin2 for i in 1..10 loop3 insert into move_test values(i);4 end loop;5 commit;6 end;7 /PL/SQL 过程已成功完成。SQL> select * rom move_test;select * rom move_test*第 1 行出现错误:ORA-00923: 未找到要求的 FROM 关键字SQL> select * from move_test;COLA----------12345678910已选择10行。SQL>

To perform online backup we should put the tablespace in begin backup mode, copy the datafiles and then put the tablespace to end backup.Since the size of a db block is usually of 8k bytes while the size of a os block is usually of 512 bytes, you can't guarantee that all the os blocks within a db block are at the same SCN during copying. The result is called a fractured block. The 'alter tablespace ... begin backup' has two effects:1. The first time a block is changed in a datafile that is in ho

9.Oracle Clusterware AdministrationOracle Clusterware: Overview• Portable cluster infrastructure that provides HA to RAC databases and/or other applications:– Monitors applications’ health - 监控应用运行情况– Restarts applications on failure - 重启失败的应用– Can fail over applications on node failure - 将应用failover到其他节点Oracle Clusterware Run-Time View在Unix平台上，clusterware stack的启动是根据/etc/inittab文件中定义的respawn条目A basic description of each process:• Cluster

首先检查一下ocr.loc文件中的配置[root@redhat1 clusterware]#cat /etc/oracle/ocr.lococrconfig_loc=/ocfs/clusterware/ocrlocal_only=false按照文档中所说的命令添加ocrmirror却报PROT-21错[root@redhat1 clusterware]# ocrconfig -replace ocrmirror /ocfs/clusterware/ocrmirrorPROT-21: Invalid parameter上网查了一下其他人的实验，发现首先需要创建文件[root@redhat1 clusterware]# touch /ocfs/clusterware/ocrmirror[root@redhat1 clusterware]# ls -ltotal 46239-rw-r----- 1 root oinstall 5963776 Feb 9 16:27 ocr-rw-r--r--

7. ServicesTraditional Workload Dispatching传统模式下，每个应用被永久性地分配到一部分相对独立的资源，这些分配到的资源必须分别满足每个应用的峰值需求。由于应用的峰值时段一般只持续几小时，因此在绝大多数时间中，大量资源处于浪费状态。 Grid Workload Dispatching在网格计算环境下，由一个全局的资源池同时对多个应用提供使用，在不同的应用间计算单元可被动态地分配和调整。 What Is a Service?• Is a means of grouping sessions that are doing the same kind of work可将完成同一种工作的会话分组• Provides

6. RAC Performance Tuning CPU and Wait Time Tuning Dimensions一般情况下，等待事件显著的话，比较可能需要instance/RAC tuning。这时增加CPU或节点很难改善问题。CPU使用率较高的话，有可能需要进行SQL tuning。这种情况下增加CPU或节点数一般可以得到较好的效果。（注意scale-up和speed-up的区别）RAC-Specific Tuning• Tune for a single instance first.• Tune for RAC:– Instance recovery– Interconnect traffic– Point of serialization can be exacerbated• RAC-reactive tuning tools:– Specific wait

4. RAC Database AdministrationUse Enterprise Manager cluster database pagesRedo Log Files and RAC每个实例必须拥有至少两组redo。使用alter database add logfile thread…命令来指定为哪个实例增加redo fileAutomatic Undo Management and RAC为每个实例指定一个专用的undo tablespaceRAC01.UNDO_TABLESPACE=undotbs3RAC02.UNDO_TABLESPACE=undotbs2Starting and Stopping RAC Instances• RAC instances can be started and stopped by using:– Enterprise Manager– Server Control (SRVCTL) utility– SQL*PlusStarting a

0. IntroductionWhat Is a Cluster?Interconnected nodes act as a single serverCluster software hides the structureDisks are available for read and write by all nodes.Operating system is the same on each machineWhat Is Oracle Real Application Clusters?Multiple instances accessing the same databaseOne Instance per nodePhysical or logical access to each database fileSoftware-controlled data accessWhy Use RACHigh availabilityScalabilityPay as you growKey grid computing featuresLevels of ScalabilityHar