Dropping Dictionary Managed Tablespaces runs very slow

[mukesh_mahadik]

Hello,

We are converting DMT to LMT & drop operation on dictionary managed tablepsace of having 2TB is extermery slow. We are using Oracle 10.2.0.2 version.We are using SAP note : 646681 to convert PSAPBTAD and PSAPBTADI Tablespaces to LMT.

Appreciate your prompt help on this.

Thanks and Regards,

Mukesh Mahadik

[Former Member · Accepted solution]

Hi Mukesh,

>> We are converting DMT to LMT & drop operation on dictionary managed tablepsace of having 2TB is extermery slow. We are using Oracle 10.2.0.2 version.We are using SAP note : 646681 to convert PSAPBTAD and PSAPBTADI Tablespaces to LMT.

I assume that you are performing import/export by using BR*Tools. So you are creating a new LTMS tablespace and planning to import into the new tablespace.

In my case, for example, I completed ~700 GB export takes approximately, 9 hours. It depends to the hardware. What about your values?

In order to increase the speed, take the database to noarchivelog mode, if it is in the archivelog mode.

Dropping a tablespace shouldn't take a long time, if it is not using by the Oracle, still. Additionally, Oracle warns you if it has a handle on the datafile, belong to the TS while dropping it.

As a summary of it, this is a DB export operation and may take long time.

Best regards,

Orkun Gedik

[mukesh_mahadik · Accepted solution]

Hello Orkun,

Thank you for your update. We have around 2 TB of Tablespace. One of the option you mentioned of DB nonarchive mode looks good to me too. The question is a the Tablespace is in offline mode is it doing any DB activities now ? So will the disabling of archive mode will help here ?

We have quite powerful HW & CPU is also 40-50 idle without any paging issues.

Thanks and Regards,

Mukesh

[Former Member · Accepted solution]

Hi Mukesh,

>> The question is a the Tablespace is in offline mode is it doing any DB activities now ? So will the disabling of archive mode will help here ?

It will help you while importing the tablespace, not during the export process.

>> We have quite powerful HW & CPU is also 40-50 idle without any paging issues.

It is very good, but please note that the most important factor is disk performance. For example, if the datafiles are distributed into the different physical disks, it will increase the import and export performance.

Best regards,

Orkun Gedik

[mukesh_mahadik · Accepted solution]

Hello Orkun,

We have already completed exp of Tablespace in around 30 hrs. Now we are dropping the Tablespace PSAPBTABD with cmd

drop tablespace PSAPBTABD including contents and datafiles including cascade constraints

We are using brspace utility per SAP note: 646681.

[Former Member · Accepted solution]

Hello Orkun,
> 
> We have already completed exp of Tablespace in around 30 hrs. Now we are dropping the Tablespace PSAPBTABD with cmd 
> 
> drop tablespace PSAPBTABD including contents and datafiles including cascade constraints
> 
> We are using brspace utility per SAP note: 646681.

What about the execution time? Still running? It shouldn't take a long time. Is the database open or not?

[mukesh_mahadik · Accepted solution]

DB is opened & dropping of Tables are very slow only out of 171371 only 4037 dropped form last 12 hrs.

[Former Member · Accepted solution]

DB is opened & dropping of Tables are very slow only out of 171371 only 4037  dropped form last 12 hrs.

Because of the tablespace has the objects (tables and indexes), in order to be at the safe side, you should wait.

Best regards,

Orkun Gedik

[mukesh_mahadik · Accepted solution]

Hello Orkun ,

Is there any way to speed up this drop tablespace process?

[Former Member · Accepted solution]

Did you created new tablespace and imported all the tables respectively?

[Former Member · Accepted solution]

Ok. There are some tricks about it, but I strongly recommend that you wait until it complete the drop tablespace process, respectively.

As a workaround I assume that you exported all the tables without any problem. So, execute the statement, below;

spool out.txt
select owner,  segment_name from dba_segments
where tablespace_name='<old_tablespace_name>' and segment_type='TABLE';
spool off

By doing so you will be able to get all the tables names belong to the tablespace. Then, create a script to truncate all the tables in the old tablespace, as below;

truncate <owner>.<table_name>;

And execute the drop tablespace command again, by using br*tools.

I emphasize that this is so dangerous and you have to know what really you are doing, before the operation. I just show a workaround to you. I applied it previously, but I am not encouraging you to do it.

I am underlining the statement that it is good to be wait for the drop tablespace process, at this stage.

Best regards,

Orkun Gedik

[mukesh_mahadik · Accepted solution]

Hi Orkun ,

We have tried truncating the tables with reuse storage option but still we the truncation processes were taking time so we are using this method & converting LMT Tablespaces.

If I stopped/cancel the drop cmd & make the CIO enabled (AIX) filesystem and disableing the archive log mode will help here ?

Is there any issue/incosistency in Tablespace if we stop & restart the drop cmd again ?

[Former Member · Accepted solution]

Hi Mukesh,

1) At the first stage, if you are running on AIX, "cio" option should be enabled on the file system, datafiles have been located.

2) Breaking "drop tablespace" command, everytime has a risk. It is important, where you broke the process. This command creates many recursive statements on the database, at the background. At this stage, I can recommend to break, if you are working on the sandbox or test system.

3) It will not help you "noarchivelog" option, while executing "drop tablespace" statements.

Best regards,

Orkun Gedik

[mukesh_mahadik · Accepted solution]

Hi Orkun ,

Thanks for your reply. Yes, this is our test system. I'll cancel the drop operation & stop the DB to make CIO enabled as we are on IBM AIX 6.1. I hope this will make some difference in deletion process.

Thanks and Regards,

Mukesh Mahadik

[Former Member · Accepted solution]

It is good to hear that this is a test system

[mukesh_mahadik · Accepted solution]

Yes I don't want to give up on this issue. Something is there beside DMT Tablespces at Oracle level which I'm missing that's why drop operations are running very slow

[Former Member · Accepted solution]

Hi Mukesh,

This is unevitable situation, actually. Because, as I noted previously, the system is dropping tables, indexes and so on at the background when you execute the "drop tablespace <TS> including contents;" statement.

In summary, unless you do not delete all the objects before the drop operation, it will take a long time. As far I know that there's no way to speed up the process.

Best regards,

Orkun Gedik

[stefan_koehler]

Hello Mukesh,

until now i can not find any trouble shooting suggestion in this topic. Just silver bullets and suggestions like "CIO" which are really dubious in this case.

You said that you are dropping objects in a dictionary managed tablespace and that this action is slow. Well without any further information (like wait events, executed SQLs by dropping objects, etc.) it is just guessing.

But as the name said "dictionary managed tablespace" .. this issue can also be caused by the oracle dictionary like fet$, uet$.

Well before we continue the guessing .. provide more information about that particular session that is dropping or truncating the tables.

Regards

Stefan

[Former Member]

>> But as the name said "dictionary managed tablespace" .. this issue can also be caused by the oracle dictionary like fet$, uet$.

It is possible. In order to identify this problem you should perform 10046 trace on the system. But, still I have a doubt about CIO option. This is only a guess, from this point

Best regards,

Orkun Gedik

[mukesh_mahadik]

Hello Stefan,

What information exactly you will be requried here ? any logs or screen shots? We have another same system CIO enabled same DB where we are doing db reorog (moving tables from dict manged to locallly manged tablespace online ) using brspace option & its converting the 50 Tables in 10 mins so we have 1.6 mil Tables it wl take approx 20 days.

Thanks and Regards,

Mukesh

[stefan_koehler]

Hello Mukesh,

well maybe the other system has not so many extents like the other one ... maybe the other system use another access path on the oracle dictionary ... so many possibilites.

What information exactly you will be requried here?

Well start the drop tablespace or truncate table command which takes such a long time and perform the following SQL command on SQL*Plus:

shell> sqlplus / as sysdba
SQL> select v.SID, v.PROGRAM, v.EVENT , v.SECONDS_IN_WAIT, v.SQL_ID, s.SQL_TEXT
from   V$SESSION v, V$SQL s
where v.SQL_ID = s.SQL_ID;

Please post the result of that query in code tags.

Regards

Stefan

[mukesh_mahadik]

Hello Stefan,

Thanks for your help. Here is the result of query.

SQL> select v.SID, v.PROGRAM, v.EVENT , v.SECONDS_IN_WAIT, v.SQL_ID, s.SQL_TEXT

from V$SESSION v, V$SQL s

where v.SQL_ID = s.SQL_ID; 2 3

SID PROGRAM

-

---

-

---

EVENT SECONDS_IN_WAIT

-

---

-

---

SQL_ID

-

---

SQL_TEXT

-

---

201 sqlplus@jq04a020 (TNS V1-V3)

SQL*Net message to client 0

gppz4auq99mmk

select v.SID, v.PROGRAM, v.EVENT , v.SECONDS_IN_WAIT, v.SQL_ID, s.SQL_TEXT from

V$SESSION v, V$SQL s where v.SQL_ID = s.SQL_ID

SID PROGRAM

-

---

-

---

EVENT SECONDS_IN_WAIT

-

---

-

---

SQL_ID

-

---

SQL_TEXT

-

---

207 OMS

enq: TT - contention 32548

f4zm81577q7k0

BEGIN DBMS_SPACE.ISDATAFILEDROPPABLE_NAME(:1,:2); END;

SID PROGRAM

-

---

-

---

EVENT SECONDS_IN_WAIT

-

---

-

---

SQL_ID

-

---

SQL_TEXT

-

---

SQL>

[stefan_koehler]

Hello Mukesh,

well what the heck is the application OMS? Do you use any third party software?

By the way this can not be the complete list, because of the session 207 is blocked by another one that holds a "lock on the tablespace".

Once more .. please post the output in the code tags .. it is nearly unreadable in an unformatted text.

Regards

Stefan

[mukesh_mahadik]

Hello Stefan,

We dont have any thrid party software here. Let me check if OEM is showing any blocking sessions. Here is the output of your query.

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

SQL> select v.SID, v.PROGRAM, v.EVENT , v.SECONDS_IN_WAIT, v.SQL_ID, s.SQL_TEXT

from V$SESSION v, V$SQL s

where v.SQL_ID = s.SQL_ID;

2 3

SID PROGRAM

-

---

-

---

EVENT SECONDS_IN_WAIT----

---

-

---

SQL_ID

-

---

SQL_TEXT

-

---

203 sqlplus@jq04a020 (TNS V1-V3)

SQL*Net message to client 0gppz4auq99mmk

select v.SID, v.PROGRAM, v.EVENT , v.SECONDS_IN_WAIT, v.SQL_ID, s.SQL_TEXT from

V$SESSION v, V$SQL s where v.SQL_ID = s.SQL_ID

SID PROGRAM

-

---

-

---

EVENT SECONDS_IN_WAIT

-

---

-

---

SQL_ID

-

---

SQL_TEXT

-

---

207 OMS

enq: TT - contention 39536f4zm81577q7k0

BEGIN DBMS_SPACE.ISDATAFILEDROPPABLE_NAME(:1,:2); END;

SID PROGRAM

-

---

-

---

EVENT SECONDS_IN_WAIT

-

---

-

---

SQL_ID

-

---

SQL_TEXT

-

---

SQL>

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

Thanks again for your promt reply.

Regards,

Mukesh

[mukesh_mahadik]

{ SQL> select v.SID, v.PROGRAM, v.EVENT , v.SECONDS_IN_WAIT, v.SQL_ID, s.SQL_TEXT

from V$SESSION v, V$SQL s

where v.SQL_ID = s.SQL_ID;

2 3

SID PROGRAM

-

---

-

---

EVENT SECONDS_IN_WAIT----

---

-

---

SQL_ID

-

---

SQL_TEXT

-

---

203 sqlplus@jq04a020 (TNS V1-V3)

SQL*Net message to client 0gppz4auq99mmk

select v.SID, v.PROGRAM, v.EVENT , v.SECONDS_IN_WAIT, v.SQL_ID, s.SQL_TEXT from

V$SESSION v, V$SQL s where v.SQL_ID = s.SQL_ID

SID PROGRAM

-

---

-

---

EVENT SECONDS_IN_WAIT

-

---

-

---

SQL_ID

-

---

SQL_TEXT

-

---

207 OMS

enq: TT - contention 39536f4zm81577q7k0

BEGIN DBMS_SPACE.ISDATAFILEDROPPABLE_NAME(:1,:2); END;

SID PROGRAM

-

---

-

---

EVENT SECONDS_IN_WAIT

-

---

-

---

SQL_ID

-

---

SQL_TEXT

-

---

SQL>

}

[mukesh_mahadik]

SQL> select v.SID, v.PROGRAM, v.EVENT , v.SECONDS_IN_WAIT, v.SQL_ID, s.SQL_TEXT
from V$SESSION v, V$SQL s
where v.SQL_ID = s.SQL_ID  2    3  ;

       SID PROGRAM
---------- ------------------------------------------------
EVENT                                                            SECONDS_IN_WAIT
---------------------------------------------------------------- ---------------
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
       199 sqlplus@jq04a020 (TNS V1-V3)
SQL*Net message to client                                                      0
0am75avbnapmf
select v.SID, v.PROGRAM, v.EVENT , v.SECONDS_IN_WAIT, v.SQL_ID, s.SQL_TEXT from
V$SESSION v, V$SQL s where v.SQL_ID = s.SQL_ID

       SID PROGRAM
---------- ------------------------------------------------
EVENT                                                            SECONDS_IN_WAIT
---------------------------------------------------------------- ---------------
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------


SQL>

I have killed that OMS session from OEM tool & above is the query result.

[mukesh_mahadik]

Hi Stefan,

During upgrade of SCM7.0 we have facing issue in PRARCON_UPG for /1APO/P* Tables. These tables are very large having 1.5 TB of data so we are trying to get rid of these tables.

As we are moving the /1APO/P* Tables from PSAPBTABD and I Tablespace to newly created LMT's PSAPJUNK Tablespace this activity taking a long time.

1. What if we move only valid Tables from PSAPBTABD Tablepsace to newly created LMT's Tablespace PSAPBTABD_NEW that will faster the processes.

2. Rename the original PSAPBTABD and PSAPBTAD Tablespaces to PSAPBTABD_OLD and PSAPBTABI_OLD.

3. Rename PSAPBTABD_NEW ( Valid Tables ) to PSAPBTABD ( This Tablespace will be containing valid Tables and Indexes both )

4. As dropping of DMT Tablespaces with large data having issue , take PSAPBTABD_OLD and PSAPBTABI_OLD Tablespace offline . Thus upgrade will skip these junk /1APO/P* Tables.

Will this approch work ? or Upgrade will give access for accessing /1APO/P* Tables ?

Appreciate your help.

Thanks and Regards,

Mukesh

[stefan_koehler]

Hello Mukesh,

the SQL output still shows not any running SQL or any task that point to a truncate drop operation.

Please run that SQL only on v$session while your long running task (drop or truncate) runs and post the complete output in code tags (not quote!).

select v.SID, v.PROGRAM, v.EVENT , v.SECONDS_IN_WAIT, v.SQL_ID from V$SESSION v;

Without any further information we are not able to help you.

Regards

Stefan

[mukesh_mahadik]

Hello Stefan,

The output of the query is coming very long this time. Do you have email id where I can send the log file?

Thanks and Regards,

Mukesh

[mukesh_mahadik]

Hello Stefan,

We got the solution from SAP to ignore those /1APO/P* Tables so we should be fine with Upgrade processes. We can even ignore those tables in db2 conversion exp/imp process.

Thanks again for your help and Support.

Regards,

Mukesh