698~706

병렬 order by ,group by

정렬은(order by 절) temp tablespace 를 사용하므로 해당 유저가 어떤 temp tablespace 를 사용하는지 파악 후 작업 필요

select username , default_tablespace, temporary_tablespace from dba_users;

병렬작업엔 분배가 필요하며 그걸 QC 가 실행

병렬처리 사용 예

select /*\+ parallel(emp 2) \*/ sum(sal) ,deptno from emp group by deptno

  SUM(SAL)     DEPTNO
\---\--\--\--\- \--\--\

---

-\-
      9400         30

     10875         20
      8750         10

Execution Plan
\

---

---

---

---

---

---

---

---

---

---

---

---

---

---

-\-
Plan hash value: 3475411915

\

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

\-
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
\

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

\-
|   0 | SELECT STATEMENT        |          |     3 |    21 |     3  (34)| 00:00:01 |        |      |    |
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |    |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |     3 |    21 |     3  (34)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY        |          |     3 |    21 |     3  (34)| 00:00:01 |  Q1,01 | PCWP |    |
|   4 |     PX RECEIVE          |          |    15 |   105 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |    |
|   5 |      PX SEND HASH       | :TQ10000 |    15 |   105 |     2   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   6 |       PX BLOCK ITERATOR |          |    15 |   105 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |    |
|   7 |        TABLE ACCESS FULL| EMP      |    15 |   105 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |    |
\

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

\-

Statistics
\

---

---

---

---

---

---

---

---

---

---

---

---

---

---

-\-
         12  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
        688  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

병렬힌트 미사용시

  SUM(SAL)     DEPTNO
\---\--\--\--\- \--\--\

---

-\-
      9400         30

     10875         20
      8750         10

Execution Plan
\

---

---

---

---

---

---

---

---

---

---

---

---

---

---

-\-
Plan hash value: 4067220884

\

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

--\-
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
\

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

--\-
|   0 | SELECT STATEMENT   |      |     3 |    21 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    15 |   105 |     3   (0)| 00:00:01 |
\

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

--\-

Statistics
\

---

---

---

---

---

---

---

---

---

---

---

---

---

---

-\-
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        688  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed