How much time a rollback will take?

Metalink NoteID: 169138.1
 
select
sum(distinct(ktuxesiz)) from x$ktuxe;

Workflow load distribution - Item wise

select wi.item_type ITEM_TYPE,

wit.persistence_type P_TYPE,

decode (wi.end_date, NULL, 'OPEN', 'CLOSED') Status,

count(*) COUNT

from wf_items wi,

wf_item_types wit

where wit.name = wi.item_type

group by item_type,

wit.persistence_type,

WIT.PERSISTENCE_DAYS,

decode (wi.end_date, NULL, 'OPEN', 'CLOSED')

order by decode (wi.end_date, NULL, 'OPEN', 'CLOSED'), 4 desc

Trick to make query run fast

There may be some actions that need full table scans and takes more time to execute.
Following are certatin such actions:
- Index creation
- Select count(*) from a table ; to get the number of rows
- Gather table statistics
 
To speed up the above queries we need to set the following session parameters and then execute the required action
 

alter session set sort_area_size=536870912;

alter session set sort_multiblock_read_count=128;

alter session set db_file_multiblock_read_count=128;

 

 

TOP I/O Consuming sessions

Query to find the top I/O consuming sessions:

select
sid,
username,
round(100 * total_user_io/total_io,2) tot_io_pct
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
group by
b.sid, nvl(b.username,p.name)),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
order by
3 desc;

Courtesy : Malli

Export and Import Table stats between databases

STEPS
Step1: Create a table to store the stats.
Step 2: Export the stats from the required table to the table created in step1.
Step3: Export the table using either exp or expdp from database 1
Step4: Import the table that contain stats into database2
Step5: Use dbms_stats to import the stats into database2
COMMANDS
exec dbms_stats.create_stat_table('APPS','OKC_K_ITEMS_TABLE_STATS')
exec dbms_stats.export_table_stats('OKC','OKC_K_ITEMS',null,'OKC_K_ITEMS_TABLE_STATS',null,true,'APPS')
exp apps file=exp_crp_stats.dmp tables=OKC_K_ITEMS_TABLE_STATS
imp apps file=exp_crp_stats.dmp full=y
exec dbms_stats.import_table_stats(OKC,'OKC_K_ITEMS',null,OKC_K_ITEMS_TABLE_STATS',null,true,'APPS')
Query to find the execution plan of a session.
SELECT operation, options, object_name, DEPTH, ID, COST
FROM v$sql_plan
WHERE hash_value IN (SELECT hash_value
FROM v$sql
WHERE address IN (SELECT sql_address FROM v$session WHERE SID = &SID))
AND object_name IS NOT NULL
ORDER BY DEPTH DESC