[20131019]12c Global Temporary table 统计信息的收集.txt

[20131019]12c Global Temporary table 统计信息的收集.txt

摘要:twp-optimizer-with-oracledb-12c-1963236.pdf

Optimizer with Oracle Database 12c

Session level statistics on Global Temporary Tables P22

Global temporary tables are often used to store intermediate results in an application context. A global
temporary table shares (GTT) its definition system-wide with all users with the appropriate privileges,
but the data content is always session-private. It has always been possible to gather statistics on a global
temporary table (that persist rows on commit); however in previous releases the statistics gathered
would be used by all sessions accessing that table. This was less than ideal if the volume or nature of
the data stored in the GTT differed greatly between sessions.

It is now possible to have a separate set of statistics for every session using a GTT.  Statistics sharing
on a GTT is controlled using a new DBMS_STATS preference GLOBAL_TEMP_TABLE_STATS. By default
the preference is set to SESSION, meaning each session accessing the GTT will have it's own set of
statistics. The optimizer will try to use session statistics first but if session statistics do not exist, then
optimizer will use shared statistics. If you want to revert back to the prior behavior. of only one set of
statistics used by all session, set the GLOBAL_TEMP_TABLE_STATS preference to SHARED.

1.测试环境： 

SCOTT@test01p> @ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
SCOTT@test01p> SELECT DBMS_STATS.get_param ('GLOBAL_TEMP_TABLE_STATS') GLOBAL_TEMP_TABLE_STATS  FROM DUAL;
GLOBAL_TEMP_TABLE_STATS
-----------------------
SESSION

--缺省参数设置为SESSION。

2.GLOBAL_TEMP_TABLE_STATS=session

create global temporary table GTT_T1 (d number, name varchar2(20)) on commit DELETE rows;
insert into GTT_T1 select rownum id,'test' name from dual connect by level 
SCOTT@test01p> select dbms_stats.get_prefs('GLOBAL_TEMP_TABLE_STATS',user,'GTT_T1') c20 from dual;
C20
--------------------

SESSION
--参数缺省是session。

SCOTT@test01p> select count(*) from gtt_t1 ;
  COUNT(*)
----------
       100
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
SQL_ID  1b40b6ymz94px, child number 0
-------------------------------------
select count(*) from gtt_t1
Plan hash value: 3957256632
-----------------------------------------------------------
| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |        |        |     2 (100)|
|   1 |  SORT AGGREGATE    |        |      1 |            |
|   2 |   TABLE ACCESS FULL| GTT_T1 |      1 |     2   (0)|
-----------------------------------------------------------
SCOTT@test01p> execute dbms_stats.gather_table_stats(user,'GTT_T1');
PL/SQL procedure successfully completed.
SCOTT@test01p> select count(*) from gtt_t1 ;
  COUNT(*)
----------
       100
--难道这里没有隐含commit吗？-难道分析临时表不是隐含commit吗？查询还有结果，为什么？
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
SQL_ID  1b40b6ymz94px, child number 0
-------------------------------------
select count(*) from gtt_t1
Plan hash value: 3957256632
-----------------------------------------------------------
| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |        |        |     2 (100)|
|   1 |  SORT AGGREGATE    |        |      1 |            |
|   2 |   TABLE ACCESS FULL| GTT_T1 |    100 |     2   (0)|
-----------------------------------------------------------
--分析后统计信息正确！
SCOTT@test01p> select num_rows,last_analyzed from user_tables where table_name = 'GTT_T1';
  NUM_ROWS LAST_ANALYZED
---------- -------------------
--没有信息，说明前面的分析根本没有执行。
create global temporary table GTT_T2 (d number, name varchar2(20)) on commit PRESERVE rows;
insert into GTT_T2 select rownum id,'test' name from dual connect by level 


SCOTT@test01p> select count(*) from gtt_T2 ;


  COUNT(*)


----------


       200


SCOTT@test01p> @dpc '' ''


PLAN_TABLE_OUTPUT


-------------------------------------------------------------


SQL_ID  gxbfux75kpj3d, child number 0


-------------------------------------


select count(*) from gtt_T2


Plan hash value: 1338463290


-----------------------------------------------------------


| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|


-----------------------------------------------------------


|   0 | SELECT STATEMENT   |        |        |     2 (100)|


|   1 |  SORT AGGREGATE    |        |      1 |            |


|   2 |   TABLE ACCESS FULL| GTT_T2 |    200 |     2   (0)|


-----------------------------------------------------------


Note


-----


   - dynamic statistics used: dynamic sampling (level=2)


SCOTT@test01p> execute dbms_stats.gather_table_stats(user,'GTT_T2');


PL/SQL procedure successfully completed.


SCOTT@test01p> select count(*) from gtt_T2 ;


  COUNT(*)


----------


       200


SCOTT@test01p> @dpc '' ''


PLAN_TABLE_OUTPUT


---------------------------------------------------------------------


SQL_ID  gxbfux75kpj3d, child number 0


-------------------------------------


select count(*) from gtt_T2


Plan hash value: 1338463290


-----------------------------------------------------------


| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|


-----------------------------------------------------------


|   0 | SELECT STATEMENT   |        |        |     2 (100)|


|   1 |  SORT AGGREGATE    |        |      1 |            |


|   2 |   TABLE ACCESS FULL| GTT_T2 |    200 |     2   (0)|


-----------------------------------------------------------


--注意上下执行计划的区别是前者是使用dynamic sampling。后者没有


SCOTT@test01p> select num_rows,last_analyzed from user_tables where table_name = 'GTT_T2';


  NUM_ROWS LAST_ANALYZED


---------- -------------------




--奇怪统计信息放那里呢？真不懂12c如何做到的？难道仅仅在会话的某个内存中吗？


2.GLOBAL_TEMP_TABLE_STATS=share


exec dbms_stats.set_table_prefs(user,'GTT_T1','GLOBAL_TEMP_TABLE_STATS','SHARED') ;


exec dbms_stats.set_table_prefs(user,'GTT_T2','GLOBAL_TEMP_TABLE_STATS','SHARED') ;


SCOTT@test01p> select dbms_stats.get_prefs('GLOBAL_TEMP_TABLE_STATS',user,'GTT_T1') c20 from dual;


C20


--------------------


SHARED


退出进入重复测试：


insert into GTT_T1  select rownum id,'test' name from dual connect by level 


SCOTT@test01p> select count(*) from gtt_T1 ;


  COUNT(*)


----------


       100


SCOTT@test01p> @dpc '' ''


PLAN_TABLE_OUTPUT


------------------------------------------------------------


SQL_ID  2bar3u3cr7t8r, child number 0


-------------------------------------


select count(*) from gtt_T1


Plan hash value: 3957256632


-----------------------------------------------------------


| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|


-----------------------------------------------------------


|   0 | SELECT STATEMENT   |        |        |     2 (100)|


|   1 |  SORT AGGREGATE    |        |      1 |            |


|   2 |   TABLE ACCESS FULL| GTT_T1 |    100 |     2   (0)|


-----------------------------------------------------------


Note


-----


   - dynamic statistics used: dynamic sampling (level=2)


SCOTT@test01p> execute dbms_stats.gather_table_stats(user,'GTT_T1');


PL/SQL procedure successfully completed.


SCOTT@test01p> select count(*) from gtt_T1 ;


  COUNT(*)


----------


         0


--这回结果是0，说明分析临时表完成后，隐含commit命令，导致临时表的信息清除。注gtt_t1: on commit DELETE rows.


SCOTT@test01p> select num_rows,last_analyzed from user_tables where table_name = 'GTT_T1';


  NUM_ROWS LAST_ANALYZED


---------- -------------------


         0 2013-10-18 22:18:28


--从这里也可以看出，有统计信息了。看来具有on commit DELETE rows特性的临时表的统计信息要伪造才可以。




SCOTT@test01p> insert into GTT_T2  select rownum id,'test' name from dual connect by level 


200 rows created.


SCOTT@test01p> select count(*) from gtt_T2 ;


  COUNT(*)


----------


       200


SCOTT@test01p> @dpc '' ''


PLAN_TABLE_OUTPUT


-------------------------------------


SQL_ID  gxbfux75kpj3d, child number 0


-------------------------------------


select count(*) from gtt_T2


Plan hash value: 1338463290


-----------------------------------------------------------


| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|


-----------------------------------------------------------


|   0 | SELECT STATEMENT   |        |        |     2 (100)|


|   1 |  SORT AGGREGATE    |        |      1 |            |


|   2 |   TABLE ACCESS FULL| GTT_T2 |    200 |     2   (0)|


-----------------------------------------------------------


Note


-----


   - dynamic statistics used: dynamic sampling (level=2)


SCOTT@test01p> execute dbms_stats.gather_table_stats(user,'GTT_T2');


PL/SQL procedure successfully completed.


SCOTT@test01p> select num_rows,last_analyzed from user_tables where table_name = 'GTT_T2';


  NUM_ROWS LAST_ANALYZED


---------- -------------------


       200 2013-10-18 22:24:16


SCOTT@test01p> select count(*) from gtt_T2 ;


  COUNT(*)


----------


       200


SCOTT@test01p> @dpc '' ''


PLAN_TABLE_OUTPUT


-------------------------------------


SQL_ID  gxbfux75kpj3d, child number 0


-------------------------------------


select count(*) from gtt_T2


Plan hash value: 1338463290


-----------------------------------------------------------


| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|


-----------------------------------------------------------


|   0 | SELECT STATEMENT   |        |        |     2 (100)|


|   1 |  SORT AGGREGATE    |        |      1 |            |


|   2 |   TABLE ACCESS FULL| GTT_T2 |    200 |     2   (0)|


-----------------------------------------------------------


Note


-----


   - dynamic statistics used: dynamic sampling (level=2)


--奇怪执行计划依旧使用动态取样。


--修改1个字符看看？select => Select .


SCOTT@test01p> Select count(*) from gtt_T2 ;


  COUNT(*)


----------


       200


SCOTT@test01p> @dpc '' ''


PLAN_TABLE_OUTPUT


----------------------------------------------------------------


SQL_ID  7xj17ts3qm5mv, child number 0


-------------------------------------


Select count(*) from gtt_T2


Plan hash value: 1338463290


-----------------------------------------------------------


| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|


-----------------------------------------------------------


|   0 | SELECT STATEMENT   |        |        |     2 (100)|


|   1 |  SORT AGGREGATE    |        |      1 |            |


|   2 |   TABLE ACCESS FULL| GTT_T2 |    200 |     2   (0)|


-----------------------------------------------------------


--退出再进入：


SCOTT@test01p> select num_rows,last_analyzed from user_tables where table_name = 'GTT_T2';


  NUM_ROWS LAST_ANALYZED


---------- -------------------


       200 2013-10-18 22:24:16


--统计信息已经在数据库里面。退出再进入：


SCOTT@test01p> SElect count(*) from gtt_T2 ;


  COUNT(*)


----------


         0


SCOTT@test01p> @dpc '' ''


PLAN_TABLE_OUTPUT


--------------------------------------------------------------


SQL_ID  03vac2xzj0uyr, child number 0


-------------------------------------


SElect count(*) from gtt_T2


Plan hash value: 1338463290


-----------------------------------------------------------


| Id  | Operation          | Name   | E-Rows | Cost (%CPU)|


-----------------------------------------------------------


|   0 | SELECT STATEMENT   |        |        |     2 (100)|


|   1 |  SORT AGGREGATE    |        |      1 |            |


|   2 |   TABLE ACCESS FULL| GTT_T2 |    200 |     2   (0)|


-----------------------------------------------------------


