ʹÓÃOracle sql_trace ¹¤¾ß
Ç°ÑÔ£º
sql_trace ÊÇÎÒÔÚ¹¤×÷Öо³£ÒªÓõ½µÄµ÷ÓŹ¤¾ß£¬Ïà±È½Ïstatspack ÎÒ¸üÔ¸ÒâÓÃÕâ¸ö¹¤¾ß¡£
ÒòΪÊý¾Ý¿âÂýÔÒòµÄ85%ÒÔÉÏÊÇÓÉÓÚsqlÎÊÌâÔì³ÉµÄ£¬statspackûÓÐsqlµÄÖ´Ðмƻ®¡£ÏÔʾûÓÐËüÖ±¹Û£¬·½±ã£¬¶ÔÏëÒªÕë¶ÔÐÔ²»Ç¿£¬
1£¬½éÉÜÊý¾Ý¿âµ÷ÓÅÐèÒª¾³£»áÓõ½µÄ¹¤¾ß£¬¿ÉÒԺܾ«È·µØ¸úץȡÏà¹ØsessionÕýÔÚÔËÐеÄsql¡£ÔÙͨ¹ýtkprof·ÖÎö³öÀ´sqlµÄÖ´Ðмƻ®µÈÏà¹ØÐÅÏ¢£¬´Ó¶øÅжÏÄÇЩsqlÓï¾ä´æÔÚÎÊÌâ¡£
ͳ¼ÆÈçÏÂÐÅÏ¢£¨Õª×Ö¹Ù·½Îĵµ£©£º
Parse, execute, and fetch counts
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Misses on the library cache
Username under which each parse occurred
Each commit and rollback
2£¬Ê¹ÓÃ
ʹÓÃÇ°ÐèҪעÒâµÄµØ·½
1,³õʼ»¯²ÎÊýtimed_statistics=true ÔÊÐísql trace ºÍÆäËûµÄһЩ¶¯Ì¬ÐÔÄÜÊÓͼÊÕ¼¯Óëʱ¼ä£¨cpu£¬elapsed£©ÓйصIJÎÊý¡£Ò»¶¨Òª´ò¿ª£¬²»È»Ïà¹ØÐÅÏ¢²»»á±»ÊÕ¼¯¡£ÕâÊÇÒ»¸ö¶¯Ì¬µÄ²ÎÊý£¬Ò²¿ÉÒÔÔÚsession¼¶±ðÉèÖá£
SQL>alter session set titimed_statistics=true
2,MAX_DUMP_FILE_SIZE¸ú×ÙÎļþµÄ´óСµÄÏÞÖÆ£¬Èç¹û¸ú×ÙÐÅÏ¢½Ï¶à¿ÉÒÔÉèÖóÉunlimited¡£¿ÉÒÔÊÇKB,MBµ¥Î»£¬9I¿ªÊ¼Ä¬ÈÏΪunlimitedÕâÊÇÒ»¸ö¶¯Ì¬µÄ²ÎÊý£¬Ò²¿ÉÒÔÔÚsession¼¶±ðÉèÖá£
SQL>alter system set max_dump_file_size=300
SQL>alter system set max_dump_file_size=unlimited
3,USER_DUMP_DESTÖ¸¶¨¸ú×ÙÎļþµÄ·¾¶,ĬÈÏ·¾¶ÊµÔÚ$ORACLE_BASE/admin/ORA_SID/udumpÕâÊÇÒ»¸ö¶¯Ì¬µÄ²ÎÊý£¬Ò²¿ÉÒÔÔÚsession¼¶±ðÉèÖá£
SQL>alter system set user_dump_dest=/oracle/trace
Êý¾Ý¿â¼¶±ð
ÉèÖÃslq_trace²ÎÊýΪtrue»á¶ÔÕû¸öʵÀý½øÐиú×Ù£¬°üÀ¨ËùÓнø³Ì£ºÓû§½ø³ÌºÍºǫ́½ø³Ì£¬»áÔì³É±È½ÏÑÏÖصÄÐÔÄÜÎÊÌ⣬Éú²ú»·¾³Ò»¶¨ÒªÉ÷Óá£
SQL>alter system set sql_trace=true;
Session¼¶±ð£º
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
ѧϰOracle DBAÒ²°ë¸ö¶àѧÆÚÁË£¬½ñÌìÃÍÈ»²Å·¢ÏÖ£¬ÔÀ´ÎÒµÄÊ黹ÊǺÜеģ¬ÉϿβÙ×÷ʱºòÒ²Ö»ÊÇÖªµÀ´ó¸ÅÔõô×ö£¬µ«ÊÇÒªÕæµÄÈ«²¿×Ô¼º×ö£¬¶ø²»È¥·Ê黹ÊÇÓÐÒ»¶¨µÄÄѶȵģ¬ËùÒÔÄØ£¬½ñÌ쿪ʼ½«DBA´ÓÍ·¸´Ï°Ò»±é£¬Í¬Ê±ÔÙ²Ù×÷Ò»±é¡£
µÚÒ»Õ£¬Ñ§µÄÊÇOracleµÄÌåϵ½á¹¹£ ......
ACCESS SQLÓï·¨²Î¿¼
=================
Ò». »ù´¡¸ÅÄî
¿ÉÒÔʹÓõÄÊý¾ÝÀàÐÍÈçÏ£º
1. TEXT£ºÎı¾ÐÍ£¨Ö¸¶¨³¤¶Èʱ£©£¬±¸×¢ÐÍ£¨²»Ö¸¶¨³¤¶Èʱ£©£»
2.
CHAR£¬NCHAR£¬VARCHAR£¬NVARCHAR£ºÎı¾ÐÍ£¬¿ÉÒÔÖ¸¶¨³¤¶È£¬·ñÔòĬÈÏֵΪ255£¡
3. ......
Oracle PGA²ÎÊý
PGA: ³ÌÐòÈ«¾ÖÇø£¬·þÎñÆ÷½ø³ÌʹÓõÄÄÚ´æÇøÓò£¬°üÀ¨Ìض¨·þÎñÆ÷½ø³ÌµÄÊý¾ÝºÍ¿ØÖÆÐÅÏ¢£¬ÀýÈçʹÓõIJÙ×÷ϵͳ×ÊÔ´µÈ¡£
UGA: Óû§È«¾ÖÇø£¬Ìض¨SessionʹÓõÄÄÚ´æÇøÓò£¬ÀýÈçSessionµÄSQL¹¤×÷Çø¡¢µÇ½ÈÏÖ¤ÐÅÏ¢µÈ¡£SessionµÄSQL¹¤×÷Çø´óС¶Ô²éѯÐÔÄܵÄÓ°Ïì±È½Ï¹Ø¼ü£¬shared serverģʽʱUGA´ÓSGAµÄlarge pool£¨Èç¹ ......
ÈçºÎÔÚoracleÖе¼ÈëdmpÊý¾Ý¿âÎļþ
OracleÊý¾Ýµ¼Èëµ¼³öimp/exp¾ÍÏ൱ÓÚoracleÊý¾Ý»¹ÔÓ뱸·Ý¡£expÃüÁî¿ÉÒÔ°ÑÊý¾Ý´ÓÔ¶³ÌÊý¾Ý¿â·þÎñÆ÷µ¼³öµ½±¾µØµÄdmpÎļþ£¬impÃüÁî¿ÉÒÔ°ÑdmpÎļþ´Ó±¾µØµ¼Èëµ½Ô¶´¦µÄÊý¾Ý¿â·þÎñÆ÷ÖС£ ÀûÓÃÕâ¸ö¹¦ÄÜ¿ÉÒÔ¹¹½¨Á½¸öÏàͬµÄÊý¾Ý¿â£¬Ò»¸öÓÃÀ´²âÊÔ£¬Ò»¸öÓÃÀ´ÕýʽʹÓá£
Ö´Ðл·¾ ......