Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: Неверная оценка оптимизатора

Интересует, почему оптимизатор неверно оценивает количество строк при чтении из индекса IX_Agent (оценка 3 строки, фактически 400 тыс.). Функция на основе данных в исторической таблице выводит агрегированные данные с разбивкой по дням.

Текст функции:
+
with cte as (
	select 
			StartTime
			,EndTime
			,StartDate
			,EndDate
			,operator
			,campaign

			,StateGroup
			,StateTotal


	FROM [dbo].[AgentActivity] A

	where EndTime >= @Date_Start and StartTime < @Date_End + 1
		and EndTime < @Date_End + 2
)


select	H.Interval
		,C.operator
		,sum(case when StateTotal = 'Total' then 
				datediff(ss
					,case when StartTime > Interval and StartTime < dateadd(day, 1, Interval) then StartTime
						else Interval
					end
					,case when EndTime < dateadd(day, 1, Interval) then EndTime
						else dateadd(day, 1, Interval)
					end
				) end) as Total

		,sum(case when StateGroup = 'Paused' then 
				datediff(ss
					,case when StartTime > Interval and StartTime < dateadd(day, 1, Interval) then StartTime
						else Interval
					end
					,case when EndTime < dateadd(day, 1, Interval) then EndTime
						else dateadd(day, 1, Interval)
					end
				) end) as Paused



from cte C
outer apply (
	select Interval
	from [dbo].[CalendarDays]
	where Interval >= StartDate
		and Interval <= EndDate
) H

where H.Interval >= @Date_Start and H.Interval < @Date_End + 1
group by operator, Interval

)



[dbo].[AgentActivity] - представление:
+
CREATE view [dbo].[AgentActivity] WITH SCHEMABINDING
AS

SELECT D.StartTime
		,D.EndTime

		,cast(D.StartTime as date) as StartDate
		,cast(D.EndTime as date) as EndDate
		,LA.operator
		,LC.campaign
		,LT.Call_Type
		,D.ContactID
		,S.State
		,S.DescCode_ForAgentView as OperatorState
		,case when S.State = 20 then 'Paused'
			when S.State = 30 then 'AdmWk'
			when S.State = 40 then 'Waiting'
			when S.State = 50 then 'WrapUp'
			when S.State in (70, 90) then 'Ringing'
			when S.State in (80, 100) then 'Talking'
		end as StateGroup

		,case when S.State >= 20 then 'Total' end as StateTotal

from [dbo].[TIMESTATS] D
inner join [dbo].[TIMESTATS_STATUSES] S
	on S.[State] = D.[State]
inner join (
	select	id
			,Names2 as operator
	from dbo.TIMESTATS_LOOKUP 
	where [Type] = 'A'
) LA
	on LA.id = D.agent
left join (
	select id
			,Names2 as campaign
	from dbo.TIMESTATS_LOOKUP 
	where [Type] = 'C'
) LC
	on LC.id = D.Campaign
left join (
	select id
			,Names2 as Call_Type
	from dbo.TIMESTATS_LOOKUP 
	where [Type] = 'T'
) LT
	on LT.id = D.ContactType


Индекс
+
CREATE NONCLUSTERED INDEX [IX_Agent] ON [dbo].[TIMESTATS]
(
	[Agent] ASC,
	[StartTime] ASC,
	[EndTime] ASC
)
INCLUDE ( 	[State])
Ответ: virtuOS,

Может быть, даже в моем, хотя не знаю, есть ли тот мой доклад на techdays.

В случае нескольких предикатов в Cardinality Estimator 70 (CE до 2014) использовалось предположение о независимости предикатов. И для вычисления общей селективности сложного предиката, селективности каждого простого предиката по умолчанию перемножались, что на практике оказывалось не всегда верно, т.к. часто получается, что данные в колонках между собой связаны.

В CE 120 использовать этот алгоритм по умолчанию перестали, вместо него стали использовать алгоритм, который называется Exponential Backoff, суть в том, что каждый последующий простой предикат в комплексном вносит все меньший и меньший вклад в общую селективность.

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

Главное просто знать то, что базовая гистограмма при переходе от одного оператора плана к другому изменяется в зависимости от условий и эта измененная гистограмма используется для оценок. Но поскольку для изменений до сих пор используются предположения, догадки и теория вероятности – эти изменения и оценка на их основе может сильно отличаться от реальности, что может приводить к неточностям.
Вопрос: CLR TVF SqlFunction и странное поведение оптимизатора запросов

Добрый день.

+ @@version
Microsoft SQL Server 2014 (SP1-CU4) (KB3106660) - 12.0.4436.0 (X64) 
Dec 2 2015 16:09:44
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )


В CLR реализована табличная ф-я объявленная так:
+
  [DllImport("rpcrt4.dll", SetLastError = true)]
  static extern int UuidCreateSequential(out Guid guid);

  [SqlFunction(
      IsDeterministic = false,
      IsPrecise = false,
      FillRowMethodName = "fill_guid",
      TableDefinition = "[RN] int, [SGuid] uniqueidentifier")]
  public static IEnumerable SequentialId(SqlInt32 Count)
  {}


Вопрос:
Почему запросы следующего вида
+
select *
from dbo.SomeTable
cross apply dbo.SequentialId(1)
-- or
select *, (select [SGuid] from dbo.SequentialId(1))
from dbo.SomeTable


оптимизатор запросов "оптимизирует" так, что dbo.SequentialId() вызывается только один раз (то вверх плана переместит, то table spool перед ф-ей воткнет).

Если сказано же "IsDeterministic = false".

Или я чего то не понимаю ?
Ответ:
invm
Greenhorn,

Чтобы табличная функция с константным параметром вызывалась для каждой строки таблицы нужно немного схитрить. Например, так:
select *
from dbo.SomeTable a
cross apply dbo.SequentialId(1 + a.SomeField - a.SomeField) b

Спасибо, и так тоже работает.
А так смешнее (и быстрее):
select *
from dbo.SomeTable a
cross apply dbo.SequentialId(1 ^ a.SomeField ^ a.SomeField) b
Вопрос: Насколько можно считать полезными типовые подсказки оптимизатора запросов

Наверняка многие в плане выполнения запроса,(есть еще отдельно системные вьюхи) обращали внимание на возможные подсказки. Ну типа того что рекомендуется добавить такой то такой индекс на такую то таблицу. Честно говоря я и сам знаю что и куда добавить, как то эту информацию пропускал мимо ушей. Интересует мнение, насколько эта информация бывает полезной? Возникла следующая мысль. В нашей компании реализовали продукт который может один в один воспроизводить трафик через MSSQL. То есть можно сделав бэкап с рабочей БД и записав трафик(например 30 мин.) затем все один в один воспроизводить на отдельном сервере. Появилась идея что можно было бы используя подсказки оптимизатора запросов последовательно добавлять(затем разумеется убирать) индексы. После чего выполняя запрос смотреть насколько уменьшилось время(ЦПУ,ридсы и т.п.) запроса. Можно увидетть насколько тяжелым стал индекс и самое главное все это можно делать автоматом. На выходе получаем список индексов и список запросов с информацией что насколько ускорилось. (разумеется там есть вопросы с комбинацией индексов). На основании этой информации можно уже принимать решение. Своего рода автоматизация оптимизации. Самый главный вопрос, насколько эти подсказки существенны? (разумеется все точно покажет практика)
Ответ:
МуМу
Вообщем, в чем суть. На рабочей системе наугад применять подсказки опасно.
Для этого, те компании у которых есть деньги, нанимают профессионалов, которые делают это не наугад.

А с Missing Index основную проблему я вижу в том, что оптимизатор дает эти советы на основании статистик, не всегда актуальных, а иногда и очень кривых, а также опираясь на значения параметров или же их отсутствие. Я к тому, что я бы не стал это автоматизировать, потому как на эту информацию сначала должен посмотреть человек и решить нужен реально этот индекс или же оптимизатор ошибся конкретно в своих оценках.
Представьте например, есть запрос, с условной ценой выполнения в 100 единиц, оптимизатор предложит индекс, и мы его автоматом создадим, тесты покажут уменьшение цены запроса до 85. А на самом деле, если поправить статистику/переписать запрос/воткнуть хинт/пофиксать parameter sniffing, то можно уменьшить стоимость до 5 единиц. Ну и смысл тогда от такой автоматизации?

Для меня Missing Index это больше как индикатор - оптимизатор считает что с этим запросом что то не так.

Что возможно было бы полезно, так это связь между "подсказками" и запросами, которые должны выиграть от создания этого индекса.
Вопрос: Знатокам оптимизатора. Объясните почему так ?

Oracle 11g

Почему так оптимизатор себя ведет, по частям все правильно
и по логике запроса конечный запрос, состоящий из двух частей
тоже должен также оценить.
так нет же... В чем загвоздка ?

Что ему так сносит крышу в оценке на простой, практически тривиальной ситуации ????

статистика по таблицам собрана. все поля not null

Сам запрос:
SELECT count(*) FROM large_tbl a WHERE
   (a.code1,a.code2) in  
   ( SELECT code1 , code2 FROM small_tbl WHERE name = 'Name_A' AND (условие2) ) 
