• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 989
  • Last Modified:

New to Oracle, need help speeding up a query

I've been given the assignment to "speed up and optimize these Views". Being very new to Oracle I'm kind of lost. I've posted 2 separate Views below. The 2nd View uses the first View.

Let me know if you see something that I shouldn't be doing or if you having any suggestions for improvement.

Thanks!


vExpenditureBudget
----------------------------------------------------------------------------------------------
SELECT
   B.ORG_NBR,
   B.ACCTNO,
   TO_DATE(SUBSTR(FISCL_YR,5,2) || '-01-' || SUBSTR(FISCL_YR,1,4),'MM-DD-RR') AS FiscalDate,
   CASE WHEN B.PCT = 'X' THEN ROUND(B.BUD_AMT * (B.PER1_BUD/100),2) ELSE B.PER1_BUD END AS BUD_AMT1,
   CASE WHEN B.PCT = 'X' THEN ROUND(B.BUD_AMT * (B.PER2_BUD/100),2) ELSE B.PER2_BUD END AS BUD_AMT2,
   CASE WHEN B.PCT = 'X' THEN ROUND(B.BUD_AMT * (B.PER3_BUD/100),2) ELSE B.PER3_BUD END AS BUD_AMT3,
   CASE WHEN B.PCT = 'X' THEN ROUND(B.BUD_AMT * (B.PER4_BUD/100),2) ELSE B.PER4_BUD END AS BUD_AMT4,
   CASE WHEN B.PCT = 'X' THEN ROUND(B.BUD_AMT * (B.PER5_BUD/100),2) ELSE B.PER5_BUD END AS BUD_AMT5,
   CASE WHEN B.PCT = 'X' THEN ROUND(B.BUD_AMT * (B.PER6_BUD/100),2) ELSE B.PER6_BUD END AS BUD_AMT6,
   CASE WHEN B.PCT = 'X' THEN ROUND(B.BUD_AMT * (B.PER7_BUD/100),2) ELSE B.PER7_BUD END AS BUD_AMT7,
   CASE WHEN B.PCT = 'X' THEN ROUND(B.BUD_AMT * (B.PER8_BUD/100),2) ELSE B.PER8_BUD END AS BUD_AMT8,
   CASE WHEN B.PCT = 'X' THEN ROUND(B.BUD_AMT * (B.PER9_BUD/100),2) ELSE B.PER9_BUD END AS BUD_AMT9,
   CASE WHEN B.PCT = 'X' THEN ROUND(B.BUD_AMT * (B.PER10_BUD/100),2) ELSE B.PER10_BUD END AS BUD_AMT10,
   CASE WHEN B.PCT = 'X' THEN ROUND(B.BUD_AMT * (B.PER11_BUD/100),2) ELSE B.PER11_BUD END AS BUD_AMT11,
   CASE WHEN B.PCT = 'X'
      THEN B.BUD_AMT
         - ROUND(B.BUD_AMT * (B.PER1_BUD/100),2) - ROUND(B.BUD_AMT * (B.PER2_BUD/100),2)  - ROUND(B.BUD_AMT * (B.PER3_BUD/100),2)
         - ROUND(B.BUD_AMT * (B.PER4_BUD/100),2) - ROUND(B.BUD_AMT * (B.PER5_BUD/100),2)  - ROUND(B.BUD_AMT * (B.PER6_BUD/100),2)
         - ROUND(B.BUD_AMT * (B.PER7_BUD/100),2) - ROUND(B.BUD_AMT * (B.PER8_BUD/100),2)  - ROUND(B.BUD_AMT * (B.PER9_BUD/100),2)
         - ROUND(B.BUD_AMT * (B.PER10_BUD/100),2) - ROUND(B.BUD_AMT * (B.PER11_BUD/100),2)
      ELSE B.PER12_BUD
   END AS BUD_AMT12,
   M.GRANTNBR,
   M.PROJ_CODE,
   CASE WHEN B.PER2_BUD = 0 AND B.PER3_BUD = 0 AND B.PER4_BUD = 0 AND B.PER5_BUD = 0 AND B.PER6_BUD = 0
         AND B.PER7_BUD = 0 AND B.PER8_BUD = 0 AND B.PER9_BUD = 0 AND B.PER10_BUD = 0 AND B.PER11_BUD = 0 AND B.PER12_BUD = 0
      THEN 1
      ELSE 0
   END AS UseAnnualBudget
FROM LINC.BUDGETDB_GLBUD B, LINC.BUDGETDB_GLMST M
WHERE
       M.ORG_NBR=B.ORG_NBR AND
         M.ACCTNO = B.ACCTNO
AND  ( B.MAINT <> 'D' AND B.FISCL_YR <> 0 AND M.MAINT <> 'D' )


vExpenditureBudgetMonthly (uses vExpenditureBudget)
----------------------------------------------------------------------------------------------
SELECT ORG_NBR, ACCTNO, ADD_MONTHS(FiscalDate, 0) AS FiscalDate, BUD_AMT1 AS "Budget Amount", UseAnnualBudget, GRANTNBR, PROJ_CODE FROM LINC.vExpenditureBudget UNION ALL
SELECT ORG_NBR, ACCTNO, ADD_MONTHS(FiscalDate, 1) AS FiscalDate, BUD_AMT2 AS "Budget Amount", 0 AS UseAnnualBudget, GRANTNBR, PROJ_CODE FROM LINC.vExpenditureBudget UNION ALL
SELECT ORG_NBR, ACCTNO, ADD_MONTHS(FiscalDate, 2) AS FiscalDate, BUD_AMT3 AS "Budget Amount", 0 AS UseAnnualBudget, GRANTNBR, PROJ_CODE FROM LINC.vExpenditureBudget UNION ALL
SELECT ORG_NBR, ACCTNO, ADD_MONTHS(FiscalDate, 3) AS FiscalDate, BUD_AMT4 AS "Budget Amount", 0 AS UseAnnualBudget, GRANTNBR, PROJ_CODE FROM LINC.vExpenditureBudget UNION ALL
SELECT ORG_NBR, ACCTNO, ADD_MONTHS(FiscalDate, 4) AS FiscalDate, BUD_AMT5 AS "Budget Amount", 0 AS UseAnnualBudget, GRANTNBR, PROJ_CODE FROM LINC.vExpenditureBudget UNION ALL
SELECT ORG_NBR, ACCTNO, ADD_MONTHS(FiscalDate, 5) AS FiscalDate, BUD_AMT6 AS "Budget Amount", 0 AS UseAnnualBudget, GRANTNBR, PROJ_CODE FROM LINC.vExpenditureBudget UNION ALL
SELECT ORG_NBR, ACCTNO, ADD_MONTHS(FiscalDate, 6) AS FiscalDate, BUD_AMT7 AS "Budget Amount", 0 AS UseAnnualBudget, GRANTNBR, PROJ_CODE FROM LINC.vExpenditureBudget UNION ALL
SELECT ORG_NBR, ACCTNO, ADD_MONTHS(FiscalDate, 7) AS FiscalDate, BUD_AMT8 AS "Budget Amount", 0 AS UseAnnualBudget, GRANTNBR, PROJ_CODE FROM LINC.vExpenditureBudget UNION ALL
SELECT ORG_NBR, ACCTNO, ADD_MONTHS(FiscalDate, 8) AS FiscalDate, BUD_AMT9 AS "Budget Amount", 0 AS UseAnnualBudget, GRANTNBR, PROJ_CODE FROM LINC.vExpenditureBudget UNION ALL
SELECT ORG_NBR, ACCTNO, ADD_MONTHS(FiscalDate, 9) AS FiscalDate, BUD_AMT10 AS "Budget Amount", 0 AS UseAnnualBudget, GRANTNBR, PROJ_CODE FROM LINC.vExpenditureBudget UNION ALL
SELECT ORG_NBR, ACCTNO, ADD_MONTHS(FiscalDate, 10) AS FiscalDate, BUD_AMT11 AS "Budget Amount", 0 AS UseAnnualBudget, GRANTNBR, PROJ_CODE FROM LINC.vExpenditureBudget UNION ALL
SELECT ORG_NBR, ACCTNO, ADD_MONTHS(FiscalDate, 11) AS FiscalDate, BUD_AMT12 AS "Budget Amount", 0 AS UseAnnualBudget, GRANTNBR, PROJ_CODE FROM LINC.vExpenditureBudget


0
stltodaycom
Asked:
stltodaycom
1 Solution
 
riazpkCommented:
Yoy left nothing for us here.....No information about number of rows , no information about indexes on those tables ..... no sample data....NOTHING....

please post here:

(1) explain plan of existing queries
(2) details of tables n indexes
0
 
mohammadzahidCommented:
I agree with riazpk that we don't have any information to go on and help you.

If you can submit the below items in addition to the items "riazpk" will definately give us some idea from where to start straching the surface of this performance problem.

Please give us the time it take to complete the sql statement and trace data that can be generated by setting the following parameter on in Oracle sqlplus.

What is the database version?
sql> SET AUTO TRACE ON
sql> Run the select statement.
Is this a new view or existing view that is now causing problem due to the increase in the data in the underlying tables after it was created?




0
 
pratikroyCommented:
Instead of creating your second view with the UNION ALL statements, try the following.

select A.ORG_NBR, A.ACCTNO, ADD_MONTHS(A.FiscalDate, B.RNO-1) AS FiscalDate,
       CASE WHEN B.RNO = 1 THEN A.BUD_AMT1
            WHEN B.RNO = 2 THEN A.BUD_AMT2
            WHEN B.RNO = 3 THEN A.BUD_AMT3
            WHEN B.RNO = 4 THEN A.BUD_AMT4
            WHEN B.RNO = 5 THEN A.BUD_AMT5
            WHEN B.RNO = 6 THEN A.BUD_AMT6
            WHEN B.RNO = 7 THEN A.BUD_AMT7
            WHEN B.RNO = 8 THEN A.BUD_AMT8
            WHEN B.RNO = 9 THEN A.BUD_AMT9
            WHEN B.RNO = 10 THEN A.BUD_AMT10
            WHEN B.RNO = 11 THEN A.BUD_AMT11
            WHEN B.RNO = 12 THEN A.BUD_AMT12 END AS "Budget Amount",
       CASE WHEN B.RNO = 1 THEN A.UseAnnualBudget ELSE 0 END AS UseAnnualBudget,
       A.GRANTNBR, A.PROJ_CODE
