Cache buffer chains. Логическое чтение (LIO) - это плохо?

dbstalker, 28 октября

Большинство (или я ошибаюсь?) начинающих администраторов добиваются того, чтобы все (на сколько это вообще возможно) необходимые на протяжении дня данные для работы пользователей находились в буферном кэше, чтобы им приходилось, как можно меньше обращаться к диску за данными. То есть администраторы стремятся к увеличению логического( LIO ) чтения и, соответственно, уменьшению физического ( PIO ) чтения. Показателем этой деятельности есть buffer hit ratio, который стремится к 100%. При высоком значении этого показателя считается, что проблем с чтением данных в базе нет.

Но бывают такие ситуации, что логическое чтение - это зло. Это случается тогда, когда большое число логический чтений приводит к избыточному использованию CPU и защелок, к конкуренции за защелки. А это в свою очередь приводит к росту ожиданий серверных процессов и, значит, наш пользователь ощущает явное замедление работы своего приложения. И ему как-то все равно, что наш buffer hit ratio равен 100%. Вся наша оптимизация оказалась безрезультативной. В тоже время стоит заметить, что конкуренция за защелки – явление весьма редкое, говорящее о неувязках при выполнении кода, либо о большом количестве процессов, в одно и тоже время выполняющих одинаковые действия. О том насколько дорого логическое чтение, стоит ли бороться с физическим чтением, как уменьшить избыточное чтение прочитайте в замечательной статье Why You Should Focus on LIOs Instead of PIOs .Cary Millsap/Hotsos Enterprises, Ltd.

А дальше, немного в нашем контексте о Cache buffer chains.

Давайте вспомним о защелках, групповых защелках, буферном кэше, x$bh, цепочках блоков и все с этими понятиями связанное.

Предположим, что наша база стала «тормозить», с помощью нехитрых манипуляций (v$latchholder,v$latch_children (miss , sleeps), v$system_event, V$SESSION_WAIT, V$SESSION_WAIT_HISTORY) удалось выяснить – непомерно поднялся уровень CBC (Cache buffer chains). Что это означает?

Дальше идут личные мои умозаключения, поэтому к ним прошу относиться очень осторожно, скептически.

Блоки в буферном кэше организованы в цепочки, каждая цепочка имеет защелку. Если очень многим пользователям в одно и тоже время нужен один и тот же блок или блоки, которые находятся в одной цепочке, вот в этих случаях и наблюдается высокий уровень Cache buffer chains. То есть все наши пользователи борются за одну защелку, стерегущую цепочку блоков. Если буфер данных у Вас большой, то, возможно, в цепочке находится один блок. Значит, в этом случае все сражаются за один блок (но тогда растет еще и buffer busy waits). Возможен также вариант, что Вы очень значительно увеличили размер буферного кеша, а число защелок _db_block_hash_latches осталось прежним. Поэтому цепочки образовались достаточно длинными, следовательно, поиск по ним значительно увеличился по времени, а это привело к возникновению конкуренции.

Или, например, неэффективный план запроса спровоцировал излишнее LIO: из-за неподходящего индекса серверный процесс сначала читает блоки, а затем выясняется (после использования фильтра), что эти блоки нам не нужны. Хотя интенсивно потреблялись ресурсы: CPU, защелки.

Наши дальнейшие действия должны быть направлены на уменьшение LIO. Для начала найдем объекты с горячими блоками:

select bh.tch,bh.tim,object_.owner,object_.object_name,object_.object_type
from x$bh bh,v$latch_children l_c,dba_objects object_  
where owner not in ('SYS','SYSMAN') and l_c.name = 'cache buffers chains' and tch<>0 and bh.obj=object_.data_object_id and bh.hladdr = l_c.addr 
order by 1 desc

Я не удивлюсь, если этим объектом окажется sys.dual! Классический пример: использование без особой надобности select sysdate from dual или select seq_my.nextval from dual.

Предположим, что нашли объекты с горячими блоками. С помощью v$sqlarea ищем запросы, вызвавшие работу с «горячими» блоками.Следующий запрос может быть Вам полезным в этом:

