本文共 17571 字，大约阅读时间需要 58 分钟。
[20180320]toad环境中一次fetch等于多少.txt
--//上午重新看了以前写的一篇文章,链接:
--//里面提到toad.PLSQLDev执行sql语句与sqlplus存在小量的不同,好奇心想测试toad下一次fetch是多少.1.环境:
SCOTT@book> @ &r/ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionSCOTT@test01p> create table t as select * from all_objects order by DBMS_RANDOM.random;
Table created.SCOTT@test01p> create index i_t_object_id on t(object_id);
Index created.SCOTT@test01p> select data_object_id,object_id from dba_objects where owner=user and object_name='T';
DATA_OBJECT_ID OBJECT_ID -------------- ---------- 107151 107151--//分析.
execute sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)2.在toad下跟踪sql语句.
--//我在toad,选择execute as script,执行如下: show array --//输出: arraysize 15--//不知道是否就是15.
begin dbms_monitor.session_trace_enable(session_id => 54,serial_num => 137 , waits => true , binds => true);
select * from t; begin dbms_monitor.session_trace_disable(session_id => 54,serial_num => 137);--//注:先执行select * from t;多次避免一些不必要的递归语句执行.另外在执行前选择右键"auto trace".这样避免仅仅fetch 500行的情况.
--//检查转储文件内容:
===================== PARSING IN CURSOR #140244900879464 len=15 dep=0 uid=83 oct=3 lid=83 tim=1521532392894843 hv=1134051363 ad='7c5fc930' sqlid='89km4qj1thh13' select * from t END OF STMT PARSE #140244900879464:c=0,e=96,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1601196873,tim=1521532392894842 WAIT #140244900879464: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=90485 tim=1521532392894952 WAIT #140244900879464: nam='SQL*Net message from client' ela= 1386 driver id=1413697536 #bytes=1 p3=0 obj#=90485 tim=1521532392896418 EXEC #140244900879464:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1601196873,tim=1521532392896549 WAIT #140244900879464: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=90485 tim=1521532392897127 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 32 driver id=1413697536 #bytes=8154 p3=0 obj#=90485 tim=1521532392897366 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 20 driver id=1413697536 #bytes=8144 p3=0 obj#=90485 tim=1521532392897582 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 17 driver id=1413697536 #bytes=8141 p3=0 obj#=90485 tim=1521532392897819 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 22 driver id=1413697536 #bytes=8150 p3=0 obj#=90485 tim=1521532392898066 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 20 driver id=1413697536 #bytes=8141 p3=0 obj#=90485 tim=1521532392898300 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 19 driver id=1413697536 #bytes=8151 p3=0 obj#=90485 tim=1521532392898527 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 33 driver id=1413697536 #bytes=8138 p3=0 obj#=90485 tim=1521532392898776 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 32 driver id=1413697536 #bytes=8141 p3=0 obj#=90485 tim=1521532392899015 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 20 driver id=1413697536 #bytes=8161 p3=0 obj#=90485 tim=1521532392899230 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 21 driver id=1413697536 #bytes=8146 p3=0 obj#=90485 tim=1521532392899499 FETCH #140244900879464:c=3000,e=2977,p=28,cr=17,cu=0,mis=0,r=1001,dep=0,og=1,plh=1601196873,tim=1521532392899589 WAIT #140244900879464: nam='SQL*Net message from client' ela= 192337 driver id=1413697536 #bytes=1 p3=0 obj#=90485 tim=1521532393091994 WAIT #140244900879464: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=90485 tim=1521532393092110 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 33 driver id=1413697536 #bytes=8147 p3=0 obj#=90485 tim=1521532393092334 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 23 driver id=1413697536 #bytes=8147 p3=0 obj#=90485 tim=1521532393092552 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 20 driver id=1413697536 #bytes=8144 p3=0 obj#=90485 tim=1521532393092768 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 33 driver id=1413697536 #bytes=8147 p3=0 obj#=90485 tim=1521532393093192 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 21 driver id=1413697536 #bytes=8146 p3=0 obj#=90485 tim=1521532393093401 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 20 driver id=1413697536 #bytes=8147 p3=0 obj#=90485 tim=1521532393093620 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 20 driver id=1413697536 #bytes=8144 p3=0 obj#=90485 tim=1521532393093842 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 20 driver id=1413697536 #bytes=8146 p3=0 obj#=90485 tim=1521532393094084 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 30 driver id=1413697536 #bytes=8142 p3=0 obj#=90485 tim=1521532393094309 WAIT #140244900879464: nam='SQL*Net more data to client' ela= 21 driver id=1413697536 #bytes=8140 p3=0 obj#=90485 tim=1521532393094610 FETCH #140244900879464:c=3000,e=2565,p=30,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393094649 ... --//第1次r=1001,以后都是1000.$ egrep "FETCH #140244900879464" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_15945.trc
FETCH #140244900879464:c=3000,e=2977,p=28,cr=17,cu=0,mis=0,r=1001,dep=0,og=1,plh=1601196873,tim=1521532392899589 FETCH #140244900879464:c=3000,e=2565,p=30,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393094649 FETCH #140244900879464:c=3000,e=2300,p=0,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393102406 FETCH #140244900879464:c=1998,e=2479,p=15,cr=16,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393109269 FETCH #140244900879464:c=3000,e=2389,p=15,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393116160 FETCH #140244900879464:c=2000,e=2429,p=15,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393123639 FETCH #140244900879464:c=2000,e=2361,p=7,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393130497 FETCH #140244900879464:c=3999,e=3473,p=134,cr=16,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393138503 FETCH #140244900879464:c=2999,e=3536,p=126,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393146483 FETCH #140244900879464:c=2000,e=2341,p=0,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393153398 FETCH #140244900879464:c=2999,e=2391,p=0,cr=16,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393160251 FETCH #140244900879464:c=2000,e=2305,p=0,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393167181 FETCH #140244900879464:c=3000,e=2306,p=0,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393174194 FETCH #140244900879464:c=2000,e=2308,p=0,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393181731 FETCH #140244900879464:c=2999,e=2296,p=0,cr=16,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393188953 FETCH #140244900879464:c=2000,e=2317,p=0,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393195957 FETCH #140244900879464:c=2999,e=2296,p=0,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393202949 FETCH #140244900879464:c=3000,e=2770,p=126,cr=16,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393210398 .... FETCH #140244900879464:c=2999,e=2290,p=0,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393632623 FETCH #140244900879464:c=2000,e=2394,p=0,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393639568 FETCH #140244900879464:c=3000,e=2336,p=0,cr=16,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393646568 FETCH #140244900879464:c=1999,e=2321,p=0,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393653672 FETCH #140244900879464:c=3000,e=2350,p=0,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393660515 FETCH #140244900879464:c=2000,e=2308,p=0,cr=16,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393667897 FETCH #140244900879464:c=1999,e=2292,p=0,cr=15,cu=0,mis=0,r=1000,dep=0,og=1,plh=1601196873,tim=1521532393674933 FETCH #140244900879464:c=2000,e=1846,p=0,cr=12,cu=0,mis=0,r=760,dep=0,og=1,plh=1601196873,tim=1521532393681423--//检查记录总数:
SCOTT@book> select count(*) from t; COUNT(*) ---------- 84761$ egrep "FETCH #140244900879464" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_15945.trc|wc
85 170 9541--//83*1000+1001+760 = 84761,正好一致.toad工具与sqlplus在fetch上存在不同.这样可以推断一般情况下语句toad下执行,
--//逻辑读少1个.--//在sqlplus下:
alter session set statistics_level=all; set array 1000 select * from emp;Plan hash value: 3956160932
-------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 14 |00:00:00.01 | 7 | | 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 7 | -------------------------------------------------------------------------------------------------------------------- --//逻辑读是6个.--//在toad下执行:
alter session set statistics_level=all;
set array 1000 Select /*+aaaaaa */ * from emp;--//查询字符串aaaaa,定位sql_id=0uqyfh37y1khk.
SCOTT@book> @ &r/dpc 0uqyfh37y1khk ''
PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 0uqyfh37y1khk, child number 0 ------------------------------------- Select /*+aaaaaa */ * from emp Plan hash value: 3956160932 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 14 |00:00:00.01 | 6 | | 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 | --------------------------------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------1 - SEL$1 / EMP@SEL$1
select * from v$sql where sql_id='0uqyfh37y1khk';
Record View
As of: 2018/3/20 16:04:30SQL_TEXT: Select /*+aaaaaa */ * from emp
SQL_FULLTEXT: Select /*+aaaaaa */ * from emp SQL_ID: 0uqyfh37y1khk SHARABLE_MEM: 19703 PERSISTENT_MEM: 6032 RUNTIME_MEM: 4328 SORTS: 0 LOADED_VERSIONS: 1 OPEN_VERSIONS: 0 USERS_OPENING: 0 FETCHES: 1 EXECUTIONS: 1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ PX_SERVERS_EXECUTIONS: 0 END_OF_FETCH_COUNT: 1 USERS_EXECUTING: 0 LOADS: 1 FIRST_LOAD_TIME: 2018-03-20/16:03:29 INVALIDATIONS: 0 PARSE_CALLS: 1 DISK_READS: 0 DIRECT_WRITES: 0 BUFFER_GETS: 6 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ APPLICATION_WAIT_TIME: 0 CONCURRENCY_WAIT_TIME: 0 CLUSTER_WAIT_TIME: 0 USER_IO_WAIT_TIME: 0 PLSQL_EXEC_TIME: 0 JAVA_EXEC_TIME: 0 ROWS_PROCESSED: 14 COMMAND_TYPE: 3 OPTIMIZER_MODE: ALL_ROWS OPTIMIZER_COST: 3 OPTIMIZER_ENV: E289FB89E426A8004E011000AEF9C3E2CFFA335056414555519521105555551545545558591556449665851D5511058555555155515122555415A0EA0C5551454265455454449081566E001696C6A35545150102541550441615579110A8456E820A508021000020000000000100001000000002002080007D000000000004C0CCCC001010000080830F4000006066660000000002406E820A50464626202040262320030020003020A0A05050A04001200000401F000000A5A0A0A040863E000040060C382000200000F0FF0F000083210304000200403600 OPTIMIZER_ENV_HASH_VALUE: 4224879066 PARSING_USER_ID: 83 PARSING_SCHEMA_ID: 83 PARSING_SCHEMA_NAME: SCOTT KEPT_VERSIONS: 0 ADDRESS: 000000007D140228 TYPE_CHK_HEAP: 00 HASH_VALUE: 3487615506 OLD_HASH_VALUE: 1453398800 PLAN_HASH_VALUE: 3956160932 CHILD_NUMBER: 0 SERVICE: SYS$USERS SERVICE_HASH: 0 MODULE: TOAD 12.0.0.61 MODULE_HASH: -2129441675 ACTION: ACTION_HASH: 0 SERIALIZABLE_ABORTS: 0 OUTLINE_CATEGORY: CPU_TIME: 1999 ELAPSED_TIME: 1615 OUTLINE_SID: CHILD_ADDRESS: 000000007E18BC80 SQLTYPE: 6 REMOTE: N OBJECT_STATUS: VALID LITERAL_HASH_VALUE: 0 LAST_LOAD_TIME: 2018-03-20/16:03:29 IS_OBSOLETE: N IS_BIND_SENSITIVE: N IS_BIND_AWARE: N IS_SHAREABLE: Y CHILD_LATCH: 0 SQL_PROFILE: SQL_PATCH: SQL_PLAN_BASELINE: PROGRAM_ID: 0 PROGRAM_LINE#: 0 EXACT_MATCHING_SIGNATURE: 14172186495060009189 FORCE_MATCHING_SIGNATURE: 14172186495060009189 LAST_ACTIVE_TIME: 2018/3/20 16:03:29 BIND_DATA: TYPECHECK_MEM: 0 IO_CELL_OFFLOAD_ELIGIBLE_BYTES: 0 IO_INTERCONNECT_BYTES: 0 PHYSICAL_READ_REQUESTS: 0 PHYSICAL_READ_BYTES: 0 PHYSICAL_WRITE_REQUESTS: 0 PHYSICAL_WRITE_BYTES: 0 OPTIMIZED_PHY_READ_REQUESTS: 0 LOCKED_TOTAL: 1 PINNED_TOTAL: 2 IO_CELL_UNCOMPRESSED_BYTES: 0 IO_CELL_OFFLOAD_RETURNED_BYTES: 0--//BUFFER_GETS:6.说明toad与sqlplus在fetch不同.
3.测试toad下不打开auto trace的情况,从显示上fetch=500,看看是否一致:
=====================
PARSING IN CURSOR #139777480832064 len=15 dep=0 uid=83 oct=3 lid=83 tim=1521533343854177 hv=2195969672 ad='7b5c7a50' sqlid='5y59up61f7pn8' Select * from t END OF STMT PARSE #139777480832064:c=22996,e=22741,p=1,cr=98,cu=0,mis=1,r=0,dep=0,og=1,plh=1601196873,tim=1521533343854175 WAIT #139777480832064: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1521533343854316 WAIT #139777480832064: nam='SQL*Net message from client' ela= 1846 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1521533343856234 EXEC #139777480832064:c=1000,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1601196873,tim=1521533343856364 WAIT #139777480832064: nam='Disk file operations I/O' ela= 37 FileOperation=2 fileno=4 filetype=2 obj#=90485 tim=1521533343856773 WAIT #139777480832064: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=90485 tim=1521533343857163 WAIT #139777480832064: nam='SQL*Net more data to client' ela= 33 driver id=1413697536 #bytes=8154 p3=0 obj#=90485 tim=1521533343857408 WAIT #139777480832064: nam='SQL*Net more data to client' ela= 14 driver id=1413697536 #bytes=8144 p3=0 obj#=90485 tim=1521533343857616 WAIT #139777480832064: nam='SQL*Net more data to client' ela= 17 driver id=1413697536 #bytes=8141 p3=0 obj#=90485 tim=1521533343857889 WAIT #139777480832064: nam='SQL*Net more data to client' ela= 38 driver id=1413697536 #bytes=8150 p3=0 obj#=90485 tim=1521533343858147 WAIT #139777480832064: nam='SQL*Net more data to client' ela= 19 driver id=1413697536 #bytes=8141 p3=0 obj#=90485 tim=1521533343858381 WAIT #139777480832064: nam='SQL*Net more data to client' ela= 19 driver id=1413697536 #bytes=8151 p3=0 obj#=90485 tim=1521533343858603 WAIT #139777480832064: nam='SQL*Net more data to client' ela= 21 driver id=1413697536 #bytes=8138 p3=0 obj#=90485 tim=1521533343858847 WAIT #139777480832064: nam='SQL*Net more data to client' ela= 21 driver id=1413697536 #bytes=8141 p3=0 obj#=90485 tim=1521533343859087 WAIT #139777480832064: nam='SQL*Net more data to client' ela= 19 driver id=1413697536 #bytes=8161 p3=0 obj#=90485 tim=1521533343859313 WAIT #139777480832064: nam='SQL*Net more data to client' ela= 22 driver id=1413697536 #bytes=8146 p3=0 obj#=90485 tim=1521533343859590 FETCH #139777480832064:c=3000,e=3213,p=28,cr=17,cu=0,mis=0,r=1001,dep=0,og=1,plh=1601196873,tim=1521533343859647 WAIT #139777480832064: nam='SQL*Net message from client' ela= 119119 driver id=1413697536 #bytes=1 p3=0 obj#=90485 tim=1521533343978817 =====================--//fetch=1001.
4.顺便看看PLSQLDev的情况:
--//测试结果应该是100.$ grep "FETCH #139965460872664" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_16109.trc
FETCH #139965460872664:c=1000,e=695,p=20,cr=4,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=1521533779598271 FETCH #139965460872664:c=0,e=307,p=0,cr=2,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=1521533782180292 FETCH #139965460872664:c=1000,e=279,p=0,cr=3,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=1521533782206909 FETCH #139965460872664:c=0,e=290,p=0,cr=2,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=1521533782224681 FETCH #139965460872664:c=0,e=232,p=0,cr=3,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=1521533782243138 FETCH #139965460872664:c=0,e=282,p=0,cr=2,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=1521533782261461 FETCH #139965460872664:c=999,e=290,p=0,cr=3,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=1521533782279416 FETCH #139965460872664:c=0,e=280,p=0,cr=2,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=1521533782296539 ... FETCH #139965460872664:c=0,e=282,p=0,cr=3,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=1521533797337787 FETCH #139965460872664:c=0,e=280,p=0,cr=2,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=1521533797357360 FETCH #139965460872664:c=0,e=282,p=0,cr=3,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=1521533797376466 FETCH #139965460872664:c=1000,e=701,p=0,cr=2,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=1521533797397126 FETCH #139965460872664:c=0,e=285,p=0,cr=3,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=1521533797418411 FETCH #139965460872664:c=0,e=286,p=0,cr=2,cu=0,mis=0,r=100,dep=0,og=1,plh=1601196873,tim=1521533797431384 FETCH #139965460872664:c=0,e=198,p=0,cr=2,cu=0,mis=0,r=61,dep=0,og=1,plh=1601196873,tim=1521533797456034$ grep "FETCH #139965460872664" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_16109.trc |wc
848 1696 90671--//847*100+61 = 84761,也与总记录一致.
--//不过PLSQLDev也与toad不同前面每次都是100. --//不知道这些参数是否可以修改,不熟悉toad以及PLSQLDev这方面内容.转载地址：http://hxkdl.baihongyu.com/