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Ïà·´ ......
ÏÈ¿´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 & ......
ÏÈÀ´¿´¿´¹Ù·½ÎĵµÖжÔÕâ¸ö²ÎÊýµÄ½âÊÍ
CURSOR_SHARING
PropertyDescription
Parameter type
String
Syntax
CURSOR_SHARING = { SIMILAR | EXACT | FORCE }
Default value
EXACT
Modifiable
ALTER SESSION, ALTER SYSTEM
Basic
No
CURSOR_SHARING determines what kind of SQL statements can share the same cu ......
Ò»£ºsql loader µÄÌصã
oracle×Ô¼º´øÁ˺ܶàµÄ¹¤¾ß¿ÉÒÔÓÃÀ´½øÐÐÊý¾ÝµÄǨÒÆ¡¢±¸·ÝºÍ»Ö¸´µÈ¹¤×÷¡£µ«ÊÇÿ¸ö¹¤¾ß¶¼ÓÐ×Ô¼ºµÄÌص㡣
±ÈÈç˵expºÍimp¿ÉÒÔ¶ÔÊý¾Ý¿âÖеÄÊý¾Ý½øÐе¼³öºÍµ¼³öµÄ¹¤×÷£¬ÊÇÒ»ÖֺܺõÄÊý¾Ý¿â±¸·ÝºÍ»Ö¸´µÄ¹¤¾ß£¬Òò´ËÖ÷ÒªÓÃÔÚÊý¾Ý¿âµÄÈȱ¸·ÝºÍ»Ö¸´·½Ãæ¡£ÓÐ×ÅËٶȿ죬ʹÓüòµ¥£¬¿ì½ÝµÄÓŵ㣻ͬʱҲÓÐһР......