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

Oracle Êý¾ÝÉú³É Ë÷Òý

1.½¨±í
CREATE TABLE qq(
       qq_num integer
);
2.²åÈëÊý¾Ý
²åÈëÊý¾ÝµÄ·½·¨ÓкܶàÖÖ£¬µ«ÊÇÒª²åÈë3ÒÚÊý¾Ý£¬±ØÐëÒªÓиßЧµÄËã·¨¡£ÔÚ²åÈëÊý¾ÝʱÎÒÓÃÁË×µÄ°ì·¨£¬²ÉÓÃPL/SQLÀûÓÃÁ÷³Ì¿ØÖƵÄLOOPÓï¾ä²åÈ롣ͬʱÎÒÒ²¿´ÁËÍøÉϵIJåÈëЧÂʵÄÓÅ»¯£¬ÔõÑùÀûÓýÏÉÙµÄʱ¼äÉú³É½Ï¶àµÄ²âÊÔÊý¾Ý¡£¸øÎÒÓ¡ÏóÉî¿ÌµÄÊÇÀûÓÃÖÖ×Ó±íÀ´Éú³É½Ï¶àµÄÊý¾Ý¡£
ÕâÑù×öµÄºÃ´¦ÊDZ¾ÉíÊý¾Ý¾ÍÔÚ±¾µØ´ÅÅÌ£¬¼õÉÙÁË´æÈ¡µÄI/O²Ù×÷£¬Êý¾ÝÉú³É½Ï¿ì¡£
ÎÒ²ÉÓÃ×µÄ°ì·¨ÀûÓÃ8СʱÉú³ÉÁË2ÒÚ¶àÌõÊý¾Ý¼Ç¼¡£
3.Ë÷ÒýµÄ½¨Á¢
ÓÉÓÚÒÑ´æÔڵļǼÓÐ2ÒÚÌõ£¬²ÉÓÃCREATE INDEX qq_num_index ON qq(qq_num);ºÄ·Ñʱ¼ä½ü1Сʱ¡£
Ë÷ÒýµÄºÃ´¦:²éѯЧÂÊÌá¸ß£¬Ë÷ÒýÏà¹ØµÄ“Ìî³äÒò×Ó”¡£
count£¨£©º¯ÊýʼÖÕ²»×ßË÷Òý£¬¶¼ÊÇÈ«ÅÌɨÃ裬»¹Î´ÕÒµ½½â¾ö°ì·¨¡£Ò²¾ÍÊÇÒÔÏÂPL/SQLÖ´ÐеÄʱ¼äÔÚ½¨Á¢Ë÷ÒýǰºóÊÇÒ»ÑùµÄ¡£
DECLARE
count_num NUMBER;
BEGIN
dbms_output.put_line('Óï¾äÖ´ÐпªÊ¼Ê±¼ä:'||systimestamp);
SELECT COUNT(qq_num) INTO count_num from qq WHERE qq_num IS NOT NULL;
dbms_output.put_line('½á¹û:'||count_num);
dbms_output.put_line('Óï¾äÖ´ÐнáÊøÊ±¼ä:'||systimestamp);
END;
/
µ«Êǽ¨Á¢Ë÷Òýºó£¬SELECT MAX£¨qq_num£© ºÍÆäËûÏà¹ØµÄÓï¾äµÄÖ´ÐÐʱ¼äÁ¢¼´¼õÉÙÁ˺ܶࡣ
ÒÔÏÂÊÇ´úÂ룺
DECLARE
count_num NUMBER;
BEGIN
dbms_output.put_line('Óï¾äÖ´ÐпªÊ¼Ê±¼ä:'||systimestamp);
SELECT MAX(qq_num) INTO count_num from qq;
dbms_output.put_line('½á¹û:'||count_num);
dbms_output.put_line('Óï¾äÖ´ÐнáÊøÊ±¼ä:'||systimestamp);
END;
/
½¨Á¢Ë÷Òýǰ£ºÖ´ÐÐʱ¼äΪ1·Ö7Ãë,Ë÷Òýºó0.2ÃëÄÚÍê³É¡£
½¨Á¢Ë÷ÒýÊÇÒÔ´æ´¢¿Õ¼äΪ´ú¼Û»»È¡Ê±¼äЧÂʵÄÌá¸ß¡£
Õâµ±ÖÐÓÐautotrace¿ÉÒÔ×Ô¶¯ÏÔʾSQLÓï¾äµÄÖ´Ðмƻ®£¬ÕâÖֲ鿴±ØÐëÔÚSQL *PLUSÏÂ.
¿ªÆôautotraceµÄ²½ÖèÈçÏÂ:
--DBA³õ´Î±ØÐëÖ´ÐÐ
CREATE ROLE plustrace;--ÐèÒªÓµÓÐȨÏÞ
GRANT SELECT ON v_$sesstat TO plustrace;--ÐèÒªÓÐGRANTȨÏÞ
GRANT SELECT ON v_$mystat TO plustrace;
GRANT SELECT ON v_$statname TO plustrace;
--¿ªÆô×Ô¶¯¸ú×Ù
SET AUTOTRACE ON;


Ïà¹ØÎĵµ£º

¡¾×ª¡¿OracleÖÐKill sessionµÄÑо¿

OracleÖÐKill sessionµÄÑо¿
×÷Õߣºeygle |English Version ¡¾×ªÔØÊ±ÇëÒÔ³¬Á´½ÓÐÎʽ±êÃ÷ÎÄÕ³ö´¦ºÍ×÷ÕßÐÅÏ¢¼°±¾ÉùÃ÷¡¿
Á´½Ó£ºhttp://www.eygle.com/archives/2004/06/kill_session.html
ÎÒÃÇÖªµÀ,ÔÚOracleÊý¾Ý¿âÖÐ,¿ÉÒÔͨ¹ýkill sessionµÄ·½Ê½À´ÖÕÖ¹Ò»¸ö½ø³Ì,Æä»ù±¾Óï·¨½á¹¹Îª:
alter system kill session 'sid,serial# ......

OracleÊý¾Ý¿âÌá¸ßÃüÖÐÂʼ°Ïà¹ØÓÅ»¯

1)Library CacheµÄÃüÖÐÂÊ:
.¼ÆË㹫ʽ:Library Cache Hit Ratio = sum(pinhits) / sum(pins)
SQL>SELECT SUM(pinhits)/sum(pins)    from V$LIBRARYCACHE; 
ͨ³£ÔÚ98%ÒÔÉÏ£¬·ñÔò£¬ÐèÒªÒª¿¼ÂǼӴó¹²Ïí³Ø£¬°ó¶¨±äÁ¿£¬ÐÞ¸Äcursor_sharingµÈ²ÎÊý¡£
2)¼ÆËã¹²Ïí³ØÄÚ´æÊ¹ÓÃÂÊ:
SQL>SELECT ......

±Ê¼Ç£ºOracle³£Ó÷ÖÎöº¯Êý

Oracle³£Ó÷ÖÎöº¯Êý
ROW_NUMBER
·µ»ØÓÐÐò×éÖÐÒ»ÐÐµÄÆ«ÒÆÁ¿£¬´Ó¶ø¿ÉÓÃÓÚ°´Ìض¨±ê×¼ÅÅÐòµÄÐкÅ
row_number() over(partition by ... order by ...)
RANK
¸ù¾ÝORDER BY×Ó¾äÖбí´ïʽµÄÖµ£¬´Ó²éѯ·µ»ØµÄÿһÐУ¬¼ÆËãËüÃÇÓëÆäËüÐеÄÏà¶ÔλÖá£×éÄÚµÄÊý¾Ý°´ORDER BY×Ó¾äÅÅÐò£¬È»ºó¸øÃ¿Ò»Ðи³Ò»¸öºÅ£¬´Ó¶øÐγÉÒ»¸öÐòÁУ¬¸ÃÐòÁ ......

oracle——·ÖÎöº¯ÊýOVER ()

·ÖÎöº¯ÊýÓÃÓÚ¼ÆËã»ùÓÚ×éµÄijÖÖ¾ÛºÏÖµ£¬ËüºÍ¾ÛºÏº¯ÊýµÄ²»Í¬Ö®´¦ÊÇ
¶ÔÓÚÿ¸ö×é·µ»Ø¶àÐУ¬¶ø¾ÛºÏº¯Êý¶ÔÓÚÿ¸ö×éÖ»·µ»ØÒ»ÐС£
SQL> select t.empno,t.ename,sum(t.sal)
2 from emp t;
select t.empno,t.ename,sum(t.sal)
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> selec ......

ORACLE Êý¾Ý¿âÃû¡¢ÊµÀýÃû¡¢ORACLE_SIDµÄÇø±ð

Êý¾Ý¿âÃû(DB_NAME)¡¢ÊµÀýÃû(Instance_name)¡¢ÒÔ¼°²Ù×÷ϵͳ»·¾³±äÁ¿(ORACLE_SID)
ÔÚORACLE7¡¢8Êý¾Ý¿âÖÐÖ»ÓÐÊý¾Ý¿âÃû(db_name)ºÍÊý¾Ý¿âʵÀýÃû(instance_name)¡£ÔÚORACLE8i¡¢9iÖгöÏÖÁËеIJÎÊý£¬¼´Êý¾Ý¿âÓòÃû(db_domain)¡¢·þÎñÃû(service_name)¡¢ÒÔ¼°²Ù×÷ϵͳ»·¾³±äÁ¿(ORACLE_SID)¡£ÕâЩ¶¼´æÔÚÓÚͬһ¸öÊý¾Ý¿âÖеıêʶ£¬ÓÃÓÚÇ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