ambient occlusion nvidia skyrim zolpidem 10 mg zolpidem (ambien edluar)

valium half life drug test buy valium online can valium be used for sleeping

generic xanax 0.5mg pills generic xanax overdose on xanax amount

legal order tramadol online tramadol online no prescription order+tramadol+online+in+florida

tramadol apap 37.5 325mg side effects tramadol 50 mg ultram vs ultracet vs tramadol

retin a how to get prescription where to buy retin a retin a micro not covered by insurance

soma san diego scott raynor soma online buy soma online paypal

Skip to content

Oracle数据库的企业版,标准版,个人版之间的差异

其实Oracle也可以很便宜,标准版在很多情况下已经够用了,下面的信息是对比oracle数据库企业版,标准版,个人版之间的差异.

标准版比企业版主要缺少的功能如下:
1、不支持Oracle Data Guard.想要高可用性的客户,就不能选择标准版)
2、一些Online操作,比如Online index maintenance,Online table redefinition等不支持
3、备份和恢复的某些操作受限,比如不支持Block级别的恢复(Block-level media recovery),不支持并行备份和恢复(Parallel backup and recovery),多重备份(Duplexed backup sets)等等
4、Flashback功能,在标准版中Flashback Table,Flashback Database, Flashback Transaction Query都是不支持的 5、VPD(Virtual Private Database)不支持
6、FGA(Fine-grained auditing)不支持
7、Partitioning,分区也不支持
8、Data compression,比如表压缩,分区压缩都不支持
9、Bitmapped index and bitmapped join index,不支持,(如果是数据仓库系统就不能选择标准版了)
10、Export transportable tablespace,(注意,这里只是导入不支持),但是Import transportable tablespace,包括跨平台的import都是支持的,也就是说你如果选择了标准版,那么想把数据库简单地转移到其它平台上是比较费劲的事情了 11、一些并行操作都不支持,包括Parallel query,Parallel DML,Parallel index build,Parallel Data Pump export and import。
12、不支持Streams,又少了一个高可用性的可行性方案 13、不支持Advanced Replication的multimaster方式,再少一个高可用性方案,当然实体化视图的复制方案仍然是支持的 14、不支持Connection Manager

以下是官方描述的不同版本企业版、标准版之间的差异:

Differences between Enterprise, Standard and Personal Edition

Starting point: Note 465455.1

Content of this note:

Server
Version Note # ABSTRACT
====================================================================

8.1 NOTE.112591.1 Differences Between Enterprise, Standard and Personal Editions on Oracle 8.1

9.0 NOTE.161556.1 Differences between Oracle9i Personal, Standard and Enterprise on NT/2000

9.2 NOTE.269040.1 Differences Between Enterprise, Standard and Personal Editions on Oracle 9.2

10.1 NOTE.271886.1 Differences Between Different Editions of Oracle Database 10G

10.2 NOTE.465465.1 Differences Between Enterprise, Standard and Personal Editions on Oracle 10.2

11.1 Note.465460.1 Differences Between Enterprise, Standard and Personal Editions on Oracle 11.1

11.2 Note.1084132.1 Differences Between Enterprise, Standard and Personal Editions on Oracle 11.2

这里将11.2的差异说明贴出:

转到底部转到底部

I

APPLIES TO:

Oracle Database – Enterprise Edition – Version 11.2.0.1.0 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.
***Checked for relevance on 25-Mar-2015***

DETAILS

Feature/Option SE1 SE EE Notes
High Availability
Oracle Fail Safe Y Y Y Windows only
Oracle RAC One Node N N Y Extra cost option
Oracle Data Guard—Redo Apply N N Y
Oracle Data Guard—SQL Apply N N Y
Oracle Data Guard—Snapshot Standby N N Y
Oracle Active Data Guard N N Y Extra cost option
Rolling Upgrades—Patch Set, Database, and Operating System N N Y
Online index rebuild N N Y
Online index-organized table organization N N Y ALTERTABLEMOVEONLINEoperations
Online table redefinition N N Y Using theDBMS_REDEFINITIONpackage
Duplexed backup sets N N Y
Block change tracking for fast incremental backup N N Y
Unused block compression in backups N N Y
Block-level media recovery N N Y
Lost Write Protection N N Y
Automatic Block Repair N N Y Requires Active Data Guard option
Parallel backup and recovery N N Y
Tablespace point-in-time recovery N N Y
Trial recovery N N Y
Fast-start fault recovery N N Y
Flashback Table N N Y
Flashback Database N N Y
Flashback Transaction N N Y
Flashback Transaction Query N N Y
Oracle Total Recall N N Y Extra cost option
Scalability
Oracle Real Application Clusters N Y Y Extra cost with EE, included with SE
Automatic Workload Management N Y Y Requires Oracle Real Application Clusters
Performance
Client Side Query Cache N N Y
Query Results Cache N N Y
PL/SQL Function Result Cache N N Y
In-Memory Database Cache N N Y Extra cost option
Database Smart Flash Cache N N Y Solaris and Oracle Enterprise Linux only
Support for Oracle Exadata Storage Server Software N N Y
Security
Advanced Security Option N N Y Extra cost option
Oracle Label Security N N Y Extra cost option
Virtual Private Database N N Y
Fine-grained auditing N N Y
Oracle Database Vault N N Y Extra cost option
Secure External Password Store N N Y
Development Platform
SQLJ Y Y Y Requires Oracle Programmer
Oracle Developer Tools for Visual Studio .NET Y Y Y Windows only
Microsoft Distributed Transaction Coordinator support Y Y Y Windows only
Active Directory integration Y Y Y Windows only
Native .NET Data Provider—ODP.NET Y Y Y Windows only
.NET Stored Procedures Y Y Y Windows only
Manageability
Oracle Change Management Pack N N Y Extra cost option
Oracle Configuration Management Pack N N Y Extra cost option
Oracle Diagnostic Pack N N Y Extra cost option
Oracle Tuning Pack N N Y Extra cost option, also requires the Diagnostic Pack
Oracle Provisioning and Patch Automation Pack N N Y Extra cost option
Oracle Real Application Testing N N Y Extra cost option
Database Resource Manager N N Y
Instance Caging N N Y
SQL Plan Management N N Y
VLDB, Data Warehousing, Business Intelligence
Oracle Partitioning N N Y Extra cost option
Oracle OLAP N N Y Extra cost option
Oracle Data Mining N N Y Extra cost option
Oracle Data Profiling and Quality N N Y Extra cost option
Oracle Data Watch and Repair Connector N N Y Extra cost option
Oracle Advanced Compression N N Y Extra cost option
Basic Table Compression N N Y
Bitmapped index, bitmapped join index, and bitmap plan conversions N N Y
Parallel query/DML N N Y
Parallel statistics gathering N N Y
Parallel index build/scans N N Y
Parallel Data Pump Export/Import N N Y
In-memory Parallel Execution N N Y
Parallel Statement Queuing N N Y
Transportable tablespaces, including cross-platform N N Y Import of transportable tablespaces supported into SE, SE1, and EE
Summary management—Materialized View Query Rewrite N N Y
Asynchronous Change Data Capture N N Y
Integration
Basic Replication Y Y Y SE1/SE: read-only, updateable materialized view
Advanced Replication N N Y Multi-master replication
Oracle Streams Y Y Y SE1/SE: no capture from redo
Database Gateways Y Y Y Separate product license
Messaging Gateway N N Y
Networking
Oracle Connection Manager N N Y Available via a custom install of the Oracle Database client, usually installed on a separate machineSee “Oracle Connection Manager” for more information
Infiniband Support N N Y
Content Management
Oracle Spatial N N Y Extra cost option
Semantic Technologies (RDF/OWL) N N Y Requires Oracle Spatial and the Oracle Partitioning option

_datafile_write_errors_crash_instance设置建议

该参数在11.2.0.2以前默认是false,在11.2.0.2后默认为true,作用为在出现io错误的时候数据库选择是offline出现io错误相关的datafile还是直接将instance crash.当为true时候,数据库在发生io错误时候会直接瘫痪.报错ORA-63999等,前阵子我碰到这样的错误,一般碰到此类错误都是从IO传输层,存储和系统网络之间找问题,该参数设置为FALSE或者TRUE只是从业务影响层面广度的考虑.所以一旦碰到IO错误,考调整此参数只是治标不治本,根源还需要从IO传输层的各层面找问题.可以考虑设置为false,减少因为io错误而导致影响的范围增加.

在考虑此参数时候多数已经是碰到IO错误了,所以此时候应该考虑下,数据库坏块的产生控制影响,对以下几个参数给予考虑:

DB_ULTRA_SAFE
DB_BLOCK_CHECKING
DB_LOST_WRITE_PROTECT
DB_BLOCK_CHECKSUM

建议设置参数db_ultra_safe为DATA_ONLY,会稍微增加数据库主机的消耗,以加强对数据的校验以便能及时发现问题,设置该参数会去自动修改对应的另外3个参数:

DB_BLOCK_CHECKING will be set to MEDIUM.(当前为FALSE)
DB_LOST_WRITE_PROTECT will be set to TYPICAL. (当前为TYPICAL)
DB_BLOCK_CHECKSUM will be set to FULL. (当前为NONE)

以下是国外一个工程师对该参数的建议:

Param ‘_datafile_write_errors_crash_instance’ , TRUE or FALSE?

Since 11.2.0.2 there’s a new parameter, “_datafile_write_errors_crash_instance” to prevent the intance to crash when a write error on a datafile occurs . But.. should I use this or not. The official text of this parameter:

This fix introduces a notable change in behaviour in that
from 11.2.0.2 onwards an I/O write error to a datafile will
now crash the instance.

Before this fix I/O errors to datafiles not in the system tablespace
offline the respective datafiles when the database is in archivelog mode.
This behavior is not always desirable. Some customers would prefer
that the instance crash due to a datafile write error.

This fix introduces a new hidden parameter to control if the instance
should crash on a write error or not:
_datafile_write_errors_crash_instance

With this fix:
If _datafile_write_errors_crash_instance = TRUE (default) then
any write to a datafile which fails due to an IO error causes
an instance crash.

If _datafile_write_errors_crash_instance = FALSE then the behaviour
reverts to the previous behaviour (before this fix) such that
a write error to a datafile offlines the file (provided the DB is
in archivelog mode and the file is not in SYSTEM tablespace in
which case the instance is aborted)
When you ask Oracle for advice, you get the following answer:

20+ years ago a feature was added to Oracle to offline a datafile when there was an error writing a dirty buffer to it and it was not part of the system tablespace. At that time it made sense to do this since neither RAC or even
OPS was implemented and storage arrays did not exist. Then the most likelycause of an I/O error was a problem with the direct attached disk drive holding the datafile. By offlining the datafile the database might be able to continue running. Customers assumed that a disk failure would require restoring a backup and doing a media recovery so taking the file offline might improve availability. High availability was not expected.
Today almost all customers use highly available storage arrays accessible from multiple hosts. Now most I/O errors are either transient or are local to the host that encounters them. Real disk failures are hidden by the storage array redundancy. Customers expect a disk failure to have no effect on the operation of the database.

Unfortunately the code to offline a datafile on an I/O error is still there. The effect is that an error on one node in a cluster offlines the datafile and usually takes down the entire application on all nodes or even crashes all instances if the problem is with an undo tablespace. For example dismounting a file system on one node in a cluster makes that node get I/O errors for the files on that file system. This makes a mistake on one node take down the entire cluster.

Offlining a datafile on a write error is even a problem with single instance. Most I/O errors today will go away if the database is restarted on another machine or if the current machine is rebooted. However if the I/O error took a datafile offline, then the administrator must do a media recovery to make the application function again. This is an unusual procedure that takes awhile.

