API tools faq
paste
Guest User

TraceFileAnalizedResult

a guest
Aug 13th, 2018
326
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 28.51 KB | None | 0 0
raw download clone embed print report
  1.  
  2. TKPROF: Release 12.2.0.1.0 - Development on Mon Aug 13 19:54:55 2018
  3.  
  4. Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
  5.  
  6. Trace file: C:\Users\admin\Desktop\orcl_ora_5560_SINGLESQLTRACE.trc
  7. Sort options: default
  8.  
  9. ********************************************************************************
  10. count = number of times OCI procedure was executed
  11. cpu = cpu time in seconds executing
  12. elapsed = elapsed time in seconds executing
  13. disk = number of physical reads of buffers from disk
  14. query = number of buffers gotten for consistent read
  15. current = number of buffers gotten in current mode (usually for update)
  16. rows = number of rows processed by the fetch or execute call
  17. ********************************************************************************
  18.  
  19. SQL ID: 3nkd3g3ju5ph1 Plan Hash: 2853959010
  20.  
  21. select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1,
  22. spare2
  23. from
  24. obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
  25. and linkname is null and subname is null
  26.  
  27.  
  28. call count cpu elapsed disk query current rows
  29. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  30. Parse 1 0.00 0.00 0 0 0 0
  31. Execute 2 0.00 0.00 0 0 0 0
  32. Fetch 2 0.00 0.00 1 8 0 2
  33. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  34. total 5 0.00 0.00 1 8 0 2
  35.  
  36. Misses in library cache during parse: 1
  37. Misses in library cache during execute: 1
  38. Optimizer mode: CHOOSE
  39. Parsing user id: SYS (recursive depth: 1)
  40. Number of plan statistics captured: 1
  41.  
  42. Rows (1st) Rows (avg) Rows (max) Row Source Operation
  43. ---------- ---------- ---------- ---------------------------------------------------
  44. 1 1 1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=4 pr=1 pw=0 time=4 us starts=0 cost=4 size=83 card=1)
  45. 1 1 1 INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=3 us starts=0 cost=3 size=0 card=1)(object id 37)
  46.  
  47. ********************************************************************************
  48.  
  49. SQL ID: 1gu8t96d0bdmu Plan Hash: 2035254952
  50.  
  51. select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,
  52. nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,
  53. t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,
  54. t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),
  55. nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),
  56. nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,
  57. ts.logicalread
  58. from
  59. tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
  60.  
  61.  
  62. call count cpu elapsed disk query current rows
  63. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  64. Parse 1 0.00 0.00 0 0 0 0
  65. Execute 2 0.00 0.00 0 0 0 0
  66. Fetch 1 0.00 0.00 2 4 0 1
  67. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  68. total 4 0.00 0.00 2 4 0 1
  69.  
  70. Misses in library cache during parse: 1
  71. Misses in library cache during execute: 1
  72. Optimizer mode: CHOOSE
  73. Parsing user id: SYS (recursive depth: 1)
  74. Number of plan statistics captured: 1
  75.  
  76. Rows (1st) Rows (avg) Rows (max) Row Source Operation
  77. ---------- ---------- ---------- ---------------------------------------------------
  78. 1 1 1 MERGE JOIN OUTER (cr=4 pr=2 pw=0 time=2 us starts=0 cost=2 size=189 card=1)
  79. 1 1 1 TABLE ACCESS CLUSTER TAB$ (cr=3 pr=2 pw=0 time=2 us starts=0 cost=2 size=137 card=1)
  80. 1 1 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=1 pw=0 time=1 us starts=0 cost=1 size=0 card=1)(object id 3)
  81. 0 0 0 BUFFER SORT (cr=1 pr=0 pw=0 time=0 us starts=0 cost=0 size=52 card=1)
  82. 0 0 0 TABLE ACCESS BY INDEX ROWID TAB_STATS$ (cr=1 pr=0 pw=0 time=0 us starts=0 cost=0 size=52 card=1)
  83. 0 0 0 INDEX UNIQUE SCAN I_TAB_STATS$_OBJ# (cr=1 pr=0 pw=0 time=0 us starts=0 cost=0 size=0 card=1)(object id 430)
  84.  
  85. ********************************************************************************
  86.  
  87. SQL ID: 7ng34ruy5awxq Plan Hash: 3992920156
  88.  
  89. select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,
  90. i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,
  91. i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
  92. nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
  93. i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
  94. nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,
  95. null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,
  96. ist.logicalread
  97. from
  98. ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,
  99. min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4)))
  100. valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where
  101. i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
  102.  
  103.  
  104. call count cpu elapsed disk query current rows
  105. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  106. Parse 1 0.00 0.00 0 0 0 0
  107. Execute 1 0.01 0.00 0 0 0 0
  108. Fetch 2 0.00 0.00 1 7 0 1
  109. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  110. total 4 0.01 0.00 1 7 0 1
  111.  
  112. Misses in library cache during parse: 1
  113. Misses in library cache during execute: 1
  114. Optimizer mode: CHOOSE
  115. Parsing user id: SYS (recursive depth: 1)
  116. Number of plan statistics captured: 1
  117.  
  118. Rows (1st) Rows (avg) Rows (max) Row Source Operation
  119. ---------- ---------- ---------- ---------------------------------------------------
  120. 1 1 1 SORT ORDER BY (cr=7 pr=1 pw=0 time=7 us starts=0 cost=7 size=374 card=2)
  121. 1 1 1 HASH JOIN OUTER (cr=7 pr=1 pw=0 time=6 us starts=0 cost=6 size=374 card=2)
  122. 1 1 1 NESTED LOOPS OUTER (cr=4 pr=0 pw=0 time=2 us starts=0 cost=2 size=288 card=2)
  123. 1 1 1 TABLE ACCESS CLUSTER IND$ (cr=3 pr=0 pw=0 time=2 us starts=0 cost=2 size=184 card=2)
  124. 1 1 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=1 us starts=0 cost=1 size=0 card=1)(object id 3)
  125. 0 0 0 TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=1 pr=0 pw=0 time=0 us starts=0 cost=0 size=52 card=1)
  126. 0 0 0 INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=1 pr=0 pw=0 time=0 us starts=0 cost=0 size=0 card=1)(object id 432)
  127. 1 1 1 VIEW (cr=3 pr=1 pw=0 time=3 us starts=0 cost=3 size=43 card=1)
  128. 1 1 1 SORT GROUP BY (cr=3 pr=1 pw=0 time=3 us starts=0 cost=3 size=15 card=1)
  129. 1 1 1 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=3 pr=1 pw=0 time=2 us starts=0 cost=2 size=15 card=1)
  130. 6 6 6 INDEX RANGE SCAN I_CDEF2 (cr=2 pr=0 pw=0 time=1 us starts=5 cost=1 size=0 card=4)(object id 54)
  131.  
  132. ********************************************************************************
  133.  
  134. SQL ID: 5n1fs4m2n2y0r Plan Hash: 299250003
  135.  
  136. select pos#,intcol#,col#,spare1,bo#,spare2,spare3
  137. from
  138. icol$ where obj#=:1
  139.  
  140.  
  141. call count cpu elapsed disk query current rows
  142. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  143. Parse 1 0.00 0.00 0 0 0 0
  144. Execute 1 0.00 0.00 0 0 0 0
  145. Fetch 6 0.00 0.00 1 12 0 5
  146. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  147. total 8 0.00 0.00 1 12 0 5
  148.  
  149. Misses in library cache during parse: 1
  150. Misses in library cache during execute: 1
  151. Optimizer mode: CHOOSE
  152. Parsing user id: SYS (recursive depth: 1)
  153. Number of plan statistics captured: 1
  154.  
  155. Rows (1st) Rows (avg) Rows (max) Row Source Operation
  156. ---------- ---------- ---------- ---------------------------------------------------
  157. 5 5 5 TABLE ACCESS BY INDEX ROWID ICOL$ (cr=12 pr=1 pw=0 time=2 us starts=0 cost=2 size=54 card=2)
  158. 5 5 5 INDEX RANGE SCAN I_ICOL1 (cr=7 pr=1 pw=0 time=1 us starts=12 cost=1 size=0 card=2)(object id 42)
  159.  
  160. ********************************************************************************
  161.  
  162. SQL ID: 83taa7kaw59c1 Plan Hash: 3765558045
  163.  
  164. select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
  165. nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
  166. scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
  167. rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
  168. nvl(spare3,0)
  169. from
  170. col$ where obj#=:1 order by intcol#
  171.  
  172.  
  173. call count cpu elapsed disk query current rows
  174. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  175. Parse 1 0.00 0.00 0 0 0 0
  176. Execute 1 0.00 0.00 0 0 0 0
  177. Fetch 13 0.00 0.00 0 3 0 12
  178. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  179. total 15 0.00 0.00 0 3 0 12
  180.  
  181. Misses in library cache during parse: 1
  182. Misses in library cache during execute: 1
  183. Optimizer mode: CHOOSE
  184. Parsing user id: SYS (recursive depth: 1)
  185. Number of plan statistics captured: 1
  186.  
  187. Rows (1st) Rows (avg) Rows (max) Row Source Operation
  188. ---------- ---------- ---------- ---------------------------------------------------
  189. 12 12 12 SORT ORDER BY (cr=3 pr=0 pw=0 time=3 us starts=0 cost=3 size=708 card=12)
  190. 12 12 12 TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 time=2 us starts=0 cost=2 size=708 card=12)
  191. 1 1 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=1 us starts=0 cost=1 size=0 card=1)(object id 3)
  192.  
  193. ********************************************************************************
  194.  
  195. SQL ID: 9tgj4g8y4rwy8 Plan Hash: 3755742892
  196.  
  197. select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,
  198. NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),
  199. NVL(scanhint,0),NVL(bitmapranges,0)
  200. from
  201. seg$ where ts#=:1 and file#=:2 and block#=:3
  202.  
  203.  
  204. call count cpu elapsed disk query current rows
  205. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  206. Parse 2 0.00 0.00 0 0 0 0
  207. Execute 2 0.00 0.00 0 0 0 0
  208. Fetch 2 0.01 0.00 2 6 0 2
  209. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  210. total 6 0.01 0.00 2 6 0 2
  211.  
  212. Misses in library cache during parse: 1
  213. Misses in library cache during execute: 1
  214. Optimizer mode: CHOOSE
  215. Parsing user id: SYS (recursive depth: 1)
  216. Number of plan statistics captured: 2
  217.  
  218. Rows (1st) Rows (avg) Rows (max) Row Source Operation
  219. ---------- ---------- ---------- ---------------------------------------------------
  220. 1 1 1 TABLE ACCESS CLUSTER SEG$ (cr=3 pr=1 pw=0 time=2 us starts=0 cost=2 size=68 card=1)
  221. 1 1 1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=1 pw=0 time=1 us starts=0 cost=1 size=0 card=1)(object id 9)
  222.  
  223. ********************************************************************************
  224.  
  225. SQL ID: 87gaftwrm2h68 Plan Hash: 1218588913
  226.  
  227. select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname
  228. from
  229. obj$ o where o.obj#=:1
  230.  
  231.  
  232. call count cpu elapsed disk query current rows
  233. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  234. Parse 1 0.00 0.00 0 0 0 0
  235. Execute 1 0.00 0.00 0 0 0 0
  236. Fetch 1 0.00 0.00 1 3 0 1
  237. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  238. total 3 0.00 0.00 1 3 0 1
  239.  
  240. Misses in library cache during parse: 1
  241. Misses in library cache during execute: 1
  242. Optimizer mode: CHOOSE
  243. Parsing user id: SYS (recursive depth: 1)
  244. Number of plan statistics captured: 1
  245.  
  246. Rows (1st) Rows (avg) Rows (max) Row Source Operation
  247. ---------- ---------- ---------- ---------------------------------------------------
  248. 1 1 1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=1 pw=0 time=3 us starts=0 cost=3 size=83 card=1)
  249. 1 1 1 INDEX RANGE SCAN I_OBJ1 (cr=2 pr=1 pw=0 time=2 us starts=0 cost=2 size=0 card=1)(object id 36)
  250.  
  251. ********************************************************************************
  252.  
  253. SQL ID: 6aq34nj2zb2n7 Plan Hash: 2874733959
  254.  
  255. select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
  256. from
  257. objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
  258. grantee# order by col#, grantee#
  259.  
  260.  
  261. call count cpu elapsed disk query current rows
  262. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  263. Parse 1 0.00 0.00 0 0 0 0
  264. Execute 1 0.00 0.00 0 0 0 0
  265. Fetch 1 0.00 0.00 1 2 0 0
  266. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  267. total 3 0.00 0.00 1 2 0 0
  268.  
  269. Misses in library cache during parse: 1
  270. Misses in library cache during execute: 1
  271. Optimizer mode: CHOOSE
  272. Parsing user id: SYS (recursive depth: 1)
  273. Number of plan statistics captured: 1
  274.  
  275. Rows (1st) Rows (avg) Rows (max) Row Source Operation
  276. ---------- ---------- ---------- ---------------------------------------------------
  277. 0 0 0 SORT GROUP BY (cr=2 pr=1 pw=0 time=4 us starts=0 cost=4 size=15 card=1)
  278. 0 0 0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=1 pw=0 time=3 us starts=0 cost=3 size=15 card=1)
  279. 0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=1 pw=0 time=2 us starts=0 cost=2 size=0 card=1)(object id 62)
  280.  
  281. ********************************************************************************
  282.  
  283. SQL ID: 2q93zsrvbdw48 Plan Hash: 2874733959
  284.  
  285. select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
  286. from
  287. objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by
  288. grantee#
  289.  
  290.  
  291. call count cpu elapsed disk query current rows
  292. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  293. Parse 1 0.00 0.00 0 0 0 0
  294. Execute 1 0.00 0.00 0 0 0 0
  295. Fetch 1 0.00 0.00 0 2 0 0
  296. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  297. total 3 0.00 0.00 0 2 0 0
  298.  
  299. Misses in library cache during parse: 1
  300. Misses in library cache during execute: 1
  301. Optimizer mode: CHOOSE
  302. Parsing user id: SYS (recursive depth: 1)
  303. Number of plan statistics captured: 1
  304.  
  305. Rows (1st) Rows (avg) Rows (max) Row Source Operation
  306. ---------- ---------- ---------- ---------------------------------------------------
  307. 0 0 0 SORT GROUP BY (cr=2 pr=0 pw=0 time=4 us starts=0 cost=4 size=15 card=1)
  308. 0 0 0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=0 pw=0 time=3 us starts=0 cost=3 size=15 card=1)
  309. 0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=2 us starts=0 cost=2 size=0 card=1)(object id 62)
  310.  
  311. ********************************************************************************
  312.  
  313. SQL ID: 3w4qs0tbpmxr6 Plan Hash: 1224215794
  314.  
  315. select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3
  316. from
  317. cdef$ where robj#=:1
  318.  
  319.  
  320. call count cpu elapsed disk query current rows
  321. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  322. Parse 1 0.00 0.00 0 0 0 0
  323. Execute 1 0.00 0.00 0 0 0 0
  324. Fetch 1 0.00 0.00 0 2 0 0
  325. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  326. total 3 0.00 0.00 0 2 0 0
  327.  
  328. Misses in library cache during parse: 1
  329. Misses in library cache during execute: 1
  330. Optimizer mode: CHOOSE
  331. Parsing user id: SYS (recursive depth: 1)
  332. Number of plan statistics captured: 1
  333.  
  334. Rows (1st) Rows (avg) Rows (max) Row Source Operation
  335. ---------- ---------- ---------- ---------------------------------------------------
  336. 0 0 0 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=2 pr=0 pw=0 time=2 us starts=0 cost=2 size=84 card=3)
  337. 0 0 0 INDEX RANGE SCAN I_CDEF3 (cr=2 pr=0 pw=0 time=1 us starts=0 cost=1 size=0 card=1)(object id 55)
  338.  
  339. ********************************************************************************
  340.  
  341. SQL ID: gx4mv66pvj3xz Plan Hash: 1932954096
  342.  
  343. select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
  344. rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3
  345. from
  346. cdef$ where obj#=:1
  347.  
  348.  
  349. call count cpu elapsed disk query current rows
  350. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  351. Parse 1 0.00 0.00 0 0 0 0
  352. Execute 1 0.00 0.00 0 0 0 0
  353. Fetch 7 0.00 0.00 0 14 0 6
  354. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  355. total 9 0.00 0.00 0 14 0 6
  356.  
  357. Misses in library cache during parse: 1
  358. Misses in library cache during execute: 1
  359. Optimizer mode: CHOOSE
  360. Parsing user id: SYS (recursive depth: 1)
  361. Number of plan statistics captured: 1
  362.  
  363. Rows (1st) Rows (avg) Rows (max) Row Source Operation
  364. ---------- ---------- ---------- ---------------------------------------------------
  365. 6 6 6 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=14 pr=0 pw=0 time=2 us starts=0 cost=2 size=192 card=4)
  366. 6 6 6 INDEX RANGE SCAN I_CDEF2 (cr=8 pr=0 pw=0 time=1 us starts=25 cost=1 size=0 card=4)(object id 54)
  367.  
  368. ********************************************************************************
  369.  
  370. SQL ID: 53saa2zkr6wc3 Plan Hash: 3954488388
  371.  
  372. select intcol#,nvl(pos#,0),col#,nvl(spare1,0)
  373. from
  374. ccol$ where con#=:1
  375.  
  376.  
  377. call count cpu elapsed disk query current rows
  378. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  379. Parse 1 0.00 0.00 0 0 0 0
  380. Execute 6 0.00 0.00 0 0 0 0
  381. Fetch 16 0.00 0.00 1 32 0 10
  382. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  383. total 23 0.00 0.00 1 32 0 10
  384.  
  385. Misses in library cache during parse: 1
  386. Misses in library cache during execute: 1
  387. Optimizer mode: CHOOSE
  388. Parsing user id: SYS (recursive depth: 1)
  389. Number of plan statistics captured: 1
  390.  
  391. Rows (1st) Rows (avg) Rows (max) Row Source Operation
  392. ---------- ---------- ---------- ---------------------------------------------------
  393. 1 1 1 TABLE ACCESS BY INDEX ROWID CCOL$ (cr=4 pr=1 pw=0 time=3 us starts=0 cost=3 size=16 card=1)
  394. 1 1 1 INDEX RANGE SCAN I_CCOL1 (cr=3 pr=1 pw=0 time=2 us starts=0 cost=2 size=0 card=1)(object id 57)
  395.  
  396. ********************************************************************************
  397.  
  398. SQL ID: 96g93hntrzjtr Plan Hash: 2239883476
  399.  
  400. select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
  401. sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
  402. spare1, spare2, avgcln
  403. from
  404. hist_head$ where obj#=:1 and intcol#=:2
  405.  
  406.  
  407. call count cpu elapsed disk query current rows
  408. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  409. Parse 1 0.00 0.00 0 0 0 0
  410. Execute 12 0.00 0.00 0 0 0 0
  411. Fetch 12 0.00 0.00 2 36 0 12
  412. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  413. total 25 0.00 0.00 2 36 0 12
  414.  
  415. Misses in library cache during parse: 1
  416. Misses in library cache during execute: 1
  417. Optimizer mode: RULE
  418. Parsing user id: SYS (recursive depth: 1)
  419. Number of plan statistics captured: 1
  420.  
  421. Rows (1st) Rows (avg) Rows (max) Row Source Operation
  422. ---------- ---------- ---------- ---------------------------------------------------
  423. 1 1 1 TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=3 pr=2 pw=0 time=0 us starts=0)
  424. 1 1 1 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=1 pw=0 time=0 us starts=0)(object id 426)
  425.  
  426. ********************************************************************************
  427.  
  428. SQL ID: db78fxqxwxt7r Plan Hash: 3312420081
  429.  
  430. select /*+ rule */ bucket, endpoint, col#, epvalue
  431. from
  432. histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
  433.  
  434.  
  435. call count cpu elapsed disk query current rows
  436. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  437. Parse 1 0.01 0.00 0 0 0 0
  438. Execute 8 0.00 0.00 0 0 0 0
  439. Fetch 8 0.03 0.15 6 24 0 94
  440. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  441. total 17 0.04 0.15 6 24 0 94
  442.  
  443. Misses in library cache during parse: 1
  444. Misses in library cache during execute: 1
  445. Optimizer mode: RULE
  446. Parsing user id: SYS (recursive depth: 1)
  447. Number of plan statistics captured: 1
  448.  
  449. Rows (1st) Rows (avg) Rows (max) Row Source Operation
  450. ---------- ---------- ---------- ---------------------------------------------------
  451. 20 20 20 SORT ORDER BY (cr=3 pr=2 pw=0 time=0 us starts=0 cost=0 size=0 card=0)
  452. 20 20 20 TABLE ACCESS CLUSTER HISTGRM$ (cr=3 pr=2 pw=0 time=0 us starts=19)
  453. 1 1 1 INDEX UNIQUE SCAN I_OBJ#_INTCOL# (cr=2 pr=1 pw=0 time=0 us starts=0)(object id 421)
  454.  
  455. ********************************************************************************
  456.  
  457. SQL ID: 9jpk7931105mh Plan Hash: 1282975746
  458.  
  459. select *
  460. from
  461. TestTable
  462.  
  463.  
  464. call count cpu elapsed disk query current rows
  465. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  466. Parse 1 0.01 0.01 0 0 0 0
  467. Execute 1 0.00 0.00 0 0 0 0
  468. Fetch 1 2.92 28.96 7427 7436 0 74
  469. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  470. total 3 2.93 28.97 7427 7436 0 74
  471.  
  472. Misses in library cache during parse: 1
  473. Optimizer mode: ALL_ROWS
  474. Parsing user id: 92
  475. Number of plan statistics captured: 1
  476.  
  477. Rows (1st) Rows (avg) Rows (max) Row Source Operation
  478. ---------- ---------- ---------- ---------------------------------------------------
  479. 74 74 74 TABLE ACCESS FULL TestTable (cr=7436 pr=7427 pw=0 time=2017 us starts=73 cost=2017 size=6734 card=74)
  480.  
  481. ********************************************************************************
  482.  
  483. declare
  484.  
  485. l_line varchar2(32767);
  486.  
  487. l_done number;
  488.  
  489. l_buffer varchar2(32767) := '';
  490.  
  491. l_lengthbuffer number := 0;
  492.  
  493. l_lengthline number := 0;
  494.  
  495. begin
  496.  
  497. loop
  498.  
  499. dbms_output.get_line( l_line, l_done );
  500.  
  501. if (l_buffer is null) then
  502.  
  503. l_lengthbuffer := 0;
  504.  
  505. else
  506.  
  507. l_lengthbuffer := length(l_buffer);
  508.  
  509. end if;
  510.  
  511. if (l_line is null) then
  512.  
  513. l_lengthline := 0;
  514.  
  515. else
  516.  
  517. l_lengthline := length(l_line);
  518.  
  519. end if;
  520.  
  521. exit when l_lengthbuffer + l_lengthline > :maxbytes OR l_lengthbuffer + l_lengthline > 32767 OR l_done = 1;
  522.  
  523. l_buffer := l_buffer || l_line || chr(10);
  524.  
  525. end loop;
  526.  
  527. :done := l_done;
  528.  
  529. :buffer := l_buffer;
  530.  
  531. :line := l_line;
  532.  
  533. end;
  534.  
  535. call count cpu elapsed disk query current rows
  536. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  537. Parse 1 0.00 0.00 0 0 0 0
  538. Execute 1 0.00 0.00 0 0 0 1
  539. Fetch 0 0.00 0.00 0 0 0 0
  540. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  541. total 2 0.00 0.00 0 0 0 1
  542.  
  543. Misses in library cache during parse: 0
  544. Optimizer mode: ALL_ROWS
  545. Parsing user id: 92
  546.  
  547.  
  548.  
  549. ********************************************************************************
  550.  
  551. OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
  552.  
  553. call count cpu elapsed disk query current rows
  554. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  555. Parse 2 0.01 0.01 0 0 0 0
  556. Execute 2 0.00 0.00 0 0 0 1
  557. Fetch 1 2.92 28.96 7427 7436 0 74
  558. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  559. total 5 2.93 28.97 7427 7436 0 75
  560.  
  561. Misses in library cache during parse: 1
  562.  
  563.  
  564. OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
  565.  
  566. call count cpu elapsed disk query current rows
  567. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  568. Parse 15 0.01 0.00 0 0 0 0
  569. Execute 40 0.01 0.01 0 0 0 0
  570. Fetch 73 0.04 0.17 18 155 0 146
  571. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  572. total 128 0.07 0.19 18 155 0 146
  573.  
  574. Misses in library cache during parse: 14
  575. Misses in library cache during execute: 14
  576.  
  577. 2 user SQL statements in session.
  578. 39 internal SQL statements in session.
  579. 41 SQL statements in session.
  580. ********************************************************************************
  581. Trace file: C:\Users\admin\Desktop\orcl_ora_5560_SINGLESQLTRACE.trc
  582. Trace file compatibility: 12.2.0.0
  583. Sort options: default
  584.  
  585. 1 session in tracefile.
  586. 2 user SQL statements in trace file.
  587. 39 internal SQL statements in trace file.
  588. 41 SQL statements in trace file.
  589. 16 unique SQL statements in trace file.
  590. 464 lines in trace file.
  591. 40 elapsed seconds in trace file.
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!