from
LINC.vExpenditureBudget A, (select rownum rno from
(select 1 from dual group by cube(1,1,1,1)) where rownum <= 12) B
/

This will save you from selecting from the first view 12 times for each month. Let me know if that helped.

If the above query does'nt help then answer the following questions :
1. How many records are there in the base tables (LINC.BUDGETDB_GLBUD and LINC.BUDGETDB_GLMST) ?
2. How many records are there in the view - LINC.vExpenditureBudget ?
3. In the second view (vExpenditureBudgetMonthly ), you will have exactly 12 times the number of records in LINC.vExpenditureBudget ?
4. The Explain Plan as mentioned by the experts above will definitely help in optimising the queries. Post the explain plans of both the SQLs. And preferably the explain plan of my SQL above also, so that we could compare.



0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
stltodaycomAuthor Commented:
Thanks for everyone's response.
Answers or statements to questions that have been asked:

1) explain plan of existing queries
 Do you want me to copy the details of the Execution plans? They tend to be pretty long.
 Would the cost be of help?

2) details of tables n indexes
In Table LINC.BUDGETDB_GLBUD the only Indexed field is GLB_DTIME
In Table LINC.BUDGETDB_GLMST the only Indexed field is GLB_DTIME

3) What is the database version
Oracle 9i Release 9.2.0.1.0

4) Is this a new view or existing view that is now causing problem due to the increase in the data in the underlying tables after it was created?
They are both new Views

5) How many records are there in the base tables (LINC.BUDGETDB_GLBUD and LINC.BUDGETDB_GLMST)?
LINC.BUDGETDB_GLBUD:45966
LINC.BUDGETDB_GLMST:25034

6) How many records are there in the view - LINC.vExpenditureBudget?
61414

7) In the second view (vExpenditureBudgetMonthly ), you will have exactly 12 times the number of records in LINC.vExpenditureBudget ?
It returned 181,837 records

8) The Explain Plan as mentioned by the experts above will definitely help in optimising the queries. Post the explain plans of both the SQLs. And preferably the explain plan of my SQL above also, so that we could compare.
Your View seems to run in the same amount of time as the original View, but when I select the 'Explain' plan I get an error: "The EXPLAIN PLAN cannot be viewed because the cached plan for this cursor is malformed. Contact Oracle support for assistance."

I've been using SQL Scratchpad in Oracle Enterprise Manager to run these queries, is there a better tool to use in Oracle Enterprise Manager? I apologize for so little information, I'm very new to Oracle plus much of my information is coming from a client.

Thanks!

0
 
riazpkCommented:
Yes we need explain plans ... doesn't matters how long it is ...

How you gathered statistics (paste here the exact command you used) ?

0
 
riazpkCommented:
The most  bad thing, i think, is the vExpenditureBudgetMonthly view definition ...12 Full Table Scans...ohh my GOD :(

Try the following (as  pratikroy suggested):

create view vExpenditureBudgetMonthly
as
select A.ORG_NBR, A.ACCTNO, ADD_MONTHS(A.FiscalDate, B.RNO-1) AS FiscalDate,
       CASE WHEN B.RNO = 1 THEN A.BUD_AMT1
            WHEN B.RNO = 2 THEN A.BUD_AMT2
            WHEN B.RNO = 3 THEN A.BUD_AMT3
            WHEN B.RNO = 4 THEN A.BUD_AMT4
            WHEN B.RNO = 5 THEN A.BUD_AMT5
            WHEN B.RNO = 6 THEN A.BUD_AMT6
            WHEN B.RNO = 7 THEN A.BUD_AMT7
            WHEN B.RNO = 8 THEN A.BUD_AMT8
            WHEN B.RNO = 9 THEN A.BUD_AMT9
            WHEN B.RNO = 10 THEN A.BUD_AMT10
            WHEN B.RNO = 11 THEN A.BUD_AMT11
            WHEN B.RNO = 12 THEN A.BUD_AMT12 END AS "Budget Amount",
       CASE WHEN B.RNO = 1 THEN A.UseAnnualBudget ELSE 0 END AS UseAnnualBudget,
       A.GRANTNBR, A.PROJ_CODE
from
LINC.vExpenditureBudget A, (select rownum rno from
(select 1 from dual group by cube(1,1,1,1)) where rownum <= 12) B
/


Then run your query and paste here the excution plan .....
0
 
riazpkCommented:
Please finalize this question
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now