ITPub博客

那么究竟什么是DETERMINISTIC Functions，看看doc的解释！DETERMINISTIC FunctionsAny user-written function used in a function-based index must have been declared with the DETERMINISTIC keyword to indicate that the function will always return the same output return value for any given set of input argument values, now and in the future.[@more@]SQL> desc tt 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- ID

从10g开始CBO下group by之后不能保证数据是按照分组字段排序的！[@more@]大致步骤：SQL> select id , avg(age) from tt1 group by id; ID AVG(AGE)---------- ---------- 6 0 4 0 3 0 0 0 10 1执行计划----------------------------------------------------------Plan hash value: 112617873---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (

在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

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

不使用绑定变量，而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

我一直都把oracle中的temp tbs理解成win下的虚拟内存和unix下的swap分区。temp使用不当会影响oracle数据库的性能。1、临时表空间的用途1）disk sort在了解disk sort排序之前，先来看一段doc（When the WORKAREA_SIZE...

有图片，所以这里没有放内容，查看的话，大家可以直接看看下面的连接！http://www.itpub.net/showthread.php?s=&postid=8782314#post8782314