-
编辑 删除
新应用上线又见老问题
忙过了上一周上线的日子 终于可以喘口气了 总结一下目前发现的应用问题.1 还是发现了低效pl/sql2 还要加强对系统设计人员对于函数索引的培训...Load Profile~~~~~~~~~~~~ Per Second Per Transaction--------------- ---------------Redo size: 770,883.85 18,746.07Logical reads: 19,073.28 463.82Block changes: 3,656.68 88.92Physical reads: 698.60 16.99Physical writes: 278.54 6.77User calls: 1,313.17 31.93Parses: 703.90 17.12Hard parses: 18.46 0.45Sorts: 48.68 1.18Logons: 0.29 0.01Executes: 1,469.75 35.74Transactio
-
编辑 删除
查看每次增量备份每个数据文件data blocks修改率(自从上次备份分后)
如果我们的备份策略是: 周日0级 周一，二 1 differential 周三 1 cumulative周四,五 1 differential 周六 1 cumulative我们可以通过查询数据字典试图v$backup_datafile了解到每天数据块修改的情况.[@more@]v$backup_datafile : V$BACKUP_DATAFILE displays information about control files and datafiles in backup sets from the control file.字段解释FILE# : Datafile number; set to 0 for control file DATAFILE_BLOCKS : Size of the datafile in blocks at backup time. This value is also the number of bl
-
编辑 删除
Enqueue events part two
可以被lock manager并行锁的enqueue resources的最大数量由ENQUEUE_RESOURCES初始化参数控制.该参数默认值通常来讲已经足够,如果在应用程序中使用并行DML操作,可以考虑增加ENQUEUE_RESOURCES的值.[@more@]关于ENQUEUE_RESOURCES [10..65535] At database startup time, Oracle allocates the number of enqueues specified by the ENQUEUE_RESOURCES parameter. The default value of ENQUEUE_RESOURCES is derived from the SESSIONS parameter and is usually adequate, as long as its value is greater than DML_LOCKS + 20. For thre
-
编辑 删除
Enqueue events part one
当处理enqueue等待时,熟记这些要点:1 Enqueues 是应用到数据库对象的locks.与latch(应用于SGA内存)不同.2 Enqueues 是由应用程序发起,具有事务性.(具体参见下文)3 Oracle session 正在等候获取一个特定的enqueue. enqueue的名字和mode被记录在P1参数里.针对不同类型的enqueue竞争应采用不同的方式解决.4 一直到Oracle 9i,enqueue wait event 代表所有enqueue waits；从Oracle 10g开始enqueue waits被分类定义(下文详细介绍).[@more@]What Is an Enqueue?一个enqueue是什么由具体环境决定. 如果它被看成是动词,则表示将一个lock请求置于一个
-
编辑 删除
session_cached_cursors
Is a long time since I wrote sth on Oracle last time,for I 've addressed myself to learning English from April this year.Now I come back and feel cool that English is not an obstacle for me to learn Oracle deeply ... ...Let's get to the point .[@more@]Oracle 10g后 默认将session_cached_cursors设置为20(之前为0) 表明了什么？Oracle建议在session中cache cursors.当然前提是应用中是sharable SQL(充分绑定变量)Why?大量重复执行相同sql可以:1 节省解析时间 2 降低cpu资源 If SESSION_CACHED_CURSORS is not set, it
-
编辑 删除
How to convert TIMESTAMP value to a DATE value
PL/SQL - Version: 9.0.1.4 to 10.2.0.0Information in this document applies to any platform.[@more@]Currently there are no built in functions to convert TIMESTAMP to DATE, but the following PL/SQL function can be used to achieve the same:Create or replace function convert_ts_to_date(tstamp timestamp) return date is V_DATE date; V_DAY VARCHAR2(2); V_MONTH VARCHAR2(2); V_YEAR VARCHAR2(4);BEGIN V_DAY:=EXTRACT(DAY FROM tstamp); V_MONTH:=EXTRACT(MONTH FROM tstamp); V_YEAR:=EXTRACT(YEAR FR
