Extended Optimizer Statistics in Oracle 11g Improve Performance

最新推荐文章于 2020-09-25 10:39:58 发布
最新推荐文章于 2020-09-25 10:39:58 发布
阅读量1.2k 收藏
点赞数 1
分类专栏： Oracle Tuning oracle之统计信息 文章标签： oracle extended statistics dbms_Stats 统计信息
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yidian815/article/details/17611949
版权

oracle优化器会利用统计信息来选择最优或者次优的执行计划，在确定执行计划的过程中，oracle可以非常很好的分析每个表列的数据分布特征，但是对于列与列之间的某些关联关系，oracle是无能为力的，例如城市名称和邮编之间的对应关系。值得庆幸的是，在oracle11g中，引入了基于列组合和关系表达式的统计分析功能，这无疑为优化器做出更正确的抉择提供了有力的支持。我们称扩展统计信息功能。

对于扩展统计分析功能，我们大概可以分为两类：基于列组合和基于表达式。而实现的方法也存在两种：通过DBMS_STATS.CREATE_EXTENDED_STATS和通过method_opt参数

DBMS_STATS.CREATE_EXTENDED_STATS和METHOD_OPT

创建扩展的统计信息后，我们可以在DBA_STAT_EXTENSIONS和dba_tab_col_statistics视图中查询到相关信息。示例如下：

首先看一下METHOD_OPT方法

SQL> show user
USER 为 "SH"
SQL> exec dbms_stats.delete_table_stats('SH','CUSTOMERS');

PL/SQL 过程已成功完成。

SQL> exec  Dbms_Stats.drop_Extended_Stats('SH','CUSTOMERS','(cust_state_province,country_id)');

PL/SQL 过程已成功完成。

SQL> Select * From User_Stat_Extensions;

未选定行

SQL> select * from user_tab_col_statistics where table_name='CUSTOMERS';

未选定行

