oracle ÅÅÐòÄÚ´æ
ÎÒÔÚhttp://zhidao.baidu.com/question/123262452.html?fr=msg¡¡ÌáµÄÎÊÌ⣬ÕûÀíµ½ÕâÀï¡¡·Ç³£¸Ðл zjwssg
µÄ»Ø´ð
ÅÅÐòÄÚ´æÉæ¼°µ½PGA¡£
ʲôʱºòʹÓÃ×Ô¶¯PGAÄÚ´æ¹ÜÀí£¿Ê²Ã´Ê±ºòʹÓÃÊÖ¶¯PGAÄÚ´æ¹ÜÀí£¿
°×ÌìϵͳÕý³£ÔËÐÐʱÊʺÏʹÓÃ×Ô¶¯PGAÄÚ´æ¹ÜÀí£¬ÈÃOracle¸ù¾Ýµ±Ç°¸ºÔØ×Ô¶¯¹ÜÀí¡¢·ÖÅäPGAÄÚ´æ¡£
Ò¹ÀïÓû§ÊýÉÙ¡¢½øÐÐά»¤µÄʱºò¿ÉÒÔÉ趨µ±Ç°»á»°Ê¹ÓÃÊÖ¶¯PGAÄÚ´æ¹ÜÀí£¬Èõ±Ç°µÄά»¤²Ù×÷»ñµÃ¾¡¿ÉÄܶàµÄÄڴ棬¼Ó¿ìÖ´ÐÐËٶȡ£
È磺·þÎñÆ÷ƽʱÔËÐÐÔÚ×Ô¶¯PGAÄÚ´æ¹ÜÀíģʽÏ£¬Ò¹ÀïÓиöÈÎÎñÒª´ó±í½øÐÐÅÅÐòÁ¬½Óºó¸üУ¬¾Í¿ÉÒÔÔڸòÙ×÷sessionÖÐÁÙʱ¸ü¸ÄΪÊÖ¶¯PGAÄÚ´æ¹ÜÀí£¬È»ºó·ÖÅä´óµÄSORT_AREA_SIZEºÍHASH_AREA_SIZE£¨50%ÉõÖÁ80%Äڴ棬Ҫȷ±£ÎÞÆäËûÓû§Ê¹Óã©£¬ÕâÑùÄÜ´ó´ó¼Ó¿ìϵͳÔËÐÐËٶȣ¬ÓÖ²»Ó°Ïì°×Ìì¸ß·åÆÚ¶ÔϵͳÔì³ÉµÄÓ°Ïì¡£
²Ù×÷ÃüÁî
»á»°¼¶¸ü¸Ä
ALTER SESSION SET WORKAREA_SIZE_POLICY = {AUTO | MANAUL}£»
ALTER SESSION SET SORT_AREA_SIZE = 65536£»
ALTER SESSION SET HASH_AREA_SIZE = 65536£»
ѧÒÔÖÂÓÃ
1£¬ÅÅÐòÇø£º
pga_aggregate_targetΪ100MB£¬µ¥¸ö²éѯÄÜÓõ½5%Ò²¾ÍÊÇ5MBʱÅÅÐòËùÐèʱ¼ä
SQL> create table sorttable as select * from all_objects;
±íÒÑ´´½¨¡£
SQL> insert into sorttable (select * from sorttable);
ÒÑ´´½¨49735ÐС£
SQL> insert into sorttable (select * from sorttable);
ÒÑ´´½¨99470ÐС£
SQL> set timing on;
SQL> set autotrace traceonly;
SQL> select * from sorttable order by object_id;
ÒÑÑ¡Ôñ198940ÐС£
ÒÑÓÃʱ¼ä: 00: 00: 50.49
Session¼¶ÐÞ¸ÄÅÅÐòÇøΪ30mbËùÐèʱ¼ä
SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
»á»°ÒѸü¸Ä¡£
ÒÑÓÃʱ¼ä: 00: 00: 00.02
SQL> ALTER SESSION SET SORT_AREA_SIZE = 30000000;
»á»°ÒѸü¸Ä¡£
ÒÑÓÃʱ¼ä: 00: 00: 00.01
SQL> select * from sorttable order by object_id;
ÒÑÑ¡Ôñ198940ÐС£
ÒÑÓÃʱ¼ä: 00: 00: 10.76
¿ÉÒÔ¿´µ½ËùÐèʱ¼ä´Ó50.49Ãë¼õÉÙµ½10.31Ã룬ËÙ¶ÈÌáÉýºÜÃ÷ÏÔ¡£
2£¬É¢ÁÐÇø£º
pga_aggregate_targetΪ1
Ïà¹ØÎĵµ£º
minusÔËËã
·µ»ØÔÚµÚÒ»¸ö²éѯ½á¹ûÖÐÓëµÚ¶þ¸ö²éѯ½á¹û²»ÏàͬµÄÄDz¿·ÖÐмǼ¡£
ÓÐÄÄЩ¹¤ÖÖÔڲƻᲿÖÐÓУ¬¶øÔÚÏúÊÛ²¿ÖÐûÓУ¿
exp:selectjobfromaccount
minus
selectjobfromsales;
ÓëunionÏà·´ ......
ORDER BY ÅÅÐò
ASC ÉýÐò(ĬÈÏ)
DESC ½µÐò
select * from s_emp order by dept_id , salary desc
²¿ÃźÅÉýÐò£¬¹¤×ʽµÐò
¹Ø¼ü×ÖdistinctÒ²»á´¥·¢ÅÅÐò²Ù×÷¡£
select * from employee order by 1; //°´µÚÒ»×Ö¶ÎÅÅÐò
NULL±»ÈÏΪÎÞÇî´ó¡£order by ¿ÉÒÔ¸ú±ðÃû¡£
select table_name ......
¾ÍÊÇÓÉÆÕͨ×Ö·û£¨ÀýÈç×Ö·ûaµ½z£©ÒÔ¼°ÌØÊâ×Ö·û£¨³ÆΪԪ×Ö·û£©×é³ÉµÄÎÄ×Öģʽ¡£¸ÃģʽÃèÊöÔÚ²éÕÒÎÄ×ÖÖ÷Ìåʱ´ýÆ¥ÅäµÄÒ»¸ö»ò¶à¸ö×Ö·û´®¡£ÕýÔò±í´ïʽ×÷Ϊһ¸öÄ£°å£¬½«Ä³¸ö×Ö·ûģʽÓëËùËÑË÷µÄ×Ö·û´®½øÐÐÆ¥Åä¡£
±¾ÎÄÏêϸµØÁгöÁËÄÜÔÚÕýÔò±í´ïʽÖÐʹÓã¬ÒÔÆ¥ÅäÎı¾µÄ¸÷ÖÖ×Ö·û¡£µ±ÄãÐèÒª½âÊÍÒ»¸öÏÖÓеÄÕýÔò±í´ïʽʱ£¬¿ÉÒÔ×÷Ϊһ¸ö¿ ......
ÏÈ¿´Oracle ¹Ù·½½âÊÍ
Oracle managed file (OMF)
A file that is created automatically by the Oracle database server when it is needed and automatically deleted when it is no longer needed.
ÈçºÎÅжÏÄãµÄÊý¾Ý¿âÊÇ·ñΪ֧³ÖOMF
SQL> show parameter db_create_file_dest;
NAME & ......
1
¡¢°²×°Ç°µÄ×¼±¸
1.1
¼ì²éÓ²¼þ
ÎïÀíÄڴ棺
# grep
MemTotal /proc/meminfo
½»»»¿Õ¼ä£º
# grep
SwapTotal /proc/meminfo
½»»»¿Õ¼äµÄ´óСÊǸù¾ÝÎïÀíÄÚ´æµÄ´óСÀ´ÉèÖõģ¬ÎïÀíÄÚ´æÉÙÓÚ
2G
µÄÔò½»»»¿Õ¼äÉèΪÎïÀíÄÚ´æµÄÁ½±¶£¬ÎïÀíÄÚ´æ´óÓÚ
2G
µÄÔò½»»»¿Õ¼äºÍÎïÀíÄÚ´æ´óСһÑù¡£Èç¹û²»·ûºÏÉÏÊö¹æ¶¨£¬ÄÇô¾ÍÐèÒª ......