and (a.code1='AAA') and (a.code2='BBB')


Сначала смотрим части запроса
Все правильно оценивает по отдельности

1. Такой запрос в отдельности - правильно оценивает (1 строка возвращается0
SELECT code1, code2 FROM small_tbl WHERE name = 'Name_A' and (условие2) and (code1='AAA') and (code2='BBB') 
------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Rows  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |     1 |
|*  1 |     TABLE ACCESS BY INDEX ROWID| small_tbl             |     1 |
|*  2 |      INDEX RANGE SCAN          | i_small1              |     1 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(условие2)
   2 - access("name"='Name_A' and (code1='AAA') and (code2='BBB'))


2. Другая часть запроса - тоже правильно оценивает (~13млн строк)
SELECT count(*) FROM large_tbl WHERE (code1='AAA') and (code2='BBB')
--------------------------------------------------------
| Id  | Operation         | Name               | Rows  |
--------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |     1 |
|   1 |  SORT AGGREGATE   |                    |     1 |
|*  2 |   INDEX RANGE SCAN| i_large1           |    13M|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("code2"='AAA' AND "code2"='BBB')


3. Но все вместе теперь - неправильно, резко урезает оценку итоговую
(хотя по факту - получается по-прежнему 13млн строк)
SELECT count(*) FROM large_tbl a WHERE
   (a.code1,a.code2) in  
   ( SELECT code1 , code2 FROM small_tbl WHERE name = 'Name_A' AND (условие2) ) 
and (a.code1='AAA') and (a.code2='BBB')
------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Rows  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |     1 |
|   1 |  SORT AGGREGATE                |                       |     1 |
|   2 |   NESTED LOOPS                 |                       |   270K|
|   3 |    SORT UNIQUE                 |                       |     1 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| small_tbl             |     1 |
|*  5 |      INDEX RANGE SCAN          | i_small1              |     1 |
|*  6 |    INDEX RANGE SCAN            | i_large1              |  2128K|
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(условие2)
   5 - access("name"='Name_A' AND "code1"='AAA' AND "code2"='BBB')
   6 - access("A".code2"="code2" AND "A"."code1"="code1")
       filter("A"."code1"='AAA' AND "A"."code2"='BBB') 

Почему оценка вдруг по index range i_large1 падает сразу в 6 раз (а если промониторить реальное выполнение - там естественно будет при проходе по-прежнему 13млн) ?

причем если условие (and (a.code1='AAA') and (a.code2='BBB')
просто внести в подзапрос, убрав снаружи - то правильно оценивает (13 млн строк)

или если в подзапрос добавить hint /*+ NO_UNNEST */ - тоже правильная оценка

Почему важно - т.к. из-за этого, при дальнейшем усложнении запроса оптимизатор все дальше отрывается от реальности и становится совсем нехорошо.
Ответ:
xtender
ничего_не_понимаю.
SELECT count(*) FROM large_tbl a WHERE
   (a.code1,a.code2) in  
   ( SELECT code1 , code2 FROM small_tbl WHERE name = 'Name_A' AND (условие2) ) 
and (a.code1='AAA') and (a.code2='BBB')
Во-первых, не понимаю зачем так писать, если code1 и code1 и так на входе..

так приложение написано. проверка по in - как база, а в зависимости от ситуаций разные доп.условия динамически добавляются, ну вот иногда и появляются такие конструкции, из-за которых плохеет. Почему оптимизатор только в этом запутывается неясно, ведь догадывается же он (по плану видно), что внутри получается конструкция вида
 ... in  ( SELECT code1 , code2 FROM small_tbl WHERE name = 'Name_A' AND (условие2) and (code1='AAA') and (code2='BBB')
-т.е. фактически все должно свестись к проверке - есть ли что-то в подзапросе, и если не ноль, тогда проход по large_tbl по этим 13млн, удовлетворяющих условию (a.code1='AAA') and (a.code2='BBB').
Ну почему он такой тупой ? Можно еще понять ситуации, которые от распределения зависят, от логики. Но здесь-то проще простого все.
Вопрос: Глюк оптимизатора.

DB2 9.7
Имеем таблицу миллион записей, 170 полей, поля без фанатизма т.е. BLOB и т.п. нет, длина записи 1400.

Выбираем:
Select * from changes.man
where fa='Иванов' and im='Иван' and ot='Иванович'

Выбирает десяток строчек мгновенно.

Выбираем:
Select * from changes.man
where fa='Иванов' and im='Иван' and ot='Иванович'
order by ChangeDate

1й раз думает минут 7, последующие секунд 40-50. Без order by - мгновенно, причём, этот факт до оптимизатора не доходит и он продолжает упорствовать. Если подождать минут 10-15 начинает думать больше 50 секунд.

При попытке сгенерить план запроса ругается:
[IBM][CLI Driver][DB2/NT64] SQL0220N Столбец "DB2ADMIN.EXPLAIN_OBJECT" таблицы объяснения "PAGES" не содержит подходящего определения или же отсутствует. SQLSTATE=55002

Т.е., я так понимаю, нарушилось что-то в данных, на основании которых оптимизируются запросы.

Очень хочется понять, куда копать.

На всякий случай индексы:
(Почему именно так, даже не догадываюсь - не моё)
+
ALTER TABLE "CHANGES "."MAN"
ADD PRIMARY KEY
("ID",
"CHANGEDATE");

CREATE INDEX "CHANGES "."MANFIO" ON "CHANGES "."MAN"
("FA" ASC,
"IM" ASC,
"OT" ASC,
"POL" ASC,
"RDAT" ASC,
"NPERS" ASC,
"CHANGEDATE" ASC,
"ID" ASC)

COMPRESS YES ALLOW REVERSE SCANS;

CREATE INDEX "CHANGES "."MANSNILS" ON "CHANGES "."MAN"
("NPERS" ASC,
"CHANGEDATE" ASC,
"ID" ASC)

COMPRESS YES ALLOW REVERSE SCANS;

CREATE INDEX "DB2ADMIN"."IDX_MAN_RE" ON "CHANGES "."MAN"
("RE" ASC,
"ID" ASC,
"CHANGEDATE" ASC)
PCTFREE 10
COLLECT SAMPLED DETAILED STATISTICS
COMPRESS YES ALLOW REVERSE SCANS;

CREATE UNIQUE INDEX "DB2ADMIN"."IDX1602020502141" ON "CHANGES "."MAN"
("CHANGEDATE" ASC,
"ID" ASC)

COLLECT SAMPLED DETAILED STATISTICS
COMPRESS YES ALLOW REVERSE SCANS;
Ответ: Page size для таблицы не могу определить. Но надо думать, что 4096.

А TEMPSIZE: (Temporary Table Page Size) 4096.
и Width of Rows: 1387

Сортировать в таких условиях такие строки на диске должно быть очень дорого. Но оптимизатор считает, что их должно остаться всего 16. Может, перестраховывается?
Вопрос: Клипперную БД интеллектуально перегнать в ОРакл

Есть старая клипперная БД на 40 гб. Бухучет предприятия до 2007-ого года.
ПО к этой БД есть, но на вин-7 не запускается без dosbox, т.е. считаем, что его нет. Да и никто не помнит, как оно вообще работало.
Данные устарели, но раз в год кому-то может понадобится одна-две строки. Какие именно строки понадобятся в следующем году и понадобятся ли вообще, никто не знает.

В общем, приказали нам эти дбф-ки перегнать в Оракл. Пусть там лежат. Кому надо, и в Оракле найдёт.
Оракл 11.2.

100 тыщ дбф-ок.
Куча в основном похожих файлов – кредит, дебет, сальдо, журналы и прочая хрень каждого счета за каждый год.
Но сбивать все кредиты в одну таблицу не получится, т.к. файлы весьма похожие, но структура может отличаться.

Из этого НЕ НАДО делать реляционную БД.
Нужна гениальная идея:
- как всё это хранить
- чтобы место много не занимало
- легко искать произвольный текст. Например, как решали на днях: запустили ФАРом поиск по всем папкам и dbf-кам, искали ОКПО – код предприятия. Нашли несколько похожих таблиц, данные выгрузили в Эксель, пользователь остался доволен.

Варианты:
1. Сделать в отдельной схеме (нескольких схемах) 100 тыщ разных таблиц
Плюсы – проще всего – есть прога для заливки дбф в БД с созданием таблиц. Написать скрипт для заливки всех таблиц на основании результата dir - час времени. И 8 часов будет работать, пока не зальёт - и все затраты.
Минус – много таблиц получается и сложно искать в них произвольный текст.
Минус – ограничение на длину наименования таблицы. Полный путь не влезет в название таблицы.
2. Сделать EAV-таблицы. Основная таблица - Field_id, row_number, Value_строка, Value_Намбер, Value_дата.
Минусы: на каждое поле будет уходить много доп. места на Field_id и row_number.
3. Хранить каждую таблицу отдельной строкой в XML или JSON. Сделать общую таблицу из трех основных полей: Название таблицы с полным путём, CLob с данными, sql-запрос для получения данных. Полнотекстово проиндексировать.
Минус – куча места будет уходить под название атрибутов и прочую информацию.
Ответ: для 100 тыщ дбф-ок рентабельно отрезать от них заголовки ( все до первого перевода строки в файле) ,
скормить их скрипту , который по заголовкам сформирует
create table и par файлы для sqlldr
текст за хедерами скормить sqlldr-y
потом за отдельные деньги
селектами по каталогу
можно сформировать разряженные таблицы
и консолидировать.

Вы думаете это весь профит ?
никак нет
Разместить рекламу в инете ,
быстрая загрузка дбф- ников любой структуры в оракл , (не) дорого .
:)
Вопрос: Переписать триггеры под оракл

Здравствуйте форумчане , помогите переписать код триггеров под оракл?

Код SQL
1
2
3
4
5
6
7
8
9
10
AS
BEGIN
IF (deleting) THEN
BEGIN
IF (EXISTS(SELECT COUNT(*) FROM doc_vklad WHERE doc_vklad.id_vkladchik=OLD.id_vkladchik)) THEN
exception delete_not;
ELSE
DELETE FROM vkladchik WHERE vkladchik.id_vkladchik = OLD.id_vkladchik;
END
END
Код SQL
1
2
3
4
5
6
7
8
AS
BEGIN
IF (inserting) THEN
BEGIN
INSERT INTO doc_vklad (doc_vklad.data_nach, doc_vklad.num_doc, doc_vklad.bank, doc_vklad.data_okonch, doc_vklad.id_vklad,doc_vklad.id_otdelenie_banka,doc_vklad.id_vkladchik,doc_vklad.id_bank_work)
VALUES (CURRENT_DATE ,NEW.num_doc, NEW.bank, NEW.data_okonch, NEW.id_vklad,NEW.id_otdelenie_banka,NEW.id_vkladchik,NEW.id_bank_work);
END
END
Код SQL
1
2
3
4
5
6
7
8
9
AS
BEGIN
IF (deleting) THEN
BEGIN
IF (EXISTS(SELECT COUNT(*) FROM prixod_order WHERE prixod_order.id_bank_work=OLD.id_bank_work) AND
EXISTS(SELECT COUNT(*) FROM rasxod_order WHERE rasxod_order.id_bank_work=OLD.id_bank_work)) THEN
exception delete_not;
END
END
Код SQL
1
2
3
4
5
6
7
8
AS
DECLARE variable c INTEGER;
BEGIN
SELECT COUNT(*) FROM vklad WHERE vklad.id_v_vklad=OLD.id_v_vklad INTO: c ;
IF (deleting AND c>0) THEN
exception delete_not;
 
END
Ответ: Grossmeister, делал эти триггеры по примерам с другой базы для лабораторной, вот теперь нужно подобные для оракла , а синтаксис оракла не понимаю, постоянно ругается
Вопрос: не могу подключится оракл датабасе

привет ребята
не могу подключится оракл датабасе


это listener

---------------------------------

# listener.ora Network Configuration File: D:\app\AZ\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\AZ\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\AZ\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

ADR_BASE_LISTENER = D:\app\AZ

---------------------------------------------



а это tnsnames



# tnsnames.ora Network Configuration File: D:\app\AZ\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)





и снимок подключения с sql developer загружил



спасибо за помош

К сообщению приложен файл. Размер - 105Kb
Ответ: если при условии, что листенер запущен, все настроено tnsnames верно в 64bit и клиентское ПО не видит базу то необходимо установить 32 бит клиента оракла и в клиенте так же настроить tnsnames
Вопрос: Репликация с Оракл

Добрый день!

MS SQL Server Enterprise edition 2014 sp2

Модно ли на сервер создать публикацию на несколько таблиц а подписчиком что бы был Оракл?
Ответ:
andrew shalaev
Добрый день!

MS SQL Server Enterprise edition 2014 sp2

Модно ли на сервер создать публикацию на несколько таблиц а подписчиком что бы был Оракл?


а что ?
Вопрос: В каких случаях оптимизатор может выбрать более дорогой execution plan?

Добрый день,

Интересует общий вопрос:
Всегда ли оптимизатор будет выбирать план запроса основываясь на минимальных значениях phisical/logical reads & cpu time. Если нет, то в каких случаях?

Есть ли в MSSQL возможность как в Оракле подкручивать оптимизатор меняя его приоритеты с минимизации затрат ресурсов на минимизацию response time?

SQL Server 2008 R2
Ответ: Большое спасибо за ответы и ссылки.