TROUBLESHOOTING STEPS
In case the errors are seen consistently and no known issues matches to your issue then do the following
Common Causes Solutions:
① Check the client software version. An incompatible client software is a common cause of this error. Upgrade the client software to current version (i.e. match the server version).
The following note explains the supported combinations of clients for a particular RDBMS Server release.
Similar issues are reported when the client installation is faulty.
alter system set "_optim_peek_user_binds"=false;
In few cases, the problem application will work for a while before it terminates with the error. This could be due to the TIMEOUT settings at the Application Server (or) Forms Server.,etc. To resolve this either tune the failing application to complete within the TIMEOUT value (or) reset the TIMEOUT to an optimal value.
Check the 'Current SQL Statement' section of the incident trace files. Does each trace file point to the same SQL statement. If so, there may be a problem with the code.
That you use latest compatible version of JDBC Drivers in your application side.
Monitor and try to handle correctly the timeouts from the application
Check that idle or died programs/processes are not just killed at the client side but using a good cleaning procedure.
Increase queue sizes / buffers and similar from the jdbc side.
Further diagnostics regarding JDBC Application driver can be found:
E.g.:
如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!
诗檀软件专业数据库修复团队
①.、1、调整数据结构的设计.这一部分在开发信息系统之前完成,程序员需要考虑是否使用ORACLE数据库的分区功能,对于经常访问的数据库表是否需要建立索引等.
实际上,上述数据库优化措施之间是相互联系的.ORACLE数据库性能恶化表现基本上都是用户响应时间比较长,需要用户长时间的等待.但性能恶化的原因却是多种多样的,有时是多个因素共同造成了性能恶化的结果,这就需要数据库管理员有比较全面的计算机知识,能够敏感地察觉到影响数据库性能的主要原因所在.另外,良好的数据库管理工具对于优化数据库性能也是很重要的.
ORACLE数据库性能优化工具
常用的数据库性能优化工具有：
①.、1、ORACLE数据库在线数据字典,ORACLE在线数据字典能够反映出ORACLE动态运行情况,对于调整数据库性能是很有帮助的.
ORACLE数据库的系统性能评估
信息系统的类型不同,需要关注的数据库参数也是不同的.数据库管理员需要根据自己的信息系统的类型着重考虑不同的数据库参数.
①.、1、在线事务处理信息系统（OLTP）,这种类型的信息系统一般需要有大量的Insert、Update操作,典型的系统包括民航机票发售系统、银行储蓄系统等.OLTP系统需要保证数据库的并发性、可靠性和最终用户的速度,这类系统使用的ORACLE数据库需要主要考虑下述参数：
l l 数据库回滚段是否足够?
l l 是否需要建立ORACLE数据库索引、聚集、散列?
l l 系统全局区（SGA）大小是否足够?
l l SQL语句是否高效?
l l 是否采用B*-索引或者bitmap索引?
l l 是否采用并行SQL查询以提高查询效率?
l l 是否采用PL/SQL函数编写存储过程?
l l 有必要的话,需要建立并行数据库提高数据库的查询效率
SQL语句的调整原则
SQL语言是一种灵活的语言,相同的功能可以使用不同的语句来实现,但是语句的执行效率是很不相同的.程序员可以使用EXPLAIN PLAN语句来比较各种实现方案,并选出最优的实现方案.总得来讲,程序员写SQL语句需要满足考虑如下规则：
①.、1、尽量使用索引.试比较下面两条SQL语句：
语句A：SELECT dname, deptno FROM dept WHERE deptno NOT IN
(SELECT deptno FROM emp);
语句B：SELECT dname, deptno FROM dept WHERE NOT EXISTS
(SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);
这两条查询语句实现的结果是相同的,但是执行语句A的时候,ORACLE会对整个emp表进行扫描,没有使用建立在emp表上的deptno索引,执行语句B的时候,由于在子查询中使用了联合查询,ORACLE只是对emp表进行的部分数据扫描,并利用了deptno列的索引,所以语句B的效率要比语句A的效率高一些.
SELECT stuff FROM taba a, tabb b, tabc c
WHERE a.acol between :alow and :ahigh
AND b.bcol between :blow and :bhigh
AND c.ccol between :clow and :chigh
AND a.key1 = b.key1
这个SQL例子中,程序员首先需要选择要查询的主表,因为主表要进行整个表数据的扫描,所以主表应该数据量最小,所以例子中表A的acol列的范围应该比表B和表C相应列的范围小.
CPU参数的调整
使用操作相同命令可以看到CPU的使用情况,一般UNIX操作系统的服务器,可以使用sar –u命令查看CPU的使用率,NT操作系统的服务器,可以使用NT的性能管理器来查看CPU的使用率.
数据库管理员还可以通过查看v$sesstat数据字典来获得当前连接ORACLE数据库各个会话占用的CPU时间,从而得知什么会话耗用服务器CPU比较多.
出现CPU资源不足的情况是很多的：SQL语句的重解析、低效率的SQL语句、锁冲突都会引起CPU资源不足.
①.、数据库管理员可以执行下述语句来查看SQL语句的解析情况：
SELECT * FROM V$SYSSTAT
WHERE NAME IN
('parse time cpu', 'parse time elapsed', 'parse count (hard)');
这里parse time cpu是系统服务时间,parse time elapsed是响应时间,用户等待时间
waite time = parse time elapsed – parse time cpu
由此可以得到用户SQL语句平均解析等待时间=waite time / parse count.这个平均等待时间应该接近于0,如果平均解析等待时间过长,数据库管理员可以通过下述语句
SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA
ORDER BY PARSE_CALLS;
来发现是什么SQL语句解析效率比较低.程序员可以优化这些语句,或者增加ORACLE参数SESSION_CACHED_CURSORS的值.
SELECT BUFFER_GETS, EXECUTIONS, SQL_TEXT FROM V$SQLAREA;
查看低效率的SQL语句,优化这些语句也有助于提高CPU的利用率.
内存参数的调整
内存参数的调整主要是指ORACLE数据库的系统全局区（SGA）的调整.SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区.
①.、 1、 共享池由两部分构成：共享SQL区和数据字典缓冲区,共享SQL区是存放用户SQL命令的区域,数据字典缓冲区存放数据库运行的动态信息.数据库管理员通过执行下述语句：
select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache;
select (sum(gets - getmisses - usage - fixed)) / sum(gets) "Row Cache" from v$rowcache;
SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');
来查看数据库数据缓冲区的使用情况.查询出来的结果可以计算出来数据缓冲区的使用命中率=1 - ( physical reads / (db block gets + consistent gets) ).
select name,value from v$sysstat where name in ('redo entries','redo log space requests');查看日志缓冲区的使用情况.查询出的结果可以计算出日志缓冲区的申请失败率：
申请失败率=requests/entries,申请失败率应该接近于0,否则说明日志缓冲区开设太小,需要增加ORACLE数据库的日志缓冲区.
第一段： 磁盘方面调优
① 规范磁盘阵列
分离下面的东西,避免磁盘竞争
SYSTEM表空间
TEMPORARY表空间
UNDO表空间
联机重做日志（放在最快的磁盘上）
操作系统磁盘
ORACLE安装目录
经常被访问的数据文件
索引表空间
归档区域（应该总是与将要恢复的数据分离）
例：
通过下列语句查询确定IO问题
select name ,phyrds,phywrts,readtim,writetim
from v$filestat a,v$datafile b
where a.file#=b.file# order by readtim desc;
u 增大日志文件的大小,从而增加处理大型INSERT,DELETE,UPDATE操作的比例
查询日志文件状态
select a.member,b.* from v$logfile a,v$log b where a.GROUP#=b.GROUP#
查询日志切换时间
from v$log_history a ,v$log_history b
where a.RECID=b.RECID+1
order by a.FIRST_TIME desc
增大日志文件大小,以及对每组增加日志文件（一个主文件、一个多路利用文件）
u 增大LOG_CHECKPOINT_INTERVAL参数,现已不提倡使用它
如果低于每半小时切换一次日志,就增大联机重做日志大小.如果处理大型批处理任务时频繁进行切换,就增大联机重做日志数目.
alter database add logfile member '/log.ora' to group 1;
alter database drop logfile member '/log.ora';
修改三个初始参数：
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=CLOUDSEA_UNDO
UNDO_RETENTION=#of minutes
第二段： 初始化参数调优
① 重要初始化参数
l SGA_MAX_SIZE
l SGA_TARGET
l PGA_AGGREGATE_TARGET
l DB_CACHE_SIZE
l SHARED_POOL_SIZE
它设定了用来存储和处理内存中数据的SGA区域大小,从内存中取数据比磁盘快10000倍以上
根据以下查询出数据缓存命中率
select sum(decode(name,'physical reads',value,0)) phys,
sum(decode(name,'db block gets',value,0)) gets,
sum(decode(name,'consistent gets',value,0)) con_gets,
(1- (sum(decode(name,'physical reads',value,0))/(sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0)) ) ))*100 Hitratio
from v$sysstat;
在调整DB_CACHE_SIZE时使用V$DB_CACHE_ADVICE
select size_for_estimate, estd_physical_read_factor, estd_physical_reads
from v$db_cache_advice
where name = 'DEFAULT';
如果查询的命中率过低,说明缺少索引或者索引受到限制,通过V$SQLAREA视图查询执行缓慢的SQL
正确地调整此参数可以同等可能地共享SQL语句,使得在内存中便能找到使用过的SQL语句.为了减少硬解析次数,优化对共享SQL区域的使用,需尽量使用存储过程、使用绑定变量
select ((1- sum(getmisses)/(sum(gets)+sum(getmisses)))*100) hitratio
from v$rowcache
where gets+getmisses 0;
SELECT SUM(PINS) "EXECUTIONS",SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",1 - SUM(RELOADS)/SUM(PINS)
FROM V$LIBRARYCACHE;
第三段： SQL调优1. 使用提示
①1 改变执行路径
通过OPTIMIZER_MODE参数指定优化器使用方法,默认ALL_ROWS
ALL_ROWS 可得最佳吞吐量执行查询所有行
FIRST_ROWS(n) 可使优化器最快检索出第一行:
select /*+ FIRST_ROWS(1) */ store_id,... from tbl_store
允许开发人员改变访问的实际查询方式,经常使用INDEX提示
CLUSTER 强制使用集群
FULL
HASH
当不指定任何INDEX时,优化器会选择最佳的索引
SELECT /*+ INDEX */ STORE_ID FROM TBL_STORE
INDEX_DESC
INDEX_COMBINE 用来指定多个位图索引,而不是选择其中最好的索引
INDEX_JOIN 只需访问这些索引,节省了重新检索表的时间
INDEX_FFS 执行一次索引的快速全局扫描,只处理索引,不访问具体表
INDEX_SS
INDEX_SSX_ASC
INDEX_SS_DESC
NO_INDEX
NO_INDEX_FFS
NO_INDEX_SS
对于数据仓库非常有帮助
FACT
MERGE
NO_EXPAND 语法：/*+ NO_EXPAND */ column1,...
保证OR组合起的IN列表不会陷入困境,/*+ FIRST_ROWS NO_EXPAND */
NO_FACT
NO_MERGE
NO_QUERY_TRANSFORMATION
NO_REWRITE
NO_STAR_TRANSFORMATION
NO_UNSET
REWRITE
STAR_TRANSFORMATION
UNSET
USE_CONCAT
显示如何将连接表中的数据合并在一起,可用两提示直接影响连接顺序.LEADING指定连接顺序首先使用的表,ORDERED告诉优化器基于FROM子句中的表顺序连接这些表,并使用第一个表作为驱动表（最行访问的表）
ORDERED语法：/*+ ORDERED */ column 1,....
访问表顺序根据FROM后的表顺序来
LEADING语法：/*+ LEADING(TABLE1) */ column 1,....
类似于ORDER,指定驱动表
NO_USE_HASH
NO_USE_MERGE
NO_USE_NL
USE_HASH前提足够的HASH_AREA_SIZE或PGA_AGGREGATE_TARGET
通常可以为较大的结果集提供最佳的响应时间
USE_MERGE
USE_NL 通常可以以最快速度返回一个行
USE_NL_WITH_INDEX
NO_PARALLEL
NO_PARALLEL_INDEX
PARALLEL
PARALLEL_INDEX
PQ_DISTRIBUTE
APPEND 不会检查当前所用块中是否有剩余空间,而直接插入到表中,会直接将数据添加到新的块中.
CACHE 会将全表扫描全部缓存到内存中,这样可直接在内存中找到数据,不用在磁盘上查询
CURSOR_SHARING_EXACT
DRIVING_SITE
DYNAMIC_SAMPLING
MODEL_MIN_ANALYSIS
NOAPPEND
NOCACHE
NO_PUSH_PRED
NO_PUSH_SUBQ
NO_PX_JOIN_FILTER
PUSH_PRED
PUSH_SUBQ 强制先执行子查询,当子查询很快返回少量行时,这些行可以用于限制外部查询返回行数,可极大地提高性能
例：select /*+PUSH_SUBQ */ emp.empno,emp.ename
From emp,orders
where emp.deptno=(select deptno from dept where loc='1')
PX_JOIN_FILTER
QB_NAME
HASH_VALUE：SQL语句的Hash值.
ADDRESS：SQL语句在SGA中的地址.
PARSING_USER_ID：为语句解析第一条CURSOR的用户
VERSION_COUNT：语句cursor的数量
KEPT_VERSIONS：
SHARABLE_MEMORY：cursor使用的共享内存总数
PERSISTENT_MEMORY：cursor使用的常驻内存总数
RUNTIME_MEMORY：cursor使用的运行时内存总数.
SQL_TEXT：SQL语句的文本（最大只能保存该语句的前1000个字符）.
MODULE,ACTION：用了DBMS_APPLICATION_INFO时session解析第一条cursor时信息
SORTS: 语句的排序数
CPU_TIME: 语句被解析和执行的CPU时间
ELAPSED_TIME: 语句被解析和执行的共用时间
PARSE_CALLS: 语句的解析调用(软、硬)次数
EXECUTIONS: 语句的执行次数
INVALIDATIONS: 语句的cursor失效次数
LOADS: 语句载入(载出)数量
ROWS_PROCESSED: 语句返回的列总数
select b.username,a.DISK_READS,a.EXECUTIONS,a.DISK_READS/decode(a.EXECUTIONS,0,1,a.EXECUTIONS) rds_exec_ratio,a.SQL_TEXT
from v$sqlarea a ,dba_users b
where a.PARSING_USER_ID=b.user_id and a.DISK_READS100 order by a.DISK_READS desc;
与V$SQLAREA类似
select * from
where rank_buffers 11
查看表上的索引
select a.table_name,a.index_name,a.column_name,a.column_position,a.table_owner
from dba_ind_columns a
where a.table_owner='CLOUDSEA'
如: select name from tbl where no=?
建立索引：create index test on tbl(name,no) tablespace cloudsea_index storage(....)
对于系统中很关键的查询,可以考虑建立此类连接索引
QUERY_REWRITE_ENALED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED (OR ENFORCED)
create index test on sum(test);
将常用的相对小的表缓存到内存中,但注意会影响到嵌套循环连接上的驱动表
alter table tablename cache;
SELECT ...FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT='A');
(方法一: 高效)
SELECT ....FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = 'A'
(方法二: 最高效)
SELECT ....FROM EMP E WHERE NOT EXISTS (SELECT 'X' FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = 'A');
第四段： 使用STATSPACK和AWR报表调整等待和闩锁
在$ORACLE_HOME/RDBMS/ADMIN下
Spcreate.sql 通过调用spcusr.sql spctab.sql 和spcpkg.sql创建STATSPACK环境,使用SYSDBA运行它
Spdrop.sql 调用sptab.sql和spdusr.sql删除整个STATSPACK环境,使用SYSDBA运行它
Spreport.sql 这是生成报表的主要脚本,由PERFSTAT用户运行
Sprepins.sql 为指定的数据库和实例生成实例报表
Sprepsql.sql 为指定的SQL散列值生成SQL报表
Sprsqins.sql 为指定的数据库和实例生成SQL报表
Spauto.sql 使用DBMS_JOB自动进行统计数据收集（照相）
Sprepcon.sql 配置SQLPLUS变量来设置像阈值这样的内容的配置文件
Spurge.sql 删除给定数据库实例一定范围内的快照ID,不删除基线快照
Sptrunc.sql 截短STATSPACK表里所有性能数据
第五段： 执行快速系统检查1. 缓冲区命中率
查询缓冲区命中率
select (1 - (sum(decode(name, 'physical reads',value,0)) /
(sum(decode(name, 'db block gets',value,0)) +
sum(decode(name, 'consistent gets',value,0))))) * 100 "Hit Ratio"
from v$sysstat;
任何事情都有它的源头,要解决问题,也得从源头开始,影响ORACLE性能的源头非常多,主要包括如下方面：
数据库的硬件配置：CPU、内存、网络条件
① CPU：在任何机器中CPU的数据处理能力往往是衡量计算机性能的一个标志,并且ORACLE是一个提供并行能力的数据库系统,在CPU方面的要求就更高 了,如果运行队列数目超过了CPU处理的数目,性能就会下降,我们要解决的问题就是要适当增加CPU的数量了,当然我们还可以将需要许多资源的进程 KILL掉；
@[toc]
其它类型的表设计可以看博客：
普通表和分区表区别,分区表分成几部分就有几个segment,RANGE_PART_TAB是一个分区表
引用Oracle官方文档的说法, ：
分区类型：分区分为范围分区、列表分区、HASH分区、组合分区四种,图来自Oracle官方网站
关键字partition by range
散列分区也叫hash分区,partitions后接分区数,尽量设置为偶数,
组合分区又称复合分区,主要有两种：oracle11之前只支持范围列表分区(RANGE-LIST)和范围散列分区(RANGE-HASH),oracle11之后支持(范围范围分区)RANGE-RANGE、 (列表范围分区)LIST-RANGE、(列表散列分区)LIST-HASH、(列表列表分区)LIST-LIST这几种组合,为了避免每个主分区中都写相同的从分区,可以用模板方式(subpartition template)
图来自Oracle官方网站：
TRUNCATE子分区
分区相关查询