Oracle 10g StatisticÊý¾Ýͳ¼Æ
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 TYPE VALUE
---
Ïà¹ØÎĵµ£º
×Ô¼ºÔÚ×öÕâ¸ö³ÌÐòµÄʱºò¿´¹ýºÜ¶àµÄ×ÊÁÏ£¬ÉÏÍøÒ²²éÁ˲»ÉÙµÄ×ÊÁÏ£¬¿ÉÊǶà°ë˵µÄÊdzöÉñÈ뻯£¬ÔÆÀïÎíÀï...²»¹â¿´Á˲»Ã÷°×£¬¶øÇÒÊÇÓÐÃ÷°×Ò»µãµÄÈË£¬¿´ÁËÒ²±äµÄÓÐЩģºýÁË¡£
ÕâÀïÎÒÑÚÊÎÒ»Ì×ÍêÕûµÄjava jdbc Á¬½ÓOracle9iµÄ·¶Àý¡£
package com.lxh.dbcon;//´ò°ü
import ......
Oracle±Ê¼Ç
l ¹ØÓÚTRUNCº¯Êý
SELECT
RELATED_ID ,
DOC_ID ,
CAT_ID ,
CAT_CODE ,
RELEASE_DATE ,
&n ......
ÓÉÓÚÒÔÇ°¶¼ÊÇÔÚsqlserver 2005´¦Àí£¬ÏÖÔÚ¿Í»§ÒªÇóoracleÊý¾Ý¿â·þÎñÆ÷£¬
×î³õµÄ´úÂëΪ£º
allRecordSize = (Integer) rs1.getObject(1); //Integer allRecordSize=0;
µ±Ö´ÐеÄʱºò±¨£ºBigDecimalÎÞ·¨×ª»¯ÎªIntegerÀàÐÍ
ΪÁ˼æÈÝÁ½ÕßÐ޸ĺóµÄ´úÂëΪ£º
Object o = rs1.getObject(1);
&nbs ......
SQLµÄÓÅ»¯Ó¦¸Ã´Ó5¸ö·½Ãæ½øÐе÷Õû£º
1.È¥µô²»±ØÒªµÄ´óÐͱíµÄÈ«±íɨÃè
2.»º´æСÐͱíµÄÈ«±íɨÃè
3.¼ìÑéÓÅ»¯Ë÷ÒýµÄʹÓÃ
4.¼ìÑéÓÅ»¯µÄÁ¬½Ó¼¼Êõ
5.¾¡¿ÉÄܼõÉÙÖ´Ðмƻ®µÄCost
SQLÓï¾ä£º
ÊǶÔÊý¾Ý¿â(Êý¾Ý)½øÐвÙ×÷µÄΩһ;¾¶£»
ÏûºÄÁË70%~90%µÄÊý¾Ý¿â×ÊÔ´£»¶ÀÁ¢ÓÚ³ÌÐòÉè¼ÆÂß¼£¬Ïà¶ÔÓÚ¶Ô³ÌÐòÔ´´úÂëµÄÓÅ»¯£¬¶ÔSQLÓï¾äµÄÓÅ» ......
µ±Ç°Èκΰ汾µÄORACLE¿Í»§¶ËÔÚÈκΰ汾µÄWINDOWS7É϶¼²»ÄÜÕý³£Íê³É°²×°¡£
Ö÷ÒªÊÇÒòΪORACLE°²×°µÄÏȾöÌõ¼þÀï²Ù×÷ϵͳ°æ±¾²»·ûºÏ£¬µ«ÊÇÕâ¸öÒÉÎÊ¿ÉÒÔÐÞ¸Ärefhost.xml´¦Àí£¬ ¾ßÌåÊÇÔÚrefhost.xmlÖÐÌí¼Ó
<!--Microsoft Windows 7-->
<OPERATING_SYSTEM>
& ......