1.查询内存和磁盘排序情况：
SELECT *
FROM v$sysstat WHERE NAME LIKE 'workarea executions - %' OR NAME IN ('sorts (memory)', 'sorts (disk)');2.反联结子查询
- 反联结查询是一种返回在一张表中的和另一张表的某些记录集不匹配的记录的查询，因为这事实上是常规联结行为的反操作，所以用反联结这个词描述这种操作。反联结通常表现为使用NOT IN或NOT EXISTS的子查询。
也许最自然和最常见的表现反联结的方法是同时NOT操作符合IN操作符。
SQL> alter system flush buffer_cache;
System altered.
SELECT /* Nulls - No index */ COUNT (*) FROM google_customers WHERE (cust_first_name, cust_last_name) NOT IN ( SELECT cust_first_name, cust_last_name FROM microsoft_customers);COUNT(*)
---------- 1087Elapsed: 00:00:00.90
Execution Plan
----------------------------------------------------------Plan hash value: 1119169911--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 34 | 36 (6)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 34 | | || 2 | MERGE JOIN ANTI NA | | 20 | 680 | 36 (6)| 00:00:01 || 3 | SORT JOIN | | 1999 | 33983 | 18 (6)| 00:00:01 || 4 | TABLE ACCESS FULL| GOOGLE_CUSTOMERS | 1999 | 33983 | 17 (0)| 00:00:01 ||* 5 | SORT UNIQUE | | 1999 | 33983 | 18 (6)| 00:00:01 || 6 | TABLE ACCESS FULL| MICROSOFT_CUSTOMERS | 1999 | 33983 | 17 (0)| 00:00:01 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------5 - access(INTERNAL_FUNCTION("CUST_FIRST_NAME")=INTERNAL_FUNCTION("CUST_FIRST_NAM
E") AND INTERNAL_FUNCTION("CUST_LAST_NAME")=INTERNAL_FUNCTION("CUST_LAST_NAME")) filter(INTERNAL_FUNCTION("CUST_LAST_NAME")=INTERNAL_FUNCTION("CUST_LAST_NAME" ) AND INTERNAL_FUNCTION("CUST_FIRST_NAME")=INTERNAL_FUNCTION("CUST_FIRST_NAME")) Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 120 consistent gets 118 physical reads 0 redo size 527 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 1 rows processedSELECT /* Nulls - No index */
COUNT (*) FROM google_customers gc WHERE NOT EXISTS ( SELECT 0 FROM microsoft_customers mc WHERE mc.cust_first_name = gc.cust_first_name 8 AND mc.cust_last_name = gc.cust_last_name); COUNT(*)---------- 1087Elapsed: 00:00:00.89
Execution Plan
----------------------------------------------------------Plan hash value: 2488544940-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 34 | 34 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 34 | | ||* 2 | HASH JOIN ANTI | | 20 | 680 | 34 (0)| 00:00:01 || 3 | TABLE ACCESS FULL| GOOGLE_CUSTOMERS | 1999 | 33983 | 17 (0)| 00:00:01 || 4 | TABLE ACCESS FULL| MICROSOFT_CUSTOMERS | 1999 | 33983 | 17 (0)| 00:00:01 |-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access("MC"."CUST_FIRST_NAME"="GC"."CUST_FIRST_NAME" AND
"MC"."CUST_LAST_NAME"="GC"."CUST_LAST_NAME") Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 120 consistent gets 118 physical reads 0 redo size 527 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed