Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Долгий SELECT  [new]
kapelan
Member

Откуда: хутор БольшойБугор
Сообщений: 722
SQL> ed
Wrote file afiedt.buf

  1  explain plan for
  2    SELECT r.*
  3       FROM rem r
  4       WHERE  EXISTS
  5         (SELECT  2
  6          FROM    MV_paym_rem ps
  7          WHERE    r.ROWID = ps.remitt_rowid  AND
  8   (  ( ps.partition_date >= to_date('20060422', 'YYYYMMDD') )
  9   AND  ( ps.partition_date <  to_date('20060428', 'YYYYMMDD') )  )
 10   AND
 11   (ps.account_group,ps.account) IN (
 12        SELECT   account_group,account
 13        FROM     ACC_VIEW p
 14          JOIN   ORG_ACC_VIEWS org_v
 15            ON   p.acc_view_id = org_v.acc_view_id
 16          JOIN   CF_ACC_VIEW cf
 17            ON   cf.acc_view_id = p.acc_view_id
 18         WHERE    organization_id= 'ORG1119361849760' )
 19*  )
SQL> /

Explained.

Elapsed: 00:00:00.05
SQL> @D:\oracle\product\10.1.0\em_1\rdbms\admin\utlxpls.sql

Plan Table
------------------------------------------------------------------------------------------
| Operation                 |  Name              |  Rows | Bytes|  Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------
| SELECT STATEMENT          |                    |    46 |   44K|     51 |       |       |
|  NESTED LOOPS             |                    |    46 |   44K|     51 |       |       |
|   VIEW                    |VW_SQ_1             |    46 |  322 |      3 |       |       |
|    SORT UNIQUE            |                    |    46 |    4K|        |       |       |
|     NESTED LOOPS          |                    |    46 |    4K|      3 |       |       |
|      MERGE JOIN CARTESIAN |                    |   134K|   10M|      3 |       |       |
|       NESTED LOOPS        |                    |     2 |  100 |      1 |       |       |
|        INDEX RANGE SCAN   |ORG_ACC_VIEWS_PK    |     2 |   66 |      1 |       |       |
|        INDEX UNIQUE SCAN  |ACC_VIEW_PK         |     1 |   17 |        |       |       |
|       BUFFER SORT         |                    |    86K|    2M|      3 |       |       |
|        INDEX RANGE SCAN   |KKK_17              |    86K|    2M|      1 |       |       |
|      INDEX UNIQUE SCAN    |CF_ACC_VIEW_PK      |     1 |   27 |        |       |       |
|   TABLE ACCESS BY USER ROW|rem                 |     1 |  981 |      1 | ROWID | ROW L |
------------------------------------------------------------------------------------------

16 rows selected.

Elapsed: 00:00:00.01
SQL> 

--------------------------------------------------------------------
TKPROF: Release 9.2.0.4.0 - Production on Thu Apr 27 22:59:46 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Trace file: ft_ora_9238.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       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.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: 49  
********************************************************************************

SELECT COUNT(*)
 FROM
 (
  SELECT r.*
     FROM rem r
     WHERE  EXISTS
       (SELECT  2
        FROM    MV_paym_rem ps
        WHERE    r.ROWID = ps.remitt_rowid  AND
 (  ( ps.partition_date >= to_date('20060422', 'YYYYMMDD') )
 AND  ( ps.partition_date <  to_date('20060428', 'YYYYMMDD') )  )
 AND
 (ps.account_group,ps.account) IN (
      SELECT   account_group,account
      FROM     ACC_VIEW p
        JOIN   ORG_ACC_VIEWS org_v
          ON   p.acc_view_id = org_v.acc_view_id
        JOIN   CF_ACC_VIEW cf
          ON   cf.acc_view_id = p.acc_view_id
       WHERE    organization_id= 'ORG1119361849760' )
--)
)
 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      2      0.01       0.00          0          0          0           0
Fetch        2     17.14      18.35       7684    2094158          2           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5     17.16      18.37       7684    2094158          2           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 49  



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      3      0.01       0.00          0          0          0           0
Fetch        2     17.14      18.35       7684    2094158          2           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6     17.16      18.37       7684    2094158          2           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

    2  user  SQL statements in session.
    0  internal SQL statements in session.
    2  SQL statements in session.
********************************************************************************
Trace file: ft_ora_9238.trc
Trace file compatibility: 9.00.01
Sort options: default

       1  session in tracefile.
       2  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       2  SQL statements in trace file.
       2  unique SQL statements in trace file.
      56  lines in trace file.




запрос выполняется 15-20 сек.
таблица MV_paym_rem -4млн
таблица rem -4млн
если убрать эту часть запроса:
-------------------
10   AND
 11   (ps.account_group,ps.account) IN (
 12        SELECT   account_group,account
 13        FROM     ACC_VIEW p
 14          JOIN   ORG_ACC_VIEWS org_v
 15            ON   p.acc_view_id = org_v.acc_view_id
 16          JOIN   CF_ACC_VIEW cf
 17            ON   cf.acc_view_id = p.acc_view_id
 18         WHERE    organization_id= 'ORG1119361849760' )
 
то запрос все равно медленный
-----------------
28 апр 06, 07:19    [2612257]     Ответить | Цитировать Сообщить модератору
 Re: Долгий SELECT  [new]
Apex
Member

Откуда: Made in USSR
Сообщений: 3909

------------------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
------------------------------------------------------------------------------------------
| SELECT STATEMENT | | 46 | 44K| 51 | | |
| NESTED LOOPS | | 46 | 44K| 51 | | |
| VIEW |VW_SQ_1 | 46 | 322 | 3 | | |
| SORT UNIQUE | | 46 | 4K| | | |
| NESTED LOOPS | | 46 | 4K| 3 | | |
| MERGE JOIN CARTESIAN | | 134K| 10M| 3 | | |
| NESTED LOOPS | | 2 | 100 | 1 | | |
| INDEX RANGE SCAN |ORG_ACC_VIEWS_PK | 2 | 66 | 1 | | |
| INDEX UNIQUE SCAN |ACC_VIEW_PK | 1 | 17 | | | |
| BUFFER SORT | | 86K| 2M| 3 | | |
| INDEX RANGE SCAN |KKK_17 | 86K| 2M| 1 | | |
| INDEX UNIQUE SCAN |CF_ACC_VIEW_PK | 1 | 27 | | | |
| TABLE ACCESS BY USER ROW|rem | 1 | 981 | 1 | ROWID | ROW L |
------------------------------------------------------------------------------------------
28 апр 06, 09:51    [2612591]     Ответить | Цитировать Сообщить модератору
 Re: Долгий SELECT  [new]
givanov
Member

Откуда:
Сообщений: 757
kapelan
если убрать эту часть запроса: ...
Так может ее убрать? Тем более, что там вообще ничего не понятно.
28 апр 06, 11:21    [2613117]     Ответить | Цитировать Сообщить модератору
 Re: Долгий SELECT  [new]
Alexey Polovinkin
Member

Откуда: Киев
Сообщений: 490
это что, очередная ERP-система?

Что-то у вас индекс KKK_17 больно странный ...
А соединение просто капец...
28 апр 06, 12:43    [2613716]     Ответить | Цитировать Сообщить модератору
 Re: Долгий SELECT  [new]
kapelan
Member

Откуда: хутор БольшойБугор
Сообщений: 722
DROP INDEX kkk_17


SQL> SELECT COUNT(*)
  2   FROM
  3   (
  4    SELECT r.*
  5       FROM rem r
  6       WHERE  EXISTS
  7         (SELECT  2
  8          FROM    MV_paym_rem ps
  9          WHERE    r.ROWID = ps.remitt_rowid  AND
 10   (  ( ps.partition_date >= to_date('20060422', 'YYYYMMDD') )
 11   AND  ( ps.partition_date <  to_date('20060428', 'YYYYMMDD') )  )
 12   AND
 13   (ps.account_group,ps.account) IN (
 14        SELECT   account_group,account
 15        FROM     ACC_VIEW p
 16          JOIN   ORG_ACC_VIEWS org_v
 17            ON   p.acc_view_id = org_v.acc_view_id
 18          JOIN   CF_ACC_VIEW cf
 19            ON   cf.acc_view_id = p.acc_view_id
 20         WHERE    organization_id= 'ORG1119361849760' )
 21  --)
 22  )
 23   )
 24  /

  COUNT(*)
----------
         0

Elapsed: 00:00:42.02
SQL> ed
Wrote file afiedt.buf

  1  SELECT COUNT(*)
  2   FROM
  3   (
  4    SELECT r.*
  5       FROM rem r
  6       WHERE  EXISTS
  7         (SELECT  2
  8          FROM    MV_paym_rem ps
  9          WHERE    r.ROWID = ps.remitt_rowid  AND
 10   (  ( ps.partition_date >= to_date('20060422', 'YYYYMMDD') )
 11   AND  ( ps.partition_date <  to_date('20060428', 'YYYYMMDD') )  )
 12  )
 13*  )
SQL> /

  COUNT(*)
----------
    384457

Elapsed: 00:00:41.07
SQL> 



SQL> @C:\oracle\product\10.1.0\em_1\rdbms\admin\utlxpls.sql

Plan Table
------------------------------------------------------------------------------------------
| Operation                 |  Name              |  Rows | Bytes|  Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------
| SELECT STATEMENT          |                    |   105K|  100M|  71648 |       |       |
|  HASH JOIN SEMI           |                    |   105K|  100M|  71648 |       |       |
|   PARTITION RANGE ALL     |                    |       |      |        |     1 |    16 |
|    TABLE ACCESS FULL      |rem                 |     3M|    3G|  17381 |     1 |    16 |
|   TABLE ACCESS FULL       |MV_paym_REMITTA     |   105K|    1M|   4664 |       |       |
------------------------------------------------------------------------------------------

8 rows selected.

Elapsed: 00:00:00.00
SQL> 
bez INDEX huge
28 апр 06, 16:48    [2615101]     Ответить | Цитировать Сообщить модератору
 Re: Долгий SELECT  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18367
kapelan
| PARTITION RANGE ALL | | | | | 1 | 16 |
bez INDEX huge

Ну еще бы - кто за Вас ограничит просматриваемые секции?
Наложите адекватные ограничения на ключ секционирования.
28 апр 06, 16:51    [2615118]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить