Oracle DBA by Example. Code samples, Scripts, Reference.: ASH Tables by Example I - Why did TEMPORARY Tablespace grew in size?

======================
General
======================
Customer complained that database size suddenly grew by 10Gb
Checking datafiles, it was found that temporary tablespace size is 15Gb!!

-rw-r----- 1 oracle dba 12G Apr 6 07:06 ora_temporary_01.dbf

TEMPORARY tablespaceis used for heavy sorts operations.

ora_temporary_01.dbf indeed grew from 2600Mb to 12000Mb.

Maybe one heavy SQL run by a user, with sort option...?

======================

Investigation
======================
First - Get historical data for tablespace size growth.

SELECT V$TABLESPACE.name
AS TABLESPACE_NAME,
(HIST_USAGE.tablespace_size*DBA_TABLESPACES.block_size)/1024/1024
AS TABLESPACE_SIZE_MB,
(HIST_USAGE.tablespace_usedsize*DBA_TABLESPACES.block_size)/1024/1024
AS USED_SIZE_MB,
HIST_USAGE.rtime
FROM DBA_HIST_TBSPC_SPACE_USAGE HIST_USAGE,
V$TABLESPACE,
DBA_TABLESPACES
WHERE HIST_USAGE.tablespace_id = V$TABLESPACE.ts#
AND DBA_TABLESPACES.tablespace_name = V$TABLESPACE.name
AND HIST_USAGE.tablespace_usedsize > 0
AND V$TABLESPACE.name = 'TEMPORARY'
ORDER BY HIST_USAGE.snap_id DESC;

TABLESPACE_NAME TABLESPACE_SIZE_MB USED_SIZE_MB RTIME
----------------- ------------------ ------------ --------------------
TEMPORARY 12000 1 04/07/2015 17:00:05
TEMPORARY 12000 15 04/07/2015 03:00:41
TEMPORARY 12000 15 04/06/2015 03:00:39
TEMPORARY 12000 1 04/05/2015 23:00:59
TEMPORARY 12000 1 04/05/2015 22:00:57
TEMPORARY 12000 1 04/05/2015 21:00:55
TEMPORARY 12000 15 04/05/2015 03:00:19
TEMPORARY 12000 4 04/04/2015 07:00:40
TEMPORARY 12000 15 04/04/2015 03:00:23
TEMPORARY 2600 15 04/02/2015 03:00:18
TEMPORARY 2600 15 04/01/2015 03:00:14
TEMPORARY 2600 15 03/31/2015 03:00:39

12 rows selected.

Check out the time when the change took place

Find Top SQLs that use 'ORDER BY' at that time period.

SELECT TOP_SESSIONS.sql_id,

TOP_SESSIONS.sql_plan_hash_value,

HIST_SQLTEXT.sql_text

FROM(

SELECT SESS_HISTORY.sql_id,

SESS_HISTORY.sql_plan_hash_value,

SUM(10) ash_secs

FROM DBA_HIST_SNAPSHOT HIST_SNAPSHOT,

DBA_HIST_ACTIVE_SESS_HISTORY SESS_HISTORY

WHERE 1=1

AND SESS_HISTORY.sample_time BETWEEN (SYSDATE-5) AND (SYSDATE-3)

and SESS_HISTORY.snap_id = HIST_SNAPSHOT.snap_id

AND SESS_HISTORY.dbid = HIST_SNAPSHOT.dbid

AND SESS_HISTORY.instance_number = HIST_SNAPSHOT.instance_number

-- AND SESS_HISTORY.module = 'MY_MODULE'

GROUP BY

SESS_HISTORY.sql_id,

SESS_HISTORY.sql_plan_hash_value

ORDER BY ash_secs DESC

)TOP_SESSIONS,

DBA_HIST_SQLTEXT HIST_SQLTEXT

WHERE TOP_SESSIONS.sql_id = HIST_SQLTEXT.sql_id

AND HIST_SQLTEXT.sql_text LIKE '%ORDER BY%'

AND ROWNUM < 30;

SQL_ID SQL_PLAN_HASH_VALUE SQL_TEXT
------------- ------------------- ----------------------------------------------------c4cya7khk8ymx 3149935409 SELECT ROWID, F.DATE_STRING, F.KEY1, F.KEY2, F.KEY3,
93y6r6ymvs29h 859439110 SELECT ROWID, F.DATE_STRING, F.KEY1, F.KEY2, F.KEY3, b5vd6xtzncv5v 305948821 MERGE /*+ dynamic_sampling(ST 4) dynamic_sampling 5czr9gg38at4a 3990208161 SELECT RECID, RECID, STAMP, THREAD#, SEQUENCE#, NAME, b7ad44vds3w05 658679650 select rowid, MODULE_NAME, VERSION_NUMBER FROM

Get the execution plan of one of the Top SQLs:

SELECT * FROM TABLE(DBMS_XPLAN.display_awr('b3hb5zg0jw3g5', 3149935409,NULL,'ADVANCED'));

And this is the output:

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID b3hb5zg0jw3g5

--------------------

SELECT ROWID, F.DATE_STRING, F.KEY1, F.KEY2, F.KEY3,

F.SCENARIO_ID, F.CAMPAIGN_ID, F.CATEGORY_ID, F.COUNTRY_ID,

F.CAMPAIGN_SENT_DATE, F.CAMPAGIN_RECD_DATE, F.TS_LAST_MODIFIED,

F.MESSAGE_TYPE, F.MESSAGE_ID, F.NETWORK_ID, F.VLR_NUMBER,

F.PARTITION_KEY_ID, F.MONTH_STRING, F.YEAR_STRING, F.MESSAGE_TEXT,

F.MSISDN FROM MY_SCHEMA.MY_TABLE F ORDER BY

10 DESC NULLS FIRST

Plan hash value: 3149935409

------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | | 3890K(100)| |

| 1 | SORT ORDER BY | | 24M| 15G| 17G| 3890K (1)| 12:58:01 |

| 2 | PARTITION RANGE ALL| | 24M| 15G| | 483K (1)| 01:36:40 |

| 3 | TABLE ACCESS FULL | FACT_ROAMER_CAMPAIGNS | 24M| 15G| | 483K (1)| 01:36:40 |

--------------------------------------------------------------------------------

==========================
To see full text from DBA_HIST_TEXT
==========================
SELECT TO_CHAR(DBMS_LOB.SUBSTR(DBA_HIST_SQLTEXT.sql_text,4000,1)) AS SQL_TEXT
FROM DBA_HIST_SQLTEXT
WHERE sql_id = '6rdwj7pktnp58';

==========================
To see execution plan from DBA_HIST_SQL_PLAN
==========================
SELECT parent_id||':'||id parent_and_id , operation, cost, ROUND(bytes/1024/1024) as Mb, io_cost, cpu_cost, TO_CHAR(timestamp,'yyyymmdd hh24:mi:ss') as TIMESTAMP
FROM DBA_HIST_SQL_PLAN
WHERE sql_id = '6rdwj7pktnp58'
ORDER BY id;

PARENT_AND_ID OPERATION COST MB IO_COST CPU_COST TIMESTAMP
-------------- ----------------- ---- ---------- ---------- ---------- -----------------
:0 SELECT STATEMENT 1624 20160101 02:08:04
0:1 PX COORDINATOR 20160101 02:08:04
1:2 PX SEND 1624 19 1615 114770591 20160101 02:08:04
2:3 SORT 1624 19 1615 114770591 20160101 02:08:04
3:4 PX RECEIVE 1622 19 1615 88213932 20160101 02:08:04
4:5 PX SEND 1622 19 1615 88213932 20160101 02:08:04
5:6 HASH JOIN 1622 19 1615 88213932 20160101 02:08:04
6:7 PX RECEIVE 2 0 2 23169 20160101 02:08:04
7:8 PX SEND 2 0 2 23169 20160101 02:08:04
8:9 PX BLOCK 2 0 2 23169 20160101 02:08:04
9:10 TABLE ACCESS 2 0 2 23169 20160101 02:08:04
6:11 PX BLOCK 1619 19 1613 79844933 20160101 02:08:04
11:12 TABLE ACCESS 1619 19 1613 79844933 20160101 02:08:04

=============================
Optional - Shrink TEMP Tablespace
=============================

Shrink the TEMP tablespace:

ALTER TABLESPACE TEMPORARY SHRINK SPACE KEEP 10000M;

ALTER TABLESPACE TEMPORARY SHRINK TEMPFILE '/software/oracle/db1/orainst/ora_temporary_01.dbf' KEEP 10000M;

Before
oracle@my_server:~>% find /oracle_db/db1 -type f -printf '%s %p\n'| sort -nr | head -10
16882081792 /oracle_db/db1/db_igt/ora_igt_table_01.dbf
15414075392 /oracle_db/db1/db_igt/ora_igt_table_02.dbf
15099502592 /oracle_db/db1/db_igt/ora_igt_table_03.dbf
12582920192 /oracle_db/db1/db_igt/ora_temporary_01.dbf
6501179392 /oracle_db/db1/db_igt/ora_igt_index_01.dbf
6291464192 /oracle_db/db1/db_igt/ora_undotbs_01.dbf
576724992 /oracle_db/db1/db_igt/ora_sysaux_01.dbf
419438592 /oracle_db/db1/db_igt/ora_system_01.dbf
104865792 /oracle_db/db1/db_igt/ora_workarea_01.dbf
104865792 /oracle_db/db1/db_igt/ora_dwh_table_01.dbf

After

oracle@my_server:~>% find /oracle_db/db1 -type f -printf '%s %p\n'| sort -nr | head -10
16882081792 /oracle_db/db1/db_igt/ora_igt_table_01.dbf
15414075392 /oracle_db/db1/db_igt/ora_igt_table_02.dbf
15099502592 /oracle_db/db1/db_igt/ora_igt_table_03.dbf
10486808576 /oracle_db/db1/db_igt/ora_temporary_01.dbf
6501179392 /oracle_db/db1/db_igt/ora_igt_index_01.dbf
6291464192 /oracle_db/db1/db_igt/ora_undotbs_01.dbf
576724992 /oracle_db/db1/db_igt/ora_sysaux_01.dbf
419438592 /oracle_db/db1/db_igt/ora_system_01.dbf
104865792 /oracle_db/db1/db_igt/ora_workarea_01.dbf
104865792 /oracle_db/db1/db_igt/ora_dwh_table_01.dbf

=============================
After Shrink TEMP Tablespace
=============================
After using ALTER TABLESPACE TEMPORARY SHRINK SPACE command, both the actual allocated space for TEMPORARY tablespace, and the max size limit are reduced.

Example:
Before
cd /oracle_db/db1/db_orainst
ls -l | grep temp

-rw-r----- 1 oracle dba 31457288192 Jan 10 11:29 ora_temporary_01.dbf

SELECT A.tablespace_name tablespace,
D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used
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;

TABLESPACE MB_TOTAL MB_USED
------------------------------- ---------- ----------
TEMPORARY 30000 1

SELECT * FROM dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- -------------
TEMPORARY 31457280000 20971520000 31455182848

SELECT FILE_NAME, TABLESPACE_NAME, ROUND(BYTES/1024/1024) AS Mb, AUTOEXTENSIBLE, ROUND(MAXBYTES/1024/1024) AS MAX_Mb, ROUND(USER_BYTES/1024/1024) AS user_bytes_Mb
FROM DBA_TEMP_FILES;

FILE_NAME
TABLESPACE_NAME MB AUTOEXTENSIB MAX_MB USER_BYTES_MB
------------------------------------------------------------
-------------------- ---------- ------------ ---------- -------------
/oracle_db/db1/db_igt/ora_temporary_01.dbf
TEMPORARY 30000 YES 20000 29999

ALTER TABLESPACE TEMPORARY SHRINK SPACE KEEP 10000M;

Tablespace altered.

After
cd /oracle_db/db1/db_orainst
ls -l | grep temp
-rw-r----- 1 oracle dba 10871635968 Jan 10 12:45 ora_temporary_01.dbf

SELECT FILE_NAME, TABLESPACE_NAME, ROUND(BYTES/1024/1024) AS Mb, AUTOEXTENSIBLE, ROUND(MAXBYTES/1024/1024) AS MAX_Mb, ROUND(USER_BYTES/1024/1024) AS user_bytes_Mb
FROM DBA_TEMP_FILES;

FILE_NAME
TABLESPACE_NAME MB AUTOEXTENSIB MAX_MB USER_BYTES_MB
------------------------------------------------------------
-------------------- ---------- ------------ ---------- -------------
/oracle_db/db1/db_igt/ora_temporary_01.dbf
TEMPORARY 10368 YES 20000 10367

SELECT A.tablespace_name tablespace,
D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used
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;

TABLESPACE MB_TOTAL MB_USED
------------------------------- ---------- ----------
TEMPORARY 10367.9922 1

SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
-------------------- --------------- --------------- -------------
TEMPORARY 10871627776 2088960 10869538816

Oracle DBA by Example. Code samples, Scripts, Reference.

Pages

Tuesday, April 7, 2015

ASH Tables by Example I - Why did TEMPORARY Tablespace grew in size?

1 comment: