脚本：TOP SQL（CPU Time）
Code：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
[oracle@ora12c - allah:~]$ cat display_top_cpu_sql.sh sqlplus / as sysdba <<EOF @top_cpu.sql exit; EOF [oracle@ora12c - allah:~]$ cat top_cpu.sql set long 9999999 set linesize 400 set pagesize 99 col sql_text for a80 select * from ( select rownum,sql_text,buffer_gets,disk_reads,sorts,cpu_time/1000000 cpu_sec,executions,rows_processed from v$sqlstats order by cpu_time desc ) where rownum < 11; [oracle@ora12c - allah:~]$ [oracle@ora12c - allah:~]$ sh display_top_cpu_sql.sh SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 18 18:41:49 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> SQL> ROWNUM SQL_TEXT BUFFER_GETS DISK_READS SORTS CPU_SEC EXECUTIONS ROWS_PROCESSED ---------- -------------------------------------------------------------------------------- ----------- ---------- ---------- ---------- ---------- -------------- 116 select /*+ rule */ bucket, endpoint, col#, epvalue, epvalue_raw, ep_repeat_count 121595 494 -28934 1.459767 36602 633839 from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket 78 BEGIN sys.dbms_auto_report_internal.i_save_report (:rep_ref, :snap_id, :pr_class 36327 510 0 1.406786 1 1 , :rep_id, :suc); END; 208 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctf 22479 48 3109 .530913 1632 2451 ree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.cl ufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instance s,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl( c.deferrable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,dec ode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),nvl(i.evaledition#,1) ,nvl(i.unusablebefore#,0),nvl(i.unusablebeginning#,0), ist.cachedblk,ist.cachehi t,ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicol s,min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) va lid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c .enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj# 254 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_ 91953 232 0 .452935 30798 30213 size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, a vgcln from hist_head$ where obj#=:1 and intcol#=:2 247 select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spar 29180 139 0 .357952 4633 4444 e2, spare3, signature, spare7, spare8, spare9 from obj$ where owner#=:1 and name =:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null 79 select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(sc 16034 39 2023 .347943 2023 25884 ale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,s cale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,proper ty, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0), nvl(evaledi tion#,1),nvl(unusablebefore#,0),nvl(unusablebeginning#,0) from col$ where obj#=: 1 order by intcol# 60 select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.cluco 22571 122 1452 .333951 1453 1453 ls,0),t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.em pcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl (t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigfla g, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit ,ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+) 61 select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher 11547 1214 0 .326952 558 2109 e obj#=:1 and part=:2 and version=:3 order by piece# 161 begin prvt_ilm.stopjobs(-1,true,true,:1); end; 11540 3 0 .291958 5 5 98 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper 23454 276 935 .276957 935 6646 ty,0),subname,type#,flags,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order# 10 rows selected. SQL> SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@ora12c - allah:~]$ |
——————————————————————
Ending。
