We received an urgent priority ticket to resolve a performance issue for a sql statement which was running for 4-5 hours and throwing snapshot too old error.

The query was based on finding out the duplicate records and involved joins. There were 3 tables involved in join. The 2 tables were 500 MB in size and around 500-600k records. The 3rd table was 40GB with 20-25 millions of records.

The select statement was fetching records from 3rd table and joining with other 2 tables.

To expedite the report processing I created an index on 4 columns, the columns of big table. A composite index.

1. Index with nologging and parallel clause. The 1st attempt failed because of temporary tablespace errors. In the 2nd attempt, we

After the index creation I used dbms_stats.gather_table_stats with cascade option and degree 5. It was 6 processor machine so we followed n-1 rule.

We ran the sql statement and the query finished in 1 minute 40 seconds.

The concept is Index Fast Full Scan.

I want to share my experience with alter table move tablespace. I was handling a project to migrate data mart database tables from dictionary managed tablespace to locally managed tablespace.
Database version: 9.2.0.4

The m_gnap_records table size was around 40GB and it had 210 columns. When I started this assignment, the existing plan was based on the following approach:

alter table m_gnap_records move tablespace nologging;

a. alter table m_gnap_records nologging;
b. alter table m_gnap_records move tablespace s_u_data nologging parallel 4;

Time Taken: 1 hour 55 minutes.

I tested the following also to verify the approach:

alter table m_gnap_records logging;
alter table m_gnap_records move tablespace s_u_data_test nologging parallel 4;

Time taken : 4 hours 48 minutes

I was working on a large import process. My application user provided the export file of size around 10GB.

I started the import process and it finished in approximately 2 hours ( no indexes and constraints).

imp / buffer=102400000 file=/tmp/table.p fromuser=NTUSER touser=NTUSER indexes=N constraints=n statistics=None grants=N recordlength=65535 ignore=Y commit=Y ANALYZE=N rows=y feedback=500000

After the above operation I had to run an update for 20 million records.The update operation generated around 10 GB of redo. All the tables were in no logging mode. I spent couple of hours to dig out the reason of large number of archive logs and overall update performance.

I decided to use logminer to read the archive logs to gather the details on the objects causing large redo.I copied the production archive log in the lab server and did the following:

The prod db was Nprod and lab db was NLAB.

Starting Logminer

ALTER DATABASE FORCE LOGGING;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

BEGIN
DBMS_LOGMNR_D.build (
options => DBMS_LOGMNR_D.store_in_redo_logs);
END;
/
BEGIN
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/tmp/2_2212_592338274.dbf');
END;
/
BEGIN
DBMS_LOGMNR.start_logmnr (
options => Dbms_Logmnr.DDL_Dict_Tracking);
END;
/
SELECT scn, operation, sql_redo, sql_undo FROM v$logmnr_contents;

The sql_redo and sql_undo had reference Object#.

I filtered out the output and got the object id. I executed the following query in the production database.

select object_id,object_name,owner from dba_objects where object_id in(57190,57188,64455,6379);

OBJECT_ID OBJECT_NAME OWNER
---------- -------------------- ------------------------------
47677 IAM_STATE CBADUSER
63798 T_HUMAONS NTUSER
63705 MLOG$_T_HUMAONS NTUSER
57197 STUDIO HUGUSER

The above output showed up MLOG. We were moving the data from one site to another and we never talked about materialized view logs. I discussed the Materialized view log details with application team and we decided that before export they will refresh the mview and drop all mlogs.

The initial import time was around 2 hours and update timing was 2 hour 38 minutes but after the above changes the import finished in 58 minutes and update finished in 1 hour 10 minutes.

Login User name Password  Remember me  Log in Problems?  New User? Sign Up!

Scripts ----- topdb Oracle_Handy

FAQ ----- Oracle OS

Forum Forums

G r e a t D e a l s