Как узнать, какие столбцы наиболее доступны или часто используются в Oracle 12c

Я имею дело с огромной таблицей с 618 столбцами. Команда не хочет приступать к исследованию того, какие столбцы не используются, и изменению кода для их удаления, и я понимаю ограничения по времени. Одно из предложений состоит в том, чтобы определить, какие столбцы из этой таблицы являются наиболее доступными или часто используемыми, и изменить порядок столбцов. Я оставил настройку экспертам, и мне поручили идентифицировать такие столбцы.

Итак, я запрашиваю all_tab_columns, пытаясь найти простой способ выполнить задачу. Мое внимание привлекли четыре столбца: Num_distinct, Density, Num_nulls, Sample_size. Я подумал, что соотношение между sample_size и num_nulls может быть способом идентификации наиболее часто используемых столбцов. Чем ближе это соотношение к 1, тем выше вероятность использования. Но, как обычно, это не может быть так просто, потому что я обнаружил, что получаю сумасшедшие коэффициенты, поскольку у меня есть столбцы в пределах NUM-NULLS в 80Millions-ish, а SAMPLE_SIZE составляет всего 141.

Интересно, была ли у кого-то похожая ситуация или у кого-то есть предложение о том, что является лучшим подходом для этого. Заранее спасибо.

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

выберите ts.owner, ts.table_name, ts.column_name, tc.column_id, t.num_rows, ts.num_nulls, случай, когда nvl(ts.num_nulls,0) > 0, затем (1 - round(((ts.num_nulls / t .num_rows)),6)) когда nvl(ts.num_nulls,0) = 0, то 1 end UsagePerc, ts.num_distinct, ts.density from all_tables t join all_tab_col_statistics ts on ts.owner = t.owner and ts.table_name = t.table_name объединяет all_tab_columns tc на tc.owner = ts.owner и tc.table_name = ts.table_name и tc.column_name = ts.column_name, где t.owner = 'THE_OWNER' и t.table_name = 'THE_HUGE_TABLE' в порядке ts. num_nulls, описание ts.density


oracle usage-statistics oracle12c database-performance database-tuning
person jjsai    schedule 26.03.2020    source источник
comment
Это будет довольно сложно сделать. Вы можете запросить v$sqlarea, который показывает недавно выполненные запросы, включая #times выполненных и текст sql. найдите это с результатами из user_tab_columns для рассматриваемой таблицы. Обратите внимание, что v$sqlarea является временным, и данные в нем будут постоянно меняться. Кстати, наличие таблицы с 618 столбцами звучит как гигантский недостаток дизайна. Удачи!   -  person OldProgrammer    schedule 27.03.2020
comment
Я попробую разные подходы и представлю то, что у меня есть. Я проверю твой и посмотрю, что у меня получится. наличие таблицы с 618 столбцами звучит как гигантский недостаток дизайна. Поверьте мне, я сказал им, но, видимо, уже слишком поздно. Большое вам спасибо!   -  person jjsai    schedule 27.03.2020
comment
Вы хотите найти столбцы, которые чаще других используются в запросах, и изменить таблицу, чтобы эти строки были первыми при выборе ? Это странная идея, и я не думаю, что вы что-то выиграете с таким подходом. Что вы *хотите получить в конце концов? Я даже не понимаю цели.   -  person Thorsten Kettner    schedule 27.03.2020
comment
Какую проблему вы наблюдаете? Это большое время анализа?   -  person Marmite Bomber    schedule 27.03.2020
comment
Согласитесь с другими, что изменение порядка столбцов не обязательно поможет вам в чем-либо; удаление устаревших столбцов уменьшит ввод-вывод при полном сканировании. Информация в all_tab_columns сообщает вам метрики о данных в столбцах, но не может сказать вам, как эти данные используются или как часто они используются приложением. Кроме того, точность определения столбцов, используемых при проверке SQL, не гарантируется — у вас нет возможности узнать, захвачены ли вы все возможные операторы SQL. Единственный способ узнать наверняка — просмотреть исходный код приложения; все остальное просто предположение.   -  person pmdba    schedule 27.03.2020
comment
См. здесь: asktom.oracle. com/pls/apex/, чтобы узнать о других преимуществах сохранения таблицы до 255 столбцов или меньше.   -  person pmdba    schedule 27.03.2020
comment
Спасибо всем за ваши комментарии. Я здесь только посыльный. Я знаю о значительном влиянии 618 столбцов на производительность. Я не специалист по настройке, поэтому не знаю, почему команда решила это сделать. Моя цель (задача, поставленная передо мной) — предоставить список наиболее часто используемых столбцов из одной конкретной таблицы. Еще раз спасибо за все ваши комментарии. Я ценю вашу помощь.   -  person jjsai    schedule 27.03.2020

Ответы (1)


arrow_upward
0
arrow_downward

Лучший способ узнать, как часто используются столбцы, — это включить (мелкозернистый) аудит и дать ему поработать какое-то время. Затем просмотрите полученные утверждения:

grant create session, unlimited tablespace 
  to u 
  identified by u;

create table u.t (
  c1 int, c2 int, c3 int
);

exec dbms_fga.add_policy ( 'U', 'T', 'AUDIT_COLS', enable => true, statement_types => 'INSERT,UPDATE,DELETE,SELECT' );

conn u/u

insert into t ( c1 ) values ( 1 );
insert into t values ( 2, 2, 2 );

select c2 from t;
select * from t;

update t
set    c3 = 1
where  c1 = 1;

delete t
where  c2 is null;
commit;

conn chris

select sql_text 
from   dba_fga_audit_trail;

SQL_TEXT                               
insert into t ( c1 ) values ( 1 )       
insert into t values ( 2, 2, 2 )        
select c2 from t                        
select * from t                         
update t
set    c3 = 1
where  c1 = 1    
delete t
where  c2 is null

Обратите внимание, что вы получаете полные операторы, поэтому вам нужно немного потрудиться, чтобы извлечь столбцы.

person Chris Saxon    schedule 27.03.2020
comment
Спасибо, я уточню у команды. - person jjsai; 27.03.2020