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

Откуда: Jacksonville, FL
Сообщений: 16268
Доброго всем.
Не могу понять, почему при создании индекса сортировки уходят на диски, не смотря на то, что памяти должно хватать.
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 900M

SQL> show parameter sort

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_sort                             string      BINARY
sort_area_retained_size              integer     200000000
sort_area_size                       integer     200000000

SQL> ALTER SESSION SET EVENTS '10032 trace name context forever, level 4';

Session altered.

SQL> drop index i_t2;

Index dropped.

SQL> CREATE INDEX i_t2 ON t2 ( c1 ) TABLESPACE test_sort;

Index created.

SQL> select bytes from dba_segments where segment_name='I_T2';

     BYTES
----------
 100663296

------######################################
soropn: opened (new) sort, sordef ffffffff7a3b5108, flags 0x802
        maxkey 56, nflds 2, nkflds 2
----- Current SQL Statement for this session (sql_id=ftf4kvcd2nzsf) -----
CREATE INDEX i_t2 ON t2 ( c1 ) TABLESPACE test_sort
        Abridged call stack trace:
ksedsts<-soropn<-qersoProcessULS<-qersoFetch<-qerliFetch<-kdicrws<-kdicdrv<-opiexe<-opiosq0<-kpooprx<-kpoal8<-opiodr<-ttcpip<-opitsk<-opiino<-
opiodr<-opidrv<-sou2o<-opimai_real<-ssthrdmain<-main<-_start        End of abridged call stack trace.

*** 2012-03-05 17:02:01.119
smbalo: sort ffffffff7a3b5108 out of memory, smbtab is 104879104, smbbre is 3078990

soreod: sorp ffffffff7a3b5108
---- Sort Parameters ------------------------------
sort_area_size                    1245184
sort_area_retained_size           1245184
sort_multiblock_read_count        7
max intermediate merge width      2

sorcls: sorp ffffffff7a3b5108
---- Sort Statistics ------------------------------
Initial runs                              3
Number of merges                          1
Input records                             5000000
Output records                            5000000
Disk blocks 1st pass                      14754
Total disk blocks used                    14752
Total number of comparisons performed     64985620
  Comparisons performed by in-memory sort 58093351
  Comparisons performed during merge      6892267
  Comparisons while searching for key in-memory 2
Temp segments allocated                   1
Extents allocated                         116
Uses version 2 sort
Uses asynchronous IO
    ---- Run Directory Statistics ----
Run directory block reads (buffer cache)  4
Block pins (for run directory)            1
Block repins (for run directory)          3
    ---- Direct Write Statistics -----
Write slot size                           253952
Write slots used during in-memory sort    4
Number of direct writes                   576
Num blocks written (with direct write)    14750
Block pins (for sort records)             14750
Waits for async writes                    6
    ---- Direct Read Statistics ------
Size of read slots for output             57344
Number of read slots for output           21
Number of direct sync reads               119
Number of blocks read synchronously       823
Number of direct async reads              2072
Number of blocks read asynchronously      13927
Waits for async reads                     36
---- End of Sort Statistics -----------------------
5 мар 12, 19:12    [12199134]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
брадобрей
Member

Откуда:
Сообщений: 4696
sho parameter workarea_size_policy
5 мар 12, 19:14    [12199140]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
pravednik
Member

Откуда: Jacksonville, FL
Сообщений: 16268
брадобрей
sho parameter workarea_size_policy

SQL> sho parameter workarea_size_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO
5 мар 12, 19:25    [12199198]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
брадобрей
Member

Откуда:
Сообщений: 4696
Вопрос закрыт?
5 мар 12, 19:34    [12199248]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
pravednik
Member

Откуда: Jacksonville, FL
Сообщений: 16268
брадобрей
Вопрос закрыт?

c sort_area_size вопросов не было. Сори, лишняя инфа.
С ручным управлением проблем нет
*** 2012-03-05 17:34:56.371
soreod: sorp ffffffff7a30e1c8
---- Sort Parameters ------------------------------
sort_area_size                    500000000
sort_area_retained_size           500000000
sort_multiblock_read_count        2
max intermediate merge width      13754

*** 2012-03-05 17:35:05.425
sorcls: sorp ffffffff7a30e1c8
---- Sort Statistics ------------------------------
Input records                             5000000
Output records                            5000000
Total number of comparisons performed     58083117
  Comparisons performed by in-memory sort 58083117
Total amount of memory used               170552320
Uses version 2 sort
---- End of Sort Statistics -----------------------


Тут интересует больше, как ткунть умный(workarea_size_policy=auto) оракл "эй, дружок, у тебя памяти хватает. Чего на диски лезешь" &
5 мар 12, 19:39    [12199272]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
-2-
Member

Откуда:
Сообщений: 15330
Для auto и вообще...

_smm_max_size
_smm_px_max_size
_smm_isort_cap
5 мар 12, 19:39    [12199274]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
pravednik
Member

Откуда: Jacksonville, FL
Сообщений: 16268
-2-,

NAME                 VALUE           DEFLT     TYPE                                     DESCRIPTION
-------------------- --------------- --------- ---------------------------------------- ---------------------------------------------
_smm_isort_cap       102400          TRUE      number                                   maximum work area for insertion sort(v1)
_smm_max_size        102400          TRUE      number                                   maximum work area size in auto mode (serial)
_smm_px_max_size     460800          TRUE      number                                   maximum work area size in auto mode (global)
5 мар 12, 19:44    [12199302]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
onstat-
Member

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

Мы с wurdu ковыряли эту тему несколько месяцев назад.

Скажу чесно, универсального предсказуемого результата у меня не получилось.

Слишком много факторов, для каждого индекса нужно просчитывать параметры.
5 мар 12, 20:10    [12199438]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
Timur Akhmadeev
Member

Откуда:
Сообщений: 511
pravednik
Тут интересует больше, как ткунть умный(workarea_size_policy=auto) оракл "эй, дружок, у тебя памяти хватает. Чего на диски лезешь" &

Увеличить PAT.
5 мар 12, 23:06    [12200122]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
tim_scn
Member

Откуда: Ukraine
Сообщений: 384
pravednik
-2-,

NAME                 VALUE           DEFLT     TYPE                                     DESCRIPTION
-------------------- --------------- --------- ---------------------------------------- ---------------------------------------------
_smm_isort_cap       102400          TRUE      number                                   maximum work area for insertion sort(v1)
_smm_max_size        102400          TRUE      number                                   maximum work area size in auto mode (serial)
_smm_px_max_size     460800          TRUE      number                                   maximum work area size in auto mode (global)



"_pga_max_size" Maximum size of the PGA memory for one process ???
5 мар 12, 23:36    [12200206]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
tim_scn
Member

Откуда: Ukraine
Сообщений: 384
ID 453540.1
5 мар 12, 23:45    [12200225]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
pravednik
Member

Откуда: Jacksonville, FL
Сообщений: 16268
tim_scn,
спасибо.
В итоге, те же цифры, что и в ноте.
Так еще мало
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 1600M

NAME                           VALUE      DEFLT     TYPE                                     DESCRIPTION
------------------------------ ---------- --------- ---------------------------------------- --------------------------------------------------
_pga_max_size                  335544320  TRUE      6                                        Maximum size of the PGA memory for one process

--##############################
*** 2012-03-05 21:51:53.961
soropn: opened (new) sort, sordef ffffffff7a1bbd28, flags 0x802
        maxkey 56, nflds 2, nkflds 2
----- Current SQL Statement for this session (sql_id=ftf4kvcd2nzsf) -----
CREATE INDEX i_t2 ON t2 ( c1 ) TABLESPACE test_sort
        Abridged call stack trace:
ksedsts<-soropn<-qersoProcessULS<-qersoFetch<-qerliFetch<-kdicrws<-kdicdrv<-opiexe<-opiosq0<-kpooprx<-kpoal8<-opiodr<-ttcpip<-opitsk<-opiino<-opiodr<-opidrv<-sou2o<-opimai_real<-ssthrdmain<-main<-_start
   End of abridged call stack trace.

*** 2012-03-05 21:51:58.013
smbalo: sort ffffffff7a1bbd28 out of memory, smbtab is 167778304, smbbre is 4926746

soreod: sorp ffffffff7a1bbd28
---- Sort Parameters ------------------------------
sort_area_size                    1245184
sort_area_retained_size           1245184
sort_multiblock_read_count        7
max intermediate merge width      2
*** 2012-03-05 21:52:16.688
sorcls: sorp ffffffff7a1bbd28
---- Sort Statistics ------------------------------
Initial runs                              2
Number of merges                          1
Input records                             5000000
Output records                            5000000
Disk blocks 1st pass                      14761
Total disk blocks used                    14753
Total number of comparisons performed     63014759
  Comparisons performed by in-memory sort 58016757
  Comparisons performed during merge      4998001
  Comparisons while searching for key in-memory 1
Temp segments allocated                   1
Extents allocated                         116
Uses version 2 sort
Uses asynchronous IO
    ---- Run Directory Statistics ----
Run directory block reads (buffer cache)  3
Block pins (for run directory)            1
Block repins (for run directory)          2
    ---- Direct Write Statistics -----
Write slot size                           253952
Write slots used during in-memory sort    4
Number of direct writes                   577
Num blocks written (with direct write)    14751
Block pins (for sort records)             14751
Waits for async writes                    2
    ---- Direct Read Statistics ------
Size of read slots for output             57344
Number of read slots for output           21
Number of direct sync reads               118
Number of blocks read synchronously       820
Number of direct async reads              2072
Number of blocks read asynchronously      13931
Waits for async reads                     142
---- End of Sort Statistics -----------------------


А тут уже нормально
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 1650M

NAME                 VALUE                DEFLT     TYPE                                     DESCRIPTION
-------------------- -------------------- --------- ---------------------------------------- --------------------------------------------------
_pga_max_size        346030080            TRUE      6                                        Maximum size of the PGA memory for one process

--###########
*** 2012-03-05 22:05:08.100
soropn: opened (new) sort, sordef ffffffff7dfd5e38, flags 0x802
        maxkey 56, nflds 2, nkflds 2
----- Current SQL Statement for this session (sql_id=ftf4kvcd2nzsf) -----
CREATE INDEX i_t2 ON t2 ( c1 ) TABLESPACE test_sort
        Abridged call stack trace:
ksedsts<-soropn<-qersoProcessULS<-qersoFetch<-qerliFetch<-kdicrws<-kdicdrv<-opiexe<-opiosq0<-kpooprx<-kpoal8<-opiodr<-ttcpip<-opitsk<-opiino<-opiodr<-opidrv<-sou2o<-opimai_real<-ssthrdmain<-main<-_start
   End of abridged call stack trace.

*** 2012-03-05 22:05:16.928
soreod: sorp ffffffff7dfd5e38
---- Sort Parameters ------------------------------
sort_area_size                    170552320
sort_area_retained_size           170552320
sort_multiblock_read_count        1
max intermediate merge width      10408

*** 2012-03-05 22:05:26.127
sorcls: sorp ffffffff7dfd5e38
---- Sort Statistics ------------------------------
Input records                             5000000
Output records                            5000000
Total number of comparisons performed     58083117
  Comparisons performed by in-memory sort 58083117
Total amount of memory used               170552320
Uses version 2 sort
---- End of Sort Statistics -----------------------
6 мар 12, 00:32    [12200339]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
pravednik
Member

Откуда: Jacksonville, FL
Сообщений: 16268
pravednik
В итоге, те же цифры, что и в ноте.

Упс.. не совсем так. Нота немного не те данные дает.
У меня pga_aggregate_target должна быть в 10 раз больше.
6 мар 12, 00:36    [12200347]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
pravednik
pravednik
В итоге, те же цифры, что и в ноте.

Упс.. не совсем так. Нота немного не те данные дает.
У меня pga_aggregate_target должна быть в 10 раз больше.
Ну так _smm_max_size 10% от PAT. С параллелизмом побольше.
6 мар 12, 02:31    [12200431]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
wurdu
Ну так _smm_max_size 10% от PAT. С параллелизмом побольше.
В том смысле что _smm_px_max_size > _smm_max_size
6 мар 12, 02:39    [12200436]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18511
А "_pga_max_size", насколько я понимаю, на уровне сессии установить нельзя?

Я недавно перестраивал большие индексы, выставлял
alter session set "_smm_min_size" = 1048576;
alter session set "_smm_max_size" = 52428800;
alter session set "_smm_px_max_size" = 157286400;
alter session set "_smm_auto_min_io_size" =  256;
alter session set "_smm_auto_max_io_size" = 1024;
alter system set "_pga_max_size" = 17179852800 scope=memory;
PAT=8G.
Большого выигрыша не получил (session pga memory max 881618296)

На будущее, что я не учел?
6 мар 12, 02:55    [12200447]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Вячеслав Любомудров
А "_pga_max_size", насколько я понимаю, на уровне сессии установить нельзя?

Я недавно перестраивал большие индексы, выставлял
alter session set "_smm_min_size" = 1048576;
alter session set "_smm_max_size" = 52428800;
alter session set "_smm_px_max_size" = 157286400;
alter session set "_smm_auto_min_io_size" =  256;
alter session set "_smm_auto_max_io_size" = 1024;
alter system set "_pga_max_size" = 17179852800 scope=memory;
PAT=8G.
Большого выигрыша не получил (session pga memory max 881618296)

На будущее, что я не учел?
_PGA_MAX_SIZE может быть максимум 2Gb в 64 bit системе. Соответственно, _smm_max_size также не может превышать этот лимит. Поэтому теоретический максимум "_PGA_MAX_SIZE" = 2G и "_SMM_MAX_SIZE" = 20597152. "_pga_max_size" на уровне сессии нельзя. "_smm_max_size" вроде как можно, но по крайней мере на 11.2.0.3 я не вижу чтобы параметр вступал в силу. Только на alter system. _smm_max_size по идее это аналог sort_area_size и hash_area_size для ручного управления. Но вот я пытаюсь создать индекс которому достаточно workarea в 1.8 Gb, выставляю "_SMM_MAX_SIZE"=20597152, но в памяти размещается только 1.4 Gb - и дальше на диск. С workarea_size_policy=manual, sort_area_size=2147483647 все замечательно.
Ну и надо понимать что принципиальная разница в скорости будет при переходах optimal - onepass - multipass. Если под optimal памяти нет, то для onepass требования к памяти резко снижаются и выигрыша в наличии большой PGA уже обычно не наблюдается.
6 мар 12, 05:34    [12200468]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Т.е. основных проблем на мой взгляд две. Ограничение в 2 Gb на "_pga_max_size". У нас есть DWH где можно выделить сотни гигабайт на PGA, но смысла нет, т.к. Oracle просто не сможет использовать эту память. Разве что через дикие степени параллелизма, но тогда все упрется в CPU/диски. И алгоритмы onepass, multipath. Если нам, например, нужна workarea в 1.1gb, а памяти под нее есть 1Gb, то он не сможет эффективно задействовать эту память сократив объем темпа.
6 мар 12, 05:42    [12200471]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18511
Спасибо
Про лимит в 2G был не в курсе
С workarea_size_policy=manual у меня что-то тоже не сильно срослось (правда я всего гиг выставлял, что конкретно откушивало -- не замерял)
Да, ожидал изменения именно с multipass на onepass. Хотя, возможно, и пара гигов бы не спасла
6 мар 12, 05:42    [12200472]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
tim_scn
Member

