海量数据插入性能测试

11.2.0.2的RAC系统中原本有一张大的分区表,之前为了测试exchange分区的性能需要将这张分区表上的部分分区数据复制到测试用表上,因为数据量比较大所以记以录之:

磁盘不太给力 
hdparm -tT /dev/sdd
/dev/sdd:
Timing cached reads:   13672 MB in  2.00 seconds = 6840.55 MB/sec
Timing buffered disk reads:  605 MB in  3.02 seconds = 200.33 MB/sec
cat /proc/cpuinfo |grep processor|wc -l
8
直接将源分区插入到目标分区表中
SQL> select count(*) from sales_history partition (SALES_1996) ;
COUNT(*)
----------
2568089600
SQL> select (bytes) / 1024 / 1024, segment_name, partition_name
2    from dba_segments
3   where segment_name = 'SALES_HISTORY'
4   order by bytes desc
5  /
(BYTES)/1024/1024 SEGMENT_NAME         PARTITION_NAME
----------------- -------------------- ------------------------------
288710 SALES_HISTORY        SALES_1996
232 SALES_HISTORY        SALES_H2_1997
232 SALES_HISTORY        SALES_H1_1997
SQL> set timing on;
SQL> alter session enable parallel dml;
Session altered.
SQL> insert /*+ append parallel(ss,4) */
2  into sales ss
3    select /*+ parallel(sh,4) */ * from sales_history  partition(SALES_1996) sh ;
Elapsed: 01:01:08.03                             -- 耗时61分钟
SQL> commit;
Commit complete.
Elapsed: 00:00:00.19
Workarea Size
SQL> SELECT
2         sql_id,
3         operation_type,
4         policy,
5         active_time,
6         work_area_size,
7         expected_size,
8         actual_mem_used,
9         max_mem_used,
10         number_passes,
11         tempseg_size
12    FROM (SELECT swa.workarea_address,
13                 swa.sql_id,
14                 sa.sql_text,
15                 swa.operation_type,
16                 swa.policy,
17                 swa.sid,
18                 swa.active_time / 1000 active_time,
19                 swa.work_area_size,
20                 swa.expected_size,
swa.actual_mem_used,
swa.max_mem_used,
swa.number_passes,
swa.tempseg_size,
swa.tablespace,
(CASE
WHEN sl.totalwork <> 0 THEN
sl.sofar / sl.totalwork
21   22   23   24   25   26   27   28   29                   ELSE
30                    NULL
31                 END) complete_ratio,
32                 sl.elapsed_seconds * 1000 elapsed,
33                 sl.time_remaining * 1000 time_remaining,
34                 sl.opname,
35                 s.machine,
s.program,
36   37                 s.module,
38                 s.osuser,
39                 NVL(DECODE(TYPE,
40                            'BACKGROUND',
41                            'SYS (' || b.ksbdpnam || ')',
42                            s.username),
43                     SUBSTR(p.program, INSTR(p.program, '('))) username,
44                 ROW_NUMBER() OVER(PARTITION BY swa.sql_id ORDER BY sl.last_update_time DESC) rnum
45            FROM v$sql_workarea_active swa,
46                 v$sqlarea sa,
47                 (SELECT * FROM v$session_longops WHERE sofar <> totalwork) sl,
48                 v$session s,
49                 v$process p,
50                 x$ksbdp b
51           WHERE sl.sid(+) = swa.sid
52             AND sl.sql_id(+) = swa.sql_id
53             AND swa.sid <> USERENV('sid')
54             AND sa.sql_id = swa.sql_id
55             AND s.sid = swa.sid
56             AND s.paddr = p.addr
57             AND b.inst_id(+) = USERENV('INSTANCE')
58             AND p.addr = b.ksbdppro(+)
59           ORDER BY swa.number_passes DESC, swa.work_area_size DESC)
60   WHERE rnum = 1
61  /
SQL_ID        OPERATION_TYPE                 POLICY ACTIVE_TIME WORK_AREA_SIZE EXPECTED_SIZE ACTUAL_MEM_USED MAX_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
------------- ------------------------------ ------ ----------- -------------- ------------- --------------- ------------ ------------- ------------
ak9ht406k4zn4 LOAD WRITE BUFFERS             AUTO    889394.542         541696       1048576          541696       541696             0
SQL> alter session set workarea_size_policy=MANUAL;
Session altered.
Elapsed: 00:00:00.04
SQL> alter session set sort_area_size=314572800;
Session altered.
Elapsed: 00:00:00.00
SQL> alter session set sort_area_size=314572800;
Session altered.
创建索引
create index ind_sales on sales(prod_id,cust_id,time_id,channel_id)  nologging parallel 8
/
Index created.
Elapsed: 01:04:12.68

