ITPub博客

在CBO下，oracle会把不含通配符的like表达式转为为等式，前提是like前面的字段类型必须是可变宽度字符类型的。当遇到char类型的字段时它不会这样做！[@more@]doc描述：The optimizer simplifies conditions that use the LIKE comparison operator to compare an expression with no wildcard characters into an equivalent condition that uses an equality operator instead.In the following example, the optimizer simplifies the first condition into the second:last_name LIKE 'SMITH' is transformed intolast_name = 'SMIT

可以通过dbms_stats提供的set_table_stats来修改表的统计信息从而使cbo产生我们希望的执行计划！[@more@]SQL> edit已写入 file afiedt.buf 1 DECLARE 2 num_rows NUMBER; 3 num_blocks NUMBER; 4 avg_row_len NUMBER; 5 BEGIN 6 -- retrieve the values of table statistics on OE.ORDERS 7 -- statistics table name: OE.SAVESTATS statistics ID: TEST1 8 DBMS_STATS.GET_TABLE_STATS('XYS','TT',null, 9 'MY_STAT_TAB','TEST_20080416', 10 num_rows,num_blocks,avg_row_len); 11 --

通过dbms_stats提供的几个procedure可以监视stale statistics的对象以便之后对这些对象搜集statistics。[@more@]SQL> exec dbms_stats.alter_schema_tab_monitoring('xys');PL/SQL 过程已成功完成。SQL> select * from tt;ID N---------- -1 a2 A3 B4 b5 cSQL> update tt set name='m';已更新5行。SQL> commit;提交完成。--user_tab_modifications中没有记录，oracle flush sga到user_tab_modifications可能需要一段时间，不过我们可以通过DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO来手动flushSQL> select * from user_t

利用dbms_stats的procedure gather_schema_stats 和gather_database_stats通过使用参数options 和objlist可以列出那些没有统计信息或者统计信息stale的对象。[@more@]其中options的值可以是：ValueMeaningGATHER STALEGathers statistics on tables with stale statistics.GATHERGathers statistics on all tables. (default)GATHER EMPTYGathers statistics only on tables without statistics.LIST STALECreates a list of tables with stale statistics.LIST EMPTYCreates a list of tables that do not have statistics.GATHER AUTOGa

在创建函数index之后系统会自动产生虚拟列，而这一列也是隐藏的，在创建index时系统自动使用了compute statistics选项为创建的index搜集了statistics，然而并不会为生成的虚拟列搜集statistics，这时如果需要为虚拟列搜集statistics就需要重新分析表或者单独为虚拟列搜集statistics。[@more@]SQL> create table tt(id int , name char(1));表已创建。SQL> insert into tt values(1 , 'a');已创建 1 行。SQL> insert into tt values(2 , 'A');已创建 1 行。SQL> insert into tt values(3 , 'B');已创建 1 行。SQL> insert i

dbms_stats package中提供了过程export_X_stats和import_X_stats（其中X代表column,table,index,schema,database）可以实现统计信息的export和import。通过这种方法我们可以把生产环境的统计信息导入到测试库中模拟生产库。[@more@]SQL> exec dbms_stats.create_stat_table('XYS','MY_STAT_TAB');PL/SQL 过程已成功完成。SQL> DESC MY_STAT_TAB; 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- STATID

doc上的原话，随手记录一下。 [@more@]Oracle Corporation strongly recommends that you use the DBMS_STATS package rather than ANALYZE to collect optimizer statistics. That package lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. Further, the cost-based optimizer will eventually use only statistics that have been collected by DBMS_STATS. See Oracle9i Supplied PL/SQL Packages and Types Reference for mor

对象被分析之后，所有和该对象相关的被parse过的sql都会失效，下次需要执行相同的sql时需要再次parse，因此当系统执行过搜集statistics的操作后面临的可能是在短时间内产生很多hard parse。 其实非常容易理解，因为有了新的statistics，oracle在下次执行相同的sql时会使用新的statistics产生新的执行计划。[@more@]When you generate statistics for a table, column, or index, if the data dictionary already contains statistics for the object, then Oracle updates the existing statistics. Oracle also invalidates any currentl

Use the DBMS_STATS package to generate statistics.Statistics generated include the following:[@more@]Use the DBMS_STATS package to generate statistics.Statistics generated include the following:Table statistics Number of rows Number of blocks Average row length Column statistics Number of distinct values (NDV) in column Number of nulls in column Data distribution (histogram) Index statistics Number of leaf blocks Levels Clustering factor System statistics I/O performance and utilization CPU perf

user_tab_cols differs from "USER_TAB_COLUMNS" in that hidden columns are not filtered out。10.2版本中user_tab_cols比user_tab_columns多出下面几列：HIDDEN_COLUMN VARCHAR2(3)VIRTUAL_COLUMN VARCHAR2(3)SEGMENT_COLUMN_ID NUMBERINTERNAL_COLUMN_ID NOT NULL NUMBERQUALIFIED_COL_NAME VARCHAR2(4000)[@more@]简单的测试过程如下：SQL> desc tt名称 是否为空? 类型----------------------------------------- -------- ----------------------------ID NOT NULL NUMBER(38)NAME VARCHAR2(10)AGE NUMBER(38)COL_HIDD