select tch,owner,object_name,sql_text from v$sqlarea s,
(select * from (select bh.tch,bh.tim,object_.owner,object_.object_name,object_.object_type
from x$bh bh,v$latch_children l_c,dba_objects object_  
where owner not in ('SYS','SYSMAN','SYSTEM') and l_c.name = 'cache buffers chains'and object_.object_type='TABLE'
 and tch>10 and bh.obj=object_.data_object_id and bh.hladdr = l_c.addr order by tch desc) where rownum<10) b
where instr(s.sql_text,object_name)>0

А дальше возможны варианты:

  1. менять запросы.
  2. менять среду выполнения этих запросов.

По поводу первого варианта: идти по этому пути самое правильное решение. Оптимизация запросов, приводящих к избыточному логическому чтению - это и есть лечение болезни. Методы борьбы с этим явлением: проанализировать план выполнения запроса, а далее - переписать запрос, добавить/изменить индексы, настроить запросы с помощью hint , возможно стабилизировать план запроса.

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

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

  • Увеличивать число защелок?
  • Увеличивать (уменьшить – поможет в незначительном числе случаев) размер буферного кэша? (Может дать увеличение числа хэш бакетов, а если увеличить до более, чем 2048М, то и число защелок увеличится. Таким образом, произойдет изменение перераспределения блоков в цепочках, цепочки уменьшатся.)
  • Работать над параметрами db_block_hash_latches,db_block_hash_buckets?
  • Если проблемы вызваны непомерной нагрузкой на индекс (index root), то значение _db_handles_cached с 5 поменять на 10?
  • Изменять pctfree, inittrans в объектах с горячими блоками? (Уменьшит число записей в блоке)
  • Пытаться использовать пулы KEEP/RECYCLE?
  • Сделать перемещение таблицы /реблид индекса? (чтобы изменились адреса составляющих его блоков).
  • alter session set events 'immediate trace name flush_cache'; ? (Недокументированное очищение кэша буферов. Будьте осторожны!!!!)
  • Изменить PGA_AGGREGATE_TARGET?
  • Установить space_management=auto ?
  • Пробовать сегментировать, использовать блоки разного (как правило, меньшего) размера для горячих объектов?
  • Попробовать использовать реверсивные индексы (reverse)? В индексе с обращением ключа (reverse-key index) применяется обращение байтов индексируемой колонки числового типа. Этот прием позволяет получать равномерное распределение значений колонок среди блок-листов индекса со структурой B-Tree. Этот индекс хорошо подходит для индексирования колонок с последовательной нумерацией или нумерацией с заданным шагом. Заметим, что такие индексы применяются только для возвращения отдельных строк, и с их помощью нельзя выполнить поиск значений в некотором диапазоне. Вы не можете применить опцию REVERSE к битовым индексам и исключительно индексным таблицам.

Отвечать на все эти вопросы ВАМ. Будьте осторожны! Решение за Вами.

Обязательно учтите мнение Тома Кайта:

Latch: cache buffers chains

Possible Causes

  1. Repeated access to a block (or small number of blocks), known as a hot block
  2. From AskTom:

Contention for these latches can be caused by:

  • Very long buffer chains.
  • very very heavy access to the same blocks.

Possible Suggestion

From AskTom:

When I see this, I try to see what SQL the waiters are trying to execute. Many times, what I find, is they are all running the same query for the same data (hot blocks). If you find such a query -- typically it indicates a query that might need to be tuned (to access less blocks hence avoiding the collisions).

If it is long buffer chains, you can use multiple buffer pools to spread things out. You can use DB_BLOCK_LRU_LATCHES to increase the number of latches. You can use both together.

The cache buffers chains latches are used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache. Contention on this latch usually means that there is a block that is greatly contended for (known as a hot block).

To identify the heavily accessed buffer chain, and hence the contended for block, look at latch statistics for the cache buffers chains latches using the view V$LATCH_CHILDREN. If there is a specific cache buffers chains child latch that has many more GETS, MISSES, and SLEEPS when compared with the other child latches, then this is the contended for child latch.

This latch has a memory address, identified by the ADDR column. Use the value in the ADDR column joined with the X$BH table to identify the blocks protected by this latch. For example, given the address (V$LATCH_CHILDREN.ADDR) of a heavily contended latch, this queries the file and block numbers:

SELECT OBJ data_object_id, FILE#, DBABLK,CLASS, STATE, TCH
FROM X$BH
WHERE HLADDR = 'address of latch'
ORDER BY TCH;

X$BH.TCH is a touch count for the buffer. A high value for X$BH.TCH indicates a hot block.

Many blocks are protected by each latch. One of these buffers will probably be the hot block. Any block with a high TCH value is a potential hot block. Perform this query a number of times, and identify the block that consistently appears in the output. After you have identified the hot block, query DBA_EXTENTS using the file number and block number, to identify the segment.

After you have identified the hot block, you can identify the segment it belongs to with the following query:

SELECT OBJECT_NAME, SUBOBJECT_NAME
FROM DBA_OBJECTS
WHERE DATA_OBJECT_ID = &obj;

In the query, &obj is the value of the OBJ column in the previous query on X$BH.

Why You Should Focus on LIOs Instead of PIOs .Cary Millsap/Hotsos Enterprises, Ltd. Отличная статья (полностью ее найдёте в сети). В статье Вы найдете ответы на многие вопросы. Во сколько операции ввода-вывода на диск медленнее, чем в памяти? Что такое «логическое чтение»? Насколько рентабельнее логическое чтение от физического? Стоит ли стремиться к тому, чтобы все необходимые блоки были в памяти? Как побороть излишнее LIO?

Executive Summary

Many Oracle educators teach that reducing the number of PIO calls should be the top priority of SQL optimization. However, in our field work, we commonly eliminate 50% or more of the response time from slow Oracle applications, even after they’ve been tuned to execute no PIO calls. The secret is that Oracle LIO calls are more expensive than many people understand. In this paper, I explain the following research results:

  • LIO processing is the number-one bottleneck for many business processes today, even on systems with “excellent” database buffer cache hit ratios.
  • Excessive LIO call frequency is a major scalability barrier, because LIOs consume two of the system’s most expensive resources: CPU and latches.
  • Even if you could have an infinite amount of memory and achieve a perfect 100% database buffer cache hit ratio, your system will be inefficient and unscalable if it executes more Oracle LIO calls than it needs to.
  • The statistics that database administrators commonly track can lead you to believe that PIO processing is your bottleneck when it’s not. Do not increase disk or memory capacity until after you determine the impact of PIO latency upon your end-user response times.
  • If you will focus on LIO reduction from the very beginning of a SQL optimization task instead of PIO reduction, then you will usually eliminate most of your PIOs by side-effect, because most of your PIOs are motivated by LIO calls in the first place.

How to Eliminate LIOs

When either CPU service or waits for “latch free” is a big part of your users’ response times, then you probably have an LIO problem. If you do, then the most economically efficient way to solve the problem is probably to optimize the SQL that causes too many LIOs. A high-LIO SQL statement running in one program at a time is a problem for one user who is waiting for the program’s output. It’s possibly a problem for other users as well, if they are competing for scarce CPU and latch resources with the high-LIO statement. A high-LIO statement running in dozens of concurrent programs is a catastrophic problem for everyone. In both problems, it is difficult to make any real progress by masking the issue by manipulating memory or disks. The permanent long-term solution is to eliminate unnecessary LIOs.

It’s not so hard to do. Step one: focus your attention upon LIO reduction, not PIO reduction. Stop measuring performance by watching your database buffer cache hit ratio, especially for individual SQL statements. When you look at tkprof output, pay attention to the query and current columns. When you look at v$sql, pay attention to the buffer_gets column. Don’t stop optimizing if a query consumes more than 100 LIOs per row returned,even if you’ve figured out how to reduce the PIO count for the query to zero. Lookups and simple joins should require fewer than 10 LIOs per row returned.

By eliminating LIOs first (before you try to reduce the PIO count), you will save time and money, because you’ll be reducing the fundamental unit of work in Oracle from which most resource consumption derives. Remember, a program can have a PIO count of zero and still run for hours. But a program with a low LIO count will consume only a small amount of CPU service, a small number of latch acquisitions, and of course a necessarily small number of PIOs as well, because a small number of LIOs will nearly always motivate only a small number of PIOs. Only after you have optimized the LIO requirement of an Oracle program should you consider a memory of disk upgrade. The query optimization software in the Oracle kernel gets smarter with every release (for an interesting article on this subject, see [Gorman 2001]). But still, the Oracle performance analyst will encounter systems in which the number one cause of performance trouble is poor query optimization. Some of the more common mistakes that we see in the field include:

Executing unnecessary business functions. There is no more efficient or effective system optimization technique than to determine that a business is expending scarce system resources to produce output that either nobody looks at, or output whose business function can be suitably replaced by less expensive output. Performance analysts who isolate themselves from the functional requirements of a business application also isolate themselves from opportunities to eliminate unnecessary business functions. To optimize workload, you must understand the business of that workload.

Carrying massive amounts of row source data through several stages of a complicated execution plan, only to filter out most of the rows in a late stage of the plan. Use hints and stored outlines if you must, but force Oracle to filter data as early in an execution plan as you can. The idea that all full-table scans are bad. Full-table scans are efficient in their place; use them when they are more efficient than index-driven plans. If you are in doubt about whether a full-table scan is efficient, test it.

The idea that all nested loops plans are good. Nested loops execution plans are notorious for driving “excellent” database buffer cache hit ratios, because they tend to revisit the same Oracle blocks over and over again. Replacing nested loops plans with other joins (full-table scans driving either hash joins or sort-merge joins, for example) can sometimes result in spectacular performance improvements.

Failure to use array processing. Fetching data one row at a time also drives “excellent” database buffer cache hit ratios, because they also tend to cause Oracle to revisit the same blocks over and over again. Using Oracle array operations will reduce not only LIO frequencies, but often enormous amounts of network traffic as well.

Conclusion

In system optimization and capacity planning exercises since 1990, several of my colleagues and I have created reliable SQL performance forecasts by using the assumption that a LIO+PIO operation costs about 100 times as much as a bare LIO. However, many people believe that the relative cost is 10,000-to-1. Fortunately, the average latencies of LIO+PIO and bare LIO for an Oracle session are simple to compute. In this paper, I have explained how to use easily obtainable operational data to determine for certain which ratio is closer to the truth. In 71 trace files uploaded to www.hotsos.com, the relative performance difference is only a factor of 37. The average LIO latency in our test data is about 50 microseconds; that is, Oracle LIOs execute at a typical pace of only about 20,000 per second. The average PIO latency in our data is about 2 milliseconds, which is faster than typical disk hardware latencies because of the effects of various I/O subsystem memory buffers.

The “myth of 10,000” is more than just an inconsequential technical detail. The myth is dangerous because it motivates performance analysts to make a dreadful mistake: to assume that you can optimize a system by eliminating PIOs. On the contrary, the most important optimization goal for the Oracle performance analyst is to reduce LIO counts. Even if you could install 1-millisecond disks and an infinite amount of memory for your database buffer cache, your Oracle system performance would be constrained as it probably is now, by the CPU capacity required to process all of your application’s LIO calls.

In the field, Oracle performance analysts who focus on LIO reduction routinely eliminate 50% or more of the CPU and elapsed time required to perform key business functions. Not only does LIO call reduction yield a beneficial impact upon CPU consumption, but it eliminates potentially large segments of user response time wasted on “latch free” events. Even systems with no PIO calls can waste tens of hours of CPU time per day if they execute too many LIO calls. Ironically, systems with extremely high (99%+) database buffer cache hit ratios are especially good can-didates for LIO call reduction

7 комментариев

Прокоментировать

AlexGru
7 апреля 2009 г. в 12:01

Отличная работа. Хорошая статья.

Arju
2 июля 2009 г. в 18:24
Talifa
15 декабря 2011 г. в 13:19

стабилизировать план - можно с этого места поконкретнее

dbstalker
15 декабря 2011 г. в 17:37

стабилизировать план запроса - дать конкретные установки оптимизатору как выполнять данный запрос.

Talifa
15 декабря 2011 г. в 18:05

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

dbstalker
19 декабря 2011 г. в 14:39
Talifa
19 декабря 2011 г. в 14:46

Спасибо!

 

Новый комментарий

Я не спамер: введите суму 4+8



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Нужен поставщик Дропшиппинг
10 декабря, 1 ответа
КИНО КАФЕ!!!!!!!!!!!!!!!!
10 декабря, 1 ответа