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

Откуда: от Москвы 3 тыщи наверное
Сообщений: 24
доброго времени суток,

есть такая система ДОКУМЕНТУМ 5.3 SP6
есть 2 среды: прод и тест

есть проблемный запрос который выдал ДОКУМЕНТУМ ДА переводя свой DQL в SQL:
SELECT DISTINCT d.r_object_id, d.object_name
           FROM [schema].ak_document_sp d,
                [schema].dm_group_sp g,
                [schema].dm_sysobject_r dm_sysobject_r2,
                [schema].dm_folder_r dm_folder_r1,
                [schema].cs_core_r dm_repeating1_0,
                (SELECT DISTINCT gr1.users_names AS i_all_users_names,
                                 gr2.i_supergroups_names AS group_name
                            FROM [schema].dm_group_r gr1,
                                 [schema].dm_group_r gr2
                           WHERE gr1.r_object_id = gr2.r_object_id
                             AND gr1.users_names IS NOT NULL) gr3
          WHERE (    d.r_object_id = dm_sysobject_r2.r_object_id
                 AND dm_sysobject_r2.i_folder_id = dm_folder_r1.r_object_id
                 AND dm_folder_r1.i_ancestor_id = '0b01395c8015f67b'
                 AND (   (   (dm_repeating1_0.cs_consumers = g.group_name AND gr3.i_all_users_names = 'Surname, Name')
                          OR (dm_repeating1_0.cs_consumers = 'Surname, Name')
                         )
                      OR (   (dm_repeating1_0.cs_authors = g.group_name AND gr3.i_all_users_names = 'Surname, Name')
                          OR (dm_repeating1_0.cs_authors = 'Surname, Name')
                         )
                     )
                )
            AND d.i_is_deleted = 0
            AND dm_repeating1_0.r_object_id = d.r_object_id
            AND gr3.group_name = g.group_name;

ТЕСТ - single instance - win x64 - 10.2.0.4 EE:
init.ora
*.compatible='10.2.0.3.0'
*.cursor_sharing='FORCE'
*.cursor_space_for_time=TRUE
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=17179869184
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTDBXDB)'
*.dml_locks=2948
*.job_queue_processes=10
*.log_buffer=23499776
*.open_cursors=500
*.optimizer_index_caching=95
*.optimizer_index_cost_adj=5
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=1610612736
*.plsql_debug=FALSE
*.plsql_optimize_level=2
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=60
*.sessions=670
*.sga_max_size=4194304000
*.sga_target=4194304000
*.transactions=737
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

план выполнения запроса который отрабатывает за пару сек:
SQL>
SQL> EXPLAIN PLAN FOR
  2  SELECT DISTINCT d.r_object_id, d.object_name
  3             FROM [schema].ak_document_sp d,
  4                  [schema].dm_group_sp g,
  5                  [schema].dm_sysobject_r dm_sysobject_r2,
  6                  [schema].dm_folder_r dm_folder_r1,
  7                  [schema].cs_core_r dm_repeating1_0,
  8                  (SELECT DISTINCT gr1.users_names AS i_all_users_names,
  9                                   gr2.i_supergroups_names AS group_name
 10                              FROM [schema].dm_group_r gr1,
 11                                   [schema].dm_group_r gr2
 12                             WHERE gr1.r_object_id = gr2.r_object_id
 13                               AND gr1.users_names IS NOT NULL) gr3
 14            WHERE (    d.r_object_id = dm_sysobject_r2.r_object_id
 15                   AND dm_sysobject_r2.i_folder_id = dm_folder_r1.r_object_id
 16                   AND dm_folder_r1.i_ancestor_id = '0b01395c8015f67b'
 17                   AND (   (   (dm_repeating1_0.cs_consumers = g.group_name AND gr3.i_all_users_names = 'Surname, Name')
 18                            OR (dm_repeating1_0.cs_consumers = 'Surname, Name')
 19                           )
 20                        OR (   (dm_repeating1_0.cs_authors = g.group_name AND gr3.i_all_users_names = 'Surname, Name')
 21                            OR (dm_repeating1_0.cs_authors = 'Surname, Name')
 22                           )
 23                       )
 24                  )
 25              AND d.i_is_deleted = 0
 26              AND dm_repeating1_0.r_object_id = d.r_object_id
 27              AND gr3.group_name = g.group_name;

Explained.

Elapsed: 00:00:00.73
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1428019700

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                           |    31M|  8701M|       |  1963K  (1)| 06:32:37 |
|   1 |  HASH UNIQUE                           |                           |    31M|  8701M|    18G|  1963K  (1)| 06:32:37 |
|   2 |   CONCATENATION                        |                           |       |       |       |    |          |
|*  3 |    HASH JOIN                           |                           |    24M|  6959M|       |   566  (32)| 00:00:07 |
|*  4 |     TABLE ACCESS FULL                  | DM_GROUP_R                | 23107 |   586K|       |   170   (1)| 00:00:03 |
|   5 |     NESTED LOOPS                       |                           | 74610 |    18M|       |   222   (2)| 00:00:03 |
|   6 |      MERGE JOIN CARTESIAN              |                           | 76060 |    17M|       |   220   (1)| 00:00:03 |
|   7 |       NESTED LOOPS                     |                           |     1 |   195 |       | 50   (2)| 00:00:01 |
|   8 |        NESTED LOOPS                    |                           |     1 |   178 |       | 49   (3)| 00:00:01 |
|*  9 |         HASH JOIN                      |                           |     2 |   322 |       | 48   (3)| 00:00:01 |
|  10 |          NESTED LOOPS                  |                           |   374 | 40392 |       | 27   (0)| 00:00:01 |
|  11 |           NESTED LOOPS                 |                           |   374 | 22066 |       |  9   (0)| 00:00:01 |
|* 12 |            INDEX RANGE SCAN            | D_1F01395C80000016        |     7 |   238 |       |  1   (0)| 00:00:01 |
|  13 |            TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_R            |    56 |  1400 |       |  1   (0)| 00:00:01 |
|* 14 |             INDEX RANGE SCAN           | D_1F01395C80000010        |   143 |       |       |  1   (0)| 00:00:01 |
|* 15 |           TABLE ACCESS BY INDEX ROWID  | DM_SYSOBJECT_S            |     1 |    49 |       |  1   (0)| 00:00:01 |
|* 16 |            INDEX UNIQUE SCAN           | D_1F01395C80000109        |     1 |       |       |  1   (0)| 00:00:01 |
|  17 |          TABLE ACCESS BY INDEX ROWID   | CS_CORE_R                 |  5328 |   275K|       | 20   (0)| 00:00:01 |
|* 18 |           INDEX RANGE SCAN             | CS_CORE_R_INDEX_AUTHORS   |  5328 |       |       |  2   (0)| 00:00:01 |
|* 19 |         INDEX UNIQUE SCAN              | D_1F01395C80000502        |     1 |    17 |       |  1   (0)| 00:00:01 |
|* 20 |        INDEX UNIQUE SCAN               | D_1F01395C80000500        |     1 |    17 |       |  1   (0)| 00:00:01 |
|  21 |       BUFFER SORT                      |                           | 61299 |  2574K|       |   219   (1)| 00:00:03 |
|* 22 |        TABLE ACCESS FULL               | DM_GROUP_R                | 61299 |  2574K|       |   171   (2)| 00:00:03 |
|* 23 |      INDEX RANGE SCAN                  | D_1F01395C80000018        |     1 |    29 |       |  1   (0)| 00:00:01 |
|* 24 |    HASH JOIN                           |                           |    15 |  4395 |       |   117   (0)| 00:00:02 |
|  25 |     TABLE ACCESS BY INDEX ROWID        | DM_GROUP_R                |     1 |    26 |       |  1   (0)| 00:00:01 |
|* 26 |      INDEX RANGE SCAN                  | D_1F01395C80000044        |     3 |       |       |  1   (0)| 00:00:01 |
|  27 |     TABLE ACCESS BY INDEX ROWID        | DM_GROUP_R                |     2 |    86 |       |  1   (0)| 00:00:01 |
|  28 |      NESTED LOOPS                      |                           |  1215 |   316K|       |   116   (0)| 00:00:02 |
|  29 |       NESTED LOOPS                     |                           |   548 |   119K|       | 61   (0)| 00:00:01 |
|  30 |        NESTED LOOPS                    |                           |   548 |   104K|       | 60   (0)| 00:00:01 |
|  31 |         NESTED LOOPS                   |                           |   304 | 43168 |       | 29   (0)| 00:00:01 |
|  32 |          NESTED LOOPS                  |                           |   337 | 42125 |       | 28   (0)| 00:00:01 |
|  33 |           NESTED LOOPS                 |                           |   374 | 40392 |       | 27   (0)| 00:00:01 |
|  34 |            NESTED LOOPS                |                           |   374 | 22066 |       |  9   (0)| 00:00:01 |
|* 35 |             INDEX RANGE SCAN           | D_1F01395C80000016        |     7 |   238 |       |  1   (0)| 00:00:01 |
|  36 |             TABLE ACCESS BY INDEX ROWID| DM_SYSOBJECT_R            |    56 |  1400 |       |  1   (0)| 00:00:01 |
|* 37 |              INDEX RANGE SCAN          | D_1F01395C80000010        |   143 |       |       |  1   (0)| 00:00:01 |
|* 38 |            TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_S            |     1 |    49 |       |  1   (0)| 00:00:01 |
|* 39 |             INDEX UNIQUE SCAN          | D_1F01395C80000109        |     1 |       |       |  1   (0)| 00:00:01 |
|* 40 |           INDEX UNIQUE SCAN            | D_1F01395C80000500        |     1 |    17 |       |  1   (0)| 00:00:01 |
|* 41 |          INDEX UNIQUE SCAN             | D_1F01395C80000502        |     1 |    17 |       |  1   (0)| 00:00:01 |
|* 42 |         TABLE ACCESS BY INDEX ROWID    | CS_CORE_R                 |     2 |   106 |       |  1   (0)| 00:00:01 |
|* 43 |          INDEX RANGE SCAN              | D_1F01395C80000501        |     3 |       |       |  1   (0)| 00:00:01 |
|* 44 |        INDEX RANGE SCAN                | D_1F01395C80000018        |     1 |    29 |       |  1   (0)| 00:00:01 |
|* 45 |       INDEX RANGE SCAN                 | D_1F01395C80000031        |     3 |       |       |  1   (0)| 00:00:01 |
|* 46 |    HASH JOIN                           |                           |  6232K|  1741M|       |   422  (12)| 00:00:06 |
|* 47 |     TABLE ACCESS FULL                  | DM_GROUP_R                | 23107 |   586K|       |   170   (1)| 00:00:03 |
|  48 |     NESTED LOOPS                       |                           | 18669 |  4867K|       |   208   (1)| 00:00:03 |
|  49 |      MERGE JOIN CARTESIAN              |                           | 19032 |  4423K|       |   207   (1)| 00:00:03 |
|  50 |       NESTED LOOPS                     |                           |     1 |   195 |       | 36   (0)| 00:00:01 |
|  51 |        NESTED LOOPS                    |                           |     1 |   178 |       | 35   (0)| 00:00:01 |
|* 52 |         HASH JOIN                      |                           |     1 |   161 |       | 34   (0)| 00:00:01 |
|  53 |          NESTED LOOPS                  |                           |   374 | 40392 |       | 27   (0)| 00:00:01 |
|  54 |           NESTED LOOPS                 |                           |   374 | 22066 |       |  9   (0)| 00:00:01 |
|* 55 |            INDEX RANGE SCAN            | D_1F01395C80000016        |     7 |   238 |       |  1   (0)| 00:00:01 |
|  56 |            TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_R            |    56 |  1400 |       |  1   (0)| 00:00:01 |
|* 57 |             INDEX RANGE SCAN           | D_1F01395C80000010        |   143 |       |       |  1   (0)| 00:00:01 |
|* 58 |           TABLE ACCESS BY INDEX ROWID  | DM_SYSOBJECT_S            |     1 |    49 |       |  1   (0)| 00:00:01 |
|* 59 |            INDEX UNIQUE SCAN           | D_1F01395C80000109        |     1 |       |       |  1   (0)| 00:00:01 |
|* 60 |          TABLE ACCESS BY INDEX ROWID   | CS_CORE_R                 |  1333 | 70649 |       |  6   (0)| 00:00:01 |
|* 61 |           INDEX RANGE SCAN             | CS_CORE_R_INDEX_COMSUMERS |  1336 |       |       |  1   (0)| 00:00:01 |
|* 62 |         INDEX UNIQUE SCAN              | D_1F01395C80000502        |     1 |    17 |       |  1   (0)| 00:00:01 |
|* 63 |        INDEX UNIQUE SCAN               | D_1F01395C80000500        |     1 |    17 |       |  1   (0)| 00:00:01 |
|  64 |       BUFFER SORT                      |                           | 61299 |  2574K|       |   206   (1)| 00:00:03 |
|* 65 |        TABLE ACCESS FULL               | DM_GROUP_R                | 61299 |  2574K|       |   171   (2)| 00:00:03 |
|* 66 |      INDEX RANGE SCAN                  | D_1F01395C80000018        |     1 |    29 |       |  1   (0)| 00:00:01 |
|* 67 |    HASH JOIN                           |                           |    14 |  4102 |       |   111   (0)| 00:00:02 |
|  68 |     TABLE ACCESS BY INDEX ROWID        | DM_GROUP_R                |     1 |    26 |       |  1   (0)| 00:00:01 |
|* 69 |      INDEX RANGE SCAN                  | D_1F01395C80000044        |     3 |       |       |  1   (0)| 00:00:01 |
|  70 |     TABLE ACCESS BY INDEX ROWID        | DM_GROUP_R                |     2 |    86 |       |  1   (0)| 00:00:01 |
|  71 |      NESTED LOOPS                      |                           |  1084 |   282K|       |   110   (0)| 00:00:02 |
|  72 |       NESTED LOOPS                     |                           |   488 |   106K|       | 61   (0)| 00:00:01 |
|  73 |        NESTED LOOPS                    |                           |   488 | 95160 |       | 60   (0)| 00:00:01 |
|  74 |         NESTED LOOPS                   |                           |   304 | 43168 |       | 29   (0)| 00:00:01 |
|  75 |          NESTED LOOPS                  |                           |   337 | 42125 |       | 28   (0)| 00:00:01 |
|  76 |           NESTED LOOPS                 |                           |   374 | 40392 |       | 27   (0)| 00:00:01 |
|  77 |            NESTED LOOPS                |                           |   374 | 22066 |       |  9   (0)| 00:00:01 |
|* 78 |             INDEX RANGE SCAN           | D_1F01395C80000016        |     7 |   238 |       |  1   (0)| 00:00:01 |
|  79 |             TABLE ACCESS BY INDEX ROWID| DM_SYSOBJECT_R            |    56 |  1400 |       |  1   (0)| 00:00:01 |
|* 80 |              INDEX RANGE SCAN          | D_1F01395C80000010        |   143 |       |       |  1   (0)| 00:00:01 |
|* 81 |            TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_S            |     1 |    49 |       |  1   (0)| 00:00:01 |
|* 82 |             INDEX UNIQUE SCAN          | D_1F01395C80000109        |     1 |       |       |  1   (0)| 00:00:01 |
|* 83 |           INDEX UNIQUE SCAN            | D_1F01395C80000500        |     1 |    17 |       |  1   (0)| 00:00:01 |
|* 84 |          INDEX UNIQUE SCAN             | D_1F01395C80000502        |     1 |    17 |       |  1   (0)| 00:00:01 |
|* 85 |         TABLE ACCESS BY INDEX ROWID    | CS_CORE_R                 |     2 |   106 |       |  1   (0)| 00:00:01 |
|* 86 |          INDEX RANGE SCAN              | D_1F01395C80000501        |     3 |       |       |  1   (0)| 00:00:01 |
|* 87 |        INDEX RANGE SCAN                | D_1F01395C80000018        |     1 |    29 |       |  1   (0)| 00:00:01 |
|* 88 |       INDEX RANGE SCAN                 | D_1F01395C80000031        |     3 |       |       |  1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("GR1"."R_OBJECT_ID"="GR2"."R_OBJECT_ID")
   4 - filter("GR1"."USERS_NAMES" IS NOT NULL)
   9 - access("DM_REPEATING1_0"."R_OBJECT_ID"="BLAXB_YB_"."R_OBJECT_ID")
  12 - access("DM_FOLDER_R1"."I_ANCESTOR_ID"='0b01395c8015f67b')
  14 - access("DM_SYSOBJECT_R2"."I_FOLDER_ID"="DM_FOLDER_R1"."R_OBJECT_ID")
       filter("DM_SYSOBJECT_R2"."I_FOLDER_ID" IS NOT NULL)
  15 - filter("BLAXB_YB_"."I_IS_DELETED"=0)
  16 - access("BLAXB_YB_"."R_OBJECT_ID"="DM_SYSOBJECT_R2"."R_OBJECT_ID")
  18 - access("DM_REPEATING1_0"."CS_AUTHORS"='Surname, Name')
  19 - access("BLAXB_YB_"."R_OBJECT_ID"="YRAXB_FO_"."R_OBJECT_ID")
  20 - access("BLAXB_YB_"."R_OBJECT_ID"="ESAXB_BO_"."R_OBJECT_ID")
  22 - filter("GR2"."I_SUPERGROUPS_NAMES" IS NOT NULL)
  23 - access("GR2"."I_SUPERGROUPS_NAMES"="BG_"."GROUP_NAME")
  24 - access("GR1"."R_OBJECT_ID"="GR2"."R_OBJECT_ID")
  26 - access("GR1"."USERS_NAMES"='Surname, Name')
       filter("GR1"."USERS_NAMES" IS NOT NULL)
  35 - access("DM_FOLDER_R1"."I_ANCESTOR_ID"='0b01395c8015f67b')
  37 - access("DM_SYSOBJECT_R2"."I_FOLDER_ID"="DM_FOLDER_R1"."R_OBJECT_ID")
       filter("DM_SYSOBJECT_R2"."I_FOLDER_ID" IS NOT NULL)
  38 - filter("BLAXB_YB_"."I_IS_DELETED"=0)
  39 - access("BLAXB_YB_"."R_OBJECT_ID"="DM_SYSOBJECT_R2"."R_OBJECT_ID")
  40 - access("BLAXB_YB_"."R_OBJECT_ID"="ESAXB_BO_"."R_OBJECT_ID")
  41 - access("BLAXB_YB_"."R_OBJECT_ID"="YRAXB_FO_"."R_OBJECT_ID")
  42 - filter("DM_REPEATING1_0"."CS_AUTHORS" IS NOT NULL AND LNNVL("DM_REPEATING1_0"."CS_AUTHORS"='Surname, Name'))
  43 - access("DM_REPEATING1_0"."R_OBJECT_ID"="BLAXB_YB_"."R_OBJECT_ID")
  44 - access("DM_REPEATING1_0"."CS_AUTHORS"="BG_"."GROUP_NAME")
  45 - access("GR2"."I_SUPERGROUPS_NAMES"="BG_"."GROUP_NAME")
       filter("GR2"."I_SUPERGROUPS_NAMES" IS NOT NULL)
  46 - access("GR1"."R_OBJECT_ID"="GR2"."R_OBJECT_ID")
       filter(LNNVL("DM_REPEATING1_0"."CS_AUTHORS"="BG_"."GROUP_NAME") OR LNNVL("GR1"."USERS_NAMES"='Surname, Name') OR LNNVL("DM_REPEATING1_0"."CS_AUTHORS" IS NOT NULL))
  47 - filter("GR1"."USERS_NAMES" IS NOT NULL)
  52 - access("DM_REPEATING1_0"."R_OBJECT_ID"="BLAXB_YB_"."R_OBJECT_ID")
  55 - access("DM_FOLDER_R1"."I_ANCESTOR_ID"='0b01395c8015f67b')
  57 - access("DM_SYSOBJECT_R2"."I_FOLDER_ID"="DM_FOLDER_R1"."R_OBJECT_ID")
       filter("DM_SYSOBJECT_R2"."I_FOLDER_ID" IS NOT NULL)
  58 - filter("BLAXB_YB_"."I_IS_DELETED"=0)
  59 - access("BLAXB_YB_"."R_OBJECT_ID"="DM_SYSOBJECT_R2"."R_OBJECT_ID")
  60 - filter(LNNVL("DM_REPEATING1_0"."CS_AUTHORS"='Surname, Name'))
  61 - access("DM_REPEATING1_0"."CS_CONSUMERS"='Surname, Name')
  62 - access("BLAXB_YB_"."R_OBJECT_ID"="YRAXB_FO_"."R_OBJECT_ID")
  63 - access("BLAXB_YB_"."R_OBJECT_ID"="ESAXB_BO_"."R_OBJECT_ID")
  65 - filter("GR2"."I_SUPERGROUPS_NAMES" IS NOT NULL)
  66 - access("GR2"."I_SUPERGROUPS_NAMES"="BG_"."GROUP_NAME")
  67 - access("GR1"."R_OBJECT_ID"="GR2"."R_OBJECT_ID")
       filter(LNNVL("DM_REPEATING1_0"."CS_AUTHORS"="BG_"."GROUP_NAME") OR LNNVL("GR1"."USERS_NAMES"='Surname, Name') OR LNNVL("DM_REPEATING1_0"."CS_AUTHORS" IS NOT NULL))
  69 - access("GR1"."USERS_NAMES"='Surname, Name')
       filter("GR1"."USERS_NAMES" IS NOT NULL)
  78 - access("DM_FOLDER_R1"."I_ANCESTOR_ID"='0b01395c8015f67b')
  80 - access("DM_SYSOBJECT_R2"."I_FOLDER_ID"="DM_FOLDER_R1"."R_OBJECT_ID")
       filter("DM_SYSOBJECT_R2"."I_FOLDER_ID" IS NOT NULL)
  81 - filter("BLAXB_YB_"."I_IS_DELETED"=0)
  82 - access("BLAXB_YB_"."R_OBJECT_ID"="DM_SYSOBJECT_R2"."R_OBJECT_ID")
  83 - access("BLAXB_YB_"."R_OBJECT_ID"="ESAXB_BO_"."R_OBJECT_ID")
  84 - access("BLAXB_YB_"."R_OBJECT_ID"="YRAXB_FO_"."R_OBJECT_ID")
  85 - filter("DM_REPEATING1_0"."CS_CONSUMERS" IS NOT NULL AND LNNVL("DM_REPEATING1_0"."CS_CONSUMERS"='Surname, Name') AND LNNVL("DM_REPEATING1_0"."CS_AUTHORS"='Surname, Name'))
  86 - access("DM_REPEATING1_0"."R_OBJECT_ID"="BLAXB_YB_"."R_OBJECT_ID")
  87 - access("DM_REPEATING1_0"."CS_CONSUMERS"="BG_"."GROUP_NAME")
  88 - access("GR2"."I_SUPERGROUPS_NAMES"="BG_"."GROUP_NAME")
       filter("GR2"."I_SUPERGROUPS_NAMES" IS NOT NULL)

162 rows selected.

Elapsed: 00:00:00.67
SQL>

ПРОД - RAC 2 node - win x64 - 10.2.0.4 EE:
init.ora
*.archive_lag_target=0
*.audit_trail='DB'
*.cluster_database_instances=2
*.cluster_database=true
*.compatible='10.2.0.3.0'
*.cursor_sharing='FORCE'
*.cursor_space_for_time=TRUE
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='PRODDB'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=17179869184
*.dg_broker_config_file1='E:\dr1PRODDB.dat'
*.dg_broker_config_file2='E:\dr2PRODDB.dat'
*.dg_broker_start=TRUE
*.dml_locks=2948
*.job_queue_processes=10
*.log_archive_config='dg_config=(STANDB)'
*.log_archive_dest_1='LOCATION=+DATA/'
*.log_buffer=23499776
*.open_cursors=500
*.optimizer_index_caching=95
*.optimizer_index_cost_adj=5
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=1610612736
*.plsql_debug=FALSE
*.plsql_optimize_level=2
*.processes=300
*.sessions=670
*.sga_max_size=6291456000
*.sga_target=6291456000
*.standby_file_management='MANUAL'
*.transactions=737
*.undo_management='AUTO'
PRODDB_INS1.undo_tablespace='UNDOTBS1'
PRODDB_INS2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='D:\oracle\product\10.2.0\admin\PRODDB\udump'