index和index之间是如何进行hash join的，因为index中包括了rowid，所以通过rowid可以连接。Index JoinsAn index join is a hash join of several indexes that together contain all the table columns that are referenced in the query. If an index join is used, then no table access is needed, because all the relevant column values can be retrieved from the indexes. An index join cannot be used to eliminate a sort operation. The index join is available only with the CBO.Index Join HintsYou can specify an inde

其实Fast Full Index Scans是针对full table scan而言的,Fast Full Index Scans的一些特点也和full table scan相似(如：多块读)：Fast Full Index Scans的一些特点：1.Fast full scan is available only with the CBO2.at least one column in the index key has the NOT NULL constraint3. A fast full scan accesses the data in the index itself, without accessing the table4.It cannot be used to eliminate a sort operation, because the data is not ordered by the index key.（粗略的试验了一下发现也是排好序了，按照index_k

唯一index和非唯一index的index entry中尽管都存在rowid，但是其实有一点细微的差别，在唯一index中index_column就是index_column, 数据通过index_column排序；而在非唯一index中index_column其实是index_key+rowid，如果index_key相同，那么会使用rowid排序。[@more@]Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.--=======================================================SQL> select /*+ index(a idx_tt) */ a.*,rowid from tt a wh

理解oracle lock的概念，有助于理解多用户的并发执行。[@more@]测试过程1：--===========================================================================1.测试表t是一个存在200w左右数据的大表，数据完全来自dba_objectssession1:22:52:28 SQL> select count(object_id) from t;COUNT(OBJECT_ID)---------------- 2019072已用时间: 00: 00: 01.2022:21:46 SQL> create index idx_t on t(object_id);执行上面create index语句，保证能持续一段时间，以便在session2,3,4中进行其他操作session:检测在t上都加了什么类型的

记得在北京讲课时曾经有学员问到过这个问题，当时没有详细测试，前几天看到pub上又有人提及这类问题，简单的测试了一下，还发现了一些问题。传递出去的对象权限可以cascade回收，而传递出去的系统权限却没有cascade回收![@more@]1.对象权限传递测试C:>sqlplus xys/managerSQL*Plus: Release 11.1.0.6.0 - Production on 星期一 4月 7 22:55:01 2008Copyright (c) 1982, 2007, Oracle. All rights reserved.连接到:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining a

什么是 active session ![@more@]An active session is defined as a session that is currently inside a transaction, a query, a fetch or a PL/SQL procedure, or a session that is currently holding any shared resources (for example, enqueues--enqueues are shared memory structures that serialize access to database resources and are associated with a session or transaction)

不使用绑定变量，而open_cursors又开的很大，很容易导致ORA-04031错误![@more@]SQL> connect xys/manager已连接。SQL> desc t名称 是否为空? 类型----------------------------------------- -------- ----------------------------OBJECT_ID NUMBEROBJECT_NAME VARCHAR2(128)SQL> select count(*) from t;COUNT(*)----------11457SQL> show parameter sgaNAME TYPE VALUE------------------------------------ ----------- ------------------------------lock_sga boolean FALSEpre_page_sga boolean FALSEsga_max_size big

在oltp系统中提倡使用绑定变量，使用绑定变量可以减少hard parse，避免因解析sql而过渡消耗cpu时间以及引起latch争用等一系列问题。那么到底如何使用绑定变量？可能是困扰很多人的一个问题，下面列举了一些使用绑定变量的例子或者是场合[@more@]1.sqlplus中如何使用绑定变量，可以通过variable来定义SQL> select * from tt where id=1;ID NAME---------- ----------------------------------------1 testSQL> select * from tt where id=2;ID NAME---------- ----------------------------------------2 testSQL> variable i num

如何获得server端和client端ip address和host name！[@more@]--可以通过系统提供的utl_inaddr包中的2个函数获得数据库服务器的主机名和ip地址SQL> set serveroutput onSQL> exec dbms_output.put_line(utl_inaddr.get_host_name);XYSPL/SQL 过程已成功完成。SQL> exec dbms_output.put_line(utl_inaddr.get_host_address);192.168.0.252PL/SQL 过程已成功完成。----可以通过系统提供的函数获得client端主机名和ip地址，获得client端ip地址时需要注意连接数据库时必须通过tns连接才能获得ip地址，否则不能获得SQL> select sys_

看到pub上有人问如何远程调用过程，其实大部分人都会想到通过db link事实上也确实如此，不过我相信实际在使用的时候可能没有多少人这样用而是通常会使用另外的办法[@more@]测试过程：1.首先连上库db11查看并测试了事先创建好的proc_testC:>sqlplus xys/managerSQL*Plus: Release 11.1.0.6.0 - Production on 星期三 4月 2 14:06:59 2008Copyright (c) 1982, 2007, Oracle. All rights reserved.连接到:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Appl