| search query [message #473357] |
Fri, 27 August 2010 09:15  |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
This query took lots of time in executions,this query is used to search the information:
select distinct EDFI.ITEMTYPE as ItemType,
EDFI.ITEMID as ItemId,
EDFI.CM_ITEMCODE as ItemCode,
EDFI.CM_DESCRIPTION as Description,
EDFI.CM_CREATIONDATE as CreationDate,
EDFI.CM_NAME as ItemName,
EDFI.ITEMTYPE as Category,
EDFI.OWNERID as OWNERID
from eformdynamicfieldinstance EDFI
,HCLT_PARKING_LOT IST
WHERE (UPPER(IST.CM_Description) like '%TCK68072%' OR
UPPER(IST.USER_IMPACT) like '%TCK68072%' OR
UPPER(IST.Date_Created_in_CAMS) like '%TCK68072%' OR
UPPER(IST.Cams_Notes) like '%TCK68072%' OR
UPPER(IST.PM_Smart_Status) like '%TCK68072%' OR
UPPER(IST.CM_ItemCode) like '%TCK68072%' or
UPPER(IST.MQ_Comments) like '%TCK68072%' OR
UPPER(IST.CAMS_Status) like '%TCK68072%' OR
UPPER(IST.CM_Name) like '%TCK68072%' OR
UPPER(IST.Customer_Phone) like '%TCK68072%' OR
UPPER(IST.Date_Created_in_PM_Smart) like '%TCK68072%' OR
UPPER(IST.Number_of_Attachments) like '%TCK68072%' OR
UPPER(IST.Customer_GUID) like '%TCK68072%' OR
UPPER(IST.CM_OverallStatus) like '%TCK68072%' OR
UPPER(IST.CAMS_Ticket_Type) like '%TCK68072%' OR
UPPER(IST.CAMS_Id) like '%TCK68072%' OR
UPPER(IST.Customer_Name) like '%TCK68072%')
and edfi.ownertype = 'Prj'
and edfi.ownerid = 76129
and edfi.itemtype = 'TCK_f'
AND EDFI.ITEMID = IST.itemid
AND EDFI.cm_AccessControl <= '3'
Order by ItemId
Is there any way to optimise it...
Please find the trace of this query
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.02 1 1 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 25.04 40.09 11187 508982 1243201 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 25.05 40.11 11188 508983 1243201 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 74
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY (cr=254492 pr=6633 pw=0 time=27822697 us)
1 HASH UNIQUE (cr=254492 pr=6633 pw=0 time=27822662 us)
1 TABLE ACCESS BY INDEX ROWID HCLT_PARKING_LOT (cr=254492 pr=6633 pw=0 time=27822453 us)
150415 NESTED LOOPS (cr=137888 pr=1435 pw=0 time=301819 us)
75210 TABLE ACCESS BY INDEX ROWID EFORMDYNAMICFIELDINSTANCE (cr=62501 pr=1431 pw=0 time=6242381 us)
75210 INDEX RANGE SCAN SYS_C00501664 (cr=378 pr=0 pw=0 time=24 us)(object id 238827)
75204 INDEX RANGE SCAN HCLT_PARKINGLOT (cr=75387 pr=4 pw=0 time=456070 us)(object id 235773)
Index on the table HCLT_PARKING_LOT
Sr. INDEX_NAME COLUMN_NAME COLUMN_POSITION
1 HCLT_IDX_PARKING_ITEMTYPE ITEMTYPE 1
2 HCLT_IDX_PARKING_OWNER OWNERTYPE 1
3 HCLT_IDX_PARKING_OWNER OWNERID 2
4 HCLT_IDX_PARKING_OWNER ENTERPRISEID 3
5 HCLT_IDX_PARKING_OWNER_ITEM OWNERTYPE 1
6 HCLT_IDX_PARKING_OWNER_ITEM OWNERID 2
7 HCLT_IDX_PARKING_OWNER_ITEM ITEMTYPE 3
8 HCLT_IDX_PARKING_OWNER_ITEM CM_OVERALLSTATUS 4
9 HCLT_IDX_PARKING_OWNER_ITEM ENTERPRISEID 5
10 HCLT_PARKINGLOT ITEMID 1
11 SYS_C0048357 OWNERTYPE 1
12 SYS_C0048357 OWNERID 2
13 SYS_C0048357 ITEMTYPE 3
14 SYS_C0048357 ITEMID 4
15 SYS_C0048357 ENTERPRISEID 5
INDEX ON EFORMDYAMICFIELDINSTANCE
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
TOTAL NO OF RECORDS IN EFORMDYNAMICFIELDINSTANCE:5973252
total no of RECORDS IN EFORMDYNAMICFIELDINSTANCE with
(ownertype = 'Prj' and ownerid = 76129 and itemtype = 'TCK_f') is 75991
total no of records in hclt_parking_lot is 75985
total no of records in hclt_parking_lot with matching cndition is also 75985
|
|
|
|
|
|
| Re: search query [message #473360 is a reply to message #473358] |
Fri, 27 August 2010 09:46  |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
|
this index SYS_C0048252 and SYS_C00501664 are same, actually i got the index list from other database.In both the database indexes are same.
|
|
|
|
| Re: search query [message #473429 is a reply to message #473357] |
Sat, 28 August 2010 04:29 |
Yasir Hashmi
Messages: 304
Registered: April 2006
|
Senior Member |
|
|
Hi,
You didn't post the wait events from tkprof output.
Also your parse count 2 which means oracle is hard parsing the quer y which is an expensive operation.
With index HCLT_PARKINGLOT on HCLT_PARKING_LOT,oracle is peforming 75387 consistent read via index range scan and 254492(logical)+6633(physical) reads by accessing table using rowid .
According to my understanding,this usage of index is causing db file sequential reads wait events causing performance.
Try with a full hint as select /*+ full(HCLT_PARKING_LOT) */ and then see what happens.
|
|
|
|
|
|
|
|
|
|
| Re: search query [message #473447 is a reply to message #473444] |
Sat, 28 August 2010 06:54 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Your hints will be ignored if you refer to the table by name, rather than by alias. Also, in this case, you might want to hint a hash join, rather than nested loop.
But before any of that, you would need to be sure that all your statistics are accurate.
I certainly wouldn't worry about the parsing, it is trivial in comparison to the execution time: you want the best parse you an get.
|
|
|
|
| Re: search query [message #473448 is a reply to message #473447] |
Sat, 28 August 2010 06:59 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
|
yes all the statistics are accurate and even i tried the alias name in the query but it is giving me the same time and i think because of so much or condition it is taking lots of time and for that even i tired USE_CONCAT hint, but it also not worked.
|
|
|
|
| Re: search query [message #473449 is a reply to message #473448] |
Sat, 28 August 2010 07:08 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
So what was the modified query, and what was the execution plan?
The optimizer would have to ignore USE_CONCAT, I don't see how a re-write into UNION ALL would really be possible.
And by the way, can you prove that the statistics are correct?
|
|
|
|
| Re: search query [message #473450 is a reply to message #473449] |
Sat, 28 August 2010 07:12 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
|
How can i give the proof. Actually i am seeing the plan of the query in gv$sql_plan because i can not get the trace data as i do not have the access of production box directory.
|
|
|
|
| Re: search query [message #473452 is a reply to message #473450] |
Sat, 28 August 2010 07:21 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
prashant_ora wrote on Sat, 28 August 2010 07:12How can i give the proof. You could show the output of queries that count the rows compared with queries against dba_tables.
prashant_ora wrote on Sat, 28 August 2010 07:12 Actually i am seeing the plan of the query in gv$sql_plan because i can not get the trace data as i do not have the access of production box directory.
EXPLAIN PLAN and DBMS_XPLAN might be a better option?
|
|
|
|
| Re: search query [message #473453 is a reply to message #473452] |
Sat, 28 August 2010 07:28 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
this query is returning only one row.even in my first post i gave all the stats the same i am posting here.
TOTAL NO OF RECORDS IN EFORMDYNAMICFIELDINSTANCE:5973252
total no of RECORDS IN EFORMDYNAMICFIELDINSTANCE with
(ownertype = 'Prj' and ownerid = 76129 and itemtype = 'TCK_f') is 75991
total no of records in hclt_parking_lot is 75985
total no of records in hclt_parking_lot with matching cndition is also 75985
and the sql plan i am trying to post,but formating is creating problem.
|
|
|
|
| Re: search query [message #473454 is a reply to message #473453] |
Sat, 28 August 2010 07:34 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
I suggested that you check whether the statistics are accurate, and you haven't. I asked you to post your modified query, and you haven't. I asked for the revised execution plan, and you don't have it. Please help people to help you!
|
|
|
|
|
|
|
|
|
|
|
|
| Re: search query [message #473484 is a reply to message #473452] |
Sun, 29 August 2010 02:12 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
|
One thing i also want to share is that in the where cluse there is one column called cams_notes which data type is blob, that also may be a cause of delay response
|
|
|
|
| Re: search query [message #473491 is a reply to message #473357] |
Sun, 29 August 2010 06:41 |
Yasir Hashmi
Messages: 304
Registered: April 2006
|
Senior Member |
|
|
Try commenting that column and check whether the performance increases.
Alos find the sid of the session as:
select sys_context('USERENV','SID') from dual;
Then Run the query.
From another session as sysdba issue
select event,state,seconds_in_wait from v$session_wait where sid=<sid_of_session>;
This will list put wait events if any.Post them there.
As watson has stated,if you are using alias names,table names would be ignored while using hints.
Use them as:
select /*+ full(ist) */ distinct EDFI.ITEMTYPE as ItemType,
EDFI.ITEMID as ItemId,
EDFI.CM_ITEMCODE as ItemCode,
EDFI.CM_DESCRIPTION as Description,
EDFI.CM_CREATIONDATE as CreationDate,
EDFI.CM_NAME as ItemName,
EDFI.ITEMTYPE as Category,
EDFI.OWNERID as OWNERID
from eformdynamicfieldinstance EDFI
,HCLT_PARKING_LOT IST
WHERE (UPPER(IST.CM_Description) like '%TCK68072%' OR
UPPER(IST.USER_IMPACT) like '%TCK68072%' OR
UPPER(IST.Date_Created_in_CAMS) like '%TCK68072%' OR
UPPER(IST.Cams_Notes) like '%TCK68072%' OR
UPPER(IST.PM_Smart_Status) like '%TCK68072%' OR
UPPER(IST.CM_ItemCode) like '%TCK68072%' or
UPPER(IST.MQ_Comments) like '%TCK68072%' OR
UPPER(IST.CAMS_Status) like '%TCK68072%' OR
UPPER(IST.CM_Name) like '%TCK68072%' OR
UPPER(IST.Customer_Phone) like '%TCK68072%' OR
UPPER(IST.Date_Created_in_PM_Smart) like '%TCK68072%' OR
UPPER(IST.Number_of_Attachments) like '%TCK68072%' OR
UPPER(IST.Customer_GUID) like '%TCK68072%' OR
UPPER(IST.CM_OverallStatus) like '%TCK68072%' OR
UPPER(IST.CAMS_Ticket_Type) like '%TCK68072%' OR
UPPER(IST.CAMS_Id) like '%TCK68072%' OR
UPPER(IST.Customer_Name) like '%TCK68072%')
and edfi.ownertype = 'Prj'
and edfi.ownerid = 76129
and edfi.itemtype = 'TCK_f'
AND EDFI.ITEMID = IST.itemid
AND EDFI.cm_AccessControl <= '3'
Order by ItemId
|
|
|
|
| Re: search query [message #473492 is a reply to message #473491] |
Sun, 29 August 2010 07:10 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
this is the output of the query
Sr. EVENT STATE SECONDS_IN_WAIT
1 SQL*Net message from client WAITED SHORT TIME 0
after commenting the blob column(cams_notes) it is giving the output in 10 seconds that is the improved one. So how can i improve this with blob column and also even without blob how can i optmise it more.
|
|
|
|
| Re: search query [message #473598 is a reply to message #473454] |
Mon, 30 August 2010 09:37 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
|
today even i created one domain index on cams_notes column which is a blob ,but still in explain plan that index is not using, do not know why.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