SQL>@sort_activity
SQL_ID        OPERATION_TYPE                 POLICY ACTIVE_TIME/1000 WORK_AREA_SIZE EXPECTED_SIZE ACTUAL_MEM_USED MAX_MEM_USED NUMBER_PASSES TEMPSEG_SIZE_IN_GB
------------- ------------------------------ ------ ---------------- -------------- ------------- --------------- ------------ ------------- ------------------
490ntjgc2dass SORT (v2)                      MANUAL       1275.18291              0                     287324160    310392832             1         6.94238281
SQL> set linesize 200 pagesize 1400
SQL> col opname for a20
SQL> select opname,totalwork,units,elapsed_seconds,sql_plan_options from v$session_longops  where opname='Sort Output';
OPNAME                TOTALWORK UNITS                            ELAPSED_SECONDS SQL_PLAN_OPTIONS
-------------------- ---------- -------------------------------- --------------- ------------------------------
Sort Output             1528129 Blocks                                      1809 CREATE INDEX
Sort Output             1529098 Blocks                                      1701 CREATE INDEX

Which SQL Operation May use Temp space?

Oracle中很多SQL操作都会使用Temp Space临时空间,理想状况下OLTP环境中自动/手动管理的PGA总是能在私有内存中满足这些操作的空间需求,而在Data Warehouse数据仓库中往往我们需要配置一个巨大的临时表空间(组)来满足海量的维护/查询对临时空间的需求,那么到底有哪些SQL操作时需要用到临时空间的呢?Google了一下,似乎没有一张非常完整的列表,这里由我抛砖引玉地列出一些,当然这远远不够全面:

SQL CODE Type
CREATE INDEX DDL
REBUILD INDEX DDL
ANALYZE DDL
CREATE PRIMARY KEY CONSTRAINT DDL
ENABLE CONSTRAINT DDL
CREATE TABLE AS SELECT(use permanet TBS) DDL
SELECT DISTINCT QUERY
ORDER BY Clause
GROUP BY Clause
UNION ALL Clause
UNION Clause
MINUS Clause
INTERSECT Clause
ROLLUP() FUNCTION FUNCTION
RANK() FUNCTION FUNCTION
CONNECT BY Clause
TEMPORARY TABLE Temporary Data
LOB_DATA LOB
LOB_INDEX LOB
HASH GROUP BY Operation
HASH JOIN Operation
HASH JOIN (ANTI) Operation
HASH JOIN (SEMI) Operation
SORT MERGE JOIN Operation
SORT MERGE Anti-Join Operation
SORT MERGE Semi-Join Operation
SORT GROUP BY Operation
IDX MAINTENANCE (SORT) Operation
WINDOW (SORT) Operation
ROLLUP (SORT) Operation
CONNECT-BY (SORT) Operation
UNION Operation
UNION ALL Operation
SORT AGGREGATE Operation
SORT UNIQUE Operation

Difference between parameter COMPATIBLE and OPTIMIZER_FEATURES_ENABLE

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> set linesize 200;
SQL> col name for a30;
SQL> col value for a20;
SQL> select name,value
2    from v$system_parameter
3   where name in ('compatible', 'optimizer_features_enable');
NAME                           VALUE
------------------------------ --------------------
compatible                     10.2.0.3.0
optimizer_features_enable      10.2.0.4
/* 10.2.0.4升级完毕后compatible参数默认值为10.2.0.3,不同于optimizer_features_enable */

[Read more…]

sort_area_size参数的一些表现

我们来看看该sort_area_size参数对创建索引时排序的具体影响:

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
/* 测试使用版本10.2.0.4 */
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /s01/arch
Oldest online log sequence     27
Current log sequence           34
/* 为了不受影响我们采用非归档模式 */
SQL> conn maclean/maclean
Connected.
SQL> alter session set workarea_size_policy=MANUAL;
Session altered.
/* 只有使用手动PGA管理时sort_area_size等参数才生效 */
SQL> alter session set db_file_multiblock_read_count=128;
Session altered.
/* 加大多块读参数帮助我们节约时间 */
SQL> alter session set "_sort_multiblock_read_count"=128;
Session altered.
/* 10g中sort_multiblock_read_count成为隐式参数,我们尝试手动固定它 */
SQL> set timing on;
SQL> alter session set events '10032 trace name context forever ,level 10';
Session altered.
Elapsed: 00:00:00.00
/* 在session级别设置10032事件,该事件帮助输出排序相关的统计信息*/
SQL> drop index ind_youyus;
alter session set sort_area_size=1048576;
alter session set sort_area_size=1048576;

[Read more…]

ORA-00600:[32695], [hash aggregation can't be done]错误一例

还是那个hash group by算法的问题,日志文件中出现以下记录:

*** ACTION NAME:(SQL 窗口 - 新建) 2010-09-03 14:27:54.594
*** MODULE NAME:(PL/SQL Developer) 2010-09-03 14:27:54.594
*** SERVICE NAME:(HQYDB1) 2010-09-03 14:27:54.594
*** SESSION ID:(3205.17923) 2010-09-03 14:27:54.594
*** 2010-09-03 14:27:54.594
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [32695], [hash aggregation can't be done], [], [], [], [], [], []
Current SQL statement for this session:
create table zou_201008_cell_id as
select /* g_all_cdr02,60 */
calling_num mobile_number,
lac,
lpad(cell_id,5,'0') cell_id,
count(*) c,
sum(call_duration) call_duration,
sum(decode(record_type,'00',1,0)*call_duration) moc_call_duration,
sum(decode(record_type,'01',1,0)*call_duration) mtc_call_duarion
from  g_all_cdr02
where substr(calling_num,1,7) in (select mobile_prefix from zou_mobile_prefix)
group by
calling_num ,
lac,
lpad(cell_id,5,'0')
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              100000000 ? 11055A9A0 ?
ksedmp+0290          bl       ksedst               104A54870 ?
ksfdmp+0018          bl       03F30204
kgeriv+0108          bl       _ptrgl
kgesiv+0080          bl       kgeriv               1050BE654 ? 1050BE604 ?
0000027E5 ? 080000000 ?
07FFFFFFF ?
ksesic1+0060         bl       kgesiv               43300000FFFF5310 ?
4530000000000000 ?
000000071 ? 000000001 ?
000000000 ?
qeshPartitionBuildH  bl       01F9CA24
D+04bc
qeshGBYOpenScan2+02  bl       qeshPartitionBuildH  0000027E5 ? 1105C06C0 ?
34                            D
qeshGBYOpenScan+001  bl       qeshGBYOpenScan2     FFFFFFFFFFF5740 ? 11055A938 ?
8                                                  000000000 ? 000000010 ?
qerghFetch+05e8      bl       qeshGBYOpenScan      000001000 ?
rwsfcd+0054          bl       _ptrgl
qerltFetch+036c      bl       03F2EB1C
ctcdrv+4160          bl       01F9C898
opiexe+2884          bl       ctcdrv               100000001 ? 100000001 ?
110467F30 ?
opiosq0+19f0         bl       opiexe               FFFFFFFFFFF8B50 ?
2824422142420820 ?
FFFFFFFFFFF8C10 ?
kpooprx+0168         bl       opiosq0              300000000 ? 000000000 ?
000000000 ? A4000000000000 ?
kpoal8+0400          bl       kpooprx              FFFFFFFFFFFB464 ?
FFFFFFFFFFFB068 ?
1BF000001BF ? 100000001 ?
000000000 ? A40000000000A4 ?
000000000 ? 1103878F8 ?
opiodr+0ae0          bl       _ptrgl
ttcpip+1020          bl       _ptrgl
opitsk+1124          bl       01F9F2A0
opiino+0990          bl       opitsk               000000000 ? 000000000 ?
opiodr+0ae0          bl       _ptrgl
opidrv+0484          bl       01F9E0E8
sou2o+0090           bl       opidrv               3C02DC1BBC ? 44065F000 ?
FFFFFFFFFFFF3A0 ?
opimai_real+01bc     bl       01F9B9F4
main+0098            bl       opimai_real          000000000 ? 000000000 ?
__start+0098         bl       main                 000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------

这次是因为应用人员不了解alter session的作用域,在PL/SQL Developer工具中的不同窗口(也就是不在同一会话中)中执行了”alter session set “_gby_hash_aggregation_enabled” = false;”和涉及group by操作的SQL,并导致了unpublished bug:6471770被触发。
我们比较容易地workaround绕过这个Bug:

/* 在会话级别设置优化参数_gby_hash_aggregation_enabled */
alter session set "_gby_hash_aggregation_enabled" = false;
/* 或者在语句中加入NO_USE_HASH_AGGREGATION的 hint */
select  /*+ NO_USE_HASH_AGGREGATION */ ....

以上提及的unpublished bug:6471770据称在10.2.0.5,11.1.0.7,11.2.0.1版本中被修正了。

Script:List SORT ACTIVITY监控临时空间的使用

以下脚本可以用于列出数据库内的排序活跃性能信息并监控临时空间的使用:

REM SORT ACTIVITY
set linesize 150 pagesize 1400;
SELECT d.tablespace_name "Name", 
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", 
TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999')  "HWM (M)",
TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)", 
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %" 
FROM sys.dba_tablespaces d, 
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) 
AND d.tablespace_name = t.tablespace_name(+) 
AND d.extent_management like 'LOCAL' 
AND d.contents like 'TEMPORARY'
/
alter session set nls_date_format='dd-mon-yy';
set lines 160 pages 1000 echo off feedback off
col stat_name for a25
col date_time for a40
col BEGIN_INTERVAL_TIME for a20
col END_INTERVAL_TIME for a20
prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc."
WITH sysstat AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.value e_value,
lag(ss.value, 1) over(order by ss.snap_id) b_value
from dba_hist_sysstat ss, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) >= sysdate-7
and ss.snap_id = sn.snap_id
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
and ss.dbid = (select dbid from v$database)
and ss.instance_number = (select instance_number from v$instance)
and ss.stat_name = 'sorts (disk)')
select to_char(BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char(END_INTERVAL_TIME, '_hh24_mi') date_time,
stat_name,
round((e_value - nvl(b_value,0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60
+ extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60
+ extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))),0) per_sec
from sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0
/
select temp_space/1024/1024,SQL_ID  from DBA_HIST_SQL_PLAN where temp_space>0 order by 1 asc;
--For 8.1.7 to 9.2:
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$sort_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;
--For 10.1 and above:
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
SELECT *
FROM (SELECT matching_criteria,
TO_CHAR(force_matching_signature) force_matching_signature,
sql_id,
child_number,
sql_text,
executions,
elapsed_time / 1000,
operation_type,
policy,
estimated_optimal_size,
last_memory_used,
last_execution,
active_time / 1000,
num_sort_operations,
tot_active_time / 1000,
tot_optimal_executions,
tot_onepass_executions,
tot_multipasses_executions,
all_tot_active_time / 1000,
max_tempseg_size,
parsing_schema_name
FROM (SELECT force_matching_signature,
sql_id,
child_number,
sql_text,
matching_criteria,
SUM(executions) OVER(PARTITION BY matching_criteria) executions,
SUM(elapsed_time) OVER(PARTITION BY matching_criteria) elapsed_time,
operation_type,
policy,
estimated_optimal_size,
last_memory_used,
last_execution,
active_time,
num_sort_operations,
SUM(tot_active_time) OVER(PARTITION BY matching_criteria) tot_active_time,
SUM(tot_optimal_executions) OVER(PARTITION BY matching_criteria) tot_optimal_executions,
SUM(tot_onepass_executions) OVER(PARTITION BY matching_criteria) tot_onepass_executions,
SUM(tot_multipasses_executions) OVER(PARTITION BY matching_criteria) tot_multipasses_executions,
MAX(max_tempseg_size) OVER(PARTITION BY matching_criteria) max_tempseg_size,
SUM(tot_active_time) OVER() all_tot_active_time,
ROW_NUMBER() OVER(PARTITION BY matching_criteria ORDER BY tot_multipasses_executions DESC, tot_onepass_executions DESC, last_memory_used DESC) rnum,
parsing_schema_name
FROM (SELECT s.sql_id,
s.child_number,
s.sql_text,
s.executions,
s.elapsed_time,
s.force_matching_signature,
CASE
WHEN s.force_matching_signature > 0 THEN
TO_CHAR(s.force_matching_signature)
ELSE
s.sql_id
END matching_criteria,
ROW_NUMBER() OVER(PARTITION BY s.sql_id, s.child_number ORDER BY sw.multipasses_executions DESC, sw.onepass_executions DESC, sw.last_memory_used DESC) rnum,
sw.operation_type,
sw.policy,
sw.estimated_optimal_size,
sw.last_memory_used,
sw.last_execution,
MAX(sw.max_tempseg_size) OVER(PARTITION BY s.sql_id, s.child_number) max_tempseg_size,
sw.active_time * sw.total_executions active_time,
SUM(sw.active_time * sw.total_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_active_time,
COUNT(*) OVER(PARTITION BY s.sql_id, s.child_number) num_sort_operations,
SUM(sw.optimal_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_optimal_executions,
SUM(sw.onepass_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_onepass_executions,
SUM(sw.multipasses_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_multipasses_executions,
NVL(u.username, s.parsing_schema_name) parsing_schema_name
FROM v$sql s, v$sql_workarea sw, all_users u
WHERE sw.sql_id = s.sql_id
AND sw.child_number = s.child_number
AND u.user_id(+) = s.parsing_user_id)
WHERE rnum = 1)
WHERE rnum = 1
ORDER BY tot_multipasses_executions DESC,
tot_onepass_executions     DESC,
last_memory_used           DESC)
WHERE ROWNUM <= 200
/
SELECT *
FROM (SELECT s.sid,
s.machine,
s.program,
s.module,
s.osuser,
NVL(DECODE(TYPE,
'BACKGROUND',
'SYS (' || b.ksbdpnam || ')',
s.username),
SUBSTR(p.program, INSTR(p.program, '('))) username,
NVL(SUM(CASE
WHEN sn.name = 'sorts (memory)' THEN
ss.VALUE
ELSE
0
END),
0) sorts_memory,
NVL(SUM(CASE
WHEN sn.name = 'sorts (disk)' THEN
ss.VALUE
ELSE
0
END),
0) sorts_disk,
NVL(SUM(CASE
WHEN sn.name = 'sorts (rows)' THEN
ss.VALUE
ELSE
0
END),
0) sorts_rows,
NVL(SUM(CASE
WHEN sn.name = 'physical reads direct temporary tablespace' THEN
ss.VALUE
ELSE
0
END),
0) reads_direct_temp,
NVL(SUM(CASE
WHEN sn.name = 'physical writes direct temporary tablespace' THEN
ss.VALUE
ELSE
0
END),
0) writes_direct_temp,
NVL(SUM(CASE
WHEN sn.name = 'workarea executions - optimal' THEN
ss.VALUE
ELSE
0
END),
0) workarea_exec_optimal,
NVL(SUM(CASE
WHEN sn.name = 'workarea executions - onepass' THEN
ss.VALUE
ELSE
0
END),
0) workarea_exec_onepass,
NVL(SUM(CASE
WHEN sn.name = 'workarea executions - multipass' THEN
ss.VALUE
ELSE
0
END),
0) workarea_exec_multipass
FROM v$session  s,
v$sesstat  ss,
v$statname sn,
v$process  p,
x$ksbdp    b
WHERE s.paddr = p.addr
AND b.inst_id(+) = USERENV('INSTANCE')
AND p.addr = b.ksbdppro(+)
AND s.TYPE = 'USER'
AND s.sid = ss.sid
AND ss.statistic# = sn.statistic#
AND sn.name IN ('sorts (memory)',
'sorts (disk)',
'sorts (rows)',
'physical reads direct temporary tablespace',
'physical writes direct temporary tablespace',
'workarea executions - optimal',
'workarea executions - onepass',
'workarea executions - multipass')
GROUP BY s.sid,
s.machine,
s.program,
s.module,
s.osuser,
NVL(DECODE(TYPE,
'BACKGROUND',
'SYS (' || b.ksbdpnam || ')',
s.username),
SUBSTR(p.program, INSTR(p.program, '(')))
ORDER BY workarea_exec_multipass DESC,
workarea_exec_onepass DESC,
reads_direct_temp + writes_direct_temp DESC,
sorts_rows DESC)
WHERE ROWNUM <= 200
/
SELECT rawtohex(workarea_address) workarea_address,
sql_id,
sql_text,
operation_type,
policy,
sid,
active_time,
work_area_size,
expected_size,
actual_mem_used,
max_mem_used,
number_passes,
tempseg_size,
tablespace,
complete_ratio,
elapsed,
time_remaining,
opname,
machine,
program,
module,
osuser,
username
FROM (SELECT swa.workarea_address,
swa.sql_id,
sa.sql_text,
swa.operation_type,
swa.policy,
swa.sid,
swa.active_time / 1000 active_time,
swa.work_area_size,
swa.expected_size,
swa.actual_mem_used,
swa.max_mem_used,
swa.number_passes,
swa.tempseg_size,
swa.tablespace,
(CASE
WHEN sl.totalwork <> 0 THEN
sl.sofar / sl.totalwork
ELSE
NULL
END) complete_ratio,
sl.elapsed_seconds * 1000 elapsed,
sl.time_remaining * 1000 time_remaining,
sl.opname,
s.machine,
s.program,
s.module,
s.osuser,
NVL(DECODE(TYPE,
'BACKGROUND',
'SYS (' || b.ksbdpnam || ')',
s.username),
SUBSTR(p.program, INSTR(p.program, '('))) username,
ROW_NUMBER() OVER(PARTITION BY swa.sql_id ORDER BY sl.last_update_time DESC) rnum
FROM v$sql_workarea_active swa,
v$sqlarea sa,
(SELECT * FROM v$session_longops WHERE sofar <> totalwork) sl,
v$session s,
v$process p,
x$ksbdp b
WHERE sl.sid(+) = swa.sid
AND sl.sql_id(+) = swa.sql_id
AND swa.sid <> USERENV('sid')
AND sa.sql_id = swa.sql_id
AND s.sid = swa.sid
AND s.paddr = p.addr
AND b.inst_id(+) = USERENV('INSTANCE')
AND p.addr = b.ksbdppro(+)
ORDER BY swa.number_passes DESC, swa.work_area_size DESC)
WHERE rnum = 1
/

沪ICP备14014813号

沪公网安备 31010802001379号