WORKLOAD REPOSITORY report for

DB Name	DB Id	Instance	Inst num	Startup Time	Release	RAC
DCPOS	3752968640	dcpos	1	13-Mar-13 20:03	11.2.0.1.0	NO

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
chnap-itd101	Linux x86 64-bit	80	40	4	504.14

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	2228	18-Mar-13 11:00:08	120	1.3
End Snap:	2229	18-Mar-13 12:00:10	122	1.3
Elapsed:		60.05 (mins)
DB Time:		97.86 (mins)

Report Summary

Cache Sizes

	Begin	End
Buffer Cache:	28,160M	28,160M	Std Block Size:	8K
Shared Pool Size:	15,360M	15,360M	Log Buffer:	444,868K

Load Profile

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	1.6	172.7	0.99	1.24
DB CPU(s):	1.4	147.5	0.84	1.06
Redo size:	838.8	88,886.9
Logical reads:	29,969.9	3,175,802.7
Block changes:	2.8	291.3
Physical reads:	21,703.2	2,299,805.5
Physical writes:	31.9	3,382.4
User calls:	1.3	138.7
Parses:	0.9	94.3
Hard parses:	0.0	0.4
W/A MB processed:	0.1	11.3
Logons:	0.1	8.3
Executes:	1.7	174.7
Rollbacks:	0.0	0.0
Transactions:	0.0

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	100.00	Redo NoWait %:	100.00
Buffer Hit %:	99.75	In-memory Sort %:	100.00
Library Hit %:	99.48	Soft Parse %:	99.59
Execute to Parse %:	46.02	Latch Hit %:	99.97
Parse CPU to Parse Elapsd %:	4.84	% Non-Parse CPU:	99.91

Shared Pool Statistics

	Begin	End
Memory Usage %:	0.98	0.99
% SQL with executions>1:	98.80	98.59
% Memory for SQL w/exec>1:	93.97	92.50

Top 5 Timed Foreground Events

Host CPU (CPUs: 80 Cores: 40 Sockets: 4)

Load Average Begin	Load Average End	%User	%System	%WIO	%Idle
1.94	2.12	2.6	0.6	0.4	96.6

Instance CPU

%Total CPU	%Busy CPU	%DB time waiting for CPU (Resource Manager)
1.8	52.1	0.0

Memory Statistics

	Begin	End
Host Mem (MB):	516,236.3	516,236.3
SGA use (MB):	49,152.0	49,152.0
PGA use (MB):	699.7	4,130.4
% Host Mem used for SGA+PGA:	9.66	10.32

Main Report

• Report Summary
• Wait Events Statistics
• SQL Statistics
• Instance Activity Statistics
• IO Stats
• Buffer Pool Statistics
• Advisory Statistics
• Wait Statistics
• Undo Statistics
• Latch Statistics
• Segment Statistics
• Dictionary Cache Statistics
• Library Cache Statistics
• Memory Statistics
• Streams Statistics
• Resource Limit Statistics
• Shared Server Statistics
• init.ora Parameters

Wait Events Statistics

• Time Model Statistics
• Operating System Statistics
• Operating System Statistics - Detail
• Foreground Wait Class
• Foreground Wait Events
• Background Wait Events
• Wait Event Histogram
• Wait Event Histogram Detail (64 msec to 2 sec)
• Wait Event Histogram Detail (4 sec to 2 min)
• Wait Event Histogram Detail (4 min to 1 hr)
• Service Statistics
• Service Wait Class Stats

Time Model Statistics

• Total time in database user-calls (DB Time): 5871.3s
• Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
• Ordered by % or DB time desc, Statistic name

Back to Wait Events Statistics
Back to Top

Operating System Statistics

• *TIME statistic values are diffed. All others display actual values. End Value is displayed if different
• ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name

Back to Wait Events Statistics
Back to Top

Operating System Statistics - Detail

Back to Wait Events Statistics
Back to Top

Foreground Wait Class

• s - second, ms - millisecond - 1000th of a second
• ordered by wait time desc, waits desc
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
• Captured Time accounts for 112.5% of Total DB time 5,871.32 (s)
• Total FG Wait Time: 1,593.17 (s) DB CPU time: 5,013.75 (s)

Back to Wait Events Statistics
Back to Top

Foreground Wait Events

• s - second, ms - millisecond - 1000th of a second
• Only events with Total Wait Time (s) >= .001 are shown
• ordered by wait time desc, waits desc (idle events last)
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Back to Wait Events Statistics
Back to Top

Background Wait Events

• ordered by wait time desc, waits desc (idle events last)
• Only events with Total Wait Time (s) >= .001 are shown
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Back to Wait Events Statistics
Back to Top

Wait Event Histogram

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• % of Waits: value of .0 indicates value was <.05%; value of null is truly 0
• % of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
• Ordered by Event (idle events last)

Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (64 msec to 2 sec)

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• Units for % of Total Waits: ms is milliseconds s is 1024 milliseconds (approximately 1 second)
• % of Total Waits: total waits for all wait classes, including Idle
• % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
• Ordered by Event (only non-idle events are displayed)

Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (4 sec to 2 min)

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• Units for % of Total Waits: s is 1024 milliseconds (approximately 1 second) m is 64*1024 milliseconds (approximately 67 seconds or 1.1 minutes)
• % of Total Waits: total waits for all wait classes, including Idle
• % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
• Ordered by Event (only non-idle events are displayed)

Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (4 min to 1 hr)

No data exists for this section of the report.

Back to Wait Events Statistics
Back to Top

Service Statistics

• ordered by DB Time

Back to Wait Events Statistics
Back to Top

Service Wait Class Stats

• Wait Class info for services in the Service Statistics section.
• Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
• Time Waited (Wt Time) in seconds

Back to Wait Events Statistics
Back to Top

SQL Statistics

• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by User I/O Wait Time
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Physical Reads (UnOptimized)
• SQL ordered by Executions
• SQL ordered by Parse Calls
• SQL ordered by Sharable Memory
• SQL ordered by Version Count
• Complete List of SQL Text

SQL ordered by Elapsed Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
• %Total - Elapsed Time as a percentage of Total DB time
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Captured SQL account for 96.6% of Total DB Time (s): 5,871
• Captured PL/SQL account for 0.0% of Total DB Time (s): 5,871

Back to SQL Statistics
Back to Top

SQL ordered by CPU Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• %Total - CPU Time as a percentage of Total DB CPU
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Captured SQL account for 96.6% of Total CPU Time (s): 5,014
• Captured PL/SQL account for 0.0% of Total CPU Time (s): 5,014

Back to SQL Statistics
Back to Top

SQL ordered by User I/O Wait Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• %Total - User I/O Time as a percentage of Total User I/O Wait time
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Captured SQL account for 95.1% of Total User I/O Wait Time (s): 1,507
• Captured PL/SQL account for 0.0% of Total User I/O Wait Time (s): 1,507

Back to SQL Statistics
Back to Top

SQL ordered by Gets

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• %Total - Buffer Gets as a percentage of Total Buffer Gets
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Total Buffer Gets: 107,977,290
• Captured SQL account for 92.5% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Reads

• %Total - Physical Reads as a percentage of Total Disk Reads
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Total Disk Reads: 78,193,388
• Captured SQL account for 90.4% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Physical Reads (UnOptimized)

• UnOptimized Read Reqs = Physical Read Reqts - Optimized Read Reqs
• %Opt - Optimized Reads as percentage of SQL Read Requests
• %Total - UnOptimized Read Reqs as a percentage of Total UnOptimized Read Reqs
• Total Physical Read Requests: 4,660,234
• Captured SQL account for 5.7% of Total
• Total UnOptimized Read Requests: 4,660,234
• Captured SQL account for 5.7% of Total
• Total Optimized Read Requests: 1
• Captured SQL account for 0.0% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Executions

• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Total Executions: 5,939
• Captured SQL account for 74.4% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

• Total Parse Calls: 3,206
• Captured SQL account for 77.1% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

• Only Statements with Sharable Memory greater than 1048576 are displayed

Back to SQL Statistics
Back to Top

SQL ordered by Version Count

• Only Statements with Version Count greater than 20 are displayed

Back to SQL Statistics
Back to Top

Complete List of SQL Text

SQL Id	SQL Text
018x8jv0c9900	select count(*) from user$ u, xdb.xdb$schema s where u.user# = :1 and u.name = s.xmldata.schema_owner and (xdb.dbms_xmlschema_int.xdb$Oid2IntName(s.object_id) = :2)
0k8522rmdzg4k	select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
0tkzr5pu8kv2u	select count(*) from xdb.xdb$schema s where bitand(to_number(s.xmldata.flags, 'xxxxxxxx'), 16) = 16 and xdb.dbms_xmlschema_int.xdb$Oid2IntName(s.object_id) = :1 and s.xmldata.schema_url not in (select s2.xmldata.schema_url from xdb.xdb$schema s2, user$ u2 where u2.user# = :2 and u2.name = s.xmldata.schema_owner)
0z1031991bd7w	insert into wrh$_sysmetric_summary (snap_id, dbid, instance_number, begin_time, end_time, intsize, group_id, metric_id, num_interval, maxval, minval, average, standard_deviation, sum_squares) select :snap_id, :dbid, :instance_number, begtime, endtime, intsize_csec, groupid, metricid, numintv, max, min, avg, std, sumsq FROM x$kewmsmdv WHERE groupid = 2
1att0guk2skhm	select lobtsn, lobrdba, lobobjid from x$lobstat where lobspcalloc > 0 and lobcurrtime >= :1 order by lobspcalloc desc
1ckdkd51tm7d2	select lobtsn, lobrdba, lobobjid from x$lobstat where lobspcdealloc > 0 and lobcurrtime >= :1 order by lobspcdealloc desc
1cq3qr774cu45	insert into WRH$_IOSTAT_FILETYPE (snap_id, dbid, instance_number, filetype_id, small_read_megabytes, small_write_megabytes, large_read_megabytes, large_write_megabytes, small_read_reqs, small_write_reqs, small_sync_read_reqs, large_read_reqs, large_write_reqs, small_read_servicetime, small_write_servicetime, small_sync_read_latency, large_read_servicetime, large_write_servicetime, retries_on_error) (select :snap_id, :dbid, :instance_number, filetype_id, sum(small_read_megabytes) small_read_megabytes, sum(small_write_megabytes) small_write_megabytes, sum(large_read_megabytes) large_read_megabytes, sum(large_write_megabytes) large_write_megabytes, sum(small_read_reqs) small_read_reqs, sum(small_write_reqs) small_write_reqs, sum(small_sync_read_reqs) small_sync_read_reqs, sum(large_read_reqs) large_read_reqs, sum(large_write_reqs) large_write_reqs, sum(small_read_servicetime) small_read_servicetime, sum(small_write_servicetime) small_write_servicetime, sum(small_sync_read_latency) small_sync_read_latency, sum(large_read_servicetime) large_read_servicetime, sum(large_write_servicetime) large_write_servicetime, sum(retries_on_error) retries_on_error from v$iostat_file group by filetype_id)
1gfaj4z5hn1kf	delete from dependency$ where d_obj#=:1
2sx38dz82grr8	UPDATE "POS"."PRODUCT_INF" SET "BARCODE" = :a1, "SIZEGROUP" = :a2, "UPDATEDATE" = :a3, "VERSIONID" = :a4 WHERE "PRODUCTID" = :b0
2w2w2x51dk89q	select o.name from obj$ o where o.type# = 9 and bitand(o.flags, 2048) = 2048 and o.name like 'SYSFN%' and (sysdate-o.ctime) > 0.0007
350myuyx0t1d6	insert into wrh$_tablespace_stat (snap_id, dbid, instance_number, ts#, tsname, contents, status, segment_space_management, extent_management, is_backup) select :snap_id, :dbid, :instance_number, ts.ts#, ts.name as tsname, decode(ts.contents$, 0, (decode(bitand(ts.flags, 16), 16, 'UNDO', 'PERMANENT')), 1, 'TEMPORARY') as contents, decode(ts.online$, 1, 'ONLINE', 2, 'OFFLINE', 4, 'READ ONLY', 'UNDEFINED') as status, decode(bitand(ts.flags, 32), 32, 'AUTO', 'MANUAL') as segspace_mgmt, decode(ts.bitmapped, 0, 'DICTIONARY', 'LOCAL') as extent_management, (case when b.active_count > 0 then 'TRUE' else 'FALSE' end) as is_backup from sys.ts$ ts, (select dfile.ts#, sum( case when bkup.status = 'ACTIVE' then 1 else 0 end ) as active_count from v$backup bkup, file$ dfile where bkup.file# = dfile.file# and dfile.status$ = 2 group by dfile.ts#) b where ts.online$ != 3 and bitand(ts.flags, 2048) != 2048 and ts.ts# = b.ts#
3m8smr0v7v1m6	INSERT INTO sys.wri$_adv_message_groups (task_id, id, seq, message#, fac, hdr, lm, nl, p1, p2, p3, p4, p5) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)
3nkd3g3ju5ph1	select obj#, type#, ctime, mtime, stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
4yyb4104skrwj	update obj$ set obj#=:4, type#=:5, ctime=:6, mtime=:7, stime=:8, status=:9, dataobj#=:10, flags=:11, oid$=:12, spare1=:13, spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
50rfmcah65kr0	WITH TB AS ( SELECT D.MANAGENTCLIENTID CLIENTID, M.CLIENTDESC CLIENTDESC, F.NAME STYLENO, H.DESCRIPTION STYLESET, J.DESCRIPTION STYLECATEGORY, F.DESCRIPTION STYLEDESC, K.DESCRIPTION STYLESORT, FLOOR((A.WORKDATE -TO_DATE(:B1 , 'yyyy-mm-dd'))/14) AS WEEKS, MAX(F.STANDUNITPRICE) UNITPRICE, COUNT(DISTINCT C.BRANCHCODE) SHOPS , SUM(B.QUANTITY) QUANTITY, SUM(B.AMT) AMT, CASE WHEN SUM(B.QUANTITY)*MAX(F.STANDUNITPRICE)=0 THEN 0 ELSE ROUND(SUM(B.AMT)/(SUM(B.QUANTITY)*MAX(F.STANDUNITPRICE)), 2) END DISCOUNT FROM YS_SALE A INNER JOIN YS_SALEDETAIL B ON A.SALEID=B.SALEID INNER JOIN YS_BRANCH C ON A.BRANCHID=C.BRANCHID INNER JOIN YS_CLIENT D ON C.KEEPERCLIENTID=D.CLIENTID INNER JOIN YS_GOODS E ON B.BARCODE=E.BARCODE INNER JOIN YS_GOODSSTYLE F ON E.STYLEID=F.STYLEID INNER JOIN YS_GOODSSTYLESET H ON F.STYLESETID=H.STYLESETID INNER JOIN YS_GOODSSTYLECATEGORY J ON J.STYLECATEGORYID=F.STYLECATEGORYID INNER JOIN YS_GOODSSTYLESORT K ON K.STYLESORTID=F.STYLESORTID INNER JOIN YS_AGENT M ON M.MANAGENTCLIENTID=D.MANAGENTCLIENTID WHERE A.WORKDATE BETWEEN TO_DATE(:B1 , 'yyyy-mm-dd') AND TO_DATE(:B2 , 'yyyy-mm-dd') AND A.SALETYPE<>1 AND SUBSTR(F.NAME, 2, 2)='32' AND LENGTH(F.NAME)>8 AND A.BRANCHID NOT IN(SELECT BRANCHID FROM YS_BRANCH19) GROUP BY D.MANAGENTCLIENTID, M.CLIENTDESC, F.NAME, F.DESCRIPTION, H.DESCRIPTION, J.DESCRIPTION, K.DESCRIPTION , FLOOR((A.WORKDATE -TO_DATE(:B1 , 'yyyy-mm-dd'))/14) ) SELECT CLIENTDESC, STYLENO, STYLESET, STYLECATEGORY, STYLEDESC, STYLESORT, MAX(UNITPRIC E), MAX(SHOPS) , SUM(QUANTITY) "ÀÛ»ýÊýÁ¿", SUM(AMT) "ÀÛ»ý½ð¶î" , CASE WHEN SUM(QUANTITY)*MAX(UNITPRICE)=0 THEN 0 ELSE ROUND(SUM(AMT)/(SUM(QUANTITY)*MAX(UNITPRICE)), 2) END "ÕÛ¿Û" , SUM(CASE WHEN WEEKS=0 THEN QUANTITY END) "1sl", SUM(CASE WHEN WEEKS=0 THEN AMT END) "1je", SUM(CASE WHEN WEEKS=0 THEN DISCOUNT END) "1zk" , SUM(CASE WHEN WEEKS=1 THEN QUANTITY END) "2sl", SUM(CASE WHEN WEEKS=1 THEN AMT END) "2je", SUM(CASE WHEN WEEKS=1 THEN DISCOUNT END) "2zk" , SUM(CASE WHEN WEEKS=2 THEN QUANTITY END) "3sl", SUM(CASE WHEN WEEKS=2 THEN AMT END) "3je", SUM(CASE WHEN WEEKS=2 THEN DISCOUNT END) "3zk" , SUM(CASE WHEN WEEKS=3 THEN QUANTITY END) "4sl", SUM(CASE WHEN WEEKS=3 THEN AMT END) "4je", SUM(CASE WHEN WEEKS=3 THEN DISCOUNT END) "4zk" , SUM(CASE WHEN WEEKS=4 THEN QUANTITY END) "5sl", SUM(CASE WHEN WEEKS=4 THEN AMT END) "5je", SUM(CASE WHEN WEEKS=4 THEN DISCOUNT END) "5zk" , SUM(CASE WHEN WEEKS=5 THEN QUANTITY END) "6sl", SUM(CASE WHEN WEEKS=5 THEN AMT END) "6je", SUM(CASE WHEN WEEKS=5 THEN DISCOUNT END) "6zk" , SUM(CASE WHEN WEEKS=6 THEN QUANTITY END) "7sl", SUM(CASE WHEN WEEKS=6 THEN AMT END) "7je", SUM(CASE WHEN WEEKS=6 THEN DISCOUNT END) "7zk" , SUM(CASE WHEN WEEKS=7 THEN QUANTITY END) "8sl", SUM(CASE WHEN WEEKS=7 THEN AMT END) " ;8je" , SUM(CASE WHEN WEEKS=7 THEN DISCOUNT END) "8zk" , SUM(CASE WHEN WEEKS=8 THEN QUANTITY END) "9sl", SUM(CASE WHEN WEEKS=8 THEN AMT END) "9je", SUM(CASE WHEN WEEKS=8 THEN DISCOUNT END) "9zk" , SUM(CASE WHEN WEEKS=9 THEN QUANTITY END) "10sl", SUM(CASE WHEN WEEKS=9 THEN AMT END) "10je", SUM(CASE WHEN WEEKS=9 THEN DISCOUNT END) "10zk" , SUM(CASE WHEN WEEKS=10 THEN QUANTITY END) "11sl", SUM(CASE WHEN WEEKS=10 THEN AMT END) "11je", SUM(CASE WHEN WEEKS=10 THEN DISCOUNT END) "11zk" , SUM(CASE WHEN WEEKS=11 THEN QUANTITY END) "12sl", SUM(CASE WHEN WEEKS=11 THEN AMT END) "12je" , SUM(CASE WHEN WEEKS=11 THEN DISCOUNT END) "12zk" , SUM(CASE WHEN WEEKS=12 THEN QUANTITY END) "13sl", SUM(CASE WHEN WEEKS=12 THEN AMT END) "13je", SUM(CASE WHEN WEEKS=12 THEN DISCOUNT END) "13zk" , SUM(CASE WHEN WEEKS=13 THEN QUANTITY END) "14sl", SUM(CASE WHEN WEEKS=13 THEN AMT END) "14je", SUM(CASE WHEN WEEKS=13 THEN DISCOUNT END) "14zk" , SUM(CASE WHEN WEEKS=14 THEN QUANTITY END) "15sl", SUM(CASE WHEN WEEKS=14 THEN AMT END) "15je", SUM(CASE WHEN WEEKS=14 THEN DISCOUNT END) "15zk" FROM TB GROUP BY CLIENTID, CLIENTDESC, STYLENO, STYLESET, STYLECATEGORY, STYLEDESC, STYLESORT ORDER BY CLIENTID ASC, STYLENO ASC
5h91zx386wbht	WITH awr_params as ( SELECT /*+ NO_MERGE(map) USE_HASH(map) */ p.parameter_hash as parameter_hash, max(map.parameter_name) as parameter_name, max (map.keh_id) as keh_id, max (case when p.snap_id = :bid then upper(p.value) else null end) as first_value, max (case when p.snap_id = :eid then upper(p.value) else null end) as last_value, max (case when p.isdefault = 'TRUE' then upper(p.value) else null end) as default_value, max (upper(p.value)) as max_value, min (upper(p.value)) as min_value, max (case when p.snap_id = :bid then decode(p.ismodified, 'FALSE', 0, 'MODIFIED', 1, 4) else null end) as first_is_mod, max (case when p.snap_id = :eid then decode(p.ismodified, 'FALSE', 0, 'MODIFIED', 1, 4) else null end) as last_is_mod FROM ( SELECT /*+ USE_HASH(xmap) */ xmap.keh_id as keh_id, xmap.parameter_hash as parameter_hash, pn.parameter_name as parameter_name FROM X$KEHPRMMAP xmap , WRH$_PARAMETER_NAME pn WHERE pn.dbid = :dbid AND pn.parameter_hash = xmap.parameter_hash ) map, WRH$_PARAMETER p WHERE p.parameter_hash = map.parameter_hash AND p.dbid = :dbid AND p.instance_number = :inst AND p.snap_id >= :bid AND p.snap_id <= :eid GROUP BY p.par ameter_hash ), def_params as (SELECT p.hash as parameter_hash, p.name as parameter_name, x.keh_id as keh_id, decode(p.name, 'db_block_size', '8192', 'gcs_server_processes', '1', 'disk_asynch_io', 'TRUE', '0') as value FROM X$KEHPRMMAP x , V$PARAMETER p WHERE x.parameter_hash = p.hash AND (p.name like '%size' OR p.name like '%target' OR p.name in ('gcs_server_processes', 'disk_asynch_io')) ) SELECT parameter_hash, nvl(a.parameter_name, p.parameter_name) as pname, nvl(a.keh_id, p.keh_id) as keh_id, case when a.keh_id is null then p.value else a.first_value end as first_value, case when a.keh_id is null then p.value else a.last_value end as last_value, case when a.keh_id is null then p.value else a.default_value end as default_value, case when a.keh_id is null then p.value else a.max_value end as min_value, case when a.keh_id is null then p.value else a.min_value end as min_value, case when a.keh_id is null then 0 else a.first_is_mod end as first_is_mod, case when a.keh_id is null then 0 else a.last_is_mod end as last_is_mod FROM awr_params a full outer join def_params p using(parameter_hash)
5ms6rbzdnq16t	select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (next_date <= :2)) or ((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)) and (this_date is null) and ((dbms_logstdby.db_is_logstdby = 0 and job < 1000000000) or (dbms_logstdby.db_is_logstdby = 1 and job >= 1000000000)) order by next_date, job
5srtgtkcbvkdc	WITH TB AS ( SELECT D.MANAGENTCLIENTID CLIENTID, M.CLIENTDESC CLIENTDESC, F.NAME STYLENO, H.DESCRIPTION STYLESET, J.DESCRIPTION STYLECATEGORY, F.DESCRIPTION STYLEDESC, K.DESCRIPTION STYLESORT, FLOOR((A.WORKDATE -TO_DATE(:B1 , 'yyyy-mm-dd'))/7) AS WEEKS, MAX(F.STANDUNITPRICE) UNITPRICE, COUNT(DISTINCT C.BRANCHCODE) SHOPS , SUM(B.QUANTITY) QUANTITY, SUM(B.AMT) AMT, CASE WHEN SUM(B.QUANTITY)*MAX(F.STANDUNITPRICE)=0 THEN 0 ELSE ROUND(SUM(B.AMT)/(SUM(B.QUANTITY)*MAX(F.STANDUNITPRICE)), 2) END DISCOUNT FROM YS_SALE A INNER JOIN YS_SALEDETAIL B ON A.SALEID=B.SALEID INNER JOIN YS_BRANCH C ON A.BRANCHID=C.BRANCHID INNER JOIN YS_CLIENT D ON C.KEEPERCLIENTID=D.CLIENTID INNER JOIN YS_GOODS E ON B.BARCODE=E.BARCODE INNER JOIN YS_GOODSSTYLE F ON E.STYLEID=F.STYLEID INNER JOIN YS_GOODSSTYLESET H ON F.STYLESETID=H.STYLESETID INNER JOIN YS_GOODSSTYLECATEGORY J ON J.STYLECATEGORYID=F.STYLECATEGORYID INNER JOIN YS_GOODSSTYLESORT K ON K.STYLESORTID=F.STYLESORTID INNER JOIN YS_AGENT M ON M.MANAGENTCLIENTID=D.MANAGENTCLIENTID WHERE A.WORKDATE BETWEEN TO_DATE(:B1 , 'yyyy-mm-dd') AND TO_DATE(:B2 , 'yyyy-mm-dd') AND A.SALETYPE<>1 AND SUBSTR(F.NAME, 2, 2)='31' AND LENGTH(F.NAME)>8 AND A.BRANCHID NOT IN(SELECT BRANCHID FROM YS_BRANCH19) GROUP BY D.MANAGENTCLIENTID, M.CLIENTDESC, F.NAME, F.DESCRIPTION, H.DESCRIPTION, J.DESCRIPTION, K.DESCRIPTION , FLOOR((A.WORKDATE -TO_DATE(:B1 , 'yyyy-mm-dd'))/7) ) SELECT CLIENTDESC, STYLENO, STYLESET, STYLECATEGORY, STYLEDESC, STYLESORT, MAX(UNITPRICE) , MAX(SHOPS) , SUM(QUANTITY) "ÀÛ»ýÊýÁ¿", SUM(AMT) "ÀÛ»ý½ð¶î" , CASE WHEN SUM(QUANTITY)*MAX(UNITPRICE)=0 THEN 0 ELSE ROUND(SUM(AMT)/(SUM(QUANTITY)*MAX(UNITPRICE)), 2) END "ÕÛ¿Û" , SUM(CASE WHEN WEEKS=0 THEN QUANTITY END) "1sl", SUM(CASE WHEN WEEKS=0 THEN AMT END) "1je", SUM(CASE WHEN WEEKS=0 THEN DISCOUNT END) "1zk" , SUM(CASE WHEN WEEKS=1 THEN QUANTITY END) "2sl", SUM(CASE WHEN WEEKS=1 THEN AMT END) "2je", SUM(CASE WHEN WEEKS=1 THEN DISCOUNT END) "2zk" , SUM(CASE WHEN WEEKS=2 THEN QUANTITY END) "3sl", SUM(CASE WHEN WEEKS=2 THEN AMT END) "3je", SUM(CASE WHEN WEEKS=2 THEN DISCOUNT END) "3zk" , SUM(CASE WHEN WEEKS=3 THEN QUANTITY END) "4sl", SUM(CASE WHEN WEEKS=3 THEN AMT END) "4je", SUM(CASE WHEN WEEKS=3 THEN DISCOUNT END) "4zk" , SUM(CASE WHEN WEEKS=4 THEN QUANTITY END) "5sl", SUM(CASE WHEN WEEKS=4 THEN AMT END) "5je", SUM(CASE WHEN WEEKS=4 THEN DISCOUNT END) "5zk" , SUM(CASE WHEN WEEKS=5 THEN QUANTITY END) "6sl", SUM(CASE WHEN WEEKS=5 THEN AMT END) "6je", SUM(CASE WHEN WEEKS=5 THEN DISCOUNT END) "6zk" , SUM(CASE WHEN WEEKS=6 THEN QUANTITY END) "7sl", SUM(CASE WHEN WEEKS=6 THEN AMT END) "7je", SUM(CASE WHEN WEEKS=6 THEN DISCOUNT END) "7zk" , SUM(CASE WHEN WEEKS=7 THEN QUANTITY END) "8sl", SUM(CASE WHEN WEEKS=7 THEN AMT END) "8 je", SUM(CASE WHEN WEEKS=7 THEN DISCOUNT END) "8zk" , SUM(CASE WHEN WEEKS=8 THEN QUANTITY END) "9sl", SUM(CASE WHEN WEEKS=8 THEN AMT END) "9je", SUM(CASE WHEN WEEKS=8 THEN DISCOUNT END) "9zk" , SUM(CASE WHEN WEEKS=9 THEN QUANTITY END) "10sl", SUM(CASE WHEN WEEKS=9 THEN AMT END) "10je", SUM(CASE WHEN WEEKS=9 THEN DISCOUNT END) "10zk" , SUM(CASE WHEN WEEKS=10 THEN QUANTITY END) "11sl", SUM(CASE WHEN WEEKS=10 THEN AMT END) "11je", SUM(CASE WHEN WEEKS=10 THEN DISCOUNT END) "11zk" , SUM(CASE WHEN WEEKS=11 THEN QUANTITY END) "12sl", SUM(CASE WHEN WEEKS=11 THEN AMT END) "12je" , SUM(CASE WHEN WEEKS=11 THEN DISCOUNT END) "12zk" , SUM(CASE WHEN WEEKS=12 THEN QUANTITY END) "13sl", SUM(CASE WHEN WEEKS=12 THEN AMT END) "13je", SUM(CASE WHEN WEEKS=12 THEN DISCOUNT END) "13zk" , SUM(CASE WHEN WEEKS=13 THEN QUANTITY END) "14sl", SUM(CASE WHEN WEEKS=13 THEN AMT END) "14je", SUM(CASE WHEN WEEKS=13 THEN DISCOUNT END) "14zk" , SUM(CASE WHEN WEEKS=14 THEN QUANTITY END) "15sl", SUM(CASE WHEN WEEKS=14 THEN AMT END) "15je", SUM(CASE WHEN WEEKS=14 THEN DISCOUNT END) "15zk" FROM TB GROUP BY CLIENTID, CLIENTDESC, STYLENO, STYLESET, STYLECATEGORY, STYLEDESC, STYLESORT ORDER BY CLIENTID ASC, STYLENO ASC
6cp74g22fzahf	WITH ash as (SELECT ash.event_id, sum(decode(ash.session_type, 1, 1, 0)) as cnt_fg, count(*) as cnt_total FROM WRH$_ACTIVE_SESSION_HISTORY ash WHERE ash.dbid = :dbid AND ash.instance_number = :inst AND ash.snap_id > :bid AND ash.snap_id <= :eid AND ash.wait_time = 0 GROUP BY ash.event_id ), evts as (SELECT event_id, sum(w_cnt) as w_cnt, sum(to_cnt) as to_cnt, sum(w_time) as w_time , sum(w_cnt_fg) as w_cnt_fg, sum(to_cnt_fg) as to_cnt_fg, sum(w_time_fg) as w_time_fg FROM (SELECT es.event_id as event_id, es.total_waits - nvl(bs.total_waits, 0) as w_cnt, es.TOTAL_TIMEOUTS - nvl(bs.TOTAL_TIMEOUTS, 0) as to_cnt, es.TIME_WAITED_MICRO - nvl(bs.TIME_WAITED_MICRO, 0) as w_time , es.total_waits_fg - nvl(bs.total_waits_fg, 0) as w_cnt_fg, es.TOTAL_TIMEOUTS_FG - nvl(bs.TOTAL_TIMEOUTS_FG, 0) as to_cnt_fg, es.TIME_WAITED_MICRO_FG - nvl(bs.TIME_WAITED_MICRO_FG, 0) as w_time_fg FROM WRH$_SYSTEM_EVENT es , WRH$_SYSTEM_EVENT bs WHERE es.dbid = :dbid AND es.instance_number = :inst AND es.snap_id = :eid AND bs.dbid(+) = :dbid AND bs.instance_number(+) = es.instance_number AND bs.snap_id(+) = :bid AND es.instance_number = bs.instance_number(+) AND es.event_id = bs.event_id(+) UNION ALL SELECT x.event_hash as event_id, 0 as w_cnt, 0 as to_cnt, 0 as w_time , 0 as w_cnt_fg, 0 as to_cnt_fg, 0 as w_time_fg FROM X$KEHEVTMAP x ) GROUP BY event_id ), awr_names as (SELECT e.event_id, e.event_name, e.wait_class_id, e.param eter1, e.parameter2, e.parameter3 FROM WRH$_EVENT_NAME e WHERE e.dbid = :dbid ), named_events as (SELECT names.event_id as event_id, names.event_name as event_name, evts.w_cnt as w_cnt, evts.to_cnt as to_cnt, evts.w_time as w_time, evts.w_cnt_fg as w_cnt_fg, evts.to_cnt_fg as to_cnt_fg, evts.w_time_fg as w_time_fg, names.wait_class_id as wait_class_id, names.parameter1 as p1, names.parameter2 as p2, names.parameter3 as p3 FROM evts, awr_names names WHERE evts.event_id = names.event_id AND names.wait_class_id <> 2723168908 ), data as (SELECT e.event_id as event_id, e.event_name as event_name, e.wait_class_id as wait_class_id, e.p1 as p1, e.p2 as p2, e.p3 as p3, e.w_cnt as total_w_cnt, e.to_cnt as total_to_cnt, e.w_time as total_w_time, e.w_cnt_fg as fg_w_cnt, e.to_cnt_fg as fg_to_cnt, e.w_time_fg as fg_w_time, nvl(a.cnt_total, 0) as total_ash_cnt, nvl(a.cnt_fg, 0) as fg_ash_cnt FROM named_events e, ash a WHERE e.event_id = a.event_id(+) ), final_query as (SELECT d.event_name, d.event_id, d.p1 as p1, case when (p1 is null OR p1 = '0') then 0 else 1 end as p1valid, d.p2 as p2, case when (p2 is null OR p2 = '0') then 0 else 1 end as p2valid, d.p3 as p3, case when (p3 is null OR p3 = '0') then 0 else 1 end as p3valid, nvl(e.keh_id, 0) as keh_evt_id, c.class# as class_num, d.wait_class_id as wait_class_id, c.keh_id as keh_ecl_id, round(d.total_ash_cnt, 0) as total_ash_cnt , round(d.fg_ash_cnt, 0) as fg_ash_cnt, round(d.fg_w_cnt, 0) as fg_w_cnt, round(d.fg_to_cnt, 0) as fg_to_cnt, round(d.fg_w_time, 0) as fg_w_time, round(d.total_w_cnt, 0) as total_w_cnt, round(d.total_to_cnt, 0) as total_to_cnt, round(d.total_w_time, 0) as total_w_time from data d, X$KEHEVTMAP e , X$KEHECLMAP c where d.event_id = e.event_hash(+) and d.wait_class_id = c.class_hash ) SELECT * FROM final_query WHERE keh_evt_id > 0 OR total_w_time > 0 ORDER BY wait_class_id, fg_w_time DESC, event_id
6wm3n4d7bnddg	SELECT source, (case when time_secs < 1 then 1 else time_secs end) as time_secs, operation FROM ( SELECT 1 as source, trunc((sysdate - cast(ll.log_date as date)) * 86400) as time_secs, decode(ll.operation, 'OPEN', 0 , 1 ) as operation, ll.log_id as log_id FROM DBA_SCHEDULER_WINDOW_LOG ll , ( SELECT max(l.log_id) as max_log_id FROM DBA_SCHEDULER_WINDOW_LOG l , DBA_SCHEDULER_WINGROUP_MEMBERS m WHERE l.window_name = m.window_name AND m.window_group_name = 'MAINTENANCE_WINDOW_GROUP' AND l.operation in ('OPEN', 'CLOSE') AND CAST(l.log_date AS DATE) < ( SELECT cast(min(end_interval_time) as date) as btime FROM WRM$_SNAPSHOT bsi WHERE bsi.dbid = :dbid AND bsi.instance_number = :inst AND bsi.snap_id = :bid ) ) max_log WHERE ll.log_id = max_log.max_log_id UNION ALL SELECT 2 as source, trunc((sysdate - cast(l.log_date as date)) * 86400) as time_secs, decode(l.operation, 'OPEN', 0 , 1 ) as operation, l.log_id as log_id FROM DBA_SCHEDULER_WINDOW_LOG l , DBA_SCHEDULER_WINGROUP_MEMBERS m WHERE l.window_name = m.window_name AND m.window_group_name = 'MAINTENANCE_WINDOW_GROUP' AND l.operation in ('OPEN', 'CLOSE') AND CAST(l.log_date AS DATE) <= ( SELECT cast(max(end_interval_time) as date) as etime FROM WRM$_SNAPSHOT esi WHERE esi.dbid = :dbid AND esi.instance_number = :inst AND esi.snap_id = :eid ) AND CAST(l.log_date AS DATE) >= ( SELECT cast(min(end_interval_time) as date) as btime FROM WRM$_SNAPSHOT bsi WHERE bsi.dbid = :dbid AND bsi.instance_number = :inst AND bsi.snap_id = :bid ) UNION ALL SELECT 0 as source, trunc((sysdate - et.etime) * 86400) as time_secs, 3 as operation, 0 as log_id FROM ( SELECT cast(max(end_interval_time) as date) as etime FROM WRM$_SNAPSHOT esi WHERE esi.dbid = :dbid AND esi.instance_number = :inst AND esi.snap_id = :eid ) et UNION ALL SELECT 0 as source, trunc((sysdate - bt.btime) * 86400) as time_secs, 2 as operation, 0 as log_id FROM ( SELECT cast(min(end_interval_time) as date) as btime FROM WRM$_SNAPSHOT bsi WHERE bsi.dbid = :dbid AND bsi.instance_number = :inst AND bsi.snap_id = :bid ) bt ) WHERE time_secs >= 0 ORDER B Y source ASC, time_secs DESC, log_id ASC
6xvp6nxs4a9n4	select nvl(sum(space), 0) from recyclebin$ where ts# = :1
84qubbrsr0kfn	insert into wrh$_latch (snap_id, dbid, instance_number, latch_hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, sleep2, sleep3, sleep4, wait_time) select :snap_id, :dbid, :instance_number, hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, sleep2, sleep3, sleep4, wait_time from v$latch order by hash
8f1sjvfxuup9w	select t.*, o.*, m.comments from sys.all_tables t, sys.all_tab_comments m, sys.all_objects o where t.owner = :object_owner and t.table_name = :object_name and m.owner (+) = :object_owner and m.table_name (+) = :object_name and o.owner (+) = :object_owner and o.object_name (+) = :object_name and :object_type is not null and :sub_object is null
8wutkrpf8j81q	select max(procedure#) from procedurec$ where obj#=:1
91k4w10zgrg4m	select object_name, object_type from sys.all_objects o where o.owner = :schema and o.object_type in ('TABLE', 'VIEW', 'PACKAGE', 'TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')
971vpk0tp7ahm	select procedure#, entrypoint# from procedurec$ where obj#=:1 order by procedure#
9wncfacx0nj9h	insert into smon_scn_time (thread, time_mp, time_dp, scn, scn_wrp, scn_bas, num_mappings, tim_scn_map) values (0, :1, :2, :3, :4, :5, :6, :7)
ab3swhv5g138y	select OBJOID, CLSOID, RUNTIME, PRI, JOBTYPE, SCHLIM, WT, INST, RUNNOW, ENQ_SCHLIM from ( select a.obj# OBJOID, a.class_oid CLSOID, decode(bitand(a.flags, 16384), 0, a.next_run_date, a.last_enabled_time) RUNTIME, (2*a.priority + decode(bitand(a.job_status, 4), 0, 0, decode(a.running_instance, :1, -1, 1))) PRI, 1 JOBTYPE, decode(a.schedule_limit, NULL, decode(bitand(a.flags, 4194304), 4194304, p.schedule_limit, NULL), a.schedule_limit) SCHLIM, a.job_weight WT, decode(a.running_instance, NULL, 0, a.running_instance) INST, decode(bitand(a.flags, 16384), 0, 0, 1) RUNNOW, decode(bitand(a.job_status, 8388608), 0, 0, 1) ENQ_SCHLIM from sys.scheduler$_job a, sys.scheduler$_program p, v$database v, v$instance i where a.program_oid = p.obj#(+) and bitand(a.job_status, 515) = 1 and bitand(a.flags, 1048576) = 0 and ((bitand(a.flags, 134217728 + 268435456) = 0) or (bitand(a.job_status, 1024) <> 0)) and bitand(a.flags, 4096) = 0 and (a.next_run_date <= :2 or bitand(a.flags, 16384) <> 0) and a.instance_id is null and (a.class_oid is null or (a.class_oid is not null and a.class_oid in (select b.obj# from sys.scheduler$_class b where b.affinity is null))) and (a.database_role = v.database_role or (a.database_role is null and v.database_role = 'PRIMARY' )) and ( i.logins = 'ALLOWED' or bitand(a.flags, 17179869184) <> 0 ) union all select l.obj#, l.class_oid, decode(bitand(l.flags, 16384), 0, l.next_run_date, l.last_enabled_time), (2*decode(bitand(l.flags, 8589934592), 0, q.priority, pj.priority) + decode(bitand(l.job_status, 4), 0, 0, decode(l.running_instance, :3, -1, 1))), 1, decode(bitand(l.flags, 8589934592), 0, q.schedule_limit, decode(pj.schedule_limit, NULL, q.schedule_limit, pj.schedule_limit)), decode(bitand(l.flags, 8589934592), 0, q.job_weight, pj.job_weight), decode(l.running_instance, NULL, 0, l.running_instance), decode(bitand(l.flags, 16384), 0, 0, 1), decode(bitand(l.job_status, 8388608), 0, 0, 1) from sys.scheduler$_lightweight_job l, sys.scheduler$_program q, (select sl.obj# obj#, decode(bitand(sl.flags, 8589934592), 0, sl.program_oid, spj.program_oid) program_oid, decode(bitand(sl.flags, 8589934592), 0, NULL, spj.priority) priority, decode(bitand(sl.flags, 8589934592), 0, NULL, spj.job_weight) job_weight, decode(bitand(sl.flags, 8589934592), 0, NULL, spj.schedule_limit) schedule_limit from sys.scheduler$_lightweight_job sl, scheduler$_job spj where sl.program_oid = spj.obj#(+)) pj , v$instance i where pj.obj# = l.ob j# and pj.program_oid = q.obj#(+) and (:4 = 0 or l.running_instance = :5) and bitand(l.job_status, 515) = 1 and ((bitand(l.flags, 134217728 + 268435456) = 0) or (bitand(l.job_status, 1024) <> 0)) and bitand(l.flags, 4096) = 0 and (l.next_run_date <= :6 or bitand(l.flags, 16384) <> 0) and l.instance_id is null and (l.class_oid is null or (l.class_oid is not null and l.class_oid in (select w.obj# from sys.scheduler$_class w where w.affinity is null))) and ( i.logins = 'ALLOWED' or bitand(l.flags, 17179869184) <> 0 ) union all select c.obj#, 0, c.next_start_date, 0, 2, c.duration, 1, 0, 0, 0 from sys.scheduler$_window c , v$instance i where bitand(c.flags, 1) <> 0 and bitand(c.flags, 2) = 0 and bitand(c.flags, 64) = 0 and c.next_start_date <= :7 and i.logins = 'ALLOWED' union all select d.obj#, 0, d.next_start_date + d.duration, 0, 4, numtodsinterval(0, 'minute'), 1, 0, 0, 0 from sys.scheduler$_window d , v$instance i where bitand(d.flags, 1) <> 0 and bitand(d.flags, 2) = 0 and bitand(d.flags, 64) = 0 and d.next_start_date <= :8 and i.logins = 'ALLOWED' union all select f.obj#, 0, e.attr_tstamp, 0, decode(bitand(e.flags, 131072), 0, 2, 3), e.attr_intv, 1, 0, 0, 0 from sys.scheduler$_global_attribute e, sys.obj$ f, sys.obj$ g, v$instance i where e.obj# = g.obj# and g.name = 'CURRENT_OPEN_WINDOW' and e.value = f.name and f.type# = 69 and e.attr_tstamp is not null and e.attr_intv is not null and i.logins = 'ALLOWED' union all select i.obj#, 0, h.attr_tstamp + h.attr_intv, 0, decode(bitand(h.flags, 131072), 0, 4, 5), numtodsinterval(0, 'minute'), 1, 0, 0, 0 from sys.scheduler$_global_attribute h, sys.obj$ i, sys.obj$ j, v$instance ik where h.obj# = j.obj# and j.name = 'CURRENT_OPEN_WINDOW' and h.value = i.name and i.type# = 69 and h.attr_tstamp is not null and h.attr_intv is not null and ik.logins = 'ALLOWED') order by RUNTIME, JOBTYPE, CLSOID, PRI, WT DESC, OBJOID
aykvshm7zsabd	select size_for_estimate, size_factor * 100 f, estd_physical_read_time, estd_physical_reads from v$db_cache_advice where id = '3'
bd502nbh9abbs	select s.synonym_name object_name, o.object_type from all_synonyms s, sys.all_objects o where s.owner in ('PUBLIC', :schema) and o.owner = s.table_owner and o.object_name = s.table_name and o.object_type in ('TABLE', 'VIEW', 'PACKAGE', 'TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')
bm2pwrpcr8ru6	select sga_size s, sga_size_factor * 100 f, estd_db_time t, estd_physical_reads p from v$sga_target_advice order by sga_size
bn4b3vjw2mj3u	SELECT OBJOID, CLSOID, DECODE(BITAND(FLAGS, 16384), 0, RUNTIME, LETIME), (2*PRI + DECODE(BITAND(STATUS, 4), 0, 0, decode(INST, :1, -1, 1))), JOBTYPE, SCHLIM, WT, INST, RUNNOW, ENQ_SCHLIM, INST_ID FROM ( select a.obj# OBJOID, a.class_oid CLSOID, a.next_run_date RUNTIME, a.last_enabled_time LETIME, a.flags FLAGS, a.job_status STATUS, 1 JOBTYPE, a.priority PRI, decode(a.schedule_limit, NULL, decode(bitand(a.flags, 4194304), 4194304, b.schedule_limit, NULL), a.schedule_limit) SCHLIM, a.job_weight WT, decode(a.running_instance, NULL, 0, a.running_instance) INST, decode(bitand(a.flags, 16384), 0, 0, 1) RUNNOW, decode(bitand(a.job_status, 8388608), 0, 0, 1) ENQ_SCHLIM, a.instance_id INST_ID from sys.scheduler$_job a, sys.scheduler$_program b, v$database v , v$instance i where a.program_oid = b.obj#(+) and (a.database_role = v.database_role or (a.database_role is null and v.database_role = 'PRIMARY')) and ( i.logins = 'ALLOWED' or bitand(a.flags, 17179869184) <> 0 ) union all select c.obj#, c.class_oid, c.next_run_date, c.last_enabled_time, c.flags, c.job_status, 1, decode(bitand(c.flags, 8589934592), 0, d.priority, pj.priority), decode(bitand(c.flags, 8589934592), 0, d.schedule_limit, decode(pj.s chedule_limit, NULL, d.schedule_limit, pj.schedule_limit)), decode(bitand(c.flags, 8589934592), 0, d.job_weight, pj.job_weight), decode(c.running_instance, NULL, 0, c.running_instance), decode(bitand(c.flags, 16384), 0, 0, 1) RUNNOW, decode(bitand(c.job_status, 8388608), 0, 0, 1) ENQ_SCHLIM, c.instance_id INST_ID from sys.scheduler$_lightweight_job c, sys.scheduler$_program d, (select sl.obj# obj#, decode(bitand(sl.flags, 8589934592), 0, sl.program_oid, spj.program_oid) program_oid, decode(bitand(sl.flags, 8589934592), 0, NULL, spj.priority) priority, decode(bitand(sl.flags, 8589934592), 0, NULL, spj.job_weight) job_weight, decode(bitand(sl.flags, 8589934592), 0, NULL, spj.schedule_limit) schedule_limit from sys.scheduler$_lightweight_job sl, scheduler$_job spj where sl.program_oid = spj.obj#(+)) pj, v$instance i where pj.obj# = c.obj# and pj.program_oid = d.obj#(+) and ( i.logins = 'ALLOWED' or bitand(c.flags, 17179869184) <> 0 ) and (:2 = 0 or c.running_instance = :3)) WHERE BITAND(STATUS, 515) = 1 AND BITAND(FLAGS, 1048576) = 0 AND ((BITAND(FLAGS, 134217728 + 268435456) = 0) OR (BITAND(STATUS, 1024) <> 0)) AND BITAND(FLAGS, 4096) = 0 AND (RUNTIME <= :4 OR BITAND(FLAGS, 16384) <> 0) and ((CLSOID is not null and INST_ID is null and CLSOID in (select e.obj# from sys.scheduler$_class e where bitand(e.flags, :5) <> 0 and lower(e.affinity) = lower(:6))) or (INST_ID is not null and INST_ID = :7)) ORDER BY 3, 2, 4, 7 DESC, 1
bqpnkpzk4bt2y	declare c integer := 0; p1 varchar2(500); p2 varchar2(500); expr varchar2(500); dblink varchar2(500); part1_type integer; object_number integer; dp integer; begin :object_type := null; :object_owner := null; :object_name := null; :sub_object := null; expr := :part1; if :part2 is not null then expr := expr || '.' || :part2; end if; if :part3 is not null then expr := expr || '.' || :part3; end if; loop begin sys.dbms_utility.name_resolve(name => expr, context => c, schema => :object_owner, part1 => p1, part2 => p2, dblink => dblink, part1_type => part1_type, object_number => object_number); if part1_type = 1 then :object_type := 'INDEX'; end if; if part1_type = 2 then :object_type := 'TABLE'; end if; if part1_type = 4 then :object_type := 'VIEW'; end if; if part1_type = 5 then :object_type := 'SYNONYM'; end if; if part1_type = 6 then :object_type := 'SEQUENCE'; end if; if part1_type = 7 then :object_type := 'PROCEDURE'; end if; if part1_type = 8 then :object_type := 'FUNCTION'; end if; if part1_type = 9 then :object_type := 'PACKAGE'; end if; if part1_type = 12 then :object_type := 'TRIGGER'; end if; if part1_type = 13 then :object_type := 'TYPE'; end if; if part1_type = 28 then :object_type := 'JAVA SOURCE'; end if; if part1_type = 29 then :object_type := 'JAVA CLASS'; end if; if :object_type is null then select object_type into :object_type from sys.all_objects where object_id = object_number; end if; exception when others then null; end; c := c + 1; if c > 9 then dp := instr(expr, '.', -1); if dp > 0 then if :sub_object is not null then :sub_object := '.' || :sub_object; end if; :sub_object := upper(substr(expr, dp + 1)) || :sub_object; expr := substr(expr, 1, dp - 1); c := 0; end if; end if; exit when (:object_type is not null) or (c > 9); end loop; if :object_type is not null then if p1 is null then :object_name := p2; elsif p2 is null then :object_name := p1; if :object_name = :part1 and :part2 is not null then :sub_object := :part2; end if; if :object_name = :part2 and :part3 is not null then :sub_object := :part3; end if; else :object_name := p1; :sub_object := p2; end if; return; end if; begin if :part2 is null and :part3 is null then select 'USER', null, :part1 into :object_type, :object_owner, :object_name from sys.all_users u where u.username = :part1 and rown um = 1; return; end if; exception when no_data_found then null; end; begin if :part2 is null and :part3 is null and :deep != 0 then select 'ROLE', null, :part1 into :object_type, :object_owner, :object_name from sys.session_roles r where r.role = :part1 and rownum = 1; return; end if; exception when no_data_found then null; end; if :deep != 0 then begin if :part2 is null then select constraint_type, owner, constraint_name into :object_type, :object_owner, :object_name from sys.all_constraints c where c.constraint_name = :part1 and c.owner = :cur_schema and rownum = 1; else select constraint_type, owner, constraint_name, :part3 into :object_type, :object_owner, :object_name, :sub_object from sys.all_constraints c where c.constraint_name = :part2 and c.owner = :part1 and rownum = 1; end if; if :object_type = 'P' then :object_type := 'PRIMARY KEY'; end if; if :object_type = 'U' then :object_type := 'UNIQUE KEY'; end if; if :object_type = 'R' then :object_type := 'FOREIGN KEY'; end if; if :object_type = 'C' then :object_type := 'CHECK CONSTRAINT'; end if; return; exception when no_data_found then null; end; end if; end;
bsa0wjtftg3uw	select file# from file$ where ts#=:1
bunssq950snhf	insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbid, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS from v$sga_target_advice
c5za69x5y4p34	begin -- Call the procedure proc_sale_week(rq1 => :rq1, rq2 => :rq2, sp_result => :sp_result); end;
chsyr0gssbuqf	select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
cm5vu20fhtnq1	select /*+ connect_by_filtering */ privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
csnp95dz2r8ss	select file#, block# from recyclebin$ where ts# = :1 and file# != 0 and block# != 0 and space = 0
d83wuwxj37spw	select m.clientdesc clientdesc, e.styleno, e.colordesc, sum(b.quantity) quantity, sum(b.amt) amt from ys_sale a inner join ys_saledetail b on a.saleid=b.saleid inner join ys_branch c on a.branchid=c.branchid inner join ys_client D on c.KEEPERCLIENTID=D.CLIENTID inner join product_inf e on e.productid=b.productid inner join ys_agent m on m.managentclientid=d.managentclientid where a.workdate between to_date('2012-12-03', 'yyyy-mm-dd') and to_date('2013-03-17', 'yyyy-mm-dd') and a.saletype<>1 and substr(e.styleno, 2, 2)='31' and length(e.styleno)>8 and a.branchid not in(select branchid from ys_branch19) group by m.clientdesc, e.styleno, e.colordesc
dayq182sk41ks	insert into wrh$_memory_target_advice (snap_id, dbid, instance_number, memory_size, memory_size_factor, estd_db_time, estd_db_time_factor, version) select :snap_id, :dbid, :instance_number, memory_size, memory_size_factor, estd_db_time, estd_db_time_factor, version from v$memory_target_advice
dcstr36r0vz0d	select procedure#, procedurename, properties, itypeobj# from procedureinfo$ where obj#=:1 order by procedurename desc, overload# desc
f80h0xb1qvbsk	SELECT sys.wri$_adv_seq_msggroup.nextval FROM dual
fdjtrdaz8hpvg	INSERT /*+ APPEND LEADING(@"SEL$F5BB74E1" "H"@"SEL$2" "A"@"SEL$1") USE_NL(@"SEL$F5BB74E1" "A"@"SEL$1") */ INTO WRH$_ACTIVE_SESSION_HISTORY ( snap_id, dbid, instance_number, sample_id, sample_time , session_id, session_serial#, session_type , flags , user_id , sql_id, sql_child_number, sql_opcode, force_matching_signature , top_level_sql_id, top_level_sql_opcode , sql_plan_hash_value, sql_plan_line_id , sql_plan_operation#, sql_plan_options# , sql_exec_id, sql_exec_start , plsql_entry_object_id, plsql_entry_subprogram_id , plsql_object_id, plsql_subprogram_id , qc_instance_id, qc_session_id, qc_session_serial# , event_id, seq#, p1, p2, p3 , wait_time, time_waited , blocking_session, blocking_session_serial#, blocking_inst_id , current_obj#, current_file#, current_block#, current_row# , top_level_call#, consumer_group_id, xid, remote_instance#, time_model , service_hash, program, module, action, client_id, machine, port, ecid , tm_delta_time, tm_delta_cpu_time, tm_delta_db_time, delta_time, delta_read_io_requests, delta_write_io_requests, delta_read_io_bytes, delta_write_io_bytes, delta_interconnect_io_bytes, pga_allocated, temp_space_allocated ) (SELECT :snap_id, :dbid, :instance_number, a.sample_id, a.sample_time , a.session_id, a.session_serial#, a.session_type , decode(a.flags, 0, to_number(NULL), a. flags) , a.user_id , a.sql_id, a.sql_child_number, a.sql_opcode, a.force_matching_signature , a.top_level_sql_id, a.top_level_sql_opcode , a.sql_plan_hash_value, a.sql_plan_line_id , a.sql_plan_operation#, a.sql_plan_options# , a.sql_exec_id, a.sql_exec_start , a.plsql_entry_object_id, a.plsql_entry_subprogram_id , a.plsql_object_id, a.plsql_subprogram_id , a.qc_instance_id, a.qc_session_id, a.qc_session_serial# , a.event_id, a.seq#, a.p1, a.p2, a.p3 , a.wait_time, a.time_waited , a.blocking_session, a.blocking_session_serial#, a.blocking_inst_id , a.current_obj#, a.current_file#, a.current_block#, a.current_row# , a.top_level_call#, a.consumer_group_id, a.xid, a.remote_instance#, a.time_model , a.service_hash , substrb(a.program, 1, 64) , a.module, a.action, a.client_id, a.machine, a.port, a.ecid , decode(a.tm_delta_time, 0, to_number(null), a.tm_delta_time), decode(a.tm_delta_time, 0, to_number(null), a.tm_delta_cpu_time), decode(a.tm_delta_time, 0, to_number(null), a.tm_delta_db_time), decode(a.delta_time, 0, to_number(null), a.delta_time), decode(a.delta_time, 0, to_number(null), decode(a.delta_read_io_requests, 0, to_number(null), a.delta_read_io_requests)), decode(a.delta_time, 0, to_number(null), decode(a.delta_write_io_requests, 0, to_number(null), a.delta_write_io_requests)), decode(a.delta_time, 0, to_number(null), decode(a.delta_read_io_bytes, 0, to_number(null), a.delta_read_io_bytes)), decode(a.delta_time, 0, to_number(null), decode(a.delta_write_io_bytes, 0, to_number(null), a.delta_write_io_bytes)), decode(a.delta_time, 0, to_number(null), decode(a.delta_interconnect_io_bytes, 0, to_number(null), a.delta_interconnect_io_bytes)), decode(a.pga_allocated, 0, to_number(null), a.pga_allocated), decode(a.pga_allocated, 0, to_number(null), decode(a.temp_space_allocated, 0, to_number(null), a.temp_space_allocated)) FROM x$ash a, (SELECT h.sample_addr, h.sample_id FROM x$kewash h WHERE ( (h.sample_id >= :begin_flushing) and (h.sample_id < :latest_sample_id) ) and (h.is_awr_sample = 'Y') ) shdr WHERE shdr.sample_addr = a.sample_addr and shdr.sample_id = a.sample_id and a.need_awr_sample = 'Y')
fnk7155mk2jq6	insert into wrh$_sysmetric_history (snap_id, dbid, instance_number, begin_time, end_time, intsize, group_id, metric_id, value) select :snap_id, :dbid, :instance_number, begtime, endtime, intsize_csec, groupid, metricid, value from x$kewmdrmv order by groupid, metricid, begtime
fsbqktj5vw6n9	select next_run_date, obj#, run_job, sch_job from (select decode(bitand(a.flags, 16384), 0, a.next_run_date, a.last_enabled_time) next_run_date, a.obj# obj#, decode(bitand(a.flags, 16384), 0, 0, 1) run_job, a.sch_job sch_job from (select p.obj# obj#, p.flags flags, p.next_run_date next_run_date, p.job_status job_status, p.class_oid class_oid, p.last_enabled_time last_enabled_time, p.instance_id instance_id, 1 sch_job from sys.scheduler$_job p where bitand(p.job_status, 3) = 1 and ((bitand(p.flags, 134217728 + 268435456) = 0) or (bitand(p.job_status, 1024) <> 0)) and bitand(p.flags, 4096) = 0 and p.instance_id is NULL and (p.class_oid is null or (p.class_oid is not null and p.class_oid in (select b.obj# from sys.scheduler$_class b where b.affinity is null))) UNION ALL select q.obj#, q.flags, q.next_run_date, q.job_status, q.class_oid, q.last_enabled_time, q.instance_id, 1 from sys.scheduler$_lightweight_job q where bitand(q.job_status, 3) = 1 and ((bitand(q.flags, 134217728 + 268435456) = 0) or (bitand(q.job_status, 1024) <> 0)) and bitand(q.flags, 4096) = 0 and q.instance_id is NULL and (q.class_oid is null or (q.class_oid is not null and q.class_oid in (select c.obj# from sys.scheduler$_class c where c.affinity is null))) UNION ALL select j.job, 0, from_tz(ca st(j.next_date as timestamp), to_char(systimestamp, 'TZH:TZM')), 1, NULL, from_tz(cast(j.next_date as timestamp), to_char(systimestamp, 'TZH:TZM')), NULL, 0 from sys.job$ j where (j.field1 is null or j.field1 = 0) and j.this_date is null) a order by 1) where rownum = 1
fvkynu9xgck8h	select PGA_TARGET_FOR_ESTIMATE s, PGA_TARGET_FACTOR * 100 f, ESTD_TIME t, BYTES_PROCESSED b from v$pga_target_advice order by PGA_TARGET_FOR_ESTIMATE
g00cj285jmgsw	update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where obj# = :objn
gpwhsyjufj328	INSERT INTO "POS"."YS_GOODS" ("GOODSID", "ISACTIVE", "BARCODE", "BRANDID", "SIZEID", "COLORID", "STYLEID", "CREATEUSERID", "CREATEDATE", "UPDATEUSERID", "UPDATEDATE", "VERSIONID", "ISSYNC") VALUES (:a0, :a1, :a2, :a3, :a4, :a5, :a6, :a7, :a8, :a9, :a10, :a11, :a12)
gvjxjrrx385ah	UPDATE "POS"."PRODUCT_INF" SET "VERSIONID" = :a1 WHERE "PRODUCTID" = :b0

Back to SQL Statistics
Back to Top

Instance Activity Statistics

• Instance Activity Stats
• Instance Activity Stats - Absolute Values
• Instance Activity Stats - Thread Activity

Instance Activity Stats

• Ordered by statistic name

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

• Statistics with absolute values (should not be diffed)

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

• Statistics identified by '(derived)' come from sources other than SYSSTAT

Back to Instance Activity Statistics
Back to Top

IO Stats

• IOStat by Function summary
• IOStat by Filetype summary
• IOStat by Function/Filetype summary
• Tablespace IO Stats
• File IO Stats

IOStat by Function summary

• 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
• ordered by (Data Read + Write) desc

Back to IO Stats
Back to Top

IOStat by Filetype summary

• 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
• Small Read and Large Read are average service times, in milliseconds
• Ordered by (Data Read + Write) desc

Back to IO Stats
Back to Top

IOStat by Function/Filetype summary

• 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
• Ordered by (Data Read + Write) desc for each function

Back to IO Stats
Back to Top

Tablespace IO Stats

• ordered by IOs (Reads + Writes) desc

Back to IO Stats
Back to Top

File IO Stats

• ordered by Tablespace, File