Oracle 10g StatisticÊý¾Ýͳ¼Æ
http://www.ej38.com/showinfo/Oracle-108709.html
Oracle 10g statisticÊý¾Ýͳ¼Æ,Oracle»á¸ù¾ÝÕâЩͳ¼ÆÐÅÏ¢À´¾ö¶¨ÊÇ×ßRBO(Rule-BasedOptimization)£¬»¹ÊÇ×ßCBO(Cost-BasedOptimization)£¬»áȥѡÔñÄÄÖÖÖ´Ðмƻ®¸ü»®Ëã,Ó°ÏìÊÇ·ñ×ßÏà¹ØµÄË÷ÒýµÈ.Èç¹ûÊÇCBOµÄ»°£¬ËüÒÀ¿¿×¼È·µÄ£¨»òÕß˵±È½Ï׼ȷµÄ£©Í³¼ÆÐÅÏ¢À´²úÉúÓÅ»¯µÄÖ´Ðз¾¶,Èç¹ûûÓÐ×ö¹ýͳ¼Æ£¬CBOÒ²¾ÍûÓÐ×öcostÆÀ¹ÀµÄÒÀ¾Ý£¬ËùÒÔËäÈ»ÊÇCBO£¬µ«ÊÇʵ¼ÊÉÏ»¹ÊÇÓÃRBOÁË£¬¶øÇÒÈç¹û²»³£×öͳ¼ÆµÄ»°£¬ÓÉÓÚCBOÊÇÒÔͳ¼ÆÎªÒÀ¾ÝµÄ£¬ËùÒÔÕâʱCBOµÄÒÀ¾ÝÐÅÏ¢ÓÐÎÊÌ⣬CBOÒ²»á²»×¼¡£ ËùÒÔ DBA ÐèҪȷ±£¶¨ÆÚÊÕ¼¯Í³¼ÆÐÅÏ¢£¬´´½¨ÁíÒ»¸öÖ´Ðк˶ÔÇåµ¥¡£
¾Ù¸öÀý×Ó£º
Ò»´Î²âÊÔ£¬Ò»¸ösqlÓï¾äÖ´ÐÐÒª20·ÖÖÓ£¬ÓÐʱºò»¹³ö²»Á˽á¹û£¬·¢Ïֲ鿴ִÐмƻ®£¬·¢ÏÖ¾ÓÈ»×ßÁËÈ«±íɨÃ裨±íÖдóÔ¼300wÌõ¼Ç¼£©£¬ÎªÉ¶²»ÓÃË÷ÒýÄØ£¬²é¿´Ë÷Òý״̬£¬Ò»ÇÐÕý³£¡£·ÖÎöÁËÏà¹ØµÄ±í£¬È»ºóÖØÐÂÖ´ÐÐ3·ÖÖӸ㶨£¡
ÊÀÊÂÎÞ¾ø¶Ô£¬analyze±í»áÔö¼ÓCBOÖ´ÐеÄÐÔÄÜ£¿²»Ò»¶¨µÄ¡£
ÎÒ¾ÍÅöµ½Ò»¸öÓï¾ä·ÖÎöºóÒªÖ´ÐÐ30¶à·ÖÖÓ£¬É¾³ý·ÖÎöºó£¬Ö»Òª30Ãë¡£
ºÜ¶àÇé¿öϲ»Ò»¶¨µÄ£¬×îºÃÊÇ×Ô¼º´ÓÖ´Ðмƻ®Åжϡ£
analyze table tablename compute statistics for all indexes;
analyze table tablename delete statistics
˳±ã²¹³äÒ»µã£¬±íÖ»ÓзÖÎöÁËÖ®ºó£¬num_rows²Å»áÓÐÖµ¡£
select * from user_all_tables a where a.num_rows <10;
analyze table tablename compute statistics;
SELECT 'ANALYZE TABLE ' || TABLE_NAME || ' COMPUTE STATISTICS;'
from (SELECT DISTINCT TABLE_NAME from ALL_COL_COMMENTS);
SQLÓï¾äµÄÖ´Ðмƻ®×ß²»×ßË÷Òý³ýÁËÓëStatisticÓйØÏµ£¬»¹ÓÐpfileµÄÒ»¸ö²ÎÊýÓйأºoptimizer_index_cost_adj. ¸Ã²ÎÊýÓ°ÏìÓÅ»¯Æ÷Ñ¡ÔñË÷Òý»¹ÊÇÈ«±íɨÃèµÄÇãÏò,½¨Ò齫ÆäÉèΪ40.
ÔÚ 10g ÖУ¬Í¨¹ýÉèÖóõʼ»¯²ÎÊý STATISTIC_LEVEL Ϊ TYPICAL »ò ALL£¬¾Í¿ÉÒÔ×Ô¶¯ÊÕ¼¯Í³¼ÆÐÅÏ¢(ĬÈÏֵΪ TYPICAL£¬Òò´Ë¿ÉÒÔËæ¼´ÆôÓÃ×Ô¶¯ÊÕ¼¯Í³¼ÆÐÅÏ¢µÄ¹¦ÄÜ)¡£Oracle Êý¾Ý¿â 10g ¾ßÓÐÒ»¸öÔ¤¶¨ÒåµÄµ÷¶È³ÌÐò×÷Òµ£¬Ãû³ÆÎª GATHER_STATS_JOB£¬ËüÓÉ STATISTIC_LEVEL ²ÎÊýµÄÊʵ±ÊýÖµËù¼¤»î¡£
SQL> show parameter statistics_
NAME &nb
Ïà¹ØÎĵµ£º
ËÀËøµÄÔÒò
1¡£Ä£ÄâËÀËø
1¡£1¡£Ö÷±í
-- Create table
create table WDZ1
(
WDZ1ID NUMBER not null,
MEMO VARCHAR2(20)
)
;
alter table WDZ1
add constraint XXXXXX primary key (WDZ1ID);
1¡£2¡£´Ó±í£¨Ã»ÓÐÍ⽡µÄË÷Òý£©
-- Create table
create table WDZ2
(
WDZ2ID NUMBER not null,
WDZ1ID NUMBER,
M ......
¿ª·¢»·¾³ VS2008+NET3.5+ORACLE10G
Êý¾Ý¿âÖбíÉè¼Æ
CREATE TABLE CCOP (
A CHAR(20) NULL,
B CHAR(20) NULL,
C CHAR(20) NULL,
D CHAR(20) NULL
)
EXCELµÄµÚÒ»ÐÐǰËÄÁÐÁÐÃûΪ A B C D
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using Sys ......
OracleÖÐstart with...connect by prior×Ó¾äÓ÷¨
connect by Êǽṹ»¯²éѯÖÐÓõ½µÄ£¬Æä»ù±¾Óï·¨ÊÇ£º
select ... from tablename start with Ìõ¼þ1
connect by Ìõ¼þ2
where Ìõ¼þ3;
Àý£º
select * from table
start with org_id = 'HBHqfWGWPy'
connect by prior org_id = parent_id;
¼òµ ......
ÏÞÖÆ¿ØÖÆÎļþµÄ´óС
a.)²éѯ¿É¸´Óò¿·ÖµÄ±£´æÌìÊý(²»ÄÜÖ±½Ó¿ØÖÆ´óС,±£´æÌìÊýÔ½´óÊý¾ÝÔ½¶à)
SELECT name,value from v$parameter WHERE name = 'control_file_record_keep_time';
b.)ÉèÖÿɸ´Óò¿·Ö±£´æÊ±¼ä
ALTER SYSTEM SET CONTROL_FILE_RECORD_KEEP_TIME = 14;
......