If the database instances do not crash it takes longer for the administrator to find out that the application is not working even though the database appears to be up and running. This is a problem with both RAC and single
instance.

Question: One concern is that a failed datafile write to a non-critical tablespace will bring down the database when it occurs in the only open instance.

It is true that there may be some situations where taking the file offline would be better. On the other hand there are cases where crashing in single instance is better because rebooting the server or restarting the instance will bring it up sooner with no need for manual intervention. Since we have to choose without knowing much about the system we have to base our choice on the odds of the failure being one case or the other. Twenty years ago a datafile was on one disk, almost all I/O errors were disk failures and a disk failure always meant doing media recovery. In that situation taking the datafile offline was clearly the right thing to do, even if the tablespace was critical to the application – it was going to need media recovery in any case.

Today systems are much different.

– Storage arrays and mirroring mean that disk failures almost never require media recovery. I/O write errors usually stop happening when the system is reinitialized.
– Many customers have mechanisms like CRS to automatically restart the database, possibly on a different node.
Now it is much more likely that restarting the instance will resolve the problem without doing any media recovery, and it will happen automatically. The chance that the application can continue running with the offline datafile has always been slight, but when media recovery was going to be required anyway there was no harm in trying to offline the file. Now there is a lot of harm in offlining the file since it prevents automatic recovery and requires an administrator to perform tasks he is unfamiliar with. Today crashing the instance has a better chance of getting the application running sooner.
So Oracle advises to leave the parameter default (=TRUE) and use the new feature. The system is then more capable to recover without interfering needed of a DBA. But when someone has different experiences, feel free to comment on this post….

性能优化的一点体会

1、优化是基于目标的,我们的最终目的是达到一个目标,而不是做优化。目标的合理性决定了优化项目的成败。刚刚开始给用户优化的时候,我会把所有能够调整的东西一次性全部调整完毕。哪怕有些调整给系统性能带来的好处不到0.1%。对于生产系统,不确定因素十分多,而很多参数方面的调整本身就是双刃剑,如果你无法预期其调整的影响,那么这种调整是存在风险的,在实施的时候就应该慎重考虑。现在我做优化的项目,往往会根据用户的优化目标,然后在此基础上进行分析,制定方案,实施的结果虽然一般会超出客户的期望,但是我不会在生产系统上做一些没把握的事情。锦上添花的事情,有时候也要考虑考虑,是否值得,因为弄不好,锦上添花会变成画蛇添足。
2、1+1不一定大于1,在优化过程中,抓住主要矛盾,解决主要问题,而不要胡子眉毛一把抓。很多调整之间有关联性,甚至是互斥的,不合理的调整可能带来更坏的结果。
3、客户需要的是系统的优化,而不仅仅是DB的优化。客户的目标里,看到的是一个系统,而不是一个孤立的DB。在10年前,我可能会说,OS的问题,你们还是找一下厂家。而现在,我会对客户说,你放心,我们做的是系统优化。
4、用适当的方式和应用开发厂商配合。很多优化项目由于无法和应用厂商有效的配合,其效果大打折扣。因为应用是和系统性能关系最为紧密的。如果应用开发厂商不能很好的配合,那么优化项目将举步维艰。如果你和开发厂商说,“你这个SQL开销太大,需要修改一下”,那么得到可能就是强烈的反对。如果你说“这个SQL开销太大,我给你们提供了几个方案,第一是,。。。”,这样你很可能会得到比较好的结果。你是优化专家,找出几个TOP SQL这样的工作,不需要专家来完成,而专家的职责,不仅仅是发现TOP SQL,而是如何解决掉TOP SQL。
5、不要相信什么优化规则,实际上并没有条条框框限制你,实现目标的任何方法你都可以使用。对于一个初级DBA来说,可能老DBA会告诉你,什么是对的,什么是不对的。而对错都是相对的,如果你已经深刻的理解了Oracle以及系统优化,那么就像金庸小说里的内功高手一样,无招胜有招。

对于老白的几个观点,深深的赞同!

SPM固定执行计划以及踩bug事一件

原有2个sql语句有多个表连接,执行计划一直在走错误的执行计划.表级统计信息以及索引规划都已经是最新(这里统计信息有狗血不做描述),只是SQL里还有六个绑定变量以及字段的柱状图影响了执行计划,在这个优化里没有删除柱状图和对绑定变量的影响进行处理(星形连接不建议使用绑定变量),现场环境微妙最终选择通过sql profile以及spm对这2个sql的执行计划进行固定处理.先用sqlprofile固定后让sql重新解析后发现未能生效,逐用spm的方式固定.

这里以其中一个sql_id为bwwnw7r1gzhdf的语句为例,这是收集到对应1个小时内的sqlrpt,其中plan_hash_value为711942702执行计划为正确的执行计划,从报告中可以看到这个sql选择了错误的执行计划,并且从中也可以看到sql有多个执行计划.当中执行计划正确与否的判断方式就不做描述.
 

SQL ID: bwwnw7r1gzhdf

# Plan Hash Value Total Elapsed Time(ms) Executions 1st Capture Snap ID Last Capture Snap ID
1 3052678239 13,512,877 10 25060 25060
2 3392573872 0 0 25060 25060
3 4134955434 0 0 25060 25060
4 1564064893 0 0 25060 25060
5 2504448979 0 0 25060 25060
6 147966509 0 0 25060 25060
7 711942702 0 0 25060 25060

 
通过coe_xfr_sql_profile.sql脚本对bwwnw7r1gzhdf的sql进行固定711942702,生成sql profile的名字为coe_bwwnw7r1gzhdf_711942702.
(该部分可以参考
1.Using Sqltxplain to create a ‘SQL Profile’ to consistently reproduce a good plan (文档 ID 1487302.1)
2.Automatic SQL Tuning and SQL Profiles (文档 ID 271196.1)
3.Correcting Optimizer Cost Estimates to Encourage Good Execution Plans Using the COE XFR SQL Profile Script (文档 ID 1955195.1))

让sql从新解析后从v$sql视图中的sql profile字段没有看到生效的迹象,监控中的执行计划也是依旧错误的.

 

SQL>  select name,created,status from dba_sql_profiles;

NAME                           CREATED                        STATUS
------------------------------ ------------------------------ --------
coe_bwwnw7r1gzhdf_711942702    26-JUN-15 02.09.30.000000 PM   ENABLED
coe_g87an0j5djjpm_334801256    26-JUN-15 11.30.25.000000 AM   ENABLED

SQL>  select SQL_ID, SQL_PROFILE,PLAN_HASH_VALUE from V$SQL where SQL_ID='bwwnw7r1gzhdf' and sql_profile is not null;

no rows

SQL>  select sql_profile,EXECUTIONS,PLAN_HASH_VALUE,parse_calls,ELAPSED_TIME/1000000,
ELAPSED_TIME/1000000/EXECUTIONS,LAST_LOAD_TIME,ROWS_PROCESSED
from v$sql where EXECUTIONS>0 and sql_id='bwwnw7r1gzhdf' order by LAST_LOAD_TIME desc;
...

逐对profile进行disable并drop

=====disable profile==============
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'coe_bwwnw7r1gzhdf_711942702',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'coe_g87an0j5djjpm_334801256',
attribute_name => 'STATUS',
value => 'ENABLED');
END;
/

=====drop profile=================
begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_bwwnw7r1gzhdf_711942702');
end;
/

begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_g87an0j5djjpm_334801256');
end;
/

由于已经存在了正确的执行计划,所以通过DBMS_SPM直接创建baseline,并通过DBMS_SPM包对该sql的baseline的enable,accept,fixed三个属性指定为yes.

该部分可以参考:
Plan Stability Features (Including SQL Plan Management (SPM)) (文档 ID 1359841.1)

为sql创建baseline

variable cnt number;
execute :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => 'bwwnw7r1gzhdf', PLAN_HASH_VALUE => 711942702) ; 

验证该baseline已经生成

SQL> set linesize 200
SQL> Select Sql_Handle, Plan_Name, Origin, Enabled, Accepted,Fixed,Optimizer_Cost,Sql_Text
From Dba_Sql_Plan_Baselines 
Where Sql_Text Like '%FROM P1EDBADM.MES_PROCESSOPERATIONSPEC%' Order By Last_Modified;


SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX OPTIMIZER_COST SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --- -------------- --------------------------------------------------------------------------------
SQL_995463d3d1edd710           SQL_PLAN_9kp33ug8yvpsh4af503b5 MANUAL-LOAD    YES YES NO              69 SELECT D.LOTNAME LOT, D.PRODUCTNAME GLASS, TO_CHAR(D.CREATETIME, 'YYYY-MM-DD HH2

为sqlbaseline的fixed属性改为yes

variable cnt number;
execute :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => 'bwwnw7r1gzhdf', PLAN_HASH_VALUE => 711942702,fixed => 'yes') ; 
验证修改完成
SQL> set linesize 200
SQL> Select Sql_Handle, Plan_Name, Origin, Enabled, Accepted,Fixed,Optimizer_Cost,Sql_Text
  2  From Dba_Sql_Plan_Baselines 
  3  Where Sql_Text Like '%FROM P1EDBADM.MES_PROCESSOPERATIONSPEC%' Order By Last_Modified;

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX OPTIMIZER_COST SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --- -------------- --------------------------------------------------------------------------------
SQL_995463d3d1edd710           SQL_PLAN_9kp33ug8yvpsh4af503b5 MANUAL-LOAD    YES YES YES            574 SELECT D.LOTNAME LOT, D.PRODUCTNAME GLASS, TO_CHAR(D.CREATETIME, 'YYYY-MM-DD HH2

最终验证生效

SQL> Select Sql_Handle, Plan_Name, Origin, Enabled, Accepted,Fixed,Optimizer_Cost,Sql_Text
  2  From Dba_Sql_Plan_Baselines 
  3  Where Sql_Text Like '%FROM P1EDBADM.MES_PROCESSOPERATIONSPEC%' Order By Last_Modified;

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX OPTIMIZER_COST SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- --- -------------- --------------------------------------------------------------------------------
SQL_995463d3d1edd710           SQL_PLAN_9kp33ug8yvpsh4af503b5 MANUAL-LOAD    YES YES YES            574 SELECT D.LOTNAME LOT, D.PRODUCTNAME GLASS, TO_CHAR(D.CREATETIME, 'YYYY-MM-DD HH2
SQL_2e1c8025edb165b3           SQL_PLAN_2w7404rqv2tdm56eb6fa8 MANUAL-LOAD    YES YES YES            311 SELECT 1 " ", D.LOTNAME LOT, D.PRODUCTNAME GLASS, TO_CHAR(MAX(H.EVENTTIME), 'YYY

SPM主要和2个参数有关,一个是baseline生效(optimizer_user_sql_plan_baselines,前提是accept属性要为yes,否则会产生干扰),一个是捕获sql语句生成baseline(optimizer_cature_sql_plan_baselines).在数据库中我一般不开启捕获,但是开启baseline生效.
开启的语法:

alter system set optimizer_user_sql_plan_baselines=true scope=both;
alter system set optimizer_cature_sql_plan_baselines=true scope=both;

关闭的语法:

alter system set optimizer_user_sql_plan_baselines=false scope=both;
alter system set optimizer_cature_sql_plan_baselines=false scope=both;

开启捕获的情况在一些11g版本中会触发该bug
Bug 9910484 – SQL Plan Management Capture uses excessive space in SYSAUX (文档 ID 9910484.8)
此bug会造成sysaux的表空间暴增,主要为sqllob$data,我遇见的是在一天内从2g增长到4g.关闭了捕获后,该现象消失.
删除掉不必要的baseline后可以通过shrink的方式回收sysaux的空间,具体可以参考
Reducing the Space Usage of the SQL Management Base in the SYSAUX Tablespace (文档 ID 1499542.1)