план выполнения запроса который висит днями:
SQL> EXPLAIN PLAN FOR
  2  SELECT DISTINCT d.r_object_id, d.object_name
  3             FROM [schema].ak_document_sp d,
  4                  [schema].dm_group_sp g,
  5                  [schema].dm_sysobject_r dm_sysobject_r2,
  6                  [schema].dm_folder_r dm_folder_r1,
  7                  [schema].cs_core_r dm_repeating1_0,
  8                  (SELECT DISTINCT gr1.users_names AS i_all_users_names,
  9                                   gr2.i_supergroups_names AS group_name
 10                              FROM [schema].dm_group_r gr1,
 11                                   [schema].dm_group_r gr2
 12                             WHERE gr1.r_object_id = gr2.r_object_id
 13                               AND gr1.users_names IS NOT NULL) gr3
 14            WHERE (    d.r_object_id = dm_sysobject_r2.r_object_id
 15                   AND dm_sysobject_r2.i_folder_id = dm_folder_r1.r_object_id
 16                   AND dm_folder_r1.i_ancestor_id = '0b01395c8015f67b'
 17                   AND (   (   (dm_repeating1_0.cs_consumers = g.group_name AND gr3.i_all_users_names = 'Surname, Name')
 18                            OR (dm_repeating1_0.cs_consumers = 'Surname, Name')
 19                           )
 20                        OR (   (dm_repeating1_0.cs_authors = g.group_name AND gr3.i_all_users_names = 'Surname, Name')
 21                            OR (dm_repeating1_0.cs_authors = 'Surname, Name')
 22                           )
 23                       )
 24                  )
 25              AND d.i_is_deleted = 0
 26              AND dm_repeating1_0.r_object_id = d.r_object_id
 27              AND gr3.group_name = g.group_name;

Explained.

Elapsed: 00:00:00.09
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 347272830

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                    |     5 |  1415 |    36   (3)| 00:00:01 |
|   1 |  HASH UNIQUE                           |                    |     5 |  1415 |    36   (3)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID          | DM_GROUP_R         |     1 |    20 |     1   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                        |                    |     5 |  1415 |    35   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                       |                    |    28 |  7364 |    32   (0)| 00:00:01 |
|   5 |      MERGE JOIN CARTESIAN              |                    |     5 |  1095 |    31   (0)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID      | CS_CORE_R          |     3 |   159 |     1   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                    |                    |     1 |   190 |    10   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                   |                    |     1 |   137 |     9   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                  |                    |    50 |  6050 |     8   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                 |                    |    50 |  5250 |     7   (0)| 00:00:01 |
|  11 |            NESTED LOOPS                |                    |    71 |  4189 |     4   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN           | D_1F01395C80000016 |     3 |   102 |     1   (0)| 00:00:01 |
|  13 |             TABLE ACCESS BY INDEX ROWID| DM_SYSOBJECT_R     |    21 |   525 |     1   (0)| 00:00:01 |
|* 14 |              INDEX RANGE SCAN          | D_1F01395C80000010 |   105 |       |     1   (0)| 00:00:01 |
|* 15 |            TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_S     |     1 |    46 |     1   (0)| 00:00:01 |
|* 16 |             INDEX UNIQUE SCAN          | D_1F01395C80000109 |     1 |       |     1   (0)| 00:00:01 |
|* 17 |           INDEX UNIQUE SCAN            | D_1F01395C80000502 |     1 |    16 |     1   (0)| 00:00:01 |
|* 18 |          INDEX UNIQUE SCAN             | D_1F01395C80000500 |     1 |    16 |     1   (0)| 00:00:01 |
|* 19 |         INDEX RANGE SCAN               | D_1F01395C80000501 |     3 |       |     1   (0)| 00:00:01 |
|  20 |       BUFFER SORT                      |                    | 54051 |  1530K|    30   (0)| 00:00:01 |
|  21 |        INDEX FULL SCAN                 | D_1F01395C80000018 | 54051 |  1530K|    21   (0)| 00:00:01 |
|  22 |      TABLE ACCESS BY INDEX ROWID       | DM_GROUP_R         |     6 |   264 |     1   (0)| 00:00:01 |
|* 23 |       INDEX RANGE SCAN                 | D_1F01395C80000031 |     6 |       |     1   (0)| 00:00:01 |
|* 24 |     INDEX RANGE SCAN                   | IDX$$_6818001B     |     6 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

   2 - filter("GR1"."USERS_NAMES" IS NOT NULL AND ("DM_REPEATING1_0"."CS_CONSUMERS"="PI_"."GROUP_NAME
              " AND "GR1"."USERS_NAMES"='Surname, Name' AND "DM_REPEATING1_0"."CS_CONSUMERS" IS NOT NULL OR
              "DM_REPEATING1_0"."CS_CONSUMERS"='Surname, Name' OR
              "DM_REPEATING1_0"."CS_AUTHORS"="PI_"."GROUP_NAME" AND "GR1"."USERS_NAMES"='Surname, Name' AND
              "DM_REPEATING1_0"."CS_AUTHORS" IS NOT NULL OR "DM_REPEATING1_0"."CS_AUTHORS"='Surname, Name'))
  12 - access("DM_FOLDER_R1"."I_ANCESTOR_ID"='0b01395c8015f67b')
  14 - access("DM_SYSOBJECT_R2"."I_FOLDER_ID"="DM_FOLDER_R1"."R_OBJECT_ID")
       filter("DM_SYSOBJECT_R2"."I_FOLDER_ID" IS NOT NULL)
  15 - filter("XQRJC_YB_"."I_IS_DELETED"=0)
  16 - access("XQRJC_YB_"."R_OBJECT_ID"="DM_SYSOBJECT_R2"."R_OBJECT_ID")
  17 - access("XQRJC_YB_"."R_OBJECT_ID"="PSRJC_IO_"."R_OBJECT_ID")
  18 - access("VRRJC_EO_"."R_OBJECT_ID"="PSRJC_IO_"."R_OBJECT_ID")
       filter("XQRJC_YB_"."R_OBJECT_ID"="VRRJC_EO_"."R_OBJECT_ID")
  19 - access("DM_REPEATING1_0"."R_OBJECT_ID"="XQRJC_YB_"."R_OBJECT_ID")
  23 - access("GR2"."I_SUPERGROUPS_NAMES"="PI_"."GROUP_NAME")
  24 - access("GR1"."R_OBJECT_ID"="GR2"."R_OBJECT_ID")

51 rows selected.

Elapsed: 00:00:00.48
SQL>
7 окт 10, 10:28    [9566630]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
sqltuner
Member

Откуда: от Москвы 3 тыщи наверное
Сообщений: 24
не стал запускать этим методом
автор

Давай сделаем так: если 10g, то в SQL *Plus, для хорошего и плохого запроса
set serveroutput off;
select /*+ gather_plan_statistics */ ... from ... where;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));


потому что один нормально отрабатывает, а другой висит.

таблицы анализировал ө не помогло

что посоветуйте делать???
7 окт 10, 10:31    [9566647]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
тУпик
Guest
1. этот запрос висит сутками именно при запуске из-под документум или просто в sqlplus например?
2. а если разбить на части
например часть запроса вложенная вьюха
8 (SELECT DISTINCT gr1.users_names AS i_all_users_names,
9 gr2.i_supergroups_names AS group_name
10 FROM [schema].dm_group_r gr1,
11 [schema].dm_group_r gr2
12 WHERE gr1.r_object_id = gr2.r_object_id
13 AND gr1.users_names IS NOT NULL)
тоже долго висит?
7 окт 10, 10:47    [9566773]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
sqltuner
Member

Откуда: от Москвы 3 тыщи наверное
Сообщений: 24
тУпик
1. этот запрос висит сутками именно при запуске из-под документум или просто в sqlplus например?
2. а если разбить на части
например часть запроса вложенная вьюха
8 (SELECT DISTINCT gr1.users_names AS i_all_users_names,
9 gr2.i_supergroups_names AS group_name
10 FROM [schema].dm_group_r gr1,
11 [schema].dm_group_r gr2
12 WHERE gr1.r_object_id = gr2.r_object_id
13 AND gr1.users_names IS NOT NULL)
тоже долго висит?


1) из под обоих висит
2) этот вложенный запрос работает? выдает гдето после 1-1,5 минуты
7 окт 10, 10:57    [9566867]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
тУпик
Guest
А r_object_id случайно не уникально в [schema].dm_group_r?
А вообще насколько понимаю, у вас же нет возможности изменить сам SQL?
То есть только индексы и т.п.?
7 окт 10, 11:18    [9567074]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
huliGUN
Member

Откуда: Ахметов Сити; Санкт Харьков; Донецк (Киев);
Сообщений: 466
1) а не гуйня ли написана, если у вас схему одинаковые:
  8                  (SELECT DISTINCT gr1.users_names AS i_all_users_names,
  9                                   gr2.i_supergroups_names AS group_name
 10                              FROM [schema].dm_group_r gr1,
 11                                   [schema].dm_group_r gr2
 12                             WHERE gr1.r_object_id = gr2.r_object_id
 13                               AND gr1.users_names IS NOT NULL

Да и чего сессия то ждет, которая сутками весит?
7 окт 10, 11:23    [9567133]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
тУпик
Guest
huliGUN
а не гуйня ли написана, если у вас схему одинаковые

Если схемы одинаковые, но r_object_id не уникально, то возможно, так и задумано.
Если нет возможности переписать сам запрос.
Возможно, стоит разобраться по полкам, начав с ускорения подзапроса в плюсе
Индексами, хинтами и т.п. И потом может как-то посмотреть в сторону хранимых шаблонов (сам никогда не юзал правда :) ).
7 окт 10, 11:26    [9567172]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
sqltuner
Member

Откуда: от Москвы 3 тыщи наверное
Сообщений: 24
тУпик
А r_object_id случайно не уникально в [schema].dm_group_r?
А вообще насколько понимаю, у вас же нет возможности изменить сам SQL?
То есть только индексы и т.п.?

да только индексы
7 окт 10, 11:30    [9567210]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
PaulEr
Member

Откуда:
Сообщений: 3794
Параметры оптимизаторы на тесте и проде какие?
7 окт 10, 11:32    [9567241]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
PaulEr
Member

Откуда:
Сообщений: 3794
Как собиралась статистика по таблицам и индексам на тесте и проде?Что-то количество строк, ожидаемых к получению очень разнятся. В итоге, какая выборка получается?Таблицы одинаковые по содержанию на тесте и проде?
7 окт 10, 11:38    [9567319]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
Андрей Панфилов
Member

Откуда: Москва > Melbourne
Сообщений: 3778
huliGUN
1) а не гуйня ли написана, если у вас схему одинаковые:
  8                  (SELECT DISTINCT gr1.users_names AS i_all_users_names,
  9                                   gr2.i_supergroups_names AS group_name
 10                              FROM [schema].dm_group_r gr1,
 11                                   [schema].dm_group_r gr2
 12                             WHERE gr1.r_object_id = gr2.r_object_id
 13                               AND gr1.users_names IS NOT NULL

Да и чего сессия то ждет, которая сутками весит?
это стандартный кусок документума, который определяет в каких группах состоит пользователь, вообще имеет смысл сделать мат. представление на этот кусок.
7 окт 10, 11:48    [9567437]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
-2-
Member

Откуда:
Сообщений: 15330
Почему бы не погадать на ожиданиях?
7 окт 10, 12:03    [9567587]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Навскидку, с /*+ use_concat */ быстро работает?
7 окт 10, 12:08    [9567638]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
PaulEr
Member

Откуда:
Сообщений: 3794
PaulEr
Параметры оптимизаторы на тесте и проде какие?


Извиняюсь, проглядел по иниту параметры.
7 окт 10, 12:10    [9567652]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
Андрей Панфилов
Member

Откуда: Москва > Melbourne
Сообщений: 3778
-2-
Почему бы не погадать на ожиданиях?

там нечего особо гадать:

у ТС примерно следующая схема:
dm_folder - это объект типа "папка" - в представляется таблицами dm_sysobject_s (уникальный идентификатор r_object_id, имя object_name и пр.), dm_folder_s (уникальный идентификатор r_object_id - тупо чтобы представление dm_folder_sp содержало только папки), dm_sysobject_r (уникальный идентификатор r_object_id, ссылка на папку i_folder_id), dm_folder_r (ссылка на все верхние папки i_ancestor_id)

ak_document - это какой-то наследник dm_sysobject (т.е. у него есть тоже r_object_id) + cs_core_r какие-то атрибуты объекта

Запрос ТС ищет все объекты ak_document в папке '0b01395c8015f67b' и ниже, при условии что cs_consumers или cs_authors - конкретный пользователь или группа, в которой состоит пользователь.

судя по cursor_sharing='FORCE' - скорее всего оптимизатор успел где-то подхватить биндинги для "хорошей" папки и теперь везде использует неподходящий план.
7 окт 10, 12:26    [9567802]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
sqltuner
Member

Откуда: от Москвы 3 тыщи наверное
Сообщений: 24
wurdu
Навскидку, с /*+ use_concat */ быстро работает?

с этим хинтом на тесте выдала резудьтат за 1 секунду
без хинта за 3 секунды
7 окт 10, 13:27    [9568489]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
sqltuner
wurdu
Навскидку, с /*+ use_concat */ быстро работает?

с этим хинтом на тесте выдала резудьтат за 1 секунду
без хинта за 3 секунды
Не понял про 3 секунды, ты же говорил что висит.
7 окт 10, 13:30    [9568517]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
sqltuner
Member

Откуда: от Москвы 3 тыщи наверное
Сообщений: 24
PaulEr
Как собиралась статистика по таблицам и индексам на тесте и проде?Что-то количество строк, ожидаемых к получению очень разнятся. В итоге, какая выборка получается?Таблицы одинаковые по содержанию на тесте и проде?

там по умолчанию собирались, но для прода отдельно каждую таблицу проанализировал.
по кол-во строк на проде больше, последний фул импорт в тестовую среду был в середине сентября.
7 окт 10, 13:32    [9568527]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
sqltuner
Member

Откуда: от Москвы 3 тыщи наверное
Сообщений: 24
wurdu
sqltuner
wurdu
Навскидку, с /*+ use_concat */ быстро работает?

с этим хинтом на тесте выдала резудьтат за 1 секунду
без хинта за 3 секунды
Не понял про 3 секунды, ты же говорил что висит.

это я прогонял на тестовой среде.

test: hint - 1 sec, no hint - 3 sec
prod: hint - 1 min 47 sec, no hint - 1,5 den
7 окт 10, 13:36    [9568571]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
sqltuner
Member

Откуда: от Москвы 3 тыщи наверное
Сообщений: 24
через дбконсоль запустил SQL Tuning Advisor для этого запроса, он мне выдал "Consider accepting the recommended SQL profile.benefit - 99.99%" Я заимплементил. Заново запускаю скрипт ө висит как и раньше. Как за'accept'ить SQL profile.
7 окт 10, 13:52    [9568730]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
Без гмо
Guest
попробуй избавиться от этого в плохом плане
MERGE JOIN CARTESIAN
7 окт 10, 13:54    [9568756]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
PaulEr
Member

Откуда:
Сообщений: 3794
sqltuner
PaulEr
Как собиралась статистика по таблицам и индексам на тесте и проде?Что-то количество строк, ожидаемых к получению очень разнятся. В итоге, какая выборка получается?Таблицы одинаковые по содержанию на тесте и проде?

там по умолчанию собирались, но для прода отдельно каждую таблицу проанализировал.
по кол-во строк на проде больше, последний фул импорт в тестовую среду был в середине сентября.

А по умолчанию это как? И как для прода анализировали?
7 окт 10, 14:18    [9568941]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
Андрей Панфилов
Member

Откуда: Москва > Melbourne
Сообщений: 3778
sqltuner,

У Вас такой DQL-запрос сколько выполняется:

select d.r_object_id, d.object_name from ak_document_sp d where any ( cs_consumers in (select group_name from dm_group where any i_all_users_names='Surname, Name') or cs_authors in (select group_name from dm_group where any i_all_users_names='Surname, Name') or cs_consumers = 'Surname, Name' or cs_authors ='Surname, Name') and folder('My Cool Folder', DESCEND)

?
7 окт 10, 15:28    [9569619]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
sqltuner
Member

Откуда: от Москвы 3 тыщи наверное
Сообщений: 24
PaulEr
sqltuner
PaulEr
Как собиралась статистика по таблицам и индексам на тесте и проде?Что-то количество строк, ожидаемых к получению очень разнятся. В итоге, какая выборка получается?Таблицы одинаковые по содержанию на тесте и проде?

там по умолчанию собирались, но для прода отдельно каждую таблицу проанализировал.
по кол-во строк на проде больше, последний фул импорт в тестовую среду был в середине сентября.

А по умолчанию это как? И как для прода анализировали?


на 10ке по умолчанию ведь собирается статистика,
analyze table [table_name] compute statistics - через TOAD
7 окт 10, 15:54    [9569825]     Ответить | Цитировать Сообщить модератору
 Re: тюнинг медленного запроса  [new]
PaulEr
Member

Откуда:
Сообщений: 3794
sqltuner,

По умолчанию лучше все-таки использовать пакет dbms_stats.
7 окт 10, 16:46    [9570307]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить