Home » RDBMS Server » Performance Tuning » insert statement runs slow
| insert statement runs slow [message #200064] |
Fri, 27 October 2006 13:56  |
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  |
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 |
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 |
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 #200719 is a reply to message #200629] |
Wed, 01 November 2006 01:27 |
scottwmackey
Messages: 515
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 |
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Apr 29 13:29:33 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
