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(*)
----------
1087
Elapsed: 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 processed
SELECT /* 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(*)
----------
1087
Elapsed: 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
转载于:https://blog.51cto.com/7642644/1664124