使用pipelined函数返回表的所有记录与直接从表查询记录。

显示全部楼层 · 2007-1-24 14:35:02

1、通过pipelined函数
Statistics
----------------------------------------------------------
4489recursive calls

0db block gets
4492consistent gets

0physical reads

0redo size
115020bytes sent via SQL*Net to client
3792bytes received via SQL*Net from client
301SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)
4488rows processed
2、直接查询
Statistics
----------------------------------------------------------

0recursive calls

0db block gets
329consistent gets

0physical reads

0redo size
115020bytes sent via SQL*Net to client
3792bytes received via SQL*Net from client
301SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)
4488rows processed
为什么有这么多的 consistent gets和recursive calls?

千问 · 2007-1-24 14:35:02

Statistics
----------------------------------------------------------
23recursive calls

0db block gets
40consistent gets

0physical reads

0redo size
115020bytes sent via SQL*Net to client
3792bytes received via SQL*Net from client
301SQL*Net roundtrips to/from client

2sorts (memory)

0sorts (disk)
4488rows processed

千问 · 2007-1-24 14:35:02

Statistics
----------------------------------------------------------
41recursive calls

0db block gets
58consistent gets

0physical reads

0redo size
115020bytes sent via SQL*Net to client
3792bytes received via SQL*Net from client
301SQL*Net roundtrips to/from client

2sorts (memory)

0sorts (disk)
4488rows processed

千问 · 2007-1-24 14:35:02

利用pipe导出文本的速度
Download using:
QUERY = select * from table(pipeline_pkg.getloginstats)
FILE= pipe.txt
SEPERATOR = ,
QUOTE =
Begin Query, Wed Jul 14 11:04:29 CST 2004
Begin download, Wed Jul 14 11:04:29 CST 2004
100000 Rows write to pipe.txt , Wed Jul 14 11:04:39 CST 2004
200000 Rows write to pipe.txt , Wed Jul 14 11:04:47 CST 2004
246741 Rows write to pipe.txt , Wed Jul 14 11:04:51 CST 2004
246741 Rows exported in 00:00:22.92 seconds on 11168 row/s.
Download end, Wed Jul 14 11:04:51 CST 2004
直接查询导出文本的速度
Download using:
QUERY = select * from tab_disk_stats
FILE= table.txt
SEPERATOR = ,
QUOTE =
Begin Query, Wed Jul 14 11:05:19 CST 2004
Begin download, Wed Jul 14 11:05:19 CST 2004
100000 Rows write to table.txt , Wed Jul 14 11:05:23 CST 2004
200000 Rows write to table.txt , Wed Jul 14 11:05:28 CST 2004
246741 Rows write to table.txt , Wed Jul 14 11:05:29 CST 2004
246741 Rows exported in 00:00:10.935 seconds on 22564 row/s.
Download end, Wed Jul 14 11:05:29 CST 2004

千问 · 2007-1-24 14:35:02

用pipeline返回大量数据的速度要比直接select慢很多。

千问 · 2007-1-24 14:35:02

关注一下先

千问 · 2007-1-24 14:35:02

recursive calls
--------系统级的，比如数据字典对象的，空间的分配、系统数据字典的物理读…… 都是递归调用，很显然，pipe 涉及到了系统层的一些调用，那也是很正常的

千问 · 2007-1-24 14:35:02

TKPROF: Release 9.2.0.5.0 - Production on Wed Jul 14 12:12:04 2004
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.
Trace file: lfxdb92_ora_3812.trc
Sort options: default
********************************************************************************
count= number of times OCI procedure was executed
cpu= cpu time in seconds executing
elapsed= elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query= number of buffers gotten for consistent read
current= number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session set sql_trace = true

call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse00.00 0.00
0
0
0 0
Execute10.00 0.00
0
0
0 0
Fetch00.00 0.00
0
0
0 0
------- -------------- ---------- ---------- ---------- --------------------
total10.00 0.00
0
0
0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 30
********************************************************************************
select metadata
from
kopm$where name='DB_FDO'

call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.00 0.00
0
0
0 0
Execute10.00 0.00
0
0
0 0
Fetch10.00 0.00
0
2
0 1
------- -------------- ---------- ---------- ---------- --------------------
total30.00 0.00
0
2
0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
----------------------------------------------------------
1TABLE ACCESS BY INDEX ROWID KOPM$
1 INDEX UNIQUE SCAN I_KOPM1 (object id 351)
********************************************************************************
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,
o.dataobj#,o.flags
from
obj$ o where o.obj#=:1

call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.00 0.00
0
0
0 0
Execute10.00 0.00
0
0
0 0
Fetch10.00 0.00
0
3
0 1
------- -------------- ---------- ---------- ---------- --------------------
total30.00 0.00
0
3
0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
********************************************************************************
select *
from
table(pipeline_pkg.getloginstats) where rownum< 1000

call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.13 0.14
0 77
0 0
Execute10.00 0.00
0
0
0 0
Fetch 680.10 0.09
0 14
0 999
------- -------------- ---------- ---------- ---------- --------------------
total 700.23 0.24
0 91
0 999
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 30
Rows Row Source Operation
----------------------------------------------------------
999COUNT STOPKEY
999 COLLECTION ITERATOR PICKLER FETCH
********************************************************************************
select user#
from
sys.user$ where name = 'OUTLN'

call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.00 0.00
0
0
0 0
Execute10.00 0.00
0
0
0 0
Fetch10.00 0.00
0
2
0 1
------- -------------- ---------- ---------- ---------- --------------------
total30.00 0.00
0
2
0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
----------------------------------------------------------
1TABLE ACCESS BY INDEX ROWID USER$
1 INDEX UNIQUE SCAN I_USER1 (object id 44)
********************************************************************************
SELECT *
FROM
TAB_DISK_STATS

call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.07 0.06
0
0
0 0
Execute10.00 0.00
0
0
0 0
Fetch10.00 0.00
0 12
01000
------- -------------- ---------- ---------- ---------- --------------------
total30.07 0.07
0 12
01000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 30 (recursive depth: 1)
********************************************************************************
alter session set sql_trace = false

call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.00 0.00
0
0
0 0
Execute10.00 0.00
0
0
0 0
Fetch00.00 0.00
0
0
0 0
------- -------------- ---------- ---------- ---------- --------------------
total20.00 0.00
0
0
0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 30

********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse20.13 0.14
0 77
0 0
Execute30.00 0.00
0
0
0 0
Fetch 680.10 0.09
0 14
0 999
------- -------------- ---------- ---------- ---------- --------------------
total 730.23 0.24
0 91
0 999
Misses in library cache during parse: 2
Misses in library cache during execute: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse40.07 0.06
0
0
0 0
Execute40.00 0.00
0
0
0 0
Fetch40.00 0.00
0 19
01003
------- -------------- ---------- ---------- ---------- --------------------
total 120.07 0.07
0 19
01003
Misses in library cache during parse: 4
4userSQL statements in session.
3internal SQL statements in session.
7SQL statements in session.
********************************************************************************
Trace file: lfxdb92_ora_3812.trc
Trace file compatibility: 9.02.00
Sort options: default
1session in tracefile.
4userSQL statements in trace file.
3internal SQL statements in trace file.
7SQL statements in trace file.
7unique SQL statements in trace file.
141lines in trace file.

千问 · 2007-1-24 14:35:02

一个概念上的转换而已么

千问 · 2007-1-24 14:35:02

利用pipelined函数来实现一些复杂的报表，应当是不错的主意。