Откуда: Ukraine
Сообщений: 384
pravednik,

всегда пожалуйста;)
6 мар 12, 10:03    [12200864]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
pravednik
Member

Откуда: Jacksonville, FL
Сообщений: 16268
wurdu
_PGA_MAX_SIZE может быть максимум 2Gb в 64 bit системе. Соответственно, _smm_max_size также не может превышать этот лимит. Поэтому теоретический максимум "_PGA_MAX_SIZE" = 2G и "_SMM_MAX_SIZE" = 20597152

Подскажите, _SMM_MAX_SIZE ведь в килобайтах.
Тогда 20597152 ~ 19Gb ?
Или я немного не так понял ?
6 мар 12, 11:11    [12201310]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
pravednik
wurdu
_PGA_MAX_SIZE может быть максимум 2Gb в 64 bit системе. Соответственно, _smm_max_size также не может превышать этот лимит. Поэтому теоретический максимум "_PGA_MAX_SIZE" = 2G и "_SMM_MAX_SIZE" = 20597152

Подскажите, _SMM_MAX_SIZE ведь в килобайтах.
Тогда 20597152 ~ 19Gb ?
Или я немного не так понял ?
Да все так. У меня 5-ка чего-то затесалась не к месту.
6 мар 12, 11:15    [12201352]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
Paranoiac
Member

Откуда: Saint Petersburg
Сообщений: 389
а имеется ли вообще хоть какая-то возможность в manual выставить sort_area_size>2G на 64 разрядной системе?
16 янв 13, 12:04    [13776990]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Paranoiac
а имеется ли вообще хоть какая-то возможность в manual выставить sort_area_size>2G на 64 разрядной системе?
Нет.
16 янв 13, 12:20    [13777114]     Ответить | Цитировать Сообщить модератору
 Re: Create index. Disk sorts  [new]
ORA__SQL
Member

Откуда: Moscow
Сообщений: 1774
А почему бы не распараллелить сортировку:
SQL> create table my_table(n number);
 
Table created

SQL> explain plan for create index my_index on my_table(n);
 
Explained

SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2778074136
--------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |          |    82 |  1066 |     3   (0)| 00:00:0
|   1 |  INDEX BUILD NON UNIQUE| MY_INDEX |       |       |            |
|   2 |   SORT CREATE INDEX    |          |    82 |  1066 |            |
|   3 |    TABLE ACCESS FULL   | MY_TABLE |    82 |  1066 |     2   (0)| 00:00:0
--------------------------------------------------------------------------------
Note
-----
   - estimated index size: 65536  bytes
 
14 rows selected

SQL> explain plan for create index my_index on my_table(n) parallel 32;
 
Explained

SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 535124730
--------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT   |          |    82 |  1066 |     2   (0)| 00:00
|   1 |  PX COORDINATOR          |          |       |       |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |    82 |  1066 |            |
|   3 |    INDEX BUILD NON UNIQUE| MY_INDEX |       |       |            |
|   4 |     SORT CREATE INDEX    |          |    82 |  1066 |            |
|   5 |      PX RECEIVE          |          |    82 |  1066 |     2   (0)| 00:00
|   6 |       PX SEND RANGE      | :TQ10000 |    82 |  1066 |     2   (0)| 00:00
|   7 |        PX BLOCK ITERATOR |          |    82 |  1066 |     2   (0)| 00:00
|   8 |         TABLE ACCESS FULL| MY_TABLE |    82 |  1066 |     2   (0)| 00:00
--------------------------------------------------------------------------------
Note
-----
   - estimated index size: 65536  bytes
 
19 rows selected
16 янв 13, 12:32    [13777219]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить