Home » SQL & PL/SQL » SQL & PL/SQL » pointers (fighter)
pointers [message #347997] Mon, 15 September 2008 05:52 Go to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi,

I have a procedure which queries on a materialized view (MV) that has 12000 (12k) records.
The procedure taking nearly 3 mints to get all the rows.
How can we reduce this time.

PROCEDURE get_status_mv_combin_test( rc_status OUT tp_refcursor)
     AS
      BEGIN
     
     OPEN rc_status FOR
     
        SELECT  
 
 
        APP_NAME,	
        APP_ID,	
        APP_STATE_NAME,	
        APP_COST_CENTER	,
        APP_CLICKSTREAM,	
        APP_URL,	
        GNO	,
        GCO,	
        GSO	,
        COCKPIT_PAUSE,	
        COCKPIT_SORT,	
        CORP,	
        SSO_ENABLED	,
        PAUSE_REASON	,
        APP_SSO_PAIR	,
        APP_STATE_DT,
        NETWORK_STATUS,	
        SERVER_STATUS,	
        REST_STATUS	,
        YIELD_1HR,	
        YIELD_24HRS	,
        YIELD_7DAYS,	
        AVAIL_1HR	,
        AVAIL_24HRS	,
        AVAIL_7DAYS,	
        MV_DATE	,
        ALERT_DT,
        ALERT_TYPE,	
        ALERT_ID ,
        host_id,
        host_name,
        hoststatus,
        id,
        sla,
        transaction_name,
        location_name,
        t_status,
        response_time,
        transaction_id,
        --w_iter_date status_date,
        date_Time
  
  
                  FROM mv_app_tx_combin_status_test;
                   
 END get_status_mv_combin_test; 


The mv_app_tx_combin_status_test is created on different materialized views and tables.

I am new to this. Please help me.

Regards,
Pointers.
Re: pointers [message #347998 is a reply to message #347997] Mon, 15 September 2008 06:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That sounds very slow - is your application doing any processing as it retrieves each row.

could you check how long this query takes to run from the server:
SELECT count(nvl(app_name,'A')) FROM mv_app_tx_combin_status_test;
Re: needles [message #348002 is a reply to message #347997] Mon, 15 September 2008 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem is not with this procedure, it is with the way you use the resulting cursor.

By the way, useful title.

Regards
Michel
Re: pointers [message #348017 is a reply to message #347997] Mon, 15 September 2008 07:11 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
I have run the query
SELECT count(nvl(app_name,'A')) FROM mv_app_tx_combin_status_test

and the time took is 1.83 seconds.

Please advice how can I tune the above issue.

Regards,
Pointers.
Re: pointers [message #348019 is a reply to message #348017] Mon, 15 September 2008 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 15 September 2008 13:19
The problem is not with this procedure, it is with the way you use the resulting cursor.

By the way, useful title.

Regards
Michel


Re: pointers [message #348023 is a reply to message #348019] Mon, 15 September 2008 07:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's possible that you've got significant network problems, or that there is some significant processing going on in the application, but as Michel says - the problem is not with the database.
It can read all the roes in the table in under 2 seconds.
Re: pointers [message #348024 is a reply to message #347997] Mon, 15 September 2008 07:32 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Sorry to bother you..
Is there any possibility of delay in the materialized view. I mean, can we do anything in the materialized view to speed up it.

Re: pointers [message #348029 is a reply to message #348024] Mon, 15 September 2008 07:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You just ran a test that showed you that it took less than 2 seconds to access every row in the able.

If you want further proof that it is not the MV that is the problem, ust write a piece of pl/sql to run in SQL*Plus that will call that procedure and then fetch every row from the refcursor, and see how long it takes.

The materialized view is functionally just a able with some complex additions to it. If the MV is a Fast Refresh On Commit type, and there is a lot of activity going on in it while you are trying to query it, then that could cause a problem, but it would have to be a lot of activity.

can you show us the DDL to create the MV - everything up to the first line of the SELECT statement.
Re: pointers [message #348046 is a reply to message #347997] Mon, 15 September 2008 08:40 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Thanks for your time
Here is the ddl of the MV which is called by the procedure.
create materialized view MV_APP_TX_COMBIN_STATUS_TEST
refresh force on demand
 next null
as
select 

  mvacs.APP_NAME,	
  mvacs.APP_ID,	
  mvacs.APP_STATE_NAME,	
  mvacs.APP_COST_CENTER	,
  mvacs.APP_CLICKSTREAM,	
  mvacs.APP_URL,	
  mvacs.GNO	,
  mvacs.GCO,	
  mvacs.GSO	,
  mvacs.COCKPIT_PAUSE,	
  mvacs.COCKPIT_SORT,	
  mvacs.CORP,	
  mvacs.SSO_ENABLED	,
  mvacs.PAUSE_REASON	,
  mvacs.APP_SSO_PAIR	,
  mvacs.APP_STATE_DT,
  mvacs.NETWORK_STATUS,	
  mvacs.SERVER_STATUS,	
  mvacs.REST_STATUS	,
  mvacs.YIELD_1HR,	
  mvacs.YIELD_24HRS	,
  mvacs.YIELD_7DAYS,	
  mvacs.AVAIL_1HR	,
  mvacs.AVAIL_24HRS	,
  mvacs.AVAIL_7DAYS,	
  mvacs.MV_DATE	,
  mvacs.ALERT_DT,
  mvacs.ALERT_TYPE,	
  mvacs.ALERT_ID ,
  ts.host_id,
  ts.host_name,
  ts.hoststatus,
  ts.id,
  ts.sla,
  ts.transaction_name,
  ts.location_name,
  ts.t_status,
  ts.response_time,
  ts.transaction_id,
  --w_iter_date status_date,
  ts.date_Time
					   
					   
from 

  (select  
	APP_NAME,	
	APP_ID,	
	APP_STATE_NAME,	
	APP_COST_CENTER	,
	APP_CLICKSTREAM,	
	APP_URL,
	GNO	,
	GCO,	
	GSO	,
	COCKPIT_PAUSE,	
	COCKPIT_SORT,	
	CORP,	
	SSO_ENABLED	,
	PAUSE_REASON	,
	APP_SSO_PAIR	,
	APP_STATE_DT,
    	NETWORK_STATUS,	
	SERVER_STATUS,	
	REST_STATUS	,
    	YIELD_1HR,	
	YIELD_24HRS	,
	YIELD_7DAYS,	
	AVAIL_1HR	,
	AVAIL_24HRS	,
	AVAIL_7DAYS,	
	MV_DATE	,
	ALERT_DT,
	ALERT_TYPE,	
	ALERT_ID 

  from mv_app_current_state) mvacs,
      
      (  SELECT        grid.session_id,
                       grid.session_name,
                       grid.host_id,
                       grid.host_name,
                       grid.hoststatus,
                       raw_data.id,
                       grid.sla,
                       grid.transaction_name,
                       grid.location_name,
                       decode(raw_data.t_status,NULL,'-',raw_data.t_status) t_status,
                       raw_data.response_time,
                       grid.transaction_id,
                       --w_iter_date status_date,
                       raw_data.date_Time
                  FROM (SELECT app_id session_id,
                               record_id id,
                               to_char(ROUND(mv.TX_SLA,2),'90.9') sla,
                               mv.TRANSACTION_NAME,
                               mv.HOST_NAME host_name,
                               DECODE(mv.tx_status_id, -1, '-', DECODE(mv.tx_status_id,NULL, '-', 1, 'down',DECODE(sign(ROUND(        (mv.TX_SLA),2) - mv.tx_total_time),-1,'oos', 'normal'))) t_status,
                               mv.tx_total_time response_time,
                               mv.HOST_ID,
                               mv.TRANSACTION_ID,
                               mv.date_time
                          FROM v_tx_current_state mv
                        ) raw_data,
                       (SELECT  h.host_id,
                               a.app_id session_id,
                               a.app_name session_name,
                               NVL(h.host_short_name, h.host_name) host_name,
                               DECODE(hostapp_state_id,1,'G',2,'Y',3,'R',4,'B','B') hoststatus,
                               h.host_name location_name,
                               t.transaction_id, t.transaction_name,
                               to_char(ROUND((t.transaction_sla/1000),2),'999990.9') sla,-- Changed the format mask from 90.0 --to 999990.9 to accomodate higher values
                               p.pole_id
                          FROM t_transaction t,
                               v_abo a,
                               t_hostapp_map ham,
                               t_host h, t_pole p, t_zone z,t_hostapp_state_current hc
                         WHERE t.app_id = a.app_id
                           AND a.app_id = ham.hm_app_id
                           AND h.host_id = ham.hm_host_id
                           AND h.zone_id = z.zone_id
                           AND z.pole_id = p.pole_id
                           AND hc.host_id = h.host_id
                           AND hc.app_id=a.app_id
                           ORDER BY 3,session_name
                        ) grid
                 WHERE grid.host_id = raw_data.host_id (+)
                   AND grid.transaction_id = raw_data.transaction_id (+)
                  AND grid.session_id=raw_data.session_id(+)) ts
                  
                  where mvacs.app_id=ts.session_id(+)


I have just used plsql developer to test the procedure
the code is as follows
begin
  -- Call the procedure
  pkg_asb_trans_test.get_status_mv_combin_test(rc_status => :rc_status);
end;

This one only took nearly 3 minutes


Re: pointers [message #348057 is a reply to message #348046] Mon, 15 September 2008 08:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) The MV is refresh on Demand, so unless someone is rebuilding the whole MV every minute, it's very unlikely that it's MV rebuilds that are slowing you up.
Just to check, can you run
SELECT  to_char(last_refresh_date,'ddmmyyyy hh24:mi:ss') last_refresh
,to_char(sysdate,'ddmmyyyy hh24:mi:ss') sysdate
FROM dba_mviews
WHERE mview_name = 'MV_APP_TX_COMBIN_STATUS_TEST'



Are you sure that's all the code you ran, and that the code you posted at the start of this thread is all the code that is in that procedure? That code doesn't fetch any data from the cursor. It is extremely unlikely that that would take 3 minutes to execute.
Re: pointers [message #348058 is a reply to message #348046] Mon, 15 September 2008 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post:
set timing on
begin
  -- Call the procedure
  pkg_asb_trans_test.get_status_mv_combin_test(rc_status => :rc_status);
end;
/

Regards
Michel
Re: pointers [message #348064 is a reply to message #347997] Mon, 15 September 2008 09:15 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Please check this..
SQL> SELECT  to_char(last_refresh_date,'ddmmyyyy hh24:mi:ss') last_refresh
  2  ,to_char(sysdate,'ddmmyyyy hh24:mi:ss') sysdate1
  3  FROM dba_mviews
  4  WHERE mview_name = 'MV_APP_TX_COMBIN_STATUS_TEST';

LAST_REFRESH      SYSDATE1
----------------- -----------------
15092008 08:15:08 15092008 10:08:33


I have used "plsql developer test environment" to test the speed.
set timing on
begin
  -- Call the procedure
  pkg_asb_trans_test.get_status_mv_combin_test(rc_status => :rc_status);
end;
/


I have analyzed few tables nearly one hour back which are used by this MV using
analyze table tablename compute statistics
as somebody
suggested to do it.
Re: pointers [message #348065 is a reply to message #348064] Mon, 15 September 2008 09:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use PL/SQL Developer, use SQL*Plus and do it and you will see.

Regards
Michel
Re: pointers [message #348066 is a reply to message #348064] Mon, 15 September 2008 09:19 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try running the code Michel provided from Sql*Plus, ideally running oon the dataabse server. This will determine whether it;s PlSql developer or your network that are slowing things down.

Your query gets all the data from the MView, without specifying any Where clause - the statistics or lack thereof will make no difference to the execution time.
Previous Topic: Output Parameter in pipelined function
Next Topic: SQL Query help
Goto Forum:
  


Current Time: Sat Dec 10 20:18:43 CST 2016

Total time taken to generate the page: 0.07486 seconds