oracle sql tuning
alert index mem_ct monitoring usage;
desc v$object_usage;
set linesize 190
select * from v$object_usage;
SQL>SET AUTOTRACE ON;
¡¡¡¡*autotrace¹¦ÄÜÖ»ÄÜÔÚSQL*PLUSÀïʹÓÃ
¡¡¡¡ÆäËûһЩʹÓ÷½·¨£º
¡¡¡¡2.2.1¡¢ÔÚSQLPLUSÖеõ½Óï¾ä×ܵÄÖ´ÐÐʱ¼ä
¡¡¡¡SQL> set timing on;
2.2.2¡¢Ö»ÏÔʾִÐмƻ®--(»áͬʱִÐÐÓï¾äµÃµ½½á¹û)
¡¡¡¡SQL>set autotrace on explain
¡¡¡¡±ÈÈ磺
¡¡¡¡sql> select count(*) from test;
¡¡¡¡count(*)
¡¡¡¡-------------
¡¡¡¡4
¡¡¡¡Execution plan
¡¡¡¡----------------------------
¡¡¡¡0 select statement ptimitzer=choose (cost=3 card=1)
¡¡¡¡1 0 sort(aggregate)
¡¡¡¡2 1 partition range(all)
¡¡¡¡3 2 table access (full) of 't_test' (cost=3 card=900)
¡¡¡¡2.2.3¡¢Ö»ÏÔʾͳ¼ÆÐÅÏ¢---(»áͬʱִÐÐÓï¾äµÃµ½½á¹û)
¡¡¡¡SQL>set autotrace on statistics;
¡¡¡¡(±¸×¢£º¶ÔÓÚSYSÓû§£¬Í³¼ÆÐÅÏ¢½«»áÊÇ0)
¡¡¡¡2.2.4¡¢ÏÔʾִÐмƻ®£¬ÆÁ±ÎÖ´Ðнá¹û--(µ«Óï¾äʵÖÊ»¹Ö´ÐеÄ
¡¡¡¡SQL> set autotrace on traceonly;
¡¡¡¡(±¸×¢£ºÍ¬SET AUTOTRACE ON; Ö»²»¹ý²»ÏÔʾ½á¹û£¬ÏÔʾ¼Æ»®ºÍͳ¼Æ)
¡¡¡¡2.2.5¡¢½ö½öÏÔʾִÐмƻ®£¬ÆÁ±ÎÆäËûÒ»Çнá¹û--(Óï¾ä»¹ÊÇÖ´ÐÐÁË)
¡¡¡¡SQL>set autotrace on traceonly explain;
¡¡¡¡¶ÔÓÚ½ö½ö²é¿´´ó±íµÄExplain Plan·Ç³£¹ÜÓá£
¡¡¡¡2.2.6¡¢¹Ø±Õ
¡¡¡¡SQL>set autotrace off;
Ïà¹ØÎĵµ£º
-----------------------------------------------------------------------------------------------------------------------
create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null , '¹ã¶«Ê¡')
insert into tb values('002' , '001' , '¹ãÖÝÊÐ')
insert i ......
ÐèҪȨÏÞ:
grant references on test_sys to user_1;
or
grant all on test_sys to user_1;
²âÊÔ£º
sysÓû§ÏÂ:
SQL> create user user_1 identified by user_1;
Óû§ÒÑ´´½¨¡£
SQL> grant dba to user_1;
ÊÚȨ³É¹¦¡£
SQL> create table test_sys(pk_col varchar2(5))
2&nbs ......
Ò»¡¢$vi $ORACLE_HOME/network/admin/sqlnet.ora
Èç¹û¸Ãsqlnet.oraÎļþ²»´æÔÚ£¬¿ÉÒÔ²ÉÓÃÈçÏ·½Ê½Éú³É
1£©¿ÉÒÔ¿½±´$ORACLE_HOME/network/admin/samples/sqlnet.oraµ½$ORACLE_HOME/network/admin/Ŀ¼ÏÂʹÓÃ
2£©Ê¹ÓÃnetca¹¤¾ß½øÐÐÅäÖã¬NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
¶þ¡¢ÉèÖûòÐ޸IJÎÊý£¨Èç¹û²ÎÊ ......
¹ØÓÚOracleÊÂÎñµÄ×ܽá
1.ʲôÊÇÊÂÎñ£¬ÊÂÎñµÄÌØÐÔÊÇʲô£¿
ÊÂÎñµÄÈÎÎñ±ãÊÇʹÊý¾Ý¿â´ÓÒ»ÖÖ״̬±ä»»³ÉΪÁíÒ»ÖÖ״̬£¬ÕⲻͬÓÚÎļþϵͳ£¬ËüÊÇÊý¾Ý¿âËùÌØÓõġ£ËüµÄÌØÐÔÓÐËĸö£ºTOM×ܽáΪACID¼´
Ô×ÓÐÔatomicity:Óï¾ä¼¶Ô×ÓÐÔ£¬¹ý³Ì¼¶Ô×ÓÐÔ£¬ÊÂÎñ¼¶Ô×ÓÐÔ
Ò»ÖÂÐÔconsistency:״̬һÖ£¬Í¬Ò»ÊÂÎñÖв»»áÓÐÁ½ÖÖ״̬
¸ôÀëÐÔiso ......
red hat linux ϰ²×° oracle 10g
racle¿¼×ÊÁÏ:
Oracle¹Ù·½ÍøÕ¾: http://download.oracle.com/docs/html/B10813_01/toc.htm
Ò»¡¢ÒÔrootÓû§µÇ¼, ½øÐÐÈçϲÙ×÷£º
1 ¼ì²éÓ²¼þÒªÇó
* Ö÷Òª°üÀ¨£º
********************************************************************
* ÄÚ´æ: & ......