View previous topic :: View next topic
|
Author |
Message |
mlp
New User
Joined: 23 Sep 2005 Posts: 91
|
|
|
|
I am using the DSNTIAUL to extract data corresponding to a select query. The query is a join query of multiple tables. It takes lot of time to execute the job which executes the query using DSNTIAUL utility.
I tried to tune the query and measured extent of improvement with EXPLAIN. I actually did get lot of improvement but once I executed job with new query, it took more time as that of the old query.
Can somebody explain me this behaviour? |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
As your own work try to look if the SQL is getting a cartesian product
Try to revisit your query if the table indexes have been used effectively
and who knows even an index at a wrong place could screw it up all
Suggest you to sit with your DBA try to explain him the scenario he might be knowing the system better
As its hard to give a suggesion/solution without looking into Table,columns,Index,SQL and ofcourse the EXPLAIN report |
|
Back to top |
|
|
mlp
New User
Joined: 23 Sep 2005 Posts: 91
|
|
|
|
The explain analysis shows that there is roghly 70% improvement in query performance in terms of CPU ms consumed and service units consumed.
If explain is showing me 70% of improvement then I think performance of the unload job should really improve. At least a bit. But once I executed the tuned query with DSNTIAUL then the job take more time. That is why I am puzzled. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
if You are talking about elapsed meditate on the factors that influence it |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Just to add on check with your DBA if RUNSTATS has been done recently |
|
Back to top |
|
|
mlp
New User
Joined: 23 Sep 2005 Posts: 91
|
|
|
|
Not only the ELAPS time has increased but the CPU consumption is also increased.
The ealier query was doing tablespace scan for one of the query block. I just added few predicates to WHERE clause so that the table space scan got converted into index scan. Also the as I said earlier the consumed CPU ms also reduced.
Is there any other factor influencing the perfromance of the same query which is executed by DSNTIAUL? |
|
Back to top |
|
|
mlp
New User
Joined: 23 Sep 2005 Posts: 91
|
|
|
|
Below the explain report regarding the same.
Before -
Code: |
Query Q Pl M Ac M I T
Number Bl No T Ty Co O No Table Name
* * * * * * * * *
----------- -- -- - -- -- - -- ------------------
98980 1 1 3 0 0
98980 2 1 0 R 0 N 1 T1
98980 2 2 1 I 2 N 2 T2
98980 3 1 0 I 5 N 3 T3
98980 4 1 0 I 5 N 4 T4
98980 5 1 0 I 5 Y 5 T5
98980 6 1 0 I 5 Y 6 T6
98980 7 1 0 R 0 N 7 T1
98980 7 2 1 I 1 Y 8 T2
98980 8 1 0 I 5 N 9 T3
98980 9 1 0 I 5 N 10 T4
98980 10 1 0 I 5 Y 11 T5
98980 11 1 0 I 5 Y 12 T6
Query Stmt C Estimated Estimated
Number Type C Serv. Units Proc. ms Reason
* * * * * *
----------- ------ - ----------- ----------- -------------
98980 SELECT A 84757904 1525642
|
After -
Code: |
Query Q Pl M Ac M I T
Number Bl No T Ty Co O No Table Name
* * * * * * * * *
----------- -- -- - -- -- - -- ------------------
98980 1 1 3 0 0
98980 2 1 0 I 1 N 1 T1
98980 2 2 1 I 2 N 2 T2
98980 3 1 0 I 5 N 3 T3
98980 4 1 0 I 5 N 4 T4
98980 5 1 0 I 5 Y 5 T5
98980 6 1 0 I 5 Y 6 T6
98980 7 1 0 I 1 N 7 T1
98980 7 2 1 I 1 Y 8 T2
98980 8 1 0 I 5 N 9 T3
98980 9 1 0 I 5 N 10 T4
98980 10 1 0 I 5 Y 11 T5
98980 11 1 0 I 5 Y 12 T6
Query Stmt C Estimated Estimated
Number Type C Serv. Units Proc. ms Reason
* * * * * *
----------- ------ - ----------- ----------- -------------
98980 SELECT A 26165840 470986
|
|
|
Back to top |
|
|
|