| query optimization [message #514978] |
Thu, 07 July 2011 09:30  |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Hi,
There is one sql query which is taking 12 seconds with order by clause while without order by clause it was taking 2 seconds. This is the query with order by cluase...
Please help me to optimise it....
SELECT *
FROM (SELECT a.*, rownum r__
from (
SELECT iv.issueid,
iv.issuecode as Id,
iv.name,issueSeqNumber,
iv.OverallStatus as OverallStatus1,
iv.duedate AS duedate1,
a.CM_CFMStatus,
a.CM_CHECKINCHECKOUTBY,
a.FIELD30,
iv.DESCRIPTION,
iv.OVERALLSTATUS,
iv.DATEIDENTIFIED,
a.FIELD34,
a.FIELD23,
iv.PRIORITY,
iv.DUEDATE,
a.FIELD35
FROM projectissue iv,eformdynamicfieldinstance a
WHERE iv.enterpriseId = 10001
AND iv.ownertype = 'Prj'
AND iv.ownerid = 92453
AND iv.category = 'Siu'
AND (iv.accessControl <= 3 OR iv.CreatedBy = 0)
and iv.OWNERTYPE =A.OWNERTYPE
AND iv.OWNERID =A.OWNERID
AND a.ITEMTYPE =iv.CATEGORY
AND iv.ISSUEID =A.ITEMID
ORDER BY issueSeqNumber ) a
WHERE rownum < ((1 * 5) + 1))
WHERE r__ >= (((1 - 1) * 5) + 1);
This is the plan of the query
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 32155 | 4298 (1)|
|* 1 | VIEW | | 5 | 32155 | 4298 (1)|
|* 2 | COUNT STOPKEY | | | | |
| 3 | VIEW | | 423 | 2651K| 4298 (1)|
|* 4 | SORT ORDER BY STOPKEY | | 423 | 101K| 4298 (1)|
| 5 | TABLE ACCESS BY INDEX ROWID | EFORMDYNAMICFIELDINSTANCE | 1 | 37 | 3 (0)|
| 6 | NESTED LOOPS | | 423 | 101K| 4297 (1)|
|* 7 | TABLE ACCESS BY INDEX ROWID | PROJECTISSUE | 423 | 88407 | 3027 (1)|
| 8 | BITMAP CONVERSION TO ROWIDS | | | | |
| 9 | BITMAP AND | | | | |
| 10 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 11 | INDEX RANGE SCAN | PROJISSUE_IDX2 | 8425 | | 80 (2)|
| 12 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 13 | INDEX RANGE SCAN | IDX_PRJ_ISS_CAT | 8425 | | 841 (2)|
|* 14 | INDEX RANGE SCAN | IND_EFORM_DYNA_FIELD | 1 | | 2 (0)|
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
1 - filter("R__">=1)
2 - filter(ROWNUM<6)
4 - filter(ROWNUM<6)
7 - filter((TO_NUMBER("IV"."ACCESSCONTROL")<=3 OR "IV"."CREATEDBY"=0) AND
"IV"."ENTERPRISEID"=10001)
11 - access("IV"."OWNERTYPE"='Prj' AND "IV"."OWNERID"=92453)
13 - access("IV"."CATEGORY"='Siu')
14 - access("A"."OWNERTYPE"='Prj' AND "A"."OWNERID"=92453 AND "A"."ITEMTYPE"='Siu' AND
"IV"."ISSUEID"="A"."ITEMID")
Total record from the above inner query is 24000
Totoal output of the above query is 5
Total record in eformdynamicfieldinstance table is:7189143
Total record in projectissue table is:1732514
Index list from projectissue table
Sr. INDEX_NAME COLUMN_NAME COLUMN_POSITION
1 IDX_PRJ_ISS_CAT CATEGORY 1
2 IDX_PROJECTISSUE OVERALLSTATUS 1
3 PROJECTISSUE_ENTERP_FK_IDX ENTERPRISEID 1
4 PROJECTISSUE_PHASED_FK_IDX PHASEDETECTED 1
5 PROJECTISSUE_PHASEI_FK_IDX PHASEINJECTED 1
6 PROJECTISSUE_STANDA_FK_IDX STANDARDCODE 1
7 PROJECTISSUE_STAT ENTERPRISEID 1
8 PROJECTISSUE_STAT OWNERTYPE 2
9 PROJECTISSUE_STAT OWNERID 3
10 PROJECTISSUE_STAT OVERALLSTATUS 4
11 PROJECTISSUE_STAT CATEGORY 5
12 PROJECTISSUE_STAT DATECLOSED 6
13 PROJISSUE_IDX2 OWNERTYPE 1
14 PROJISSUE_IDX2 OWNERID 2
15 PROJISSUE_IDX3 RELEASE 1
16 PROJISSUE_UNQ_CONS OWNERTYPE 1
17 PROJISSUE_UNQ_CONS OWNERID 2
18 PROJISSUE_UNQ_CONS ISSUECODE 3
19 SYS_C0048514 ISSUEID 1
Index list from EFORMDYNAMICFIELDINSTANCE table
Sr. INDEX_NAME COLUMN_NAME COLUMN_POSITION
1 EFORMDYNAMICFIE_ENTERP_FK_IDX1 ENTERPRISEID 1
2 EFORM_COMPO_INDEX COMPOKEY 1
3 IDX_EDFI_CM_REL CM_RELEASE 1
4 IDX_EDFI_ITEMTYPE ITEMTYPE 1
5 IDX_EDFI_MODIFIEDDATE MODIFIEDDATE 1
6 IND_EFORM_DYNA_FIELD OWNERTYPE 1
7 IND_EFORM_DYNA_FIELD OWNERID 2
8 IND_EFORM_DYNA_FIELD ITEMTYPE 3
9 IND_EFORM_DYNA_FIELD ITEMID 4
10 IND_EFORM_DYNFL_ITEM ITEMTYPE 1
11 IND_EFORM_DYNFL_ITEM ITEMID 2
12 SYS_C0048252 OWNERTYPE 1
13 SYS_C0048252 OWNERID 2
14 SYS_C0048252 ITEMTYPE 3
15 SYS_C0048252 ITEMID 4
16 SYS_C0048252 ENTERPRISEID 5
[Updated on: Thu, 07 July 2011 09:38] by Moderator Report message to a moderator |
|
|
|
| Re: query optimization [message #514986 is a reply to message #514978] |
Thu, 07 July 2011 10:13  |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
When you use table aliases in a query it pays to apply them to every column - you've left one out - issueSeqNumber - which is the column you order by. So we can't tell which of the two tables it comes from.
Also don't use the same alias at multiple levels, it just confuses things.
Best bet it to add that column to one of the indexes that's currently being used.
|
|
|
|
| Re: query optimization [message #514990 is a reply to message #514986] |
Thu, 07 July 2011 10:31 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
|
This issueSeqNumber column is coming from projectissue table and I created a seprate index only on issueSeqNumber column just to test it ,but i did not get any positive optimised response, so I dropped it.
|
|
|
|
| Re: query optimization [message #514992 is a reply to message #514990] |
Thu, 07 July 2011 10:36 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Any given table access will normally only use 1 index on that table (unless bitmaps are involved). I'm not sure how bitmaps work with order by.
Suggest you create a composite btree index on that table including the more restrictive columns of the where clause and issueSeqNumber
|
|
|
|
|
|
| Re: query optimization [message #514994 is a reply to message #514993] |
Thu, 07 July 2011 10:43 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I couldn't possibly know.
Example:
If there are < 10% of rows where that condition is true then it is fairly restrictive.
If there are < 1% of rows where that condition is true then it is very restrictive.
Columns with lots of distinct values are restrictive.
Columns where most rows have the same value aren't.
You need to work out which is which. Since I know nothing about your tables/data I can't tell.
And note I said columns - more than one should be in the index.
|
|
|
|
|
|
| Re: query optimization [message #515055 is a reply to message #515017] |
Thu, 07 July 2011 23:45 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
I am using oracle 10g.
Now i have executed the given query in different conditions as you suggested and mentioning the output....
without changing the session paramter and without order by
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 32155 | 66 (0)|
|* 1 | VIEW | | 5 | 32155 | 66 (0)|
|* 2 | COUNT STOPKEY | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | EFORMDYNAMICFIELDINSTANCE | 1 | 37 | 3 (0)|
| 4 | NESTED LOOPS | | 6 | 1476 | 66 (0)|
|* 5 | TABLE ACCESS BY INDEX ROWID | PROJECTISSUE | 423 | 88407 | 48 (0)|
| 6 | BITMAP CONVERSION TO ROWIDS | | | | |
| 7 | BITMAP AND | | | | |
| 8 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 9 | INDEX RANGE SCAN | PROJISSUE_IDX2 | 8425 | | 4 (0)|
| 10 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 11 | INDEX RANGE SCAN | IDX_PRJ_ISS_CAT | 8425 | | 14 (0)|
|* 12 | INDEX RANGE SCAN | IND_EFORM_DYNA_FIELD | 1 | | 2 (0)|
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("R__">=1)
2 - filter(ROWNUM<6)
5 - filter((TO_NUMBER("IV"."ACCESSCONTROL")<=3 OR "IV"."CREATEDBY"=0) AND
"IV"."ENTERPRISEID"=10001)
9 - access("IV"."OWNERTYPE"='Prj' AND "IV"."OWNERID"=92453)
11 - access("IV"."CATEGORY"='Siu')
12 - access("A"."OWNERTYPE"='Prj' AND "A"."OWNERID"=92453 AND "A"."ITEMTYPE"='Siu' AND
"IV"."ISSUEID"="A"."ITEMID")
Explain plan of this query with order by clause
alter session set "_b_tree_bitmap_plans"=false;
SELECT *
FROM (SELECT a.*, rownum r__
from (
SELECT iv.issueid,
iv.issuecode as Id,
iv.name,issueSeqNumber,
iv.OverallStatus as OverallStatus1,
iv.duedate AS duedate1,
a.CM_CFMStatus,
a.CM_CHECKINCHECKOUTBY,
a.FIELD30,
iv.DESCRIPTION,
iv.OVERALLSTATUS,
iv.DATEIDENTIFIED,
a.FIELD34,
a.FIELD23,
iv.PRIORITY,
iv.DUEDATE,
a.FIELD35
FROM projectissue iv,eformdynamicfieldinstance a
WHERE iv.enterpriseId = 10001
AND iv.ownertype = 'Prj'
AND iv.ownerid = 92453
AND iv.category = 'Siu'
AND (iv.accessControl <= 3 OR iv.CreatedBy = 0)
and iv.OWNERTYPE =A.OWNERTYPE
AND iv.OWNERID =A.OWNERID
AND a.ITEMTYPE =iv.CATEGORY
AND iv.ISSUEID =A.ITEMID
ORDER BY issueSeqNumber ) a
WHERE rownum < ((1 * 5) + 1))
WHERE r__ >= (((1 - 1) * 5) + 1);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 32155 | 6275 (1)|
|* 1 | VIEW | | 5 | 32155 | 6275 (1)|
|* 2 | COUNT STOPKEY | | | | |
| 3 | VIEW | | 423 | 2651K| 6275 (1)|
|* 4 | SORT ORDER BY STOPKEY | | 423 | 101K| 6275 (1)|
| 5 | NESTED LOOPS | | 423 | 101K| 6274 (1)|
| 6 | TABLE ACCESS BY INDEX ROWID| EFORMDYNAMICFIELDINSTANCE | 4149 | 149K| 2107 (1)|
|* 7 | INDEX RANGE SCAN | IND_EFORM_DYNA_FIELD | 4149 | | 20 (0)|
|* 8 | TABLE ACCESS BY INDEX ROWID| PROJECTISSUE | 1 | 209 | 2 (0)|
|* 9 | INDEX UNIQUE SCAN | SYS_C0048514 | 1 | | 1 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("R__">=1)
2 - filter(ROWNUM<6)
4 - filter(ROWNUM<6)
7 - access("A"."OWNERTYPE"='Prj' AND "A"."OWNERID"=92453 AND "A"."ITEMTYPE"='Siu')
8 - filter("IV"."OWNERID"=92453 AND "IV"."CATEGORY"='Siu' AND
(TO_NUMBER("IV"."ACCESSCONTROL")<=3 OR "IV"."CREATEDBY"=0) AND "IV"."ENTERPRISEID"=10001
AND "IV"."OWNERTYPE"='Prj')
9 - access("IV"."ISSUEID"="A"."ITEMID")
Plan of the query without order by with the same sesion paramter
alter session set "_b_tree_bitmap_plans"=false;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 32155 | 103 (0)|
|* 1 | VIEW | | 5 | 32155 | 103 (0)|
|* 2 | COUNT STOPKEY | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | EFORMDYNAMICFIELDINSTANCE | 1 | 37 | 3 (0)|
| 4 | NESTED LOOPS | | 6 | 1476 | 103 (0)|
|* 5 | TABLE ACCESS BY INDEX ROWID| PROJECTISSUE | 423 | 88407 | 85 (0)|
|* 6 | INDEX RANGE SCAN | PROJECTISSUE_STAT | 8425 | | 5 (0)|
|* 7 | INDEX RANGE SCAN | IND_EFORM_DYNA_FIELD | 1 | | 2 (0)|
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("R__">=1)
2 - filter(ROWNUM<6)
5 - filter(TO_NUMBER("IV"."ACCESSCONTROL")<=3 OR "IV"."CREATEDBY"=0)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
6 - access("IV"."ENTERPRISEID"=10001 AND "IV"."OWNERTYPE"='Prj' AND
"IV"."OWNERID"=92453 AND "IV"."CATEGORY"='Siu')
filter("IV"."CATEGORY"='Siu')
7 - access("A"."OWNERTYPE"='Prj' AND "A"."OWNERID"=92453 AND "A"."ITEMTYPE"='Siu' AND
"IV"."ISSUEID"="A"."ITEMID")
|
|
|
|
|
|
| Re: query optimization [message #515095 is a reply to message #515065] |
Fri, 08 July 2011 01:56 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
I am getting this info
select plan_table_output from table (sys.dbms_xplan.display_cursor( '',''));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9m7787camwh4m, child number 2
begin :id := sys.dbms_transaction.local_transaction_id; end;
NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 2
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
Please suggest what to do.
|
|
|
|
|
|
|
|
|
|
| Re: query optimization [message #515164 is a reply to message #515106] |
Fri, 08 July 2011 05:51 |
 |
LNossov
Messages: 318
Registered: July 2011
Location: Germany
|
Senior Member |
|
|
|
Perhaps you have really a big traffic in the shared pool and as consequence you cannot get the execution plan. Alternatively you can get this plan with runtime statistics via sql tracing. Try it with alter session set sql_trace=true and run then your sql. You don't need for that statistics_level=all (but is also not a problem, if you do that). You can upload then your traces.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
