| 个人资料zhanjie的共享空间照片日志列表 |  工具  帮助 |
|
7月24日 v$sql v$sqlarea v$sqltext
ACCESS导入到ORACLE 昨天去甲方导数据，去之前居然没人知道要导什么，内容是什么，去了一看，ACCESS库，240W条数据，文件有740M， ◆最初建立ODBC，直接用ACCESS 导入到ORACLE。但由于字符集的问题（UTF8）导致导入失败，因为UTF8用3字节存放汉字，而导库过程是由ACCESS按自己的定义自动建立ORACLE表的，如果事先在ORACLE里建立表，然后导入会得到对象已存在的错误。为解决此问题，想在ACCESS中修改字段长度，但是由于数据量过大，导致WIN OS报内存或磁盘空间满的问题，按WIN的提示去修改注册表及释放空间等，皆解决不了问题，而由于数据量较大，浪费了很多时间。 ◆采取第二种方案SQLLDR，用ACCESS打开，然后保存成TXT文件，以逗号分割。打包压缩，FTP到服务器上，运行SQLLDR，结果报如下错误： Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Segmentation fault(coredump)
从头狼处得到解释， fact: Oracle Server - Enterprise Edition 9.2 fact: SQL*Loader fact: AIX-Based Systems symptom: Segmentation Fault (coredump)
symptom: Log file is not written symptom: Stack trace: waitpid sntpreap sslsshandler thread_tsleep
symptom: SQL*Loader fails at the end of load cause: Operating System defect fix:
1. Install the following
fixes specific to AIX version:
a. AIX 4.3.3: IY30151,IY30927 b. AIX 5.1(5L): IY30150 2. and relink SQL*Loader
executable:
a. $ cd $ORACLE_HOME/rdbms b b. $ make -f ins_rdbms.mk isqlldr 由于权属问题，不好对OS操作，所以这个方法也放弃。 ◆采用第三个方案，建立一个ZHS16GBK的数据库，先将ACCESS的数据到入到这个中间库中，然后修改字段长度，再导入到正式库中，成功。实际这个方案早在考虑当中，只是我的本子有两个库都是UTF8的，最后痛下决心，删了一个库，然后建立ZHS16GBK的数据库。 SELECT FOR UPDATE 相关的知识 单位里最大连接数很低，但由于历史原因，居然每个dml操作前都要SELECT FOR UPDATE一下，因此会造成LOCK的问题，写此文仅给一些常用FOR UPDATE的人。
statement: 一个SQL语句。
session: 一个由ORACLE用户产生的连接，一个用户可以产生多个SESSION ，但相互之间是独立的。
transaction:所有的改变都可以划分到transaction里，一个transaction包含一个或多个SQL。当一个SESSION建立的时候就是一个TRANSACTION开始的时刻，此后transaction的开始和结束由DCL控制，也就是每个COMMIT/ROLLBACK都标示着一个transaction的结束。
consistency：是对于statement级别而不是transaction级别来说的。sql statement 得到的数据都是以sql statement开始的IMAGE。
LOCK的基本情况
update, insert ,delete, select ... for update会LOCK相应的ROW 。
只有一个TRANSACTION可以LOCK相应的行，也就是说如果一个ROW已经LOCKED了，那就不能被其他TRANSACTION所LOCK了。
LOCK由statement产生但却由TRANSACTION结尾（commit，rollback），也就是说一个SQL完成后LOCK还会存在，只有在COMMIT/ROLLBACK后才会RELEASE。
SELECT.... FOR UPDATE [OF cols] [NOWAIT];
OF cols
SELECT cols FROM tables [WHERE...] FOR UPDATE [OF cols] [NOWAIT];
前面的FOR UPDATE就不说了，讲下OF
transaction A运行
select a.object_name,a.object_id from wwm2 a,wwm3 b
2 where b.status='VALID' and a.object_id=b.object_id 3* for update of a.status 则transaction B可以对b表wwm3的相应行进行DML操作,但不能对a表wwm2相应行进行DML操作.
反一下看看
transaction A运行
select a.object_name,a.object_id from wwm2 a,wwm3 b
2 where b.status='VALID' and a.object_id=b.object_id 3* for update of b.status 则transaction B可以对a表wwm2的相应行进行DML操作,但不能对b表wwm3相应行进行DML操作.
也就是说LOCK的还是行,只是如果不加OF的话会对所有涉及的表LOCK的,加了OF后只会LOCK OF 字句所在的TABLE.
NOWAIT（如果一定要用FOR UPDATE，我更建议加上NOWAIT）
当有LOCK冲突时会提示错误并结束STATEMENT而不是在那里等待.返回错误是"ORA-00054: resource busy and acquire with NOWAIT specified"
另外如下用法也值得推荐，应该酌情考虑使用。
FOR UPDATE WAIT 5
5秒后会提示ORA-30006: resource busy; acquire with WAIT timeout expired
FOR UPDATE NOWAIT SKIP LOCKED; 会提示no rows selected
TABLE LOCKS
LOCK TABLE table(s) IN EXCLUSIVE MODE [NOWAIT];
同样也是在transaction结束时才会释放lock。
DEADLOCK
transaction a lock rowA , then transaction b lock rowB
then transaction a tries to lock rowB, and transaction b tries to lock rowA
也就是说两个transaction都相互试图去lock对方已经lock的ROW，都在等待对方释放自己的lock，这样就使死锁。
deadlock也会有600提示。 ORACLE 内存管理 之二 PGA v$pgastat
需要设置两个基本点参数 : WORKAREA_SIZE_POLICY=AUTO,默认就是AUTO， PGA_AGGREGATE_TARGET总的PGA的大小 注意，9I的shared server连接需要明确设置SORT_AREA_SIZE 和 HASH_AREA_SIZE，也就是说不能用自动管理模式。10G则无此限制。 PGA_AGGREGATE_TARGET是一个上限（理论上的最大值,PL/SQL就很容易超过），ORACLE启动时并不分配那么多，你甚至可以设置大于物理MEM的大小（生产库不要这么做呀，要设置pga_aggregate_target+sga<MEM ，别挑战ORACLE的极限）。一个SESSION可能有多个sort,hash的workarea，每一个workarea最多会用到5%或100M(由两个隐藏参数控制)，因此如果预计每个sort,hash的workarea是5M，应该设置PGA_AGGREGATE_TARGET成100M。但是，随着用户的增加或工作量的增大，给每个workarea的容量可能会减少，因为有总量PGA_AGGREGATE_TARGET的限制，比如需要100个workarea，那么每个只能分配到1M。parallel query会用到最多30%(由隐藏参数控制)的PGA_AGGREGATE_TARGET，每一个parallel query的PIECE会分配相应的30%，也就是parallel query可能会用到30M，10个PARALLEL，那么每个用3M。这也就是建议用auto管理的原因,一个系统通常workload,session是随时间变化的,早上可能3个用户,中午可能300个用户,所以用固定sort,hash的参数是不合时宜的.自动管理才可以实现在用户并发少的时候分配更多的内存，在并发多的时候照顾大众，分配少的内存。ORACLE 9.2以后有了PGA advisory
SQL> show parameter pga
NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ pga_aggregate_target big integer 105906176
SQL> show parameter workarea
NAME TYPE VALUE ------------------------------------ ---------------------- ---------- workarea_size_policy string AUTO
1 select name||' '|| 2 to_char(decode( unit, 3 'bytes', value/1024/1024, 4 value ),'999,999,999.9')||' '|| 5 decode( unit, 'bytes', 'mbytes', unit ) 6* from v$pgastat SQL> /
NAME||''||TO_CHAR(DECODE(UNIT,'BYTES',VALUE/1024/1024,VALUE),'999,999,999.9')||''||DECODE(UNIT,'BYTE ---------------------------------------------------------------------------------------------------- aggregate PGA target parameter 101.0 mbytes aggregate PGA auto target 74.1 mbytes global memory bound 5.0 mbytes total PGA inuse 22.0 mbytes total PGA allocated 33.2 mbytes maximum PGA allocated 33.4 mbytes total freeable PGA memory .0 mbytes PGA memory freed back to OS .0 mbytes total PGA used for auto workareas 3.3 mbytes maximum PGA used for auto workareas 4.8 mbytes total PGA used for manual workareas .0 mbytes maximum PGA used for manual workareas 3.6 mbytes over allocation count .0 bytes processed 1,545.8 mbytes extra bytes read/written 75.1 mbytes cache hit percentage 95.4 percent
解释 aggregate PGA auto target 74.1 mbytes，可以使用的PGA，也就是说剩余的PGA total PGA inuse 22.0 mbytes 现在使用的PGA over allocation count .0 ORACLE分配的PGA超过pga_aggregate_target 的次数.这个参数可以判断pga_aggregate_target 是否设置的太小. cache hit percentage 95.4 percent 自从instance启动后的PGA 命中率,如果所有的操作都在MEM中进行没有在TEMP里运行的话应该是100%. ORACLE内存管理 之三 PGA v$sql_workarea_histogram v$pga_target_advice另外还有一个很重要的试图来观察PGA的效率v$sql_workarea_histogram pga_aggregate_target big integer 1236648591 1 SELECT 2 case when low_optimal_size < 1024*1024 3 then to_char(low_optimal_size/1024,'999999') || 4 'kb <= PGA < ' || 5 (HIGH_OPTIMAL_SIZE+1)/1024|| 'kb' 6 else to_char(low_optimal_size/1024/1024,'999999') || 7 'mb <= PGA < ' || 8 (high_optimal_size+1)/1024/1024|| 'mb' 9 end ||' '|| 10 optimal_executions||' '|| 11 onepass_executions||' '|| 12 multipasses_executions 13 from v$sql_workarea_histogram 14 where total_executions <> 0 15* order by low_optimal_size SQL> /
CASEWHENLOW_OPTIMAL_SIZE<1024*1024THENTO_CHAR(LOW_OPTIMAL_SIZE/1024,'999999')||' -------------------------------------------------------------------------------- 16kb <= PGA < 32kb 53646550 0 0 32kb <= PGA < 64kb 26062 0 0 64kb <= PGA < 128kb 20361 0 0 128kb <= PGA < 256kb 893 0 0 256kb <= PGA < 512kb 941 0 0 512kb <= PGA < 1024kb 8331 0 0 1mb <= PGA < 2mb 1836 0 0 2mb <= PGA < 4mb 505 0 0 4mb <= PGA < 8mb 218 4 0 8mb <= PGA < 16mb 294 8 0 16mb <= PGA < 32mb 261 16 0
CASEWHENLOW_OPTIMAL_SIZE<1024*1024THENTO_CHAR(LOW_OPTIMAL_SIZE/1024,'999999')||' -------------------------------------------------------------------------------- 32mb <= PGA < 64mb 51 8 0 64mb <= PGA < 128mb 5 6 2 128mb <= PGA < 256mb 0 6 52 256mb <= PGA < 512mb 0 24 59 512mb <= PGA < 1024mb 1 2 0
按PGA 的workarea去分析. 32mb <= PGA < 64mb 51 8 0 表示workarea在32m-64m 之间的在内存里运行的有51次,8次需要用到一次disk,0次多次用到disk. 128mb <= PGA < 256mb 0 6 52,为什么不用MEM而用DISK呢,因为每一个PGA PIECE最多只能是PGA_AGGREGATE的5%(估计),所以大于这部分的就只能用DISK了.
v$pga_target_advice 1 select 2 trunc(pga_target_for_estimate/1024/1024) 3 pga_target_for_estimate, 4 to_char(pga_target_factor * 100,'999.9') ||'%' 5 pga_target_factor, 6 trunc(bytes_processed/1024/1024) bytes_processed, 7 trunc(estd_extra_bytes_rw/1024/1024) estd_extra_bytes_rw, 8 to_char(estd_pga_cache_hit_percentage,'999') || '%' 9 estd_pga_cache_hit_percentage, 10 estd_overalloc_count 11* from v$pga_target_advice SQL> /
PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FAC BYTES_PROCESSED ESTD_EXTRA_BYTES_RW ----------------------- -------------- --------------- ------------------- ESTD_PGA_C ESTD_OVERALLOC_COUNT ---------- -------------------- 147 12.5% 1074286 211218 84% 44 294 25.0% 1074286 180162 86% 0 589 50.0% 1074286 170085 86% 0 884 75.0% 1074286 168992 86% 0 1179 100.0% 1074286 167579 87% 0 1415 120.0% 1074286 51551 95% 0 1651 140.0% 1074286 51551 95% 0 1886 160.0% 1074286 51380 95% 0 2122 180.0% 1074286 51186 95% 0 2358 200.0% 1074286 51186 95% 0 3538 300.0% 1074286 51186 95% 0 4717 400.0% 1074286 51186 95% 0 7076 600.0% 1074286 51186 95% 0 9434 800.0% 1074286 51186 95% 0 可以看到统计数据表现这个库有很大的问题.现在是1179M, estd_pga_cache_hit_percentage=87%,即便将PGA_TARGET扩大到8倍命中率还是不高95%.(会在以后的文章里修改次问题的) 当你重新设置pga_aggregate_target后这个view的数据会重新开始. |
|
|
