-- 1-16 ps -ef|grep niop_ root 25307 16662 5 14:09 ? 00:00:00 /oracle/app/oracle/product/11.1.0/db_1/bin/sqlldr dev_niop_dw/dev_niop_dw@102_db control=../ctrl/TW_EVT_WAP_POST.ctl data=../data/file/3006_34897.txt bad=3006_34897.txt.bad rows=300 bindsize=2560000 readsize=3000000 direct=true silent=header,feedback errors=10000 -- 1-17 chmod -R a+r data_store2/ 有时候,sqlldr调用会出现问题 [2011/01/15 12:20:50 INFO][file 3273_14301.txt has load into Oracle] [2011/01/15 12:20:53 ERROR][system() wrong, command = [$ORACLE_HOME/bin/sqlldr dev_niop_dw/dev_niop_dw@102_db control=../ctrl/TW_EVT_MM3_EOET.ctl data=../data/file/3232_14305.txt bad=3232_14305.txt.bad rows=300 bindsize=2560000 readsize=3000000 direct=true silent=header,feedback errors=10000]] [2011/01/15 12:20:53 WARNING][there is wrong in sqlldr] [2011/01/15 12:20:55 ERROR][system() wrong, command = [$ORACLE_HOME/bin/sqlldr dev_niop_dw/dev_niop_dw@102_db control=../ctrl/TW_EVT_MM3_EOET.ctl data=../data/file/3232_14305.txt bad=3232_14305.txt.bad rows=300 bindsize=2560000 readsize=3000000 direct=true silent=header,feedback errors=10000]] [2011/01/15 12:20:55 WARNING][there is wrong in sqlldr] [2011/01/15 12:21:03 INFO][file 3232_14305.txt has load into Oracle] [2011/01/15 12:21:06 INFO][file 3203_14306.txt has load into Oracle] 出现偶然因素导致的调用错误,3273_14301.txt 接着还是正确处理了 SQL*Loader-941: Error during describe of table TW_EVT_MM3_EOET ORA-04043: object TW_EVT_MM3_EOET does not exist SQL*Loader-941: Error during describe of table TW_EVT_MM3_EOET ORA-04043: object TW_EVT_MM3_EOET does not exist -- 1-17 /etc/cron.allow 中添加用户名,使其可以执行crontab命令 使用crontab的时候需要注意,启动的环境和用户的不一致,需要使用另外一个用户进行调用测试,看是否可以启动 -- 1-19 create table pclint_info( file varchar2(256), line number, type varchar2(16), msg_code varchar2(16), message varchar2(1024), in_time date default sysdate ); LOAD DATA append INTO TABLE pclint_info FIELDS TERMINATED BY '~' TRAILING NULLCOLS ( file, line, type, msg_code, message char(1024) ) ----- oracle@bi0001:~> sqlplus / as sysdba SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 19 19:34:23 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1603411968 bytes Fixed Size 2144824 bytes Variable Size 989857224 bytes Database Buffers 603979776 bytes Redo Buffers 7430144 bytes Database mounted. Database opened. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options oracle@bi0001:~> oracle@bi0001:~> lsnrctl start LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 19-JAN-2011 19:40:18 Copyright (c) 1991, 2007, Oracle. All rights reserved. Starting /oracle/app/oracle/product/11.1.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.1.0.6.0 - Production Log messages written to /opt/oracle/diag/tnslsnr/bi0001/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bi0001.site)(PORT=1521))) Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production Start Date 19-JAN-2011 19:40:18 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /opt/oracle/diag/tnslsnr/bi0001/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bi0001.site)(PORT=1521))) The listener supports no services The command completed successfully oracle@bi0001:~> -- UE 可以录制宏来提高处理速度 -- SQL> alter user system identified by oracle; -- oracle@ATAE1:~/product> export LANG=c# oracle@ATAE1:~/product> dbca insert into student cc values ('mm',1,'17');--cc 是别名,同样在这里写nologging没有任何意义 -- 1-20 sqlldr入库速度变慢了,分析原因,猜想:传入的数据每行数据量变小了,导致相同大小的文件的行数增多,进而影响了 入库性能 -- -- create tablespace NIOP_TS --logging datafile '/home/oracle/oradata/orcl/niop1.dbf' size 3072m autoextend on next 100m maxsize 4096m extent management local; -- create user niop_dw identified by niop_dw default tablespace NIOP_TS; -- select * from all_users; -- grant connect,resource to niop_dw; -- 下午的时候,sqlldr入库程序出现了严重的问题,速度很慢,1MB/s 通过直接执行sqlldr命令 $ORACLE_HOME/bin/sqlldr niop_dw/niop_dw control=TW_EVT_WAP_GET.ctl data=3001_297.txt bad=3001_297.txt.bad rows=300 bindsize=2560000 readsize=3000000 direct=true silent=header,feedback errors=10000 streamsize=25600000 发现速度还很快 比较发现,和程序中调用的有些差别,没有加数据库后缀@114_db 修改后在执行 $ORACLE_HOME/bin/sqlldr niop_dw/niop_dw@114_db control=TW_EVT_WAP_GET.ctl data=3001_297.txt bad=3001_297.txt.bad rows=300 bindsize=2560000 readsize=3000000 direct=true silent=header,feedback errors=10000 streamsize=25600000 发现速度确实很慢 问题就在这个网络的地方 ---- 2011-1-22 测试2台机器间通过sqlldr传输数据的速度 -rw-r--r-- 1 root root 31728903 Jan 12 21:39 3001_297.txt 30MB的文件,用时 Elapsed time was: 00:00:03.86 CPU time was: 00:00:00.62 之前的sqlldr命令中rows指定的太小了,在direct=true时,可以不指定rows -- 1-24 直接使用oci函数进行数据入库,30MB的数据用时4s 18:21:39.404 18:21:43.210 单线程的速度和sqlldr的速度相当 -- 1-25 要给其他用户打开目录的权限需要赋x权限 chmod 755 test 多线程 多进程的输出printf cout 都可以重定向到文件中 -- 1-26 #include <stdio.h> #include <iostream.h> #include <stdlib.h> int main() { char buf[1024] = {0}; FILE* input = NULL; input = fopen("/dev/tty", "r"); if(!input) { fprintf(stderr, "unable to open /dev/tty"); return -1; } fread(buf, 1, 10, input); printf(buf); return 0; } 从后台启动nohup 不能读写终端 -- ulimit -a 查看系统信息,如可同时打开的文件数 -- 1-27 IO性能分析 系统IO空闲的情况下,使用getline一行行读数据速度和一次读取相差不多 使用getline一行行读数据 16:37:14.2 line = 37888 16:37:14.44 17:54:28.108 line = 37888 17:54:28.135 一次读取时 16:59:46.729 31786675 16:59:46.752 #include <fstream.h> #include <fstream> #include <iostream> #include "showtime.h" using namespace std; const int LINE_LEN = 33 * 1024 * 1024; int main(int argc, char** argv) { if (argc < 2) { cout << "please put in the file name" << endl; return -1; } // char buf[LINE_LEN] = {0}; char * buf = new char[LINE_LEN]; ifstream infile; infile.open(argv[1], ios::in); if(!infile) { cout << "open file " << argv[1] << " failed " << endl; return -1; } showSt(); int bsize = 0; infile.read(buf, LINE_LEN); bsize = infile.gcount(); cout << bsize << endl; infile.close(); showEt(); delete [] buf; return 0; } -- 1-28 可以这样定义在同一个文件中 extern int iex; int iex = 0; vmstat 监控系统性能 -- 2-10 case expression when exp1 then result1 when exp2 then result2 else default_result end sql 层次查询 connect by start with to_clob() sql 查询中 all any 有时会很有用