{"id":150,"date":"2008-10-03T23:14:00","date_gmt":"2008-10-03T21:14:00","guid":{"rendered":"http:\/\/antynet.pl\/index.php\/2008\/10\/03\/oracle-optimalization\/"},"modified":"2010-08-24T22:44:01","modified_gmt":"2010-08-24T20:44:01","slug":"oracle-optimalization","status":"publish","type":"post","link":"https:\/\/www.antynet.pl\/index.php\/2008\/10\/03\/oracle-optimalization\/","title":{"rendered":"ORACLE OPTIMALIZATION"},"content":{"rendered":"

sprawdzenie liczby blok\u00f3w:<\/span>
SELECT blocks, extents FROM dba_segments WHERE segment_name='[table]’ AND owner='[owner]’;<\/span>
sprawdzenie liczby blok\u00f3w zaj\u0119tych przez segment:<\/span>
SELECT blocks, empty_blocks FROM dba_tables WHERE table_name = '[table]’ AND owner='[owner]’;
optymalizacja przestrzeni: <\/span><\/span>
zmiejszenie ilosci blok\u00f3w zformatowanych pod wcze\u015bniej zainstertowane dane, kt\u00f3re to dane nast\u0119pnie zosta\u0142y zdeletowane, a bloki zosta\u0142y automatycznie przesuni\u0119te do FREELIST, czyli obni\u017ca si\u0119 HWM, jednak zaalokowana liczba blok\u00f3w przez segment pozostaje nie zmieniona, ponadto bloki migruj\u0105 do wolnej przestrzeni w miejsce pustych blok\u00f3w dlatego zmienia si\u0119 row_id i block_id.<\/span>
ALTER TABLE [table] MOVE;
nast\u0119pnie proces Automatic Space Segment Management powinien wszystko wyczy\u015bci\u0107, oczywi\u015bcie przestrze\u0144 musi by\u0107 w trybie Extent Management Local, je\u017celi nie to trzeba to zrobi\u0107 r\u0119cznie jak poni\u017cej<\/span>
zmniejszenie zaalokowanej liczby blok\u00f3w przez segment:<\/span>
ALTER TABLE [table] ENABLE ROW MOVEMENT;ALTER TABLE [table] SHRINK SPACE;
obliczanie wielko\u015bci przestrzeni wycofania:
<\/span>undo_blk\/s – ilo\u015b\u0107 blok\u00f3w wycofa\u0144 na sekund\u0119,
86400 – ilo\u015b\u0107 sekund w ci\u0105gu dnia
undo_blk\/s = SELECT (SUM(undoblks))\/ SUM ((end_time – begin_time) * 86400) FROM v$undostat;ts_size = (show parameter undo_retention) * undo_blk\/s * db_block_size + db_block_size
lub zapytanie:<\/span>
SELECT (UR * (UPS * DBS)) + (DBS) AS „Bytes” FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention’), (SELECT (SUM(undoblks)\/SUM(((end_time – begin_time)*86400))) AS UPS FROM v$undostat), (select block_size as DBS from dba_tablespaces where tablespace_name= (select value from v$parameter where name = 'undo_tablespace’));<\/span><\/p>\n

sprawdzenie twardych zapyta\u0144:<\/span><\/span><\/div>\n
select pa.SID, pa.VALUE „Hard parses”,ex.VALUE „Execute Count” from v$sesstat pa,v$sesstat ex where pa.sid = ex.sid and pa.statistic#=(select statistic# from v$statname where name =’parse count (hard)’) and ex.statistic#=(select statistic# from v$statname where name = 'execute count’) order by „Hard parses” desc;
<\/span><\/div>\n
select * from (select sql_text,parse_calls,executions from v$sqlstats order by parse_calls)where rownum < 10 #je\u017celi zbli\u017cone parse_calls do executions to te pytania s\u0105 za cz\u0119sto parsowane<\/span><\/div>\n","protected":false},"excerpt":{"rendered":"

sprawdzenie liczby blok\u00f3w:SELECT blocks, extents FROM dba_segments WHERE segment_name='[table]’ AND owner='[owner]’;sprawdzenie liczby blok\u00f3w zaj\u0119tych przez segment:SELECT blocks, empty_blocks FROM dba_tables WHERE table_name = '[table]’ AND owner='[owner]’;optymalizacja przestrzeni: zmiejszenie ilosci blok\u00f3w zformatowanych pod wcze\u015bniej zainstertowane dane, kt\u00f3re to dane nast\u0119pnie zosta\u0142y zdeletowane, a bloki zosta\u0142y automatycznie przesuni\u0119te do FREELIST, czyli obni\u017ca si\u0119 HWM, jednak zaalokowana liczba […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[80],"tags":[68],"class_list":["post-150","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle-optimalization"],"_links":{"self":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/150","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/comments?post=150"}],"version-history":[{"count":1,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/150\/revisions"}],"predecessor-version":[{"id":206,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/150\/revisions\/206"}],"wp:attachment":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/media?parent=150"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/categories?post=150"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/tags?post=150"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}