²é¿´OracleÖ´Ðмƻ®µÄ¼¸ÖÖ·½·¨
²é¿´OracleÖ´Ðмƻ®µÄ¼¸ÖÖ·½·¨
Ò»¡¢Í¨¹ýPL/SQL Dev¹¤¾ß
1¡¢Ö±½ÓFile->New->Explain Plan Window£¬ÔÚ´°¿ÚÖÐÖ´ÐÐsql¿ÉÒԲ鿴¼Æ»®½á¹û¡£ÆäÖУ¬Cost±íʾcpuµÄÏûºÄ£¬µ¥Î»Îªn%£¬Cardinality±íʾִÐеÄÐÐÊý£¬µÈ¼ÛRows¡£
2¡¢ÏÈÖ´ÐÐ EXPLAIN PLAN FOR select * from tableA where paraA=1£¬ÔÙ select * from table(DBMS_XPLAN.DISPLAY)±ã¿ÉÒÔ¿´µ½oracleµÄÖ´Ðмƻ®ÁË£¬¿´µ½µÄ½á¹ûºÍ1ÖеÄÒ»Ñù£¬ËùÒÔʹÓù¤¾ßµÄʱºòÍÆ¼öʹÓÃ1·½·¨¡£
×¢Ò⣺PL/SQL Dev¹¤¾ßµÄCommand windowÖв»Ö§³Öset autotrance onµÄÃüÁî¡£»¹ÓÐʹÓù¤¾ß·½·¨²é¿´¼Æ»®¿´µ½µÄÐÅÏ¢²»È«£¬ÓÐЩʱºòÎÒÃÇÐèÒªsqlplusµÄÖ§³Ö¡£
¶þ¡¢Í¨¹ýsqlplus
1£®×î¼òµ¥µÄ°ì·¨
Sql> set autotrace on
Sql> select * from dual;
¡¡¡¡Ö´ÐÐÍêÓï¾äºó£¬»áÏÔʾexplain plan Óë ͳ¼ÆÐÅÏ¢¡£
¡¡¡¡Õâ¸öÓï¾äµÄÓŵã¾ÍÊÇËüµÄȱµã£¬ÕâÑùÔÚÓø÷½·¨²é¿´Ö´ÐÐʱ¼ä½Ï³¤µÄsqlÓï¾äʱ£¬ÐèÒªµÈ´ý¸ÃÓï¾äÖ´Ðгɹ¦ºó£¬²Å·µ»ØÖ´Ðмƻ®£¬Ê¹ÓÅ»¯µÄÖÜÆÚ´ó´óÔö³¤¡£Èç¹û²»ÏëÖ´ÐÐÓï¾ä¶øÖ»ÊÇÏëµÃµ½Ö´Ðмƻ®¿ÉÒÔ²ÉÓãº
Sql> set autotrace traceonly
ÕâÑù£¬¾ÍÖ»»áÁгöÖ´Ðмƻ®£¬¶ø²»»áÕæÕýµÄÖ´ÐÐÓï¾ä£¬´ó´ó¼õÉÙÁËÓÅ»¯Ê±¼ä¡£ËäȻҲÁгöÁËͳ¼ÆÐÅÏ¢£¬µ«ÊÇÒòΪûÓÐÖ´ÐÐÓï¾ä£¬ËùÒÔ¸Ãͳ¼ÆÐÅϢûÓÐÓô¦£¬Èç¹ûÖ´ÐиÃÓï¾äʱÓöµ½´íÎ󣬽â¾ö·½·¨Îª£º
(1£©ÔÚÒª·ÖÎöµÄÓû§Ï£º
Sqlplus > @ ?
dbmsadminutlxplan.sql
(2) ÓÃsysÓû§µÇ½
Sqlplus > @ ?sqlplusadminplustrce.sql
Sqlplus > grant plustrace to user_name;
- - user_nameÊÇÉÏÃæËù˵µÄ·ÖÎöÓû§
¡¡2£®ÓÃexplain planÃüÁî
(1) sqlplus > explain plan for select * from testdb.myuser
(2) sqlplus > select * from table(dbms_xplan.display);
¡¡¡¡ÉÏÃæÕâ2ÖÖ·½·¨Ö»ÄÜΪÔÚ±¾»á»°ÖÐÕýÔÚÔËÐеÄÓï¾ä²úÉúÖ´Ðмƻ®£¬¼´ÎÒÃÇÐèÒªÒѾ֪µÀÁËÄÄÌõÓï¾äÔËÐеÄЧÂʺܲÎÒÃÇÊÇÓÐÄ¿µÄÖ»¶ÔÕâÌõSQLÓï¾äÈ¥ÓÅ»¯¡£Æäʵ£¬ÔںܶàÇé¿öÏ£¬ÎÒÃÇÖ»»áÌýÒ»¸ö¿Í»§±§Ô¹ËµÏÖÔÚϵͳÔËÐкÜÂý£¬¶øÎÒÃDz»ÖªµÀÊÇÄĸöSQLÒýÆðµÄ¡£´ËʱÓÐÐí¶àÏֳɵÄÓï¾ä¿ÉÒÔÕÒ³öºÄ·Ñ×ÊÔ´±È½Ï¶àµÄÓï¾ä£¬È磺
SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions,
buffer_gets/executions AVG from v$sqlarea
WHERE executions>0 AND buffer_gets > 100000 ORDER BY 5;
ADDRESS &n
Ïà¹ØÎĵµ£º
ÐÞ¸Äoracle 10gµÄ×Ö·û¼¯
ÐÞ¸ÄÊý¾Ý¿â×Ö·û¼¯Îª£ºZHS16GBK
Ê×ÏÈÓÃscott&tiger&orclµÇ¼µ½sql/plus
²é¿´·þÎñÆ÷¶Ë×Ö·û¼¯
SQL > select * from
V$NLS_PARAMETERS;
Ð޸ģº
$sqlplus /nolog
SQL>conn / as sysdba
Èô
´ËʱÊý¾Ý¿â·þÎñÆ÷ÒÑÆô¶¯£¬ÔòÏÈÖ´ÐÐ SHUTDOWN IMMEDIATE Ãü
Áî¹Ø±ÕÊý¾Ý¿â·þÎñÆ÷£¬È»ºóÖ´ ......
The DB File Scattered Read wait event generally indicates waits related to full table scans or fast
full index scans. As full table scans are pulled into memory, they are scattered throughout the
buffer cache, since it is usually unlikely that they fall into contiguous buffers. A large numb ......
Êý¾Ý±íµÄÁ¬½ÓÓÐ:
1¡¢ÄÚÁ¬½Ó(×ÔÈ»Á¬½Ó): Ö»ÓÐÁ½¸ö±íÏàÆ¥ÅäµÄÐвÅÄÜÔÚ½á¹û¼¯ÖгöÏÖ
2¡¢ÍâÁ¬½Ó: °üÀ¨
£¨1£©×óÍâÁ¬½Ó(×ó±ßµÄ±í²»¼ÓÏÞÖÆ)
£¨2£©ÓÒÍâÁ¬½Ó(ÓÒ±ßµÄ±í²»¼ÓÏÞÖÆ)
£¨3£©È«ÍâÁ¬½Ó(×óÓÒÁ½±í¶¼²»¼ÓÏÞÖÆ)
3¡¢×ÔÁ¬½Ó(Á¬½Ó·¢ÉúÔÚÒ»Õ ......
SQL*LOADERÊÇORACLEµÄÊý¾Ý¼ÓÔØ¹¤¾ß£¬Í¨³£ÓÃÀ´½«²Ù×÷ϵͳÎļþÇ¨ÒÆµ½ORACLEÊý¾Ý¿âÖС£SQL*LOADERÊÇ´óÐÍÊý¾Ý
²Ö¿âÑ¡ÔñʹÓõļÓÔØ·½·¨¡£
¡¡¡¡ÔÚNTÏ£¬SQL*LOADERµÄÃüÁîΪSQLLDR£¬ÔÚUNIXÏÂÒ»°ãΪsqlldr/sqlload¡£
¡¡¡¡ÈçÖ´ÐУºd:\oracle>sqlldr
SQL*Loader: Release 8.1.6.0.0 - Production on ÐÇÆÚ¶þ 1ÔÂ 8 11:06:42 2 ......
Navicat for xxx ÊÇÒ»¸öÓÅÐãµÄÊý¾Ý¿â¹ÜÀí¿Í»§¶Ë£¬ÓÐ MySQL¡¢Oracle µÈ°æ±¾¡£½¨Òé´ó¼Ò×îºÃÓà Enterprise °æ±¾£¬¹¦ÄÜÈ«ÃæÒ»Ð©£¬µ«½ÏÖ®ÓÚÃâ·ÑµÄ Lite °æ£¬ÆóÒµ°æ¿ÉÊÇÒª»¨Òø×ÓÂòµÄ¡£
°²×° Navicat for Oracle ºó£¬Ê×ÏÈÐèÒª½¨Ò»¸ö“Á¬½Ó”£¬×÷Ϊij¸ö¹ÜÀíÈÎÎñµÄ±êʶ£¬ÒòΪ×÷Î ......