SQL> exec dbms_stats.gather_table_stats('SH','CUSTOMERS',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1,for columns (cust_state_province,country_id) size auto ');

PL/SQL 过程已成功完成。

SQL> select table_name,column_name from user_tab_col_statistics where table_name='CUSTOMERS';

TABLE_NAME		       COLUMN_NAME
------------------------------ ------------------------------
CUSTOMERS		       SYS_STU#S#WF25Z#QAHIHE#MOFFMM_
CUSTOMERS		       CUST_ID
CUSTOMERS		       CUST_FIRST_NAME
CUSTOMERS		       CUST_LAST_NAME
CUSTOMERS		       CUST_GENDER
CUSTOMERS		       CUST_YEAR_OF_BIRTH
CUSTOMERS		       CUST_MARITAL_STATUS
CUSTOMERS		       CUST_STREET_ADDRESS
CUSTOMERS		       CUST_POSTAL_CODE
CUSTOMERS		       CUST_CITY
CUSTOMERS		       CUST_CITY_ID

TABLE_NAME		       COLUMN_NAME
------------------------------ ------------------------------
CUSTOMERS		       CUST_STATE_PROVINCE
CUSTOMERS		       CUST_STATE_PROVINCE_ID
CUSTOMERS		       COUNTRY_ID
CUSTOMERS		       CUST_MAIN_PHONE_NUMBER
CUSTOMERS		       CUST_INCOME_LEVEL
CUSTOMERS		       CUST_CREDIT_LIMIT
CUSTOMERS		       CUST_EMAIL
CUSTOMERS		       CUST_TOTAL
CUSTOMERS		       CUST_TOTAL_ID
CUSTOMERS		       CUST_SRC_ID
CUSTOMERS		       CUST_EFF_FROM

TABLE_NAME		       COLUMN_NAME
------------------------------ ------------------------------
CUSTOMERS		       CUST_EFF_TO
CUSTOMERS		       CUST_VALID

已选择24行。

SQL> Select * From User_Stat_Extensions;

TABLE_NAME		       EXTENSION_NAME
------------------------------ ------------------------------
EXTENSION									 CREATO DRO
-------------------------------------------------------------------------------- ------ ---
CUSTOMERS		       SYS_STU#S#WF25Z#QAHIHE#MOFFMM_
("CUST_STATE_PROVINCE","COUNTRY_ID")						 USER	YES

使用method_opt会生成扩展列并直接统计扩展信息

再来看看DBMS_STATS.CREATE_EXTENDED_sTATS

SQL> exec dbms_stats.delete_table_stats('SH','CUSTOMERS');

PL/SQL 过程已成功完成。

SQL> exec  Dbms_Stats.drop_Extended_Stats('SH','CUSTOMERS','(cust_state_province,country_id)');

PL/SQL 过程已成功完成。

SQL> Select * From User_Stat_Extensions;

未选定行

SQL> select table_name,column_name from user_tab_col_statistics where table_name='CUSTOMERS';

未选定行

SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS('SH','CUSTOMERS','(cust_state_province,country_id)') FROM DUAL; 

DBMS_STATS.CREATE_EXTENDED_STATS('SH','CUSTOMERS','(CUST_STATE_PROVINCE,COUNTRY_ID)')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_

SQL> select table_name,column_name from user_tab_col_statistics where table_name='CUSTOMERS';

未选定行

SQL> Select * From User_Stat_Extensions;

TABLE_NAME		       EXTENSION_NAME		      EXTENSION 							          CREATO DRO
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------ ---
CUSTOMERS		       SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ ("CUST_STATE_PROVINCE","COUNTRY_ID")				          USER   YES

SQL> exec dbms_stats.gather_table_stats('SH','CUSTOMERS',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1 ');

PL/SQL 过程已成功完成。

SQL> select table_name,column_name from user_tab_col_statistics where table_name='CUSTOMERS';

TABLE_NAME		       COLUMN_NAME
------------------------------ ------------------------------
CUSTOMERS		       SYS_STU#S#WF25Z#QAHIHE#MOFFMM_
CUSTOMERS		       CUST_ID
CUSTOMERS		       CUST_FIRST_NAME
CUSTOMERS		       CUST_LAST_NAME
CUSTOMERS		       CUST_GENDER
CUSTOMERS		       CUST_YEAR_OF_BIRTH
CUSTOMERS		       CUST_MARITAL_STATUS
CUSTOMERS		       CUST_STREET_ADDRESS
CUSTOMERS		       CUST_POSTAL_CODE
CUSTOMERS		       CUST_CITY
CUSTOMERS		       CUST_CITY_ID

TABLE_NAME		       COLUMN_NAME
------------------------------ ------------------------------
CUSTOMERS		       CUST_STATE_PROVINCE
CUSTOMERS		       CUST_STATE_PROVINCE_ID
CUSTOMERS		       COUNTRY_ID
CUSTOMERS		       CUST_MAIN_PHONE_NUMBER
CUSTOMERS		       CUST_INCOME_LEVEL
CUSTOMERS		       CUST_CREDIT_LIMIT
CUSTOMERS		       CUST_EMAIL
CUSTOMERS		       CUST_TOTAL
CUSTOMERS		       CUST_TOTAL_ID
CUSTOMERS		       CUST_SRC_ID
CUSTOMERS		       CUST_EFF_FROM

TABLE_NAME		       COLUMN_NAME
------------------------------ ------------------------------
CUSTOMERS		       CUST_EFF_TO
CUSTOMERS		       CUST_VALID

已选择24行。

可以看出，DBMS_STATS.CREATE_EXTEND_sTATS不会直接创建统计信息，需要我们手工调用DBMS_STATS.GATHER_TABLE_STATS过程。


EXPRESSION

除了可以按照列组合的方式创建扩展统计信息，还可以按照表达式的方式来创建扩展统计信息，这对于基于函数的索引的非常有用的。

SQL> select dbms_Stats.create_extended_Stats('SH','CUSTOMERS','(MOD(CUST_ID,10))') FROM DUAL;

DBMS_STATS.CREATE_EXTENDED_STATS('SH','CUSTOMERS','(MOD(CUST_ID,10))')
----------------------------------------------------------------------------------------------------
SYS_STU1D2S2K6$TFSJ$24PUR2SN1E

SQL> Select * From User_Stat_Extensions;

TABLE_NAME		       EXTENSION_NAME
------------------------------ ------------------------------
EXTENSION									 CREATO DRO
-------------------------------------------------------------------------------- ------ ---
CUSTOMERS		       SYS_STU1D2S2K6$TFSJ$24PUR2SN1E
(MOD("CUST_ID",10))								 USER	YES


扩展统计是如何帮助优化器的？

我们通过下面的例子来演示一下扩展统计信息是如何帮助优化器正确评估sql语句的选择性的，

SQL> Select * From User_Stat_Extensions;   --没有开启扩展统计信息

未选定行

SQL> exec dbms_stats.gather_table_stats('SH','CUSTOMERS',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1 ,for columns cust_state_province size 250, for columns country_id size 250');


PL/SQL 过程已成功完成。

SQL> set autotrace on explain
SQL> Select Count(*) From Customers Where Cust_State_Province='CA';

  COUNT(*)
----------
      3341   --实际存在数量为3341


执行计划
----------------------------------------------------------
Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation	   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	       |     1 |    11 |   413	 (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |	       |     1 |    11 |	    |	       |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |  3359 | 36949 |   413	 (1)| 00:00:05 |   --优化器估计的数量为3359，与3341基本接近
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CUST_STATE_PROVINCE"='CA')

SQL> Select Count(*) From Customers Where Country_Id=52790;

  COUNT(*)
----------
     18520  --实际存在数量为 18520


执行计划
----------------------------------------------------------
Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation	   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	       |     1 |     5 |   413	 (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |	       |     1 |     5 |	    |	       | 
|*  2 |   TABLE ACCESS FULL| CUSTOMERS | 18863 | 94315 |   413	 (1)| 00:00:05 |   --优化器估计数量为18863，与18520基本接近
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COUNTRY_ID"=52790)

SQL> SELECT COUNT(*) FROM CUSTOMERS WHERE CUST_STATE_PROVINCE='CA' AND COUNTRY_ID=52790;

  COUNT(*)
----------
      3341  --实际值数量为3341，这是因为country_id和CUST_STAT_PROVINCE之间存在某种数值关系，而这种关系，oracle数据库是不知的


执行计划
----------------------------------------------------------
Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation	   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	       |     1 |    16 |   413	 (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |	       |     1 |    16 |	    |	       |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |  1142 | 18272 |   413	 (1)| 00:00:05 | 
 --由于oracle不知道country_id和CUST_STAT_PROVINCE之间隐含关系，oracle估算的方式为3359*18863/55500（表customer的记录总数）=1141.63

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52790)


SQL> Select Count(*) From Customers Where Country_Id=52770;

  COUNT(*)
----------
      7780


执行计划
----------------------------------------------------------
Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation	   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	       |     1 |     5 |   413	 (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |	       |     1 |     5 |	    |	       |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |  7380 | 36900 |   413	 (1)| 00:00:05 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COUNTRY_ID"=52770)

SQL> SELECT COUNT(*) FROM CUSTOMERS WHERE CUST_STATE_PROVINCE='CA' AND COUNTRY_ID=52770;

  COUNT(*)
----------
	 0  --实际值为0


执行计划
----------------------------------------------------------
Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation	   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	       |     1 |    16 |   413	 (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |	       |     1 |    16 |	    |	       |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |   447 |  7152 |   413	 (1)| 00:00:05 |
--------------------------------------------------------------------------------

--由于oracle不知道country_id和CUST_STAT_PROVINCE之间隐含关系，oracle估算的方式为3359*7380/55500（表customer的记录总数）=446.65








Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52770)


可以看出，如果oracle不了解列country_id和CUST_STAT_PROVINCE之间的数量关系，优化器估计的结果和实际值之间是存在差距的，下面看一下添加扩展统计信息后的结果



SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS('SH','CUSTOMERS','(cust_state_province,country_id)') FROM DUAL; 

DBMS_STATS.CREATE_EXTENDED_STATS('SH','CUSTOMERS','(CUST_STATE_PROVINCE,COUNTRY_ID)')
----------------------------------------------------------------------------------------------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_

SQL> exec dbms_stats.gather_table_stats('SH','CUSTOMERS',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1 ,for columns cust_state_province size 250, for columns country_id size 250');

PL/SQL 过程已成功完成。

SQL> select table_name,column_name,histogram from user_tab_col_statistics where table_name='CUSTOMERS' AND column_name like 'SYS%';

TABLE_NAME		       COLUMN_NAME		      HISTOGRAM
------------------------------ ------------------------------ ---------------
CUSTOMERS		       SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ NONE

SQL> set autotrace on explain
SQL> SELECT COUNT(*) FROM CUSTOMERS WHERE CUST_STATE_PROVINCE='CA' AND COUNTRY_ID=52770;

  COUNT(*)
----------
	 0


执行计划
----------------------------------------------------------
Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation	   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	       |     1 |    16 |   413	 (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |	       |     1 |    16 |	    |	       |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |   432 |  6912 |   413	 (1)| 00:00:05 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52770)

SQL> set autotrace off
SQL> exec dbms_stats.gather_table_stats('SH','CUSTOMERS',METHOD_OPT=>'FOR ALL COLUMNS SIZE auto ,for columns cust_state_province size 250, for columns country_id size 250');

PL/SQL 过程已成功完成。

SQL> select table_name,column_name,histogram from user_tab_col_statistics where table_name='CUSTOMERS' AND column_name like 'SYS%';

TABLE_NAME		       COLUMN_NAME		      HISTOGRAM
------------------------------ ------------------------------ ---------------
CUSTOMERS		       SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ FREQUENCY

SQL> set autotrace on explain
SQL> SELECT COUNT(*) FROM CUSTOMERS WHERE CUST_STATE_PROVINCE='CA' AND COUNTRY_ID=52770;

  COUNT(*)
----------
	 0


执行计划
----------------------------------------------------------
Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation	   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	       |     1 |    16 |   413	 (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |	       |     1 |    16 |	    |	       |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |     5 |    80 |   413	 (1)| 00:00:05 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52770)


扩展的统计信息发挥作用，帮助优化器做出了正确的评估。







Extended Statistics Usage Notes:  


The Oracle documentation notes these limitations on the dbms_stats.create_extended_stats extension argument:


- The extension cannot contain a virtual column.



- Extensions cannot be created on tables owned by SYS.



- Extensions cannot be created on cluster tables, index organized tables, temporary tables or external tables.



- The total number of extensions in a table cannot be greater than a maximum of (20, 10% of number of non-virtual columns in the table).



- The number of columns in a column group must be in the range [2, 32].



- A column cannot appear more than once in a column group.



- A column group can not contain expressions.



- An expression must contain at least one column.



- An expression cannot contain a subquery.



- The COMPATIBLE parameter needs to be 11.0.0.0.0 or greater





                

        

        

    




    

      

        

            

              
                
                  ezbit
                
              
            

            

                关注
              
            

        

        

          
    
            
  • 
              
                
                
                
                
                    1
                
              
              
    点赞
    
            
    • 
            
  • 
              
                
                
                
              
              
    踩
    
            
    • 
            
  • 
              
                
                
                
                
                    0
                
              
              
    
                
    
                  
                    收藏
                  
                
    
              
    
              
    
                
    
                  觉得还不错?
                  
                    一键收藏
                  
                 
                
    
              
    
            
    • 
          
  • 
            
    
              
              
            
    
              
              
              
                    0
              
            
            
    评论
    
          
    • 
          
  • 
          
    • 
          
  • 
            
              
            
              
    
            
              
                
            
    
          
    • 
        

      

      

            

                专栏目录
          










                



	

		

			

				
					
Understanding Optimizer Statistics (oracle 性能优化白皮书英文版)

				
			

			

				

					10-31
				

			

		

		

			
				
ORACLE 统计信息（Statistics）的查询，收集，还原，锁定，导入，导出

			
		

	



                

              
                



	

		

			

				
					
oracle 带有变量的语句_ORACLE获取SQL绑定变量值的方法总结

				
			

			

				

					weixin_28996271的博客
				

				

					12-28
					
					1225
					
				

			

		

		

			
				
本文总结一下ORACLE数据库中如何获取SQL绑定变量值的方法，在SQL优化调优过程中，经常会用到这方面的知识点。在此梳理、总结一下，方面日后查找、翻阅。方法1：查询V$SQLV$SQL视图中的BIND_DATA字段用来存储绑定变量的值，但是从这个视图查询绑定变量的值，有很大的局限性：1： 它的记录频率受_cursor_bind_capture_interval隐含参数控制，默认值为900，表示每...

			
		

	



                


  
              

                


	

		

			

				
					
Oracle 11g中的_optimizer_null_aware_antijoin隐含参数

				
			

			

				

					
				

				

					06-30
					
					480
					
				

			

		

		

			
				
_optimizer_null_aware_antijoin 参数是Oracle 11g引入的新参数，它用于解决在反连接（Anti-Join）时，关联列上存在空值（NULL）或关联列无非空约束的问题
默认情况下，该参数的值为t...

			
		

	





	

		

			

				
					
关于隐藏参数：_no_recovery_through_resetlogs

				
			

			

				

					cp26520的博客
				

				

					05-28
					
					433
					
				

			

		

		

			
				
这个参数用于限制恢复能否跨越resetlogs，对于数据库的恢复来说，resetlogs通常意味着不完全恢复，在数据库resetlogs打开之后，控制文件中的很多信息被改写，在Oracle 10g之前，如果数据库resetlog...

			
		

	



		

			
		



	

		

			

				
					
Best Practices for Gathering Optimizer Statistics for Oracle 12c (June 2013)-计算机科学

				
			

			

				

					04-22
				

			

		

		

			
				
June 2013Best Practices for Gathering Optimizer Statistics  with Oracle Database 12cBest Practices for Gathering Optimizer StatisticsDisclaimerThe following is intended to outline our general product ...

			
		

	





	

		

			

				
					
Oracle 11g收集多列统计信息详解

				
			

			

				

					12-15
				

			

		

		

			
				
通常，当我们将SQL语句提交给Oracle数据库时，Oracle会选择一种最优方式来执行，这是通过查询优化器Query Optimizer来实现的。CBO（Cost-Based Optimizer）是Oracle默认使用的查询优化器模式。在CBO中，SQL执行计划...

			
		

	





	

		

			

				
					
Best Practices for Gathering Optimizer Statistics with Oralce 18c

				
			

			

				

					10-29
				

			

		

		

			
				
Best Practices for Gathering Optimizer Statistics with oracle 18c. 此文档研究18c优化器必看.

			
		

	





	

		

			

				
					
pso.zip_PSO_PSo- optimizer_in_swarm

				
			

			

				

					09-24
				

			

		

		

			
				
Particle swarm optimizer, in matlab

			
		

	





	

		

			

				
					
oracle 11g和12c初始安装数据库需调整的参数

				
			

			

				

					
				

				

					08-01
					
					1046
					
				

			

		

		

			
				
一、11g版本初始安装数据库需要调整的参数：参数设置标准默认存在风险db_files1000200数据库内允许最大文件个数，初始值为200，数量太小会导致无法增加数据文件processes1500数据库最大进程数，可以有效防止数据库内部进程个数不够，影响业务正常运行sessions2272undo_retention3600900undo段回收时间，单位是秒。达到回收时间un...

			
		

	





	

		

			

				
					
oracle 11.2.0.2以后变动的一些隐含参数

				
			

			

				

					weixin_34192993的博客
				

				

					10-09
					
					144
					
				

			

		

		

			
				
1 查询隐含参数SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describFROM SYS.x$ksppi x, SYS.x$ksppcv yWHERE x.inst_id = USERENV ('Instance')AND y.inst_id = USERENV ('Instance')AND x.indx = y...

			
		

	





	

		

			

				
					
oracle系统根据optimizer statistics（指的就是work area profile）估算出optimal尺寸和onepass尺寸

				
			

			

				

					haiross的专栏
				

				

					02-12
					
					1150
					
				

			

		

		

			
				
V$SQL_WORKAREA_ACTIVE




ESTIMATED_OPTIMAL_SIZE     NUMBER     Estimated size (in bytes) required by this work area to execute the operation completely in memory (optimal execution). Derived fro

			
		

	





	

		

			

				
					
Oracle的AMM和ASMM以及相关参数探究

				
			

			

				

					
				

				

					05-26
					
					211
					
				

			

		

		

			
				
实验出发点
 

1. 数据库刚启动时候的sga信息(show sga可查看)或者通过show parameter sga/pga/db_cache_size/memory_target等等和内存有关的参数，并不能真...

			
		

	





	

		

			

				
					
隐含参数_minimum_giga_scn被Oracle废弃后如何调SCN

				
			

			

				

					jarry_gao的专栏
				

				

					06-11
					
					2008
					
				

			

		

		

			
				
Posted: August 27, 2013 |
Author: Cui Hua |
Filed under: Oracle |
Leave a comment »

隐含参数_minimum_giga_scn在Oracle 11.2.0.2打上相关的SCN的补丁以及后续的版本中已经被废弃：
Connected to Oracle Database 12c Enterprise Ed

			
		

	





	

		

			

				
					
Oracle 11g DataGuard 配置详细说明

					
热门推荐

				
			

			

				

					csdn's blog
				

				

					07-31
					
					1万+
					
				

			

		

		

			
				
Oracle 11g DataGuard 配置详细说明

1．判断DataGuard是否安装 




select * from v$option where parameter ='Oracle Data Guard';










2. 数据库环境说明

主库配置：IP：192.168.228.133(Oracle11g1)，数据库名：db1,监听服务名：pri

从库配...

			
		

	





	

		

			

				
					
Oracle12c Optimizer Statistics Concepts

				
			

			

				

					数据之路-忆梦前程
				

				

					10-18
					
					1594
					
				

			

		

		

			
				
Optimizer Statistics的简介
Oracle数据库optimizer statistics描述了数据库及其对象的详细信息。优化成本模型依赖于收集的统计信息查询涉及的对象，并运行查询的数据库和主机。统计挑选最好的一个SQL语句的执行计划


Optimizer statistics 包括以下内容：
表的统计信息
        行数
        的块数

			
		

	





	

		

			

				
					
oracle隐含参数

				
			

			

				

					renyanjie123的博客
				

				

					12-12
					
					3172
					
				

			

		

		

			
				
参数名称
			参数值
			默认值
			参数描述
			 
		 
			_4030_dump_bitvec
			4095
			TRUE
			bitvec to specify dumps prior to 4030 error
		 
			_4031_dump_bitvec
			67194879
			TRUE
			bitvec to specify dumps prior to...

			
		

	





	

		

			

				
					
oracle11g查看全库及用户的资源限制

				
			

			

				

					cuanernu0615的博客
				

				

					12-08
					
					599
					
				

			

		

		

			
				
Oracle全库的资源限制
Oracle初始化参数中很多都是用于对资源的配置，其中有一些常用的资源限制：


SQL> show parameter ga_
NAME            ...

			
		

	





	

		

			

				
					
Oracle 11g隐藏参数大全

				
			

			

				

					dbhang的博客
				

				

					09-25
					
					1414
					
				

			

		

		

			
				
Here is the list of all undocumented parameters in Oracle 11g  :




PARAMETER NAME
PARAMETER VALUE
DESCRIPTION




_4031_dump_bitvec
67194879
bitvec to specify dumps prior to 4031 error


_4031_dump_interval
300
Dump 4031 error once for each n-second inte

			
		

	





	

		

			

				
					
one or more columns in the following table do not have statistics

					
最新发布

				
			

			

				

					05-19
				

			

		

		

			
				
 Statistics are used by the query optimizer to determine the most efficient way to execute a query.  To resolve this issue, you can gather statistics on the columns in question using the ANALYZE ...

			
		

	



              



  
“相关推荐”对你有帮助么？

  

      
    
          
  • 
              
    
                  
                  
              
    
              
    非常没帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    没帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    一般
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    有帮助
    
          
    • 
          
  • 
              
    
                  
                  
              
    
              
    非常有帮助
    
          
    • 
      

      

      

          
          提交
      

      

  




          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值