This script will use a sql_id to retrieve the execution plan for the query.
with aa as (
SELECT DISTINCT ss.sql_id, ss.plan_hash_value
FROM DBA_HIST_SQLSTAT SS
WHERE SS.SQL_ID = ‘&sql_id’
)
SELECT b.*
from aa, table(dbms_xplan.display_awr(aa.sql_id,aa.plan_hash_value)) b
/
The sql below can be run to show who is using memory and how much memory.
select S.sid, S.USERNAME, PM.category, PM.ALLOCATED,
ROUND(PM.USED/(1024*1024),0) as USED_MB, PM.MAX_ALLOCATED, SQL_TEXT
from v$process_memory pm
inner join v$process p
on p.pid = pm.pid
inner join v$session s
on s.paddr = p.addr
inner join v$sql sq
on sq.sql_id = s.sql_id
order by PM.USED desc nulls last
/
The below script will query the database for the users running parallel queries.
SELECT case when px.qcinst_id is NUll then username
ELSE ‘ – ‘|| LOWER(SUBSTR(s.program,LENGTH(s.program)- 4, 4))
end as “User name”
, case when px.qcinst_id is NULL then ‘QC’ else ‘(Slave)’ end as “QC/Slave”
,TO_CHAR(px.server_set) “Slave Set”
,TO_CHAR(s.SID) “SID”
,case when px.qcinst_id is NULL then TO_CHAR(s.SID) else to_char(px.qcsid) end as “QC SID”
,px.req_degree “Requested DOP”
,px.DEGREE “Actual DOP”
,s.osuser “User_id”
,s.status
,s.schemaname “Schema”
FROM v$px_session px
JOIN v$session s on px.SID = s.SID
AND px.serial# = s.serial#
ORDER BY 5 ,1 DESC
The following script will calculate the space taken by tables and build the drop and truncate statements to allow you to simply whip through and clear your tablespace.
SELECT segment_name AS table_name,
SUM (BYTES) / (1024 * 1024) AS MB,
‘drop table ‘ || TABLE_NAME || ‘ purge;’ AS DROP_STATEMENT,
‘truncate table ‘ || TABLE_NAME || ‘;’ AS TRUNCATE_STATEMENT
FROM user_segments s,
user_tables t
WHERE S.SEGMENT_NAME = T.TABLE_NAME
GROUP BY segment_name, ‘drop table ‘ || TABLE_NAME || ‘ purge;’, ‘truncate table ‘ || TABLE_NAME || ‘;’
ORDER BY SUM (BYTES) DESC
/
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id
order by OWNER, OBJECT_NAME
/
SELECT SYSTIMESTAMP AS s.SNAPSHOT_TIME,
S.PARSING_SCHEMA_NAME,
S.OPTIMIZER_COST,
S.MODULE,
S.LAST_LOAD_TIME,
S.LAST_ACTIVE_TIME,
S.SQL_TEXT,
s.SQL_ID,
S.SQL_FULLTEXT
from sys.v_$sql s
where module is not null
and users_executing != 0
and optimizer_cost >= 5000000
order by OPTIMIZER_COST desc;
/
DECLARE
BEGIN
– Now call the stored program
oracle.kill_session(&sid,&serial);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SubStr(‘Error ‘||TO_CHAR(SQLCODE)||’: ‘||SQLERRM, 1, 255));
RAISE;
END;
SELECT *
FROM v$db_object_cache
WHERE type in (‘TRIGGER’,'PROCEDURE’,'PACKAGE BODY’,'PACKAGE’)
and EXECUTIONS > 0
and owner like UPPER(‘&DB_OWNER’)
ORDER BY owner, executions desc, loads desc, sharable_mem desc;
with invalids as
(
SELECT distinct
‘ALTER ‘ || case object_type when ‘PACKAGE BODY’ then ‘PACKAGE’ else object_type end || ‘ ‘ ||
OWNER || ‘.’ || OBJECT_NAME || ‘ COMPILE;’ as compile_sql,
case object_type
when ‘VIEW’ then 1
when ‘FUNCTION’ then 2
when ‘PROCEDURE’ then 3
when ‘PACKAGE’ then 4
else 5 END as display_order
from
dba_objects
where
status = ‘INVALID’
and
object_type in (‘PACKAGE’,'PACKAGE BODY’,'FUNCTION’,'PROCEDURE’,'VIEW’)
)
select compile_sql
from invalids
order by display_order
/
My client maintains a table in their database with every day of the year and a flag to say if the day is a working day, a bank holiday or a weekend (Saturday/Sunday). Every now and again somebody takes the responsibility to flag the bank holidays in the future.
The primary purpose of the table is for setting appointments for agents or giving a date of completion based on x number of working days in the future, so generally the data is only required for a reasonably limited period of time around the current date.
A few issues spring to mind:
1) Somebody or something needs to maintain this table
2) Holding data for something that could be done via an algorithm
3) Database reads whenever making a working day calculation
Point (3) can be dealt with by caching etc. in a Transaction type system, however batch processes have the potential to cause problems, in fact, my client was suffering from a batch job running of 8+ hours.
The solution provided to the client was to implement the Bank Holiday package attached to this post, enabling Bank Holiday and Next Working Day calculations without the need to perform data reads. The 8 hour batch job was reduced to seconds.
Examples for use:
1) Check if a date is a working day:
CREATE OR REPLACE FUNCTION IsWorkingDay
(FromDate IN DATE, NoofDays IN NUMBER DEFAULT 0)
RETURN VARCHAR2
AS
BEGIN
CASE
WHEN trim(to_char(fromdate + noofdays, ‘DAY’)) IN (‘SATURDAY’,'SUNDAY’)
THEN RETURN ‘FALSE’;
WHEN pkg_bank_holidays.fn_is_bank_holiday(fromdate + noofdays)
THEN RETURN ‘FALSE’;
ELSE
RETURN ‘TRUE’;
END CASE;
EXCEPTION
WHEN OTHERS THEN
RETURN ‘FALSE’;
END;
/
SQL> SELECT isWorkingDay(TO_DATE(’10-Aug-2010′,’dd-mon-yyyy’), 0) “Working Day?”
2 FROM DUAL
3 /
Working Day?
———————–
TRUE
2) Get the next or previous working day after a number of days in the future
CREATE OR REPLACE FUNCTION NextWorkingDay
( FromDate IN DATE DEFAULT SYSDATE, NoOfDays IN NUMBER DEFAULT 0 )
RETURN DATE AS
v_ReturnDate DATE ;
BEGIN
v_ReturnDate := FromDate + NoOfDays;
WHILE (IsWorkingDay(v_ReturnDate,0) = ‘FALSE’) LOOP
v_ReturnDate := v_ReturnDate + sign(NoOfDays);
END LOOP;
RETURN v_ReturnDate;
EXCEPTION
WHEN OTHERS THEN
RETURN null;
END NextWorkingDay;
/
SQL> SELECT NextWorkingDay(TO_DATE(’10-Aug-2010′,’dd-mon-yyyy’),5) “Next Work Day”
2 FROM DUAL
3 /
Next Work
———
16-AUG-10
read more