| 1:36p |
sliktā prakse
Izrādās, 9% no visiem DML ir hintoti. Un ne jau ar ieteiktajiem variantiem (first/all), tie aizņem tikai 0.06%.
Pa vietām:
1. - index(
2. - ordered
3. - use_nl
select
--sum(1) as total,
sum(case when upper(text) like '%SELECT%'
OR upper(text) like '%UPDATE%'
OR upper(text) like '%DELETE%'
then 1
else 0
end) AS dml,
sum(case when text like '%--+%' OR text like '%/*+%' then 1 else 0 end) as hint,
sum(case when upper(text) like '%USE_NL%' THEN 1 ELSE 0 END) AS use_nl,
sum(case when upper(text) like '%USE_HASH%' THEN 1 ELSE 0 END) AS use_hash,
sum(case when upper(text) like '%ORDERED%' THEN 1 ELSE 0 END) AS ordered,
sum(case when upper(text) like '%FIRST_ROWS%' THEN 1 ELSE 0 END) AS first_rows,
sum(case when upper(text) like '%ALL_ROWS%' THEN 1 ELSE 0 END) AS all_rows,
sum(case when upper(text) like '%INDEX(%' THEN 1 ELSE 0 END) AS ind,
sum(case when upper(text) like '%FULL(%' THEN 1 ELSE 0 END) AS full
from dba_source
where owner <> 'SYS'; |