MSSQL网页设计一样平常用的sql列表(摘抄)
CSV逻辑上由逗号分割数据的存储引擎--监控索引是不是利用
alterindex&index_namemonitoringusage;
alterindex&index_namenomonitoringusage;
select*fromv$object_usagewhereindex_name=&index_name;
--求数据文件的I/O散布
selectdf.name,phyrds,phywrts,phyblkrd,phyblkwrt,singleblkrds,readtim,writetim
?fromv$filestatfs,v$dbfiledf
wherefs.file#=df.file#orderbydf.name;
--求某个埋没参数的值
?colksppinmformata54
?colksppstvlformata54
?selectksppinm,ksppstvl
?fromx$ksppipi,x$ksppcvcv
?wherecv.indx=pi.indxandpi.ksppinmlike\_%escapeandpi.ksppinmlike%¶meer%;
--求体系中较年夜的latch
selectname,sum(gets),sum(misses),sum(sleeps),sum(wait_time)
?fromv$latch_children
groupbynamehavingsum(gets)>50orderby2;
--求回档日记的切换频次(临盆体系大概工夫会很长)
selectstart_recid,start_time,end_recid,end_time,minutesfrom(selecttest.*,rownumasrn
?from(selectb.recidstart_recid,to_char(b.first_time,yyyy-mm-ddhh24:mi:ss)start_time,
?a.recidend_recid,to_char(a.first_time,yyyy-mm-ddhh24:mi:ss)end_time,round(((a.first_time-b.first_time)*24)*60,2)minutes
?fromv$log_historya,v$log_historybwherea.recid=b.recid+1andb.first_time>sysdate-1
?orderbya.first_timedesc)test)ywherey.rn<30
?
--求回滚段正在处置的事件
selecta.name,b.xacts,c.sid,c.serial#,d.sql_text
?fromv$rollnamea,v$rollstatb,v$sessionc,v$sqltextd,v$transactione
wherea.usn=b.usnandb.usn=e.xidusnandc.taddr=e.addr
?andc.sql_address=d.addressandc.sql_hash_value=d.hash_valueorderbya.name,c.sid,d.piece;
--求出有效的工具
selectalterprocedure||object_name||compile;
?fromdba_objects
wherestatus=INVALIDandowner=&andobject_typein(PACKAGE,PACKAGEBODY);
/
selectowner,object_name,object_type,statusfromdba_objectswherestatus=INVALID;
--求process/session的形态
selectp.pid,p.spid,s.program,s.sid,s.serial#
?fromv$processp,v$sessionswheres.paddr=p.addr;
--求以后session的形态
selectsn.name,ms.value
?fromv$mystatms,v$statnamesn
wherems.statistic#=sn.statistic#andms.value>0;
--求表的索引信息
selectui.table_name,ui.index_name
?fromuser_indexesui,user_ind_columnsuic
whereui.table_name=uic.table_nameandui.index_name=uic.index_name
?andui.table_namelike&table_name%anduic.column_name=&column_name;
--显现表的外键信息
colsearch_conditionformata54
selecttable_name,constraint_name
?fromuser_constraints
?whereconstraint_type=Randconstraint_namein(selectconstraint_namefromuser_cons_columnswherecolumn_name=&1);
selectrpad(child.table_name,25,)child_tablename,
?rpad(cp.column_name,17,)referring_column,rpad(parent.table_name,25,)parent_tablename,
?rpad(pc.column_name,15,)referred_column,rpad(child.constraint_name,25,)constraint_name
?fromuser_constraintschild,user_constraintsparent,
?????user_cons_columnscp,user_cons_columnspc
wherechild.constraint_type=Randchild.r_constraint_name=parent.constraint_nameand
?child.constraint_name=cp.constraint_nameandparent.constraint_name=pc.constraint_nameand
?cp.position=pc.positionandchild.table_name=&table_name
?orderbychild.owner,child.table_name,child.constraint_name,cp.position;
--显现表的分区及子分区(user_tab_subpartitions)
coltable_nameformata16
colpartition_nameformata16
colhigh_valueformata81
selecttable_name,partition_name,HIGH_VALUEfromuser_tab_partitionswheretable_name=&table_name
--利用dbms_xplan天生一个实行企图
explainplansetstatement_id=&sql_idfor&sql;
select*fromtable(dbms_xplan.display);
--求某个事件的重做信息(bytes)
selects.name,m.value
?fromv$mystatm,v$statnames
?wherem.statistic#=s.statistic#ands.namelike%redosize%;
--求cache中缓存凌驾其5%的工具
selecto.owner,o.object_type,o.object_name,count(b.objd)
?fromv$bhb,dba_objectso
whereb.objd=o.object_id
?groupbyo.owner,o.object_type,o.object_name
?havingcount(b.objd)>(selectto_number(value)*0.05fromv$parameterwherename=db_block_buffers);
--求谁堵塞了某个session(10g)
selectsid,username,event,blocking_session,
?seconds_in_wait,wait_time
?fromv$sessionwherestatein(WAITING)andwait_class!=Idle;
--求session的OS历程ID
colprogramformata54
selectp.spid"OSThread",b.name"Name-User",s.program
?fromv$processp,v$sessions,v$bgprocessb
?wherep.addr=s.paddrandp.addr=b.paddr
UNIONALL
selectp.spid"OSThread",s.username"Name-User",s.program
?fromv$processp,v$sessionswherep.addr=s.paddrands.usernameisnotnull;
--查会话的堵塞
coluser_nameformata32
select/*+rule*/lpad(,decode(l.xidusn,0,3,0))||l.oracle_usernameuser_name,o.owner,o.object_name,s.sid,s.serial#
?fromv$locked_objectl,dba_objectso,v$sessions
wherel.object_id=o.object_idandl.session_id=s.sidorderbyo.object_id,xidusndesc;
colusernameformata15
collock_levelformata8
colownerformata18
colobject_nameformata32
select/*+rule*/s.username,decode(l.type,tm,tablelock,tx,rowlock,null)lock_level,o.owner,o.object_name,s.sid,s.serial#
?fromv$sessions,v$lockl,dba_objectso
wherel.sid=s.sidandl.id1=o.object_id(+)ands.usernameisnotnull;
--求守候的事务及会话信息/求会话的守候及会话信息
selectse.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait
?fromv$sessions,v$session_eventse
wheres.usernameisnotnullandse.sid=s.sidands.status=ACTIVEandse.eventnotlike%SQL*Net%orderbys.username;
selects.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait
?fromv$sessions,v$session_waitsw
wheres.usernameisnotnullandsw.sid=s.sidandsw.eventnotlike%SQL*Net%orderbys.username;
--求会话守候的file_id/block_id
coleventformata24
colp1textformata12
colp2textformata12
colp3textformata12
selectsid,event,p1text,p1,p2text,p2,p3text,p3
?fromv$session_wait
whereeventnotlike%SQL%andeventnotlike%rdbms%andeventnotlike%mon%orderbyevent;
selectname,wait_timefromv$latchlwhereexists(select1from(selectsid,event,p1text,p1,p2text,p2,p3text,p3
?fromv$session_wait
whereeventnotlike%SQL%andeventnotlike%rdbms%andeventnotlike%mon%
)xwherex.p1=l.latch#);
--求会话守候的工具
colownerformata18
colsegment_nameformata32
colsegment_typeformata32
selectowner,segment_name,segment_type
?fromdba_extents
wherefile_id=&file_idand&block_idbetweenblock_idandblock_id+blocks-1;
--求buffercache中的块信息
selecto.OBJECT_TYPE,substr(o.OBJECT_NAME,1,10)objname,b.objd,b.status,count(b.objd)
?from?v$bhb,dba_objectso
?whereb.objd=o.data_object_idando.owner=&1groupbyo.object_type,o.object_name,b.objd,b.status;
--求日记文件的空间利用
selectle.leseqcurrent_log_sequence#,100*cp.cpodr_bno/le.lesizpercentage_full
?fromx$kcccpcp,x$kcclele
?wherele.leseq=cp.cpodr_seq;
--求守候中的工具
select/*+rule*/s.sid,s.username,w.event,o.owner,o.segment_name,o.segment_type,
??????o.partition_name,w.seconds_in_waitseconds,w.state
?fromv$session_waitw,v$sessions,dba_extentso
?wherew.eventin(selectnamefromv$event_name?whereparameter1=file#
??andparameter2=block#andnamenotlikecontrol%)
??ando.ownersysandw.sid=s.sidandw.p1=o.file_idandw.p2>=o.block_idandw.p2<o.block_id+o.blocks
--求以后事件的重做尺寸
selectvalue
?fromv$mystat,v$statname
?wherev$mystat.statistic#=v$statname.statistic#andv$statname.name=redosize;
--叫醒smon往扫除一时段
columnpidnew_valueSmon
settermoutoff
selectp.pidfromsys.v_$bgprocessb,sys.v_$processpwhereb.name=SMONandp.addr=b.paddr
/
settermouton
oradebugwakeup&Smon
undefineSmon
--求回退率
selectb.value/(a.value+b.value),a.value,b.valuefromv$sysstata,v$sysstatb
wherea.statistic#=4andb.statistic#=5;
--求DISKREAD较多的SQL
selectst.sql_textfromv$sqls,v$sqltextst
wheres.address=st.addressands.hash_value=st.hash_valueands.disk_reads>300;
--求DISKSORT严峻的SQL
selectsess.username,sql.sql_text,sort1.blocks
?fromv$sessionsess,v$sqlareasql,v$sort_usagesort1
?wheresess.serial#=sort1.session_num
??andsort1.sqladdr=sql.address
??andsort1.sqlhash=sql.hash_value?andsort1.blocks>200;
--求工具的创立代码
columncolumn_nameformata36
columnsql_textformata99
selectdbms_metadata.get_ddl(TABLE,&1)fromdual;
selectdbms_metadata.get_ddl(INDEX,&1)fromdual;
--求表的索引
setlinesize131
selecta.index_name,a.column_name,b.status,b.index_type
fromuser_ind_columnsa,user_indexesb
wherea.index_name=b.index_nameanda.table_name=&1;
求索引中行数较多的
selectindex_name,blevel,num_rows,CLUSTERING_FACTOR,statusfromuser_indexeswherenum_rows>10000andblevel>0
selecttable_name,index_name,blevel,num_rows,CLUSTERING_FACTOR,statusfromuser_indexeswherestatusVALID
--求以后会话的SID，SERIAL#
selectsid,serial#fromv$sessionwhereaudsid=SYS_CONTEXT(USERENV,SESSIONID);
--求表空间的未用空间
colmbytesformat9999.9999
selecttablespace_name,sum(bytes)/1024/1024mbytesfromdba_free_spacegroupbytablespace_name;
--求表中界说的触发器
selecttable_name,index_type,index_name,uniquenessfromuser_indexeswheretable_name=&1;
selecttrigger_namefromuser_triggerswheretable_name=&1;
--求不决义索引的表
selecttable_namefromuser_tableswheretable_namenotin(selecttable_namefromuser_ind_columns);
--实行经常使用的历程
execprint_sql(selectcount(*)fromtab);
execshow_space2(table_name);
--求freememory
select*fromv$sgastatwherename=freememory;
selecta.name,sum(b.value)fromv$statnamea,v$sesstatbwherea.statistic#=b.statistic#groupbya.name;
检察一下谁在利用谁人能够得回滚段，大概检察一下某个能够得用户在利用回滚段，
找出领回滚段不休增加的事件，再看看怎样处置它，是不是能够将它commit，再不可
就看看可否kill它，等等,检察以后正在利用的回滚段的用户信息和回滚段信息:
setlinesize121
SELECTr.name"ROLLBACKSEGMENTNAME",l.sid"ORACLEPID",p.spid"SYSTEMPID",s.username"ORACLEUSERNAME"
FROMv$lockl,v$processp,v$rollnamer,v$sessions
WHEREl.sid=p.pid(+)ANDs.sid=l.sidANDTRUNC(l.id1(+)/65536)=r.usnANDl.type(+)=TXANDl.lmode(+)=6ORDERBYr.name;
--检察用户的回滚段的信息
selects.username,rn.namefromv$sessions,v$transactiont,v$rollstatr,v$rollnamern
wheres.saddr=t.ses_addrandt.xidusn=r.usnandr.usn=rn.usn
--天生实行企图
explainplansetstatement_id=a1for&1;
--检察实行企图
selectlpad(,2*(level-1))||operationoperation,options,OBJECT_NAME,positionfromplan_table
startwithid=0andstatement_id=a1connectbypriorid=parent_idandstatement_id=a1
--检察内存中存的利用
selectdecode(greatest(class,10),10,decode(class,1,Data,2,Sort,4,Header,to_char(class)),Rollback)"Class",
sum(decode(bitand(flag,1),1,0,1))"NotDirty",sum(decode(bitand(flag,1),1,1,0))"Dirty",
sum(dirty_queue)"OnDirty",count(*)"Total"
fromx$bhgroupbydecode(greatest(class,10),10,decode(class,1,Data,2,Sort,4,Header,to_char(class)),Rollback);
--检察表空间形态
?selecttablespace_name,extent_management,segment_space_managementfromdba_tablespaces;
?selecttable_name,freelists,freelist_groupsfromuser_tables;
--检察体系哀求情形
SELECTDECODE(name,summeddirtywritequeuelength,value)/
DECODE(name,writerequests,value)"WriteRequestLength"
FROMv$sysstatWHEREnameIN(summeddirtyqueuelength,writerequests)andvalue>0;
--盘算databuffer射中率
selecta.value+b.value"logical_reads",c.value"phys_reads",
round(100*((a.value+b.value)-c.value)/(a.value+b.value))"BUFFERHITRATIO"
fromv$sysstata,v$sysstatb,v$sysstatc
wherea.statistic#=40andb.statistic#=41andc.statistic#=42;
SELECTname,(1-(physical_reads/(db_block_gets+consistent_gets)))*100H_RATIOFROMv$buffer_pool_statistics;
--检察内存利用情形
selectleast(max(b.value)/(1024*1024),sum(a.bytes)/(1024*1024))shared_pool_used,
max(b.value)/(1024*1024)shared_pool_size,greatest(max(b.value)/(1024*1024),sum(a.bytes)/(1024*1024))-
(sum(a.bytes)/(1024*1024))shared_pool_avail,((sum(a.bytes)/(1024*1024))/(max(b.value)/(1024*1024)))*100avail_pool_pct
fromv$sgastata,v$parameterbwhere(a.pool=sharedpoolanda.namenotin(freememory))andb.name=shared_pool_size;
--检察用户利用内存情形
selectusername,sum(sharable_mem),sum(persistent_mem),sum(runtime_mem)
fromsys.v_$sqlareaa,dba_usersb
wherea.parsing_user_id=b.user_idgroupbyusername;
--检察工具的缓存情形
selectOWNER,NAMESPACE,TYPE,NAME,SHARABLE_MEM,LOADS,EXECUTIONS,LOCKS,PINS,KEPT
fromv$db_object_cachewheretypenotin(NOTLOADED,NON-EXISTENT,VIEW,TABLE,SEQUENCE)
andexecutions>0andloads>1andkept=NOorderbyowner,namespace,type,executionsdesc;
selecttype,count(*)fromv$db_object_cachegroupbytype;
--检察库缓存射中率
selectnamespace,gets,gethitratio*100gethitratio,pins,pinhitratio*100pinhitratio,RELOADS,INVALIDATIONSfromv$librarycache
--检察某些用户的hash
selecta.username,count(b.hash_value)total_hash,count(b.hash_value)-count(unique(b.hash_value))same_hash,
(count(unique(b.hash_value))/count(b.hash_value))*100u_hash_ratio
fromdba_usersa,v$sqlareabwherea.user_id=b.parsing_user_idgroupbya.username;
--检察字典射中率
select(sum(getmisses)/sum(gets))ratiofromv$rowcache;
--检察undo段的利用情形
SELECTd.segment_name,extents,optsize,shrinks,aveshrink,aveactive,d.status
FROMv$rollnamen,v$rollstats,dba_rollback_segsd
WHEREd.segment_id=n.usn(+)andd.segment_id=s.usn(+);
--有效的工具
selectowner,object_type,object_namefromdba_objectswherestatus=INVALID;
selectconstraint_name,table_namefromdba_constraintswherestatus=INVALID;
--求出某个历程，并对它举行跟踪
selects.sid,s.serial#fromv$sessions,v$processpwheres.paddr=p.addrandp.spid=&1;
execdbms_system.SET_SQL_TRACE_IN_SESSION(&1,&2,true);
execdbms_system.SET_SQL_TRACE_IN_SESSION(&1,&2,false);
--求出锁定的工具
selectdo.object_name,session_id,process,locked_mode
fromv$locked_objectlo,dba_objectsdowherelo.object_id=do.object_id;
--求以后session的跟踪文件
SELECTp1.value||/||p2.value||_ora_||p.spid||.orafilename
?FROMv$processp,v$sessions,v$parameterp1,v$parameterp2
?WHEREp1.name=user_dump_destANDp2.name=instance_name
??ANDp.addr=s.paddrANDs.audsid=USERENV(SESSIONID)ANDp.backgroundisnullANDinstr(p.program,CJQ)=0;
--求工具地点的文件及块号
selectsegment_name,header_file,header_block
fromdba_segmentswheresegment_namelike&1;
--求工具产生事件时回退段及块号
selecta.segment_name,a.header_file,a.header_block
fromdba_segmentsa,dba_rollback_segsb
wherea.segment_name=b.segment_nameandb.segment_id=&1
--9i的在线重界说表
/*假如在线重界说的表没有主键必要创立主键*/
execdbms_redefinition.can_redef_table(cybercafe,announcement);
createtableanno2asselect*fromannouncement
execdbms_redefinition.start_redef_table(cybercafe,announcement,anno2);
execdbms_redefinition.sync_interim_table(cybercafe,announcement,anno2);
execdbms_redefinition.finish_redef_table(cybercafe,announcement,anno2);
droptableanno2
execdbms_redefinition.abort_redef_table(cybercafe,announcement,anno2);
--经常使用的logmnr剧本(cybercafe)
execsys.dbms_logmnr_d.build(dictionary_filename=>esal,dictionary_location=>/home/oracle/logmnr);
execsys.dbms_logmnr.add_logfile(logfilename=>/home/oracle/oradata/esal/archive/1_24050.dbf,options=>sys.dbms_logmnr.new);
execsys.dbms_logmnr.add_logfile(logfilename=>/home/oracle/oradata/esal/archive/1_22912.dbf,options=>sys.dbms_logmnr.addfile);
execsys.dbms_logmnr.add_logfile(logfilename=>/home/oracle/oradata/esal/archive/1_22913.dbf,options=>sys.dbms_logmnr.addfile);
execsys.dbms_logmnr.add_logfile(logfilename=>/home/oracle/oradata/esal/archive/1_22914.dbf,options=>sys.dbms_logmnr.addfile);
execsys.dbms_logmnr.start_logmnr(dictfilename=>/home/oracle/logmnr/esal.ora);
createtablelogmnr2asselect*fromv$logmnr_contents;
--与权限相干的字典
ALL_COL_PRIVS????暗示列上的受权，用户和PUBLIC是被授与者
ALL_COL_PRIVS_MADE?暗示列上的受权，用户是属主和被授与者
ALL_COL_RECD????暗示列上的受权，用户和PUBLIC是被授与者
ALL_TAB_PRIVS????暗示工具上的受权，用户是PUBLIC或被授与者或用户是属主
ALL_TAB_PRIVS_MADE?暗示工具上的权限，用户是属主或授与者
ALL_TAB_PRIVS_RECD?暗示工具上的权限,用户是PUBLIC或被授与者
DBA_COL_PRIVS????数据库列上的一切受权
DBA_ROLE_PRIVS???显现已授与用户或其他脚色的脚色
DBA_SYS_PRIVS????已授与用户或脚色的体系权限
DBA_TAB_PRIVS????数据库工具上的一切权限
ROLE_ROLE_PRIVS???显现已授与用户的脚色
ROLE_SYS_PRIVS???显现经由过程脚色授与用户的体系权限
ROLE_TAB_PRIVS???显现经由过程脚色授与用户的工具权限
SESSION_PRIVS????显现用户如今可使用的一切体系权限
USER_COL_PRIVS???显现列上的权限，用户是属主、授与者或被授与者
USER_COL_PRIVS_MADE显现列上已授与的权限，用户是属主或授与者
USER_COL_PRIVS_RECD显现列上已授与的权限，用户是属主或被授与者
USER_ROLE_PRIVS???显现已授与给用户的一切脚色
USER_SYS_PRIVS???显现已授与给用户的一切体系权限
USER_TAB_PRIVS???显现已授与给用户的一切工具权限
USER_TAB_PRIVS_MADE显现已授与给其他用户的工具权限，用户是属主
USER_TAB_PRIVS_RECD显现已授与给其他用户的工具权限，用户是被授与者
--怎样用dbms_stats剖析表及形式？
execdbms_stats.gather_schema_stats(ownname=>&USER_NAME,estimate_percent=>dbms_stats.auto_sample_size,
?method_opt=>forallcolumnssizeauto,degree=>DBMS_STATS.DEFAULT_DEGREE);
execdbms_stats.gather_schema_stats(ownname=>&USER_NAME,estimate_percent=>dbms_stats.auto_sample_size,cascade=>true);
/*
FORALLCOLUMNS
FORCOLUMNScolumn|attribute[,column|attribute...],
wheresize_clauseisdefinedassize_clause:=SIZE{integer|REPEAT|AUTO|SKEWONLY}
integer--Numberofhistogrambuckets.Mustbeintherange.
REPEAT--Collectshistogramsonlyonthecolumnsthatalreadyhavehistograms.
AUTO--Oracledeterminesthecolumnstocollecthistogramsbasedondatadistributionandtheworkloadofthecolumns.
SKEWONLY--Oracledeterminesthecolumnstocollecthistogramsbasedonthedatadistributionofthecolumns
*/
对于update操作，event中依次记录旧行，新行的值。 同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。 这就引发了对varchar和char效率讨论的老问题。到底如何分配varchar的数据，是否会出现大规模的碎片？ 个人感觉没有case直观。而且默认的第三字段（还可能更多）作为groupby字段很容易造成新手的错误。 原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。 但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右） 作了些试验，发现使用CLR的存储过程或函数在达到一定的阀值的时候，系统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题！ 语句级快照和事务级快照终于为SQLServer的并发性能带来了突破。个人感觉语句级快照大家应该应用。事务级快照，如果是高并发系统还要慎用。如果一个用户总是被提示修改不成功要求重试时，会杀人的！ 现在是在考虑:如果写到服务器端,我一下搞他个10个存储过程导过去,那久之服务器不就成垃圾箱了吗?即便优化了我的中间层.
页:
[1]