--虽然记数=0,但是执行计划的信息保持还是E-Rows=200.
				


				
时间： 2024-07-31 13:16:34

		
		


		


	

	
	

		


		
[20131019]12c Global Temporary table 统计信息的收集.txt的相关文章


								

		

			

                Oracle 12c数据库优化器统计信息收集的最佳实践
			

		

		

									

				Oracle 12c数据库优化器统计信息收集的最佳实践 转载自     沃趣科技(ID:woqutech)  作者         刘金龙(译) 原文链接   http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf 导  语 Oracle优化器会为SQL语句产生所有可能的访问路径(执行计划),然后从中选择一条COST值最低的执行路径,这个cost值是			

		

	

				

		

			

                全局臨時表 GLOBAL TEMPORARY TABLE
			

		

		

									

				1.会话特有的临时表     CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification>)     ON COMMIT PRESERVE ROWS:   2.事务特有的临时表     CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification>)     ON COMMIT DELETE ROWS:    CREATE GLOBAL TE			

		

	

				

		

			

                [20150705]11G表统计信息与PUBLISH.txt
			

		

		

									

				[20150705]11G表统计信息与PUBLISH.txt --11G表统计信息可以先不发布(在PUBLISH参数的控制下),等检测合适再发布. --确实参数optimizer_use_pending_statistics为false,可以在session级别打开为true,检测统计是否有用. SYS@test> @hide optimizer_use_pending_statistics NAME                              DESCRIPTION       			

		

	

				

		

			

                [20151202]表统计信息stale百分比.txt
			

		

		

									

				[20151202]表统计信息stale百分比.txt --昨天被别人问及一个问题缺省如果某个表修改信息超过10%,oracle即认为这个表需要重新统计分析. --这个百分比如何计算的,实际上只要自己仔细观察就可以确定oracle如何算的. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ --------------			

		

	

				

		

			

                Oracle 临时事务表 全局临时表_global temporary table
			

		

		

									

				所有的操作都在一个事务里,事务提交后,此表清空,特别适合做插入删除频率特别高的临时表操作,比如插入完数据就开始查询,查询完就删掉等,用完就扔! 临时表分事务级临时表和会话级临时表. 事务级临时表只对当前事务有效,通过语句:ON COMMIT DELETE ROWS 指定. 会话级临时表对当前会话有效,通过语句:ON COMMIT PRESERVE ROWS语句指定. -- Create table create global temporary table WFM_TMP_WORKLIST (			

		

	

				

		

			

                innodb的统计信息对optimizer成本预估影响实例
			

		

		

									

				实为吾之愚见,望诸君酌之!闻过则喜,与君共勉 第一节 innodb引擎统计信息 mysql会依据innodb表的数据变化阈值来自动收集和计算表的统计信息(innodb_stats_auto_recalc)以供优化器使用,统计信息的收集是先通过获取一部分符合条件的索引页中的leaf page(是leaf page,不是non-leaf page)的数据,然后通过对这些采集的leaf page计算估计出不同值的数量,进而估算出的信息,信息采集的准确度除了和数据本身的构成有关,还与采集page数量有关			

		

	

				

		

			

                11gR2 新特性--待定的统计信息（Pending Statistic）
			

		

		

									

				 11gR2 新特性--待定的统计信息(Pending Statistic) 11gr2开始,可以使用下面类型的操作来收集优化器统计信息:1.             自动发布收集的统计信息在收集操作结束以后(默认选项publish)2.             保存新的统计信息,并且待定(暂不发布pending) 这个特性可以将新收集的统计信息置为待定状态,所以可以先验证新统计信息的有效性然后再发布. 可以使用下面的命令来查看是否默认发布新的统计信息.sys@DAVID> SELECTDBMS			

		

	

				

		

			

                oracle数据泵导入分区表统计信息报错（五）导致问题的原因
			

		

		

									

				现在已经找到了问题的原因,并且也找到了解决问题的方法,但是导致问题的原因还不清楚. 也就是说,要找到为什么这几个分区表的统计信息被锁住. Oracle的DBMS_STATS包提供了LOCK_TABLE_STATS过程,但是当前的问题显然不是手工调用这个过程造成的. 所有了一下metalink,发现Oracle在文档Doc ID: 433240.1中描述了统计信息被锁定的可能性: 手工执行DBMS_STATS包的LOCK_TABLE_STATS过程: 使用imp或impdp,导入表的时候不加载数据			

		

	

				

		

			

                oracle数据泵导入分区表统计信息报错（二） 深入研究问题的现象
			

		

		

									

				今天在进行数据泵导入操作时,发现一个bug. 上一篇记录了问题的现象,这一篇继续深入研究. 上一篇文章已经描述了问题的产生,而且提到了这个问题很难重现.无论如何去模拟实际的情况,都无法重现问题. 为了重现这个问题,在RAC数据库环境中,仿照问题表创建了分区表.并仿照问题数据库收集了统计信息的方式进行了统计信息的收集,都无法重现问题. 但是,利用问题数据库导出的统计信息,就可以重现问题.上周五发现的问题,但是由于数据不方便带回家,因此由于时间的限制仅仅测试了这么多. 今天一早到了公司,就继续这个问