SergeyGurin

2022-01-24 17:13:57 /*
ORACLE
Advisor
*/

:::GRANT
GRANT advisor TO USER;

:::Get the results of an Automatic SQL Tuning task
SELECT DBMS_AUTO_SQLTUNE.report_auto_tuning_task FROM dual;

:::Create tuning task
DECLARE
ret_val VARCHAR2(4000);
BEGIN
ret_val := dbms_sqltune.create_tuning_task( task_name=>'XMKO1', sql_id=>'dc3sxstr12ap8');
dbms_sqltune.execute_tuning_task('XMKO1', execution_params => DBMS_ADVISOR.ARGLIST('time_limit', 60));
END;

:::Report tuning task
SELECT dbms_sqltune.report_tuning_task('XMKO1') FROM dual;

:::Drop tuning task
BEGIN
DBMS_SQLTUNE.DROP_TUNING_TASK('XMKO1');
END; Відкрити / Коментувати

2022-01-24 11:38:33 /*
ORACLE
*/

:::Blocking session
SELECT vl.sid,
vl.TYPE,
vl.lmode,
vl.ctime,
vl.block,
vs.username,
vs.lockwait,
vs.status,
vs.schemaname,
vs.osuser,
vs.machine,
vs.terminal,
vs.program,
vs.sql_id,
vs.prev_sql_id,
VS.MODULE,
O.OBJECT_NAME
FROM v$lock vl,
v$session vs,
v$locked_object l,
dba_objects o
WHERE vl.TYPE = 'TX'
AND vl.sid = vs.sid
AND vs.username = USER
AND vl.SID = L.SESSION_ID
AND l.object_id = o.object_id; Відкрити / Коментувати

2022-01-24 11:11:28 /*
ORACLE
*/

:::Query to find Database Growth:
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"
FROM (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P; Відкрити / Коментувати

2022-01-24 11:10:13 /*
ORACLE
TEMP
*/

:::Temp size:
SELECT tablespace_name, ' %free=' round(sum(free_blocks) / sum(total_blocks) * 100 ,0)
from gv$sort_segment group by tablespace_name;

:::Query to check Temp Tablespace Usage:
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

:::Temp Tablespace usage by session:
SELECT S.sid ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial; Відкрити / Коментувати

2022-01-24 11:07:09 /*
ORACLE
*/

:::Schema DB-objects size:
SELECT s.segment_name,
s.segment_type,
CASE
WHEN s.segment_type = 'LOBSEGMENT'
THEN
(SELECT l.table_name ' -> ' l.column_name
FROM dba_lobs l
WHERE l.owner = s.owner
AND l.segment_name = s.segment_name
)
ELSE ''
END lob_info,
s.tablespace_name,
SUM (ROUND (bytes / 1024 / 1024 / 1024, 1)) AS GB,
s.owner
FROM dba_segments s
WHERE s.owner = 'OWNER'
GROUP BY s.segment_name, s.segment_type, s.tablespace_name, s.owner
ORDER BY GB DESC; Відкрити / Коментувати