Pavan DBA's Blog

The DBA Knowledge Store

Archive for the ‘Admin’ Category

« Previous Entries

Grant command hanging for long time

Posted by Pavan DBA on April 2, 2017


Recently I have faced one issue regarding granting some privileges to the user.

I got a request to grant select on a table to a user. when I issued grant command, it just got hung.
Even though select doesn’t hold exclusive lock on a table, I verified in v$locked_object to check if any more information can be collected.

select object_id,session_id,oracle_username from v$locked_object;

OBJECT_ID SESSION_ID ORACLE_USERNAME
———- ———- —————
26187 18 SCOTT

select object_name,owner,object_id,object_type,status from dba_objects where object_id=26187;

OBJECT_NAME OWNER OBJECT_ID OBJECT_TYPE STATUS
———– —– ——— ———– ——
OBJ$ SYS 26187 TABLE VALID

From the above, it is evident that SCOTT user is holding a lock on SYS.OBJ$ table which actually caused the issue.

After confirmation from application team, I have killed the session with id 18. After that grant command got successful.

Hope this helps…

Posted in Admin | Tagged: , , , , | 2 Comments »

TRUNCATE not releasing space from the table

Posted by Pavan DBA on January 9, 2014


Hello Friends,

this will be my first article in 2014… so before starting with, wishing all my blog readers a VERY HAPPY & PROSPEROUS NEW YEAR 2014.

Today, I want to explain about a typical behaviour of truncate. To start with, let’s look at the basic difference between delete and truncate at storage point of view.

As most of you people already know, when we delete the rows from the table using DELETE command, the extents that are allocated to the table will not be released and still table holds them, where as when using TRUNCATE with DROP STORAGE clause (which is default), it will release the space back to tablespace.

Yesterday, I have got one requirement to truncate all tables in all schemas of a database in order to release the space to tablespace (basically client want to use that space for a new schema). So, I have proceeded and found that even after truncating all tables, i could able to release only 1 GB of space to tablespace whereas my expectation is to get 45GB. Moreover, while truncating, I have used DROP STORAGE clause also.

I tried using move table, shrink space after the truncate, but still nothing productive happened.

This is a bit surprise to me and finally, I realized things as below…

When a truncate is issued on a table, Oracle Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter. However, if the minextent (along with the initial value) is large enough, this space is NOT released even after the truncate.

For example, see a table in my case which is of 586 MB in size

CREATE TABLE XYZ
(   COL1                 VARCHAR2(45 BYTE),
COL2                 VARCHAR2(45 BYTE),
COL3                 VARCHAR2(6 BYTE) )
TABLESPACE XYZ_DATA
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
INITIAL          100M
NEXT             1M
MINEXTENTS       1
MAXEXTENTS       UNLIMITED
PCTINCREASE      0
BUFFER_POOL      DEFAULT
FLASH_CACHE      DEFAULT
CELL_FLASH_CACHE DEFAULT            )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

From the above, we can see that initial extent size itself is 100M. Suppose, if we truncate this table which is having total size as 586 MB, Oracle will de-allocate only 486 MB and will keep that 100 MB attached to the table.

Like this if you have more no.of tables, the situation where the space is not releasing to tablespace will arise.

Solutions to deal this…

1. export & import of the tables either using exp/imp or expdp/impdp 2. drop the table and create it again (anyway we planned to issue truncate, so dropping won’t make big difference)

So, beware of TRUNCATE friends… if anyone has a better solution, please leave that in comments.

HAPPY LEARNING 🙂

Posted in Admin | Tagged: , , , , | 16 Comments »

Temporary tablespace guidelines

Posted by Pavan DBA on March 21, 2013


I thought to share some points a DBA need to follow while configuring temp tablespaces

1. when ever we create a user along with default permanent tablespace, it is beneficial to specify temporary tablespace also. otherwise, oracle will use that permanent tablespace for any sort operations performed by user and this will be costlier

2. it would be better to create multiple temporary tablespaces for multiple users. for example, create 2 temporary tablespaces and assign them to 10 users so that 5 users can use one temp tablespace (numbers are just for understanding)

3. always have tempfiles spread across multiple disks

4. If you are specifying storage parameters, please make sure INITIAL and NEXT values are same. Maintaining uniform extent allocation is always better for temp tablespaces

5. when using storage parameters, please don’t specify INITIAL and NEXT values too small. This will make oracle to create hundreds of small temp segments and SMON will take quite long time (sometimes may be hours of time also) to clean up those segments.

6. If possible, try to load data into table in ascending order of indexed column. If you store data like that, you can use NOSORT clause while creating index and this will improve index creation performance.

Posted in Admin | Tagged: , , , | 3 Comments »

How and When SORTING takes palce?

Posted by Pavan DBA on March 21, 2013


I have explained about temp segments residing in permanent tablespace in my earlier post https://pavandba.com/2013/03/20/temporary-segments-in-permanent-tablespace/
Today I thought to give some details about “How sorting takes place”.
First of all what statements will cause sorting in the database? I have seen so many DBA’s assuming ORDER BY or GROUP BY clauses alone will cause sorting. This is not correct. Sorting will take place for many statements and some of them are as below
1. Index creation / rebuild
2. using ORDER BY or GROUP BY clauses in SELECT statements
3. SELECT statement with DISTINCT keyword (oracle will do sorting to avoid duplicates)
4. UNION, INTERSECTION or MINUS operations
5. sort-Merge joins
6. Table Analyze (gather stats)
7. CREATE TABLE AS SELECT
8. CREATE PRIMARY KEY constraint or enable or disable that constraint etc

So, whenever we perform above operations in the database, oracle will allocate memory from RAM. We already know that the size of that memory allocated is dependent on parameter SORT_AREA_SIZE.
When using 9i or above versions, we don’t use this parameter rather we use PGA_AGGREGATE_TARGET which automates memory allocation to SORT AREA. That means SORT AREA will reside in PGA if it is a dedicated server architecture, and will reside in user golbal area (UGA) if we use shared server architecture (Multi-threaded server). This UGA is part of shared pool.

If any sorting operation completes within this SORT AREA, then we call it as IN-MEMORY SORT. This is possible when the size of data which needs to be sorted is <= SORT AREA SIZE. For example, if sort area size is 10MB and you are sorting 8MB of data, then it can be completed within PGA.
But what happens if data size is more than sort area size?
To understand, I am assuming that we have a sort area of 20MB and the data which I need to sort is 50MB.

In this case, Oracle will first picks up 20MB of data (out of 50MB) and will sort it in PGA and will send that to temporary tablespace (this is a disk I/O because tempfile will exist on disk). Then for second term, it will take another 20MB and will do the same. In third attempt, it will take final 10MB and will send to temp tablespace.
So, to sort 50MB of data, oracle will perform 3 I/O’s (frankly, there will be more than 3 I/O’s, but just for convinience, I am restricting it to 3). We know that I/O is a bad and costly operation and it can cause performance degradation.
With this we can understand that having right size for SORT AREA will avoid performance issues caused by sorting.

HAPPY LEARNING 🙂

Posted in Admin | Tagged: , , | 7 Comments »

« Previous Entries
 
%d bloggers like this: