Maclean’s Oracle Database Tech Blog Archives

Oracle在执行SQL语句时，普遍存在以下几个步骤： 当SQL语句首次执行，Oracle将确认该句语句的语法是否正确（语法解析Syntax parse）并进一步确认语句相关表和列的存在性等因素(语义解析semantic parse)以及优化器决定执行计划等步骤。整个过程称之为硬解析，硬解析消耗大量的CPU时间和系统资源。硬解析过多会有效降低系统性能。 若之前已进行过硬解析，且解析后的分析树和执行计划仍存在于共享池中，则同样的SQL仅需要软解析。软解析将输入的SQL语句转换为哈希代码，同共享池内哈希链表上的已有记录进行对比，找出对应的游标信息，使用已有的执行计划执行。 绑定变量,将实际的变量值代入SQL语句中。 执行SQL语句，查询语句将返回结果集。 不使用绑定变量的SQL语句，Oracle无法将它们视为相同的，如以下两句语句： select * from emp where empno=1234 select * from emp where empno=5678 因为自由变量的不同，Oracle认为以上是2句不同的语句，则当第一条被硬解析后，第二条SQL执行时仍无法避免硬解析。实际在以上不使用绑定变量的情况中，只要自由变量有所改变则需要一次硬解析。这是强烈建议使用绑定变量的主要原因，使用绑定变量的语句变量的实际值仅在SQL执行的最后阶段被代入。如以下语句： select * from emp where empno=:x 该语句使用绑定值:x替代自由变量，在应用中语句可能以预编译或普通编译的方式存在，仅在执行阶段代入变量值，多次执行仅需要一次硬解析，较不使用绑定变量情况性能大大提升。 同时过多的硬解析还会引发共享池碎片过多的问题。因为每当需要硬解析一个SQL或者PLSQL语句时，都需要从shared pool中分配一块连续的空闲空间来存放解析结果。Oracle首先扫描shared pool查找空闲内存，如果没有发现大小正好合适的空闲chunk，就查找更大的chunk，如果找到比请求的大小更大的空闲chunk，则将它分裂，多余部分继续放到空闲列表中。因为过多的硬解析加剧了内存段分配的需求，这样就产生了碎片问题。系统经过长时间运行后，就会产生大量小的内存碎片。当请求分配一个较大的内存块时，尽管shared pool总空闲空间还很大，但是没有一个单独的连续空闲块能满足需要。这时，就可能产生 ORA-4031错误。 通常我们可以通过以下SQL语句将系统中非绑定变量的语句找出： SELECT substr(sql_text,1,40) “SQL”, count(*) , sum(executions) “TotExecs” FROM v$sqlarea WHERE executions < 5 –-语句执行次数 GROUP BY substr(sql_text,1,40) HAVING count(*) > 30…

在Oracle 10g版本中可以使用V$UNDOSTAT视图用于监控实例中当前事务使用UNDO表空间的情况。视图中的每行列出了每隔十分钟从实例中收集到的统计信息。每行都表示了在过去7*24小时里每隔十分钟UNDO表空间的使用情况，事务量和查询长度等信息的统计快照。 UNDO表空间的使用情况会因事务量变化而变化，一般我们在计算时同时参考UNDO表空间的平均使用情况和峰值使用情况。 以下SQL语句用于计算过去7*24小时中UNDO表空间的平均使用量： select ur undo_retention, dbs db_block_size, ((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as “M_bytes” from (select value as ur from v$parameter where name = ‘undo_retention’), (select (sum(undoblks) / sum(((end_time – begin_time) * 86400))) ups from v$undostat), (select value as dbs from v$parameter where name =…

Oracle 10gr2的后续版本中添加了撤销(UNDO)信息最短保留时间段自动调优的特性，不再仅仅依据参数UNDO_RETENTION的设定，其调优原则如下： l  当撤销表空间(UNDO TABLESPACE)大小固定，Oracle将根据表空间的大小和实际的系统负载动态调整撤销信息保存时间，该最短保存时间的具体长短基于撤销表空间大小的一定比例值公式换算后获得；它总是比设定的UNDO_RETENTION大，当撤销表空间大量空闲情况下可能远远大于UNDO_RETENTION。 l  当撤销表空间设定为自动扩展空间情况下，Oracle将动态调整撤销信息最短保留时间为该时段最长查询时间(MAXQUERYLEN)加上300秒或参数UNDO_RETENTION间的较大者，即MAX((MAXQUERYLEN+300),UNDO_RENTION)；同样的，该最短保存时间可能远远大于设定的UNDO_RETENTION。 在自动调整情况下，实际的撤销信息最短保留时间可以通过查询V$UNDOSTAT视图上的TUNED_UNDORETENTION列获得。 在无法就撤销表空间做相应修改的情况，我们可以通过修改隐式参数” _UNDO_AUTOTUNE”为FALSE关闭该自动调优特性。以上设定生效后，V$UNDOSTAT视图上TUNED_UNDORETENTION列不再更新，且撤销信息最短保留时间固定为参数UNDO_RETENTION的设定值。该参数可以不用重启数据库而动态设置生效。

Oracle补全日志(Supplemental logging)特性因其作用的不同可分为以下几种：最小(Minimal),支持所有字段(all),支持主键(primary key),支持唯一键(unique),支持外键(foreign key)。包括LONG,LOB,LONG RAW及集合等字段类型均无法利用补全日志。 最小(Minimal)补全日志开启后可以使得logmnr工具支持链式行，簇表和索引组织表。可以通过以下SQL检查最小补全日志是否已经开启： SELECT supplemental_log_data_min FROM v$database; 若结果返回YES或IMPLICIT则说明已开启最小补全日志，当使用ALL,PRIMARY,UNIQUE或FOREIGN补全日志时最小补全日志默认开启(即检查结果为IMPLICIT)。 一般情况下我们在使用逻辑备库时启用主键和惟一键的补全日志，而有时表上可能没有主键，惟一键或唯一索引；我们通过以下实验总结这种情况下Oracle的表现。 首先建立相关的测试表： alter database add supplemental log data (primary key,unique index) columns ; create table test (t1 int , t2 int ,t3 int ,t4 int )； alter table test add constraint pk_t1 primary key (t1); –添加主键 随后使用循环插入一定量的数据 update test set t2=10;       commit;   — 更新数据…

Title: Generating CREATE USER DDL Statements Author:Ted Martin, a database administrator in Ottawa, Ontario, Canada. These scripts will generate SQL DDL statements related to the creation of user accounts. The types of statements generated are as follows: 1. CREATE USER and ALTER USER…QUOTA x ON [tabspace] (GENUSER.SQL) 2. CREATE role (GENROLE.SQL) 3. GRANT [role|priv] TO…

以下脚本可以用于诊断ORA-01000打开游标过多错误: set linesize 140 pagesize 1400 select to_char(100 * sess / calls, ‘999999999990.00’) || ‘%’ cursor_cache_hits, to_char(100 * (calls – sess – hard) / calls, ‘999990.00’) || ‘%’ soft_parses, to_char(100 * hard / calls, ‘999990.00’) || ‘%’ hard_parses from ( select value calls from v$sysstat where name = ‘parse count (total)’ ), ( select value…

[oracle@rh2 ~]$ oerr ora 10357 10357, 00000, “turn on debug information for direct path” // *Cause: // *Action: turn on debug information for direct path The cause of this issue was identified as unpublished Bug 9650718 In the bug, it was found that during cleanup from a direct path load, qesmm context was freed without…

1. Enable audit. Set the parameter to audit_trail=db (or db,extended) 2. Restart the database instance to enable the audit settings. 3. Set up audit for session: audit session whenever successful; 4. After a relevant period of time, check the DBA_AUDIT_SESSION view, in the documentation LOGOFF_LREAD Logical reads for the session LOGOFF_PREAD Physical reads for the…

    手动递增SCN号的几种方法   除去下面几种，还有一种方法直接修改 实例的Global Lamport SCN，在SGA中由kcsgscn变量存储，对于一个实例来说这是唯一的源SCN，所有其他的SCN均由这个source scn所驱动。 这种递增方式是直接用oradebug 修改该Global Lamport SCN kcsgscn     如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复! 诗檀软件专业数据库修复团队 服务热线 ： 13764045638   QQ号:47079569    邮箱：[email protected]     SQL> oradebug setmypid Statement processed.   SQL> select to_char(current_scn,’XXXXXXXXXXXX’) from v$database; TO_CHAR(CURRE ————- D3E1E SQL> SQL> oradebug DUMPvar SGA kcsgscn kcslf kcsgscn_ [060012658, 060012688) = 000D3E1E 00000000 00000000 00000000 0000162D…

REM Locate the top PGA user set lines 75 set pages 999 set serveroutput on spool topuser.out select * from gv$version; declare a1 number; a2 number; a3 varchar2(30); a4 varchar2(30); a5 number; a6 number; a7 number; a8 number; blankline varchar2(70); cursor code is select pid, spid, substr(username,1,20) “USER” , substr(program,1,30) “Program”, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM…