Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQLÓï¾äÓÅ»¯Êµ¼ùÖ®Ò»SQL_TRACE

SQLÓï¾äÓÅ»¯Êµ¼ùÖ®Ò»SQL_TRACE
 
»·¾³£ºÔÚPL/sqlÉϵ÷ÊÔÊý¾Ý
Pl/sql developer¹¤¾ßÁ¬½ÓʵÀýºó¼´×÷Ϊһ¸öÓû§½ø³ÌÕ¼ÓÃÒ»¸ösession£»
 
select * from  v$session t  where t.PROGRAM='plsqldev.exe' and  t.USERNAME='DZJC'
²éѯ½á¹ûÏÔʾÁ˼¸¸ö¹Ø¼üµÄ×Ö¶Î
SADDR   RAW(4)  Session address ÄÚ´æµØÖ·
SID  NUMBER  Session identifier  Ψһ±êʶ
SERIAL#  NUMBER 
Session serial number. Used to identify uniquely a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID 
STATUS  VARCHAR2(8) 
Status of the session: ACTIVE (currently executing SQL), INACTIVE, KILLED (marked to be killed), CACHED (temporarily cached for use by Oracle*XA), SNIPED (session inactive, waiting on the client) µ±ÆÚ״̬
MACHINE  VARCHAR2(64)  Operating system machine name 
PROGRAM  VARCHAR2(48)  Operating system program name Ó¦ÓóÌÐòÃû³Æ¡£
´ò¿ª´°¿ÚÒÀ´ÎÖ´ÐÐÈçÏÂÃüÁ
Æô¶¯µ±ÆÚsesison¼¶±ð ¸ú×Ù
Æô¶¯SQL¸ú×Ù
ʵÀý¼¶±ð
Alter system set sql_trace=true  scope=both;
µ±ÆÚsession¼¶±ð
Alter session set sql_trace=true;
»ò
Execute dbms_session.set_sql_trace(true);
EXECUTE dbms_system.set_sql_trace_in_session
(session_id, serial_id, true);
 
 
 
 
 
 
 
 
 
Alter  session  set sql_trace=true;
Ö´Ðдýµ÷ÊÔµÄÓï¾ä
select c.code, nvl(b.con, 0)
  from t_sys_codemap c,
       (select COUNT(1) con, m.bljg
          from V_JC_XZXK_BUSI_TIMELIMIT t, V_JC_XZXK_BUSIINDEX m
         where t.busiindexid = m.ywlsh
           and t.LIMITTYPE = 1
           and trunc(m.tjsj) >= trunc(SYSDATE, 'year')
&nb


Ïà¹ØÎĵµ£º

sqlÈÕÆÚÄ£ºý²éѯ SQLʱ¼äת»»¸ñʽ

Select * from t_user_profile where convert ( varchar ( 21 ),regDate, 120 ) like ' 2008-05-07% ' ±íÃû³Æ£ºt_user_profile ÈÕÆÚ×Ö¶ÎÃû³Æ£ºregDate
Select   *   from t_user_profile  where  convert(varchar(21),regDate,120) like '2008-05-07%'< ......

¾­µäSQLÓï¾ä´óÈ«

ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
¡¡¡¡SQL·ÖÀࣺ
¡¡¡¡DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
¡¡¡¡DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
¡¡¡¡DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
¡¡¡¡Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
¡¡¡¡1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
......

PL/SQLѧϰ±Ê¼ÇÈý


1.Ìõ¼þ¿ØÖÆ
1.1 if .. then .. end if
if Ìõ¼þ then
    Óï¾ä¶Î£»
end if;
1.2 if .. then .. else .. end if
if Ìõ¼þ then
    Óï¾ä¶Î£»
else 
    Óï¾ä¶Î£»
end if;
1.3 ifǶÌ×
2.Ñ­»·¿ØÖÆ
2.1 loop .. exit .. end loop
loop  
   & ......

sql ²éѯÌõ¼þ×Ö¶ÎΪtext»òntext µÄ½â¾ö·½°¸

sql ²éѯÌõ¼þ×Ö¶ÎΪtext»òntextµÃ½â¾ö·½°¸ÒÔ¼°varchar(max)¡¢nvarchar(max)
1¡¢ÔÚMS SQL2005¼°ÒÔÉϵİ汾ÖУ¬¼ÓÈë´óÖµÊý¾ÝÀàÐÍ£¨varchar(max)¡¢nvarchar(max)¡¢varbinary(max) £©¡£´óÖµÊý¾ÝÀàÐÍ×î¶à¿ÉÒÔ´æ´¢2^30-1¸ö×Ö½ÚµÄÊý¾Ý¡£
Õ⼸¸öÊý¾ÝÀàÐÍÔÚÐÐΪÉϺͽÏСµÄÊý¾ÝÀàÐÍ varchar¡¢nvarchar ºÍ varbinary Ïàͬ¡£
΢ÈíµÄË ......

ʹÓÃset statistics on À´¼ì²ésql tuningµÄ½á¹û


ÔÚSQL Server2005/2008ÖпÉÒÔʹÓÃÒ»ÏÂËĸöÃüÁîÀ´µ÷ÓÅsqlÓï¾äÒÔ¼°¼ì²éµ÷ÓŵĽá¹û
set
 statistics time on

set
 statistics IO on

set
 statistics profile on

set
 statistics xml on

......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