-
Originally Posted by vnktummala
... I don't have 10046 trace file.
This is a job for oradebug -one of the many Oracle unsong super heroes.
Please try this...
0- Initiate offending process.
1- Figure out the SPID of the offending process.
2- Log into the system as sysdba
3- Start oradebug...
oradebug setospid SPID-from_step_1;
4- Enable 10046...
oradebug event 10046 trace name context forever, level 12
5- Wait... wait... keep waiting... then wait a little more
6- Disable 10046 tracing...
oradebug event 10046 trace name context off
7- tkprof trace file and bingo! you got your 10046 trace on remote session.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Thanks Paul.
Unfortunately, client has not given the complete DBA privileges on this DB So I can not use oradebug. Even for the trace file I am generating, I need to ask client employee to send me the file, then I am doing tkprof in my laptop.
well, we can not customize DML queries in Informatica. We can customize the queries reading from source but not the queries writing. At the most I can do is take the risk of putting table in nologging.
I am very much pity on myself.
Paul, can you help me to understand the v$segment_statistics better?
Code:
select OBJECT_NAME,SUBOBJECT_NAME,object_type,statistic_name,statistic#,value from v$segment_statistics where obj# in (
select object_id from dba_objects where object_name='A_IDX') order by object_type,value desc;
OBJECT_NAME SUBOBJECT_ OBJECT_TYPE STATISTIC_NAME STATISTIC# VALUE
------------------------------ ---------- ------------------ ------------------------------ ---------- --------------
A_IDX INDEX space allocated 15 3757047808
A_IDX INDEX space used 14 2555428919
A_IDX INDEX logical reads 0 352393280
A_IDX INDEX db block changes 3 119409200
High value means DB is spending that much time to allocate space?
Also, I would like to update that the 12 hours INSERT job for which I started this thread is completed in two and half hours today after dropping all the Indexes associated. But still I am not happy because it has just inserted 600K rows. I am working with ETL guys to find the ETL side waits.
Thanks,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
Originally Posted by PAVB
As far as I can see wait events on db_file_sequential_read account for about 20% of the total elapsed time therefore even if you manage to get rid of 100% of this particular wait event - which is not possible - you are looking at a performance gain of about 20%
What are the chances of rewritting this Informatica mapping as a PL/SQL block? - just for testing purposes
I would write the code without using explicit cursors a.k.a. set-based and, I'll eventually use APPEND hint during insert. On the first try I'll commit only once at the end of the job.
So is Oracle waiting another approximate 20% for Informatica to push some work??
Thanks,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|