Home » RDBMS Server » Performance Tuning » insert statement runs slow
insert statement runs slow [message #200064] Fri, 27 October 2006 13:56 Go to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hi guys,

I know I have not prepared a small demo to demonstrate
my problem yet ,i will do it too shortly, meanwhile
i would like to request to see this
insert statement and give some apparent suggestion to
improve the performance.

Thanking you,



INSERT INTO w_glgroup
            (groupid, cignanoncigna, revcovclass, revcovtype, revexclufam,
             planrnc, market, mkt_carrier, carid, planshortname, planid,
             providertypeid, servicecatid, oldnew, nov_06e, nov_06r, dec_06e,
             dec_06r)
   SELECT  w_tblgroup_seq.NEXTVAL, grp.*
     FROM (SELECT   MAX (actac.cignanoncigna),
                    MAX (CASE actac.revcovclass
                            WHEN 'CAP'
                               THEN 'Cap Plan'
                            ELSE actac.revcovclass
                         END
                        ),
                    MAX (actac.revcovtype),
                    f_formatexclusionfamily (actac.revexclufam),
                    actac.planrnc, MAX (actac.groupname),
                    MAX
                       (CASE
                           WHEN actac.cignanoncigna IN ('C', 'H')
                              THEN CASE
                                     WHEN actac.revcovtype = 'FFS'
                                        THEN 'CIGNA FFS'
                                     ELSE NVL (actac.groupname,
                                               '  UNKNOWN MARKET'
                                              )
                                  END
                           ELSE actac.carname
                        END
                       ),
                    MAX (CASE
                            WHEN actac.cignanoncigna = 'C'
                               THEN NULL
                            ELSE actac.carid
                         END
                        ),
                    MAX (actac.planshortname), NVL (actac.planid, 0),
                    actac.providertypeid, actac.servicecatid, actac.oldnew,
                   NVL (SUM (  actac.expense
                              * f_allocate (actac.revstartdt,
                                            actac.revexpdt,
                                            TO_DATE ('30-OCT-06', 'DD-MON-YY'),
                                            TO_DATE ('26-NOV-06', 'DD-MON-YY')
                                           )
                             ),
                         0
                        ) nov_06e,
                    NVL (SUM (  actac.revenue
                              * f_allocate (actac.revstartdt,
                                            actac.revexpdt,
                                            TO_DATE ('30-OCT-06', 'DD-MON-YY'),
                                            TO_DATE ('26-NOV-06', 'DD-MON-YY')
                                           )
                             ),
                         0
                        ) nov_06r,
                    NVL (SUM (  actac.expense
                              * f_allocate (actac.revstartdt,
                                            actac.revexpdt,
                                            TO_DATE ('27-NOV-06', 'DD-MON-YY'),
                                            TO_DATE ('31-DEC-06', 'DD-MON-YY')
                                           )
                             ),
                         0
                        ) dec_06e,
                    NVL (SUM (  actac.revenue
                              * f_allocate (actac.revstartdt,
                                            actac.revexpdt,
                                            TO_DATE ('27-NOV-06', 'DD-MON-YY'),
                                            TO_DATE ('31-DEC-06', 'DD-MON-YY')
                                           )
                             ),
                         0
                        ) dec_06r
               FROM 
                      (SELECT 
                            auth.authorizationid,
                            splitauth.covclass revcovclass,
                            splitauth.covtype revcovtype,
                            splitauth.exclufam revexclufam, auth.planrnc,
                            auth.planid, auth.providertypeid,
                            auth.servicecatid, auth.servicetypecode,
                            auth.servicecode, auth.uomcode,
                            auth.planshortname, auth.carname, auth.carid,
                            splitauth.revstartdt, splitauth.revexpdt,
                            splitauth.revenue, splitauth.expense,
                            auth.groupname, splitauth.cignanoncigna,
                            auth.provparentid, auth.oldnew
                       FROM (SELECT /*+ PARALLEL(monthlyClose) PARALLEL_INDEX(monthlyClose) */
                                    monthlyclose.authorizationid,
                                    monthlyclose.planrnc, monthlyclose.planid,
                                    monthlyclose.providertypeid,
                                    monthlyclose.servicecatid,
                                    monthlyclose.servicetypecode,
                                    monthlyclose.servicecode,
                                    monthlyclose.uomcode,
                                    monthlyclose.planshortname,
                                    monthlyclose.carname, monthlyclose.carid,
                                    monthlyclose.groupname,
                                    monthlyclose.provparentid, 'Old' oldnew
                               FROM tblglmonthlyclose monthlyclose
                              WHERE monthlyclose.revcolumn IS NULL
                             UNION ALL
                             SELECT /*+ PARALLEL(scrub) PARALLEL_INDEX(scrub) */
                                    scrub.authorizationid, scrub.planrnc,
                                    scrub.planid, scrub.providertypeid,
                                    scrub.servicecatid, scrub.servicetypecode,
                                    scrub.servicecode, scrub.uomcode,
                                    scrub.planshortname, scrub.carname,
                                    scrub.carid, scrub.groupname,
                                    scrub.provparentid,
                                    CASE
                                       WHEN scrub.authorizationid <
                                                      (v_maxauth + 1
                                                      )
                                          THEN 'Old'
                                       ELSE 'New'
                                    END oldnew
                               FROM tblglscrub scrub) auth
                            JOIN
                            tblfinancialrptsplitauth splitauth
                            ON auth.authorizationid =
                                                     splitauth.authorizationid
                            ) actac
           GROUP BY actac.revexclufam,
                    actac.planrnc,
                    actac.providertypeid,
                    actac.servicecatid,
                    actac.oldnew,
                    actac.planid) grp;

Re: insert statement runs slow [message #200073 is a reply to message #200064] Fri, 27 October 2006 14:21 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try running the select statement by itself to see if the insert or the select is slow. Rather than spooling the rown to your screen - which will give inaccurate results - just count them.

select count(*) from (  your-query-goes-here  );


If the query is fast - the insert in the next place to look. Each index on the target table will obviously slow the insert down...


Re: insert statement runs slow [message #200079 is a reply to message #200073] Fri, 27 October 2006 15:29 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hey Andrew ! Appreciate your insight!

I ran query part and found error.

Here is what i see some strange error messages:


SQL> ed
Wrote file afiedt.buf

  1   SELECT count(*)
  2       FROM (SELECT   MAX (actac.cignanoncigna),
  3                      MAX (CASE actac.revcovclass
  4                              WHEN 'CAP'
  5                                 THEN 'Cap Plan'
  6                              ELSE actac.revcovclass
  7                           END
  8                          ),
  9                      MAX (actac.revcovtype),
 10                      f_formatexclusionfamily (actac.revexclufam),
 11                      actac.planrnc, MAX (actac.groupname),
 12                      MAX
 13                         (CASE
 14                             WHEN actac.cignanoncigna IN ('C', 'H')
 15                                THEN CASE
 16                                       WHEN actac.revcovtype = 'FFS'
 17                                          THEN 'CIGNA FFS'
 18                                       ELSE NVL (actac.groupname,
 19                                                 '  UNKNOWN MARKET'
 20                                                )
 21                                    END
 22                             ELSE actac.carname
 23                          END
 24                         ),
 25                      MAX (CASE
 26                              WHEN actac.cignanoncigna = 'C'
 27                                 THEN NULL
 28                              ELSE actac.carid
 29                           END
 30                          ),
 31                      MAX (actac.planshortname), NVL (actac.planid, 0),
 32                      actac.providertypeid, actac.servicecatid, actac.oldnew,
 33                     NVL (SUM (  actac.expense
 34                                * f_allocate (actac.revstartdt,
 35                                              actac.revexpdt,
 36                                              TO_DATE ('30-OCT-06', 'DD-MON-YY'),
 37                                              TO_DATE ('26-NOV-06', 'DD-MON-YY')
 38                                             )
 39                               ),
 40                           0
 41                          ) nov_06e,
 42                      NVL (SUM (  actac.revenue
 43                                * f_allocate (actac.revstartdt,
 44                                              actac.revexpdt,
 45                                              TO_DATE ('30-OCT-06', 'DD-MON-YY'),
 46                                              TO_DATE ('26-NOV-06', 'DD-MON-YY')
 47                                             )
 48                               ),
 49                           0
 50                          ) nov_06r,
 51                      NVL (SUM (  actac.expense
 52                                * f_allocate (actac.revstartdt,
 53                                              actac.revexpdt,
 54                                              TO_DATE ('27-NOV-06', 'DD-MON-YY'),
 55                                              TO_DATE ('31-DEC-06', 'DD-MON-YY')
 56                                             )
 57                               ),
 58                           0
 59                          ) dec_06e,
 60                      NVL (SUM (  actac.revenue
 61                                * f_allocate (actac.revstartdt,
 62                                              actac.revexpdt,
 63                                              TO_DATE ('27-NOV-06', 'DD-MON-YY'),
 64                                              TO_DATE ('31-DEC-06', 'DD-MON-YY')
 65                                             )
 66                               ),
 67                           0
 68                          ) dec_06r
 69                 FROM
 70                        (SELECT
 71                              auth.authorizationid,
 72                              splitauth.covclass revcovclass,
 73                              splitauth.covtype revcovtype,
 74                              splitauth.exclufam revexclufam, auth.planrnc,
 75                              auth.planid, auth.providertypeid,
 76                              auth.servicecatid, auth.servicetypecode,
 77                              auth.servicecode, auth.uomcode,
 78                              auth.planshortname, auth.carname, auth.carid,
 79                              splitauth.revstartdt, splitauth.revexpdt,
 80                              splitauth.revenue, splitauth.expense,
 81                              auth.groupname, splitauth.cignanoncigna,
 82                              auth.provparentid, auth.oldnew
 83                         FROM (SELECT /*+ PARALLEL(monthlyClose) PARALLEL_INDEX(monthlyClose) */
 84                                      monthlyclose.authorizationid,
 85                                      monthlyclose.planrnc, monthlyclose.planid,
 86                                      monthlyclose.providertypeid,
 87                                      monthlyclose.servicecatid,
 88                                      monthlyclose.servicetypecode,
 89                                      monthlyclose.servicecode,
 90                                      monthlyclose.uomcode,
 91                                      monthlyclose.planshortname,
 92                                      monthlyclose.carname, monthlyclose.carid,
 93                                      monthlyclose.groupname,
 94                                      monthlyclose.provparentid, 'Old' oldnew
 95                                 FROM tblglmonthlyclose monthlyclose
 96                                WHERE monthlyclose.revcolumn IS NULL
 97                               UNION ALL
 98                               SELECT /*+ PARALLEL(scrub) PARALLEL_INDEX(scrub) */
 99                                      scrub.authorizationid, scrub.planrnc,
100                                      scrub.planid, scrub.providertypeid,
101                                      scrub.servicecatid, scrub.servicetypecode,
102                                      scrub.servicecode, scrub.uomcode,
103                                      scrub.planshortname, scrub.carname,
104                                      scrub.carid, scrub.groupname,
105                                      scrub.provparentid,
106                                      CASE
107                                         WHEN scrub.authorizationid <
108                                                        ((select max(authorizationid) from tblglscrub) + 1
109                                                        )
110                                            THEN 'Old'
111                                         ELSE 'New'
112                                      END oldnew
113                                 FROM tblglscrub scrub) auth
114                              JOIN
115                              tblfinancialrptsplitauth splitauth
116                              ON auth.authorizationid =
117                                                       splitauth.authorizationid
118                              ) actac
119             GROUP BY actac.revexclufam,
120                      actac.planrnc,
121                      actac.providertypeid,
122                      actac.servicecatid,
123                      actac.oldnew,
124                     actac.planid);
.
SQL> /
 SELECT count(*)
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P012
ORA-12853: insufficient memory for PX buffers: current 16638K, max needed
964800K
ORA-04031: unable to allocate 4328 bytes of shared memory ("shared
pool","unknown object","sga heap(1,0)","PX msg pool")


Elapsed: 00:01:30.68
SQL>




Re: insert statement runs slow [message #200081 is a reply to message #200079] Fri, 27 October 2006 15:38 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
either your query is very inefficient / wasteful of memory or your SGA is too small. Try running a trace on the query and run it through tkprof. You could also try reducing the degree of parallelism. For now, try narrowing the where clause to return fewer rows...
Re: insert statement runs slow [message #200629 is a reply to message #200081] Tue, 31 October 2006 10:01 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hey Andrew Thanks for your reply,

The select statement is working fine when
i put it into insert statement, but
the speed is realy slow.

Re: insert statement runs slow [message #200719 is a reply to message #200629] Wed, 01 November 2006 01:27 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
You need to be specific regarding your terms. What is really slow? 1 seconds? 10 seconds? 10000 seconds? How many rows is it processing? Maybe it is going as fast as it is going to go. My guess is it is. You have this running in PARALLEL mode, which I hope you cleared with your higher ups, because it doesn't play very well with anybody else who may want to be doing something at the same time. But if you really want to know what is taking all the time. Run the query in parts. Begin with
SELECT /*+ PARALLEL(tblglmonthlyclose) PARALLEL_INDEX(tblglmonthlyclose) */
       COUNT(*)
FROM tblglmonthlyclose
WHERE revcolumn IS NULL

SELECT /*+ PARALLEL(tblglscrub) PARALLEL_INDEX(tblglscrub) */
       COUNT(*)
FROM tblglscrub

Look at the counts and the times. Then count the rows for the union of the two. If this query is really taking a while, my guess is that the counts will be in the millions. If I am right, then there is probably not a lot you are going to be able to do to speed up the query. The times will increase fairly significantly when you actually have to read the tables to do the groupings. The grouping you are doing will require some serious crunching. Unfortunately, that takes time. If the time is unacceptable, you will have to consider implementing some data warehousing/reporting solutions, which is someting completely different.
Re: insert statement runs slow [message #200870 is a reply to message #200719] Wed, 01 November 2006 13:42 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Many thanks to you, Scottwmackey!

I think you are putting me on path.

I will check all the criteria mentioned by you

and will get back to you.

your guess is right,it is process 17 million rows

but let me try your way and come back to you.

Thanks again.





Re: insert statement runs slow [message #201434 is a reply to message #200064] Sat, 04 November 2006 03:23 Go to previous message
Admin@Lightsql.Com
Messages: 36
Registered: November 2006
Member

I suggest you to look explain plan details of select statements. Review the query and make sure no Cartesian product. If need be get the tkprof, and then apply parallelism tricks etc.
Previous Topic: Inserts, update are taking long to execute. How to tune it?
Next Topic: Will Deleting rows from Table improve Performance?
Goto Forum:
  


Current Time: Sun Dec 04 12:53:35 CST 2016

Total time taken to generate the page: 0.13167 seconds