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

¼à¿ØÊý¾Ý¿âÐÔÄܵÄSQL»ã×Ü

¼à¿ØÊý¾Ý¿âÐÔÄܵÄSQL»ã×Ü ¡¾http://hi.baidu.com/g%5Fliying/blog/item/89711cfc27b82ff4fc037f80.html¡¿
1. ¼à¿ØÊÂÀýµÄµÈ´ý
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4;
2. »Ø¹ö¶ÎµÄÕùÓÃÇé¿ö
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
¼à¿ØÊý¾Ý¿âÐÔÄܵÄSQL»ã×Ü
1. ¼à¿ØÊÂÀýµÄµÈ´ý
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4;
2. »Ø¹ö¶ÎµÄÕùÓÃÇé¿ö
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
3. ¼à¿Ø±í¿Õ¼äµÄ I/O ±ÈÀý
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
4. ¼à¿ØÎļþϵͳµÄ I/O ±ÈÀý
select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
5.ÔÚij¸öÓû§ÏÂÕÒËùÓеÄË÷Òý
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
6. ¼à¿Ø SGA µÄÃüÖÐÂÊ
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
7. ¼à¿Ø SGA ÖÐ×ֵ仺³åÇøµÄÃüÖÐÂÊ
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "


Ïà¹ØÎĵµ£º

½â¾öͬʱÐÞ¸ÄͬһSQL¼Ç¼µÄÎÊÌâ

ÔÚÑ°ÕÒPowerDesignerÏà¹ØÐÅÏ¢µÄʱºòżȻ·¢ÏÖµÄ.ʹÓÃtimestamp
΢ÈíMSDNÄÚÈÝ:
timestamp (Transact-SQL)
¹«¿ªÊý¾Ý¿âÖÐ×Ô¶¯Éú³ÉµÄΨһ¶þ½øÖÆÊý×ÖµÄÊý¾ÝÀàÐÍ¡£timestamp ͨ³£ÓÃ×÷¸ø±íÐмӰ汾´ÁµÄ»úÖÆ¡£ ´æ´¢´óСΪ 8 ¸ö×Ö½Ú¡£ timestamp Êý¾ÝÀàÐÍÖ»ÊǵÝÔöµÄÊý×Ö£¬²»±£ÁôÈÕÆÚ»òʱ¼ä¡£ ÈôÒª¼Ç¼ÈÕÆÚ»òʱ¼ä£¬ÇëʹÓà datetime ......

SQL 2005 tips

USE tempdb
GO
CREATE TABLE AuctionItems
(
  itemid       INT          NOT NULL PRIMARY KEY NONCLUSTERED,
  itemtype     NVARCHAR(30) NOT NULL,
  whenmade     INT&nb ......

SQL 2005 Tips Bulk data transfer sample‏

CREATE PROC [dbo].[UP_EC_JOB_UpdateAddressType] 

 @Count INT  

AS 
BEGIN 
 
 SET NOCOUNT ON 
  
 DECLARE @TransactionNumber INT  
 DECLARE @Cursor CURSOR  
 
 SET @C ......

sqlÐÔÄÜÓÅ»¯

1.²éѯµÄÄ£ºýÆ¥Åä
¾¡Á¿±ÜÃâÔÚÒ»¸ö¸´ÔÓ²éѯÀïÃæʹÓà LIKE '%parm1%'—— ºìÉ«±êʶλÖõİٷֺŻᵼÖÂÏà¹ØÁеÄË÷ÒýÎÞ·¨Ê¹Óã¬×îºÃ²»ÒªÓÃ.
½â¾ö°ì·¨:
ÆäʵֻÐèÒª¶Ô¸Ã½Å±¾ÂÔ×ö¸Ä½ø£¬²éѯËٶȱã»áÌá¸ß½ü°Ù±¶¡£¸Ä½ø·½·¨ÈçÏ£º
a¡¢ÐÞ¸Äǰ̨³ÌÐò——°Ñ²éѯÌõ¼þµÄ¹©Ó¦ÉÌÃû³ÆÒ»À¸ÓÉÔ­À´µÄÎı¾ÊäÈë¸ÄΪÏÂÀ­Áб ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