¡¾×ª¡¿ OracleÖзÖÎö±íµÄ×÷ÓÃ
OracleÖзÖÎö±íµÄ×÷ÓÃ
http://diegoball.javaeye.com/blog/568009
ÎÄÕ·ÖÀà:Êý¾Ý¿â
1.·ÖÎö¸üбíµÄͳ¼ÆÐÅÏ¢,,ÓпÉÄܵ¼ÖÂÖ´Ðмƻ®¸Ä±ä..
2.ÒÔµÄanalyze table abc compute statistics;ÕâÌõΪÀý£¬Éú³ÉµÄͳ¼ÆÐÅÏ¢»á´æÔÚÓÚuser_tablesÕâ¸öÊÓͼ£¬²é¿´Ò»ÏÂselect * from user_tables where table_name='ABC';
¹Û²ìÒ»ÏÂNUM_ROWS,BLOCKS,AVG_SPACE,AVG_ROW_LEN¼¸ÁÐÄã¾Í»áÃ÷°×£¬Õâ¾ÍÊDZ仯¡£·ÖÎöÍê±íÖ®ºó£¬»á·¢ÏÖDBA_tables ÊÓͼÖУ¬ÒÔǰºÜ¶àÁÐÖµÊǿյģ¬ÏÖÔÚ¿ªÊ¼ÓÐÊý¾ÝÁË¡£ÕâЩÊý¾Ý¾ÍÊÇ·ÖÎö±íµÃµ½µÄ.
3.ÔõôÑù·ÖÎö±í»òË÷Òý
ÃüÁîÐз½Ê½¿ÉÒÔ²ÉÓÃanalyzeÃüÁî
ÈçAnalyze table tablename compute statistics;
Analyze index|cluster indexname estimate statistics;
ANALYZE TABLE tablename COMPUTE STATISTICS
FOR TABLE
FOR ALL [LOCAL] INDEXES
FOR ALL [INDEXED] COLUMNS;
ANALYZE TABLE tablename DELETE STATISTICS
ANALYZE TABLE tablename VALIDATE REF UPDATE
ANALYZE TABLE tablename VALIDATE STRUCTURE
[CASCADE]|[INTO TableName]
ANALYZE TABLE tablename LIST CHAINED ROWS [INTO TableName]
µÈµÈ¡£
Èç¹ûÏë·ÖÎöÕû¸öÓû§»òÊý¾Ý¿â£¬»¹¿ÉÒÔ²ÉÓù¤¾ß°ü£¬¿ÉÒÔ²¢ÐзÖÎö
Dbms_utility(8iÒÔǰµÄ¹¤¾ß°ü)
Dbms_stats(8iÒÔºóÌṩµÄ¹¤¾ß°ü)
Èç
dbms_stats.gather_schema_stats(User,estimate_percent=>100,cascade=> TRUE);
dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);
ÕâÊǶÔÃüÁîÓ빤¾ß°üµÄһЩ×ܽá
(1)¡¢¶ÔÓÚ·ÖÇø±í£¬½¨ÒéʹÓÃDBMS_STATS£¬¶ø²»ÊÇʹÓÃAnalyzeÓï¾ä¡£
a) ¿ÉÒÔ²¢ÐнøÐУ¬¶Ô¶à¸öÓû§£¬¶à¸öTable
b) ¿ÉÒԵõ½Õû¸ö·ÖÇø±íµÄÊý¾ÝºÍµ¥¸ö·ÖÇøµÄÊý¾Ý¡£
c) ¿ÉÒÔÔÚ²»Í¬¼¶±ðÉÏCompute Statistics£ºµ¥¸ö·ÖÇø£¬×Ó·ÖÇø£¬È«±í£¬ËùÓзÖÇø
d) ¿ÉÒÔµ¹³öͳ¼ÆÐÅÏ¢
e) ¿ÉÒÔÓû§×Ô¶¯ÊÕ¼¯Í³¼ÆÐÅÏ¢
(2)¡¢DBMS_STATSµÄȱµã
a) ²»ÄÜValidate Structure
b) ²»ÄÜÊÕ¼¯CHAINED ROWS, ²»ÄÜÊÕ¼¯CL
Ïà¹ØÎĵµ£º
´´½¨OracleÊý¾Ý¿â£¨ÒÔOracle10gΪÀý£©
ÓÐÁ½ÖÖ´´½¨Êý¾Ý¿âµÄ·½Ê½£¬Ò»ÖÖÊÇÒÔÃüÁîÐнű¾·½Ê½£¬¼´ÊÖ¶¯·½Ê½´´½¨£»ÁíÒ»ÖÖÊÇÀûÓÃOracleÌṩµÄÊý¾Ý¿âÅäÖÃÏòµ¼À´´´½¨¡£±¾ÆªÖ÷Òª½éÉÜÔÚUnixºÍWindowsÏÂÒÔÃüÁîÐнű¾·½Ê½´´½¨OracleÊý¾Ý¿â¡£
Ò»¸öÍêÕûµÄÊý¾Ý¿âϵͳ£¬Ó¦°üÀ¨Ò»¸öÎïÀí½á¹¹¡¢Ò»¸öÂß¼½á¹¹¡¢Ò»¸öÄÚ´ ......
Oracle½«±íÊÚȨ¸øÓû§µÄÃüÁî
ÃüÁgrant xxxȨÏÞ on TableA to USERA
grant select,insert,update,delete on ±íÃû to Óû§Ãû
ÀýÈ磺½«test±íµÄ²éѯȨÏÞ¸³ÓèsolidwangÕâ¸öÓû§
grant select on test to solidwang ......
oracle
Îóɾ³ýÈÕÖ¾ÎļþºóµÄ´¦Àí·½·¨£¡
½â¾ö·½·¨±³¾°
1
ÕûÀíc
Å̵ÄÀ¬»øÎļþ£¬°Éoralce
µÄ¡£log
ÈÕÖ¾Îļþɾ³ý¡£
2
È»ºóoralce
Ò»Ö±²»ÄÜʹÓã¬Ìáʾtns
ÓÐÎÊÌ⣬
È»ºóÔÙÍùÉÏÕÒ×ÊÁÏ£¬Ò»Ö±Ã»Óнâ¾öµô£¬ºóÀ´¿´µ½Ò»±ßÎÄÕÂÈçÏ£¬ÎÊÌâ½â¾öµôÁË£¬Ìغʹó¼Ò·ÖÏí£¡
======================================== ......
ORACLE³£ÓÃSQLÓÅ»¯hintÓï¾ä
http://oracle.chinaitlab.com/induction/802186.html
ÔÚSQLÓï¾äÓÅ»¯¹ý³ÌÖУ¬ÎÒÃǾ³£»áÓõ½hint,ÏÖ×ܽáÒ»ÏÂÔÚSQLÓÅ»¯¹ý³ÌÖг£¼ûOracle HINTµÄÓ÷¨£º
¡¡¡¡1. /*+ALL_ROWS*/
¡¡¡¡±íÃ÷¶ÔÓï¾ä¿éÑ¡Ôñ»ùÓÚ¿ªÏúµÄÓÅ»¯·½·¨,²¢»ñµÃ×î¼ÑÍÌÍÂÁ¿,ʹ×ÊÔ´ÏûºÄ×îС»¯.
¡¡¡¡ÀýÈç:
¡¡¡¡SELECT /*+ALL+_ROW ......