oracle培訓(xùn)講義-免費(fèi)閱讀
【正文】 Qurey：consistent reads(gets)。SQL conn scott/tigerSQL select * from dept。如果表建有索引，則要把索引停用，否則依然有大量的redo：alter index idx_test unusable。SQL select * from t order by object_id。SQL select * from t order by object_id。Statistics 0 recursive calls 24 db block gets 80 consistent gets 214 physical reads 0 redo size 219216 bytes sent via SQL*Net to client 767 bytes received via SQL*Net from client 26 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 6219 rows processedSQL /已選擇6219行。對于第二種，我們可以通過設(shè)置合適的sort_area_size和hash_area_size大小，來降低phisical reads，注意，在Oracle9i中，要先把workarea_size_policy參數(shù)設(shè)置為manual，改動sort_area_size及hash_area_size參數(shù)才會生效，8i可以直接設(shè)置sort_area_size。Statistics 0 recursive calls 0 db block gets 3156 consistent gets 0 physical reads 0 redo size 683239 bytes sent via SQL*Net to client 34702 bytes received via SQL*Net from client 3111 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6219 rows processedSQL set arraysize 4SQL /已選擇6219行。SQL create table t 2 as 3 select * from all_objects。The less logical I/O we can do,the better。Statistics 12 recursive calls 0 db block gets 92 consistent gets 0 physical reads 0 redo size 579 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 12 rows processed216。Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 39。216。set autotrace on：執(zhí)行計(jì)劃和統(tǒng)計(jì)信息都顯示。SQL conn scott/tiger已連接。SQL grant select on v_$statname to plustrace。同義詞已創(chuàng)建。SQL grant all on plan_table to public。授權(quán)成功。SQL set autotrace onSQL select ename,sal from emp。set autotrace traceonly：類似于set autotrace on，只是不顯示查詢結(jié)果。 pl/sql function calls：SQL create or replace function test return number 2 as 3 l_t number。EMP39。 side effect from modification：由于triggers、基于函數(shù)的索引引起。我們可以通過設(shè)置合適的arraysize(許多方法中的一個，適用于sql*plus)來降低邏輯I/O數(shù)量，ODBC，JDBC也有類似的設(shè)置。表已創(chuàng)建。Statistics 0 recursive calls 0 db block gets 1618 consistent gets 0 physical reads 0 redo size 495111 bytes sent via SQL*Net to client 17597 bytes received via SQL*Net from client 1556 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6219 rows processedSQL set arraysize 8SQL /已選擇6219行。SQL conn scott/tigercatalog已連接。Statistics 0 recursive calls 22 db block gets 80 consistent gets
教學(xué)教案相關(guān)推薦
鄂ICP備17016276號-1