ORACLE SQL ÓÅ»¯µÄÈô¸É·½·¨
Ò»¡¢ ÏȽéÉÜÒ»ÏÂoracleµÄSGA£ºÊý¾Ý¿âµÄϵͳȫ¾ÖÇø£¬SGAÖ÷ÒªÓÉÈý²¿·Ö¹¹³É£º¹²Ïí³Ø¡¢Êý¾Ý»º³åÇø¡¢ÈÕÖ¾»º³åÇø¡£ 1¡¢¹²Ïí³ØÓÖÓÉÁ½²¿·Ö¹¹³É£º¹²ÏíSQLÇøºÍÊý¾Ý×ֵ仺³åÇø¡£¹²ÏíSQLÇøרÃÅ´æ·ÅÓû§SQLÃüÁoracleʹÓÃ×î½ü×îÉÙʹÓõÈÓÅÏȼ¶Ëã·¨À´¸üи²¸Ç£»Êý¾Ý×ֵ仺³åÇø£¨library cache£©´æ·ÅÊý¾Ý¿âÔËÐеĶ¯Ì¬ÐÅÏ¢¡£Êý¾Ý¿âÔËÐÐÒ»¶Îʱ¼äºó£¬DBAÐèÒª²é¿´ÕâЩÄÚ´æÇøÓòµÄÃüÖÐÂÊÒÔ´ÓÊý¾Ý¿â½Ç¶È¶ÔÊý¾Ý¿âÐÔÄܵ÷ÓÅ¡£Í¨¹ýÖ´ÐÐÏÂÊöÓï¾ä²é¿´£º
select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache;
--²é¿´¹²ÏíSQLÇøµÄÖØÓÃÂÊ£¬×îºÃÔÚ90£¥ÒÔÉÏ£¬·ñÔòÐèÒªÔö¼Ó¹²Ïí³ØµÄ´óС¡£
select (sum(gets - getmisses - usage - fixED)) / sum(gets) "Row Cache" from v$rowcache;
--²é¿´Êý¾Ý×ֵ仺³åÇøµÄÃüÖÐÂÊ£¬×îºÃÔÚ90£¥ÒÔÉÏ£¬·ñÔòÐèÒªÔö¼Ó¹²Ïí³ØµÄ´óС¡£
2¡¢ Êý¾Ý»º³åÇø£º´æ·ÅsqlÔËÐнá¹ûץȡµ½µÄdata block£»
SELECT name, value from v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');
-- ²é¿´Êý¾Ý¿âÊý¾Ý»º³åÇøµÄʹÓÃÇé¿ö¡£²éѯ³öÀ´µÄ½á¹û¿ÉÒÔ¼ÆËã³öÀ´Êý¾Ý»º³åÇøµÄʹÓÃÃüÖÐÂÊ£½1 - ( physical reads / (db block gets + consistent gets) )¡£ÃüÖÐÂÊÓ¦¸ÃÔÚ90£¥ÒÔÉÏ£¬·ñÔòÐèÒªÔö¼ÓÊý¾Ý»º³åÇøµÄ´óС¡£
3¡¢ ÈÕÖ¾»º³åÇø£º´æ·ÅÊý¾Ý¿âÔËÐÐÉú³ÉµÄÈÕÖ¾¡£
select name,value from v$sysstat where name in ('redo entries','redo log space requests');
--²é¿´ÈÕÖ¾»º³åÇøµÄʹÓÃÇé¿ö¡£²éѯ³öµÄ½á¹û¿ÉÒÔ¼ÆËã³öÈÕÖ¾»º³åÇøµÄÉêÇëʧ°ÜÂÊ£ºÉêÇëʧ°ÜÂÊ£½requests/entries£¬ÉêÇëʧ°ÜÂÊÓ¦¸Ã½Ó½üÓÚ0£¬·ñÔò˵Ã÷ÈÕÖ¾»º³åÇø¿ªÉè̫С£¬ÐèÒªÔö¼ÓORACLEÊý¾Ý¿âµÄÈÕÖ¾»º³åÇø¡£
¶þ£®SqlÓï¾äµÄÖ´Ðв½Ö裺
Á˽âsqlµÄÖ´Ðв½ÖèÓÐÀûÓÚ¸üºÃµÄÓÅ»¯ËüÃÇ£¬Ã¿ÌõsqlÓï¾äÖ´ÐÐʱ¶¼Òª¾ÀúÒÔϼ¸²½£º
1. Create cursor £»
2. Parse, if it is not already in the shared pool.£»
3. Any query in the statement is processed.
4. Bind Variables
5. Execute.
6. If possible, the statement is parallelized.
7. Rows to be returned are fetched. ÆäÖУ¬Parse—ÊÇ×îÓÐÓÅ»¯Ç±Á¦µÄ½×¶Î¡£
Cursor´´½¨ºó£¬oracle½«ÔÚshare poolÖÐÑ°ÕÒÊÇ·ñ¸ÃsqlÓï¾äÒѾ´æÔÚ£¬ÈôÒѾ´æÔÚÇÒÒѾ±»parsed£¬Ôò²»ÐèÒªÔÙÖ´ÐÐparse£¬Ö±½Óµ½ÏÂÒ»²½¡£ÈôδÔÚshare poolÖÐÕ
Ïà¹ØÎĵµ£º
²éѯµ±Ç°Ê¹ÓõÄÓαêÊý¾Ý£ºselect count(*) from v$open_cursor
²éѯµ±Ç°×î´óÓαêÊý£ºshow parameter open_cursors;
ÐÞ¸Ä×î´óÓαêÊý£ºalter system set open_cursors=1000 scope=spfile;
=========================================================
²éѯµ±Ç°×î´ó»á»°Êý£ºshow parameter sessions;
ÐÞ¸Ä×î´ó»á»°Êý£ºalt ......
ÔÚÍøÂçÉÏ·¢ËÍÊý¾Ý¶¼»á·¢ÉúһЩÑÏÖصݲȫÎÊÌ⣬ÍøÂç×î´óµÄµ£ÓÇÔÚÓÚÓÐÈË¿ÉÒÔ²¶»ñÊý¾Ý£¬sqlserverÖ§³Ö²ÉÓÃÖ¤ÊéÀ´¼ÓÃÜ
1£®´´½¨Ö¤Êé
create master key encryption by
password = 'administrator123'
--ÓÃÃÜÂëadmini--
create certificate c1
encryption by password = 'administrator123'
with subject = 'c1 certific ......
ÒÔÇ°ÔÚ2000Àï¿ÉÒÔÊý¾Ýµ¼Èëµ¼³ö£¬µ±È»ÔÚ2005ÀïÒ²²»ÀýÍ⣬ͬÑùÓÐÕâÑùµÄ²Ù×÷£¬Ö»ÊÇÔÚ²Ù×÷ÉϺÍ2000ÓеãÇø±ð£¬ÏÂÃæͨ¹ýͼÎĵķ½·¨½ÌÄãÔõÑùµ¼Èëµ¼³öÊý¾Ý¡£
²Ù×÷²½Ö裺
1¡¢Èçͼ£¬ÔÚ“¶ÔÏó×ÊÁϹÜÀíÆ÷”ÀÓÒ»÷ÄãÒªµ¼Èëµ¼³öÊý¾ÝµÄÊý¾Ý¿â£¬Ñ¡Ôñ“ÈÎÎñ”ÀïµÄµ¼ÈëÊý¾Ý»òµ¼³öÊý¾Ý£¬ÎÒÕâÀïÒÔµ¼ÈëÊý¾ÝΪÀý£¬µ¼³öÊ ......
ÓÃT-SQL×öÊý¾Ý¿â·ÖÒ³²éѯҲÓкü¸ÄêÁË£¬µ«¶ÔÓÚ¸÷ÖÖ²éѯ·½·¨µÄд·¨Ò»ÖÁ¶¼Ã»ÔõôȥÀí»á£¬×î½ü²ÎÓëÁ˼¸¸öÏîÄ¿µÄ¿ª·¢£¬¼¸¸öÏîÄ¿ÖзÖÒ³²éѯµÄд·¨Ò²²»Ïàͬ£¬ÕâÒ²ÈÃÎÒ²úÉúÁËÈ·Èϼ¸ÖÖд·¨Ð§ÂÊÎÊÌâµÄÐËȤ¡££¨×¢£ºÎÒËù˵µÄ¼¸ÖÖд·¨¶¼ÊÇÐèÒª·µ»Ø×ܼǼÊýµÄ·ÖÒ³£©
1¡¢Éú³É²âÊÔÊý¾Ý
select a.* into test from sys.columns a,sys.colu ......