| Query tuning [message #470243] |
Mon, 09 August 2010 07:05  |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
Can anybody help me to reduce the cost of below query?
SQL> explain plan for
2 select identity "VOIP_NUMBER",bbvoice_fusion_works.get_status_fusion_works(si.id,si.status,si.customer_id) "STATUS",
service_id "SERVICE_ID",customer_id "CUSTOMER_ID" from service_instances si where si.service_id in (18,25,26,27,29)
and identity is not null; 3 4
Explained.
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 527K| 17M| 11173 |
|* 1 | TABLE ACCESS FULL | SERVICE_INSTANCES | 527K| 17M| 11173 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SI"."SERVICE_ID"=18 OR "SI"."SERVICE_ID"=25 OR
"SI"."SERVICE_ID"=26 OR "SI"."SERVICE_ID"=27 OR "SI"."SERVICE_ID"=29) AND
"SI"."IDENTITY" IS NOT NULL)
Index_details:
==============
INDEX_NAME COLUMN_NAME
------------------------------ ---------------------------------------------
SI_EMAIL_I EMAIL_ADDRESS
ORD_MODIFY_IDEN_I ORDER_MODIFY_IDENTIFIER
ORD_MODIFY_STAT_I ORDER_MODIFY_STATUS
INST_PK ID
INST_CS_I CUSTOMER_ID
INST_CS_I SERVICE_ID
SI_CREATED_I CREATED
SI_UPDATED_I UPDATED
SI_SERV_ID SERVICE_ID
SI_STATUS_ID STATUS
SI_IDENTITY_I IDENTITY |
|
|
|
|
|
| Re: Query tuning [message #470248 is a reply to message #470246] |
Mon, 09 August 2010 07:16  |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
SQL> select count(*) from service_instances;
COUNT(*)
----------
1633111
From the explain plan, we can see it returns 527k rows.
|
|
|
|
|
|
| Re: Query tuning [message #470251 is a reply to message #470248] |
Mon, 09 August 2010 07:34 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
The below package that we are using inside the query.
SQL> select text from user_source where name='BBVOICE_FUSION_WORKS';
TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------
PACKAGE bbvoice_fusion_works IS
FUNCTION get_status_fusion_works(inst_id service_instances.id%TYPE
,status service_instances.status%TYPE
,cust_id service_instances.customer_id%TYPE)
RETURN VARCHAR2;
END bbvoice_fusion_works;
PACKAGE BODY bbvoice_fusion_works IS
v_pk_name CONSTANT VARCHAR2(240) := 'bbvoice_fusion_works';
FUNCTION get_status_fusion_works(inst_id service_instances.id%TYPE
,status service_instances.status%TYPE
,cust_id service_instances.customer_id%TYPE)
RETURN VARCHAR2 IS
BEGIN
IF (bbvoice_services.get_parameter_value(bbvoice_service.get_instanceid(inst_id
,'0')
,'isTestAccount') = 'Y') THEN
RETURN 'test';
ELSIF (bbvoice_services.get_parameter_value(inst_id
,'isExportedNumber') = 'Y') THEN
RETURN 'exported';
ELSIF (bbvoice_services.get_parameter_value(inst_id
,'import_number_hdt') = 'Y') THEN
RETURN 'imported';
ELSIF (status IN
('new',
'order_placed',
'order_placed_voip',
'config_requested',
'config_requested_voip',
'config_failed_voip',
'order_failed_voip',
'config_failed',
'config_complete',
'exchange_complete')) THEN
RETURN 'new';
ELSIF (status IN
('active',
'modify_placed',
'modify',
'modify_failed',
'exchange_complete_voip')) THEN
RETURN 'active';
ELSIF (status IN
('cease_placed',
'ceased',
'delete_complete',
'suspend_requested',
'delete_requested',
'delete_failed',
'resume_requested',
'cease_requested',
'cease_failed',
'suspended',
'parked',
'wait_wls_response')) THEN
RETURN 'suspended';
ELSE
RETURN 'active';
END IF;
RETURN 'active';
END get_status_fusion_works;
END bbvoice_fusion_works; |
|
|
|
| Re: Query tuning [message #470253 is a reply to message #470251] |
Mon, 09 August 2010 07:37 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
Yes I want to reduce the execution time of this query.
Basically we are using this query in a meterialized view. So it is taking 4.30 hours approximately for a complete refresh.
Some more info regarding that meterialized view is given below.
SQL> select LAST_REFRESH_DATE,REFRESH_METHOD,SUMMARY,FULLREFRESHTIM,INCREFRESHTIM,CONTAINS_VIEWS,UNUSABLE,RESTRICTED_SYNTAX,INC_REFRESHABLE,KNOWN_STALE,
2 INVALID,QUERY_LEN,QUERY,REVISION from user_mview_analysis where MVIEW_NAME='BBV_FUSIONWORKS_MAT_VIEW';
LAST_REFRESH_DAT REFRESH_ S FULLREFRESHTIM INCREFRESHTIM C you are I K I QUERY_LEN QUERY REVISION
---------------- -------- - -------------- ------------- - - - - - - ---------- ------------------------------------------------------ ----------
19:00 08/08/2010 FORCE N 15741 0 N N N N Y N 256 select identity "VOIP_NUMBER" 22
,bbvoice_fusion_works.get_status_fusion_works(si.id,si
.status,si.customer_id) "STATUS"
,service_id "SERVICE_ID"
,customer_id "CUSTOMER_ID"
from service_instances si
where si.service_id in (18,25,26,27)
and identity is not null[Updated on: Mon, 09 August 2010 07:39] Report message to a moderator |
|
|
|
| Re: Query tuning [message #470256 is a reply to message #470249] |
Mon, 09 August 2010 07:45 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
I have created a copy of service instances table called ser.So I can apply any kind of changes in that table.
|
|
|
|
|
|
| Re: Query tuning [message #470275 is a reply to message #470248] |
Mon, 09 August 2010 08:19 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
sathik wrote on Mon, 09 August 2010 13:16SQL> select count(*) from service_instances;
COUNT(*)
----------
1633111
From the explain plan, we can see it returns 527k rows.
No we can see that oracle thinks it's going to return 527k rows. It could be wrong.
So issue a query to get the exact count.
Assuming it's right you're selecting approx 32% of the table - in which case oracle will almost certainly have to use a full table scan as that'll be more efficient than using indexes.
|
|
|
|
| Re: Query tuning [message #470347 is a reply to message #470275] |
Mon, 09 August 2010 10:59 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
SQL> select count(*) from service_instances;
COUNT(*)
----------
1632815
SQL> select count(*) from service_instances si where si.service_id in (18,25,26,27,29) and identity is not null;
COUNT(*)
----------
150274
As per this count, the query is retriving 10% of rows. in this case, will oracle go full table scan or index scan?
|
|
|
|
| Re: Query tuning [message #470348 is a reply to message #470259] |
Mon, 09 August 2010 11:01 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
Explain plan without PL/SQL function is given below. there is no change in the cost.
SQL> explain plan for
2 select identity "VOIP_NUMBER",service_id "SERVICE_ID",customer_id "CUSTOMER_ID" from service_instances si
3 where si.service_id in (18,25,26,27,29) and identity is not null;
Explained.
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 527K| 10M| 11173 |
|* 1 | TABLE ACCESS FULL | SERVICE_INSTANCES | 527K| 10M| 11173 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SI"."SERVICE_ID"=18 OR "SI"."SERVICE_ID"=25 OR
"SI"."SERVICE_ID"=26 OR "SI"."SERVICE_ID"=27 OR "SI"."SERVICE_ID"=29) AND
"SI"."IDENTITY" IS NOT NULL)
|
|
|
|
| Re: Query tuning [message #470352 is a reply to message #470348] |
Mon, 09 August 2010 11:17 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
sathik wrote on Mon, 09 August 2010 17:01Explain plan without PL/SQL function is given below. there is no change in the cost.
SQL> explain plan for
2 select identity "VOIP_NUMBER",service_id "SERVICE_ID",customer_id "CUSTOMER_ID" from service_instances si
3 where si.service_id in (18,25,26,27,29) and identity is not null;
Explained.
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 527K| 10M| 11173 |
|* 1 | TABLE ACCESS FULL | SERVICE_INSTANCES | 527K| 10M| 11173 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SI"."SERVICE_ID"=18 OR "SI"."SERVICE_ID"=25 OR
"SI"."SERVICE_ID"=26 OR "SI"."SERVICE_ID"=27 OR "SI"."SERVICE_ID"=29) AND
"SI"."IDENTITY" IS NOT NULL)
I wasnt expecting a cost change, I was expecting a real time improvement.
Cost isnt what you think it ism its an internal Oracle metric. I suspect that if the P/LSQL block was redone as a series of case statements it may well be faster due to less context switching (and better information feeding the optimizer), but first we should ascertain the run time without it as opposed to the "cost" change.
Edit: Although its odd that its row count estimate is so out vs reality, are the stats ok?
[Updated on: Mon, 09 August 2010 11:28] Report message to a moderator |
|
|
|
| Re: Query tuning [message #470356 is a reply to message #470352] |
Mon, 09 August 2010 11:28 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
And cost never factors in function calls in the select part.
If you're selecting 10% of the rows and oracle thinks your selecting 33% of the rows then it's doing a bad estimate.
An index on (SERVICE_ID, IDENTITY) might help.
However I suspect that the function is the bigger problem - So do as Roachcoach asks and see how long it takes to run the SQL without it.
|
|
|
|
| Re: Query tuning [message #470368 is a reply to message #470352] |
Mon, 09 August 2010 12:11 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
Yes. You are correct.
The run time for the query (without PL/SQL) is 06:07 minutes. And it fetched 150287 rows.
But if run the query with PL/SQL function, it is taking 1:11 hours to fetch 47580 rows. Really it is a big difference.
Now can you pls suggest some idea, how can I reduce the runtime of the query,when I include PL/SQL function?
|
|
|
|
| Re: Query tuning [message #470370 is a reply to message #470368] |
Mon, 09 August 2010 12:14 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
We would need to know the code for: bbvoice_services.get_parameter_value and bbvoice_service.get_instanceid
|
|
|
|
| Re: Query tuning [message #470384 is a reply to message #470368] |
Mon, 09 August 2010 13:30 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
sathik wrote on Mon, 09 August 2010 18:11Yes. You are correct.
The run time for the query (without PL/SQL) is 06:07 minutes. And it fetched 150287 rows.
But if run the query with PL/SQL function, it is taking 1:11 hours to fetch 47580 rows. Really it is a big difference.
Now can you pls suggest some idea, how can I reduce the runtime of the query,when I include PL/SQL function?
Why is the row count dimishing when you add the function in when I dont see it as a part of your WHERE clause in the first post?
|
|
|
|
| Re: Query tuning [message #470598 is a reply to message #470384] |
Tue, 10 August 2010 05:04 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
evertime an insert query is firing againist this table.Thatswhy there are some difference in the count.
Please let me know how can I reduce the runtime of PL/SQL function?
|
|
|
|
| Re: Query tuning [message #470603 is a reply to message #470370] |
Tue, 10 August 2010 05:14 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
cookiemonster wrote on Mon, 09 August 2010 18:14We would need to know the code for: bbvoice_services.get_parameter_value and bbvoice_service.get_instanceid
|
|
|
|
|
|
| Re: Query tuning [message #470634 is a reply to message #470625] |
Tue, 10 August 2010 06:27 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Next time just post the relevant bits of code directly:
FUNCTION get_parameter_value(v_iid IN INTEGER
,v_name IN VARCHAR2)
RETURN VARCHAR2 IS
p_rec tactoss_service.par_rectype;
BEGIN
p_rec := tactoss_service.get_parameter(v_iid
,v_name);
RETURN p_rec.VALUE;
EXCEPTION
WHEN tactoss_service.parameter_not_set THEN
RETURN NULL;
/****** bug fix 4325 start*****/
WHEN OTHERS THEN
dbfl_error.raise_error('E'
,'Unhandled Exception:' ||
SQLERRM
,pk_name ||
'.get_parameter_value');
RETURN NULL;
/****** bug fix 4325 end*****/
END;
Which doesn't help at all really since all it returns is the result of another function call from a different package.
And get_instanceid isn't in that package.
I assume that those two functions at some point do a query on a table.
So you need to change your query to link directly to that table to get the same rows that the function returns.
You would then use a case statement to replicate the IF statements in get_status_fusion_works.
With the lack of info I really can't say more than that.
So give it a try and post back if you get stuck.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
