Solved

Oracle: Is there any way to improve this function's performance?

Posted on 2013-02-06
11
401 Views
Last Modified: 2013-02-07
Hi EE,

i am a beginner with Oracle.

i created a function that is used in a query tool called: Oracle Discoverer.

my boss wants to now how many students were enrolled:

      by College, year

      by Pgm, year

so i created a function, and based on a flag executes the appropriate select

anyway the discoverer query is running a long time....

do you have any ideas how to speed it up?

our oracle dba is self taught too so if you have any ideas can you pls be very detailed
bec. we are learning together...

tx, sandra

create or replace function DMC_COUNT_ENROLL_BY_YEAR_PGM
         (PARM_pgm          varchar2
         ,PARM_College      varchar2
         ,PARM_Year         varchar2
         ,PARM_flag         varchar2
         ,PARM_yesNo        varchar2)

              return number is lngCount number;
 
begin
  lngCount := 0;

 
If PARM_flag = 'P' then
   SELECT COUNT (B.ID)
                 into lngCount

   FROM
     (
     SELECT distinct A.ID
           ,A.NAME
           ,A.program
           ,A.academic_year
           ,a.student_status
            from Academic_Study A
                     
         where
               A.PROGRAM = PARM_pgm              
                        And
               A.academic_YEAR = PARM_year
                        AND
               A.STUDENT_status in('AS', 'LA')
                                    AND
               DMC_IF_STUDENT_REG_COLLEG_TERM(A.PERSON_UID, A.ACADEMIC_PERIOD, A.COLLEGE) = PARM_yesNO

               
      ORDER BY A.ACADEMIC_YEAR, A.PROGRAM, A.NAME
      ) B;

 ELSE
 
        SELECT COUNT (B.ID)
                 into lngCount

        FROM
             (
               SELECT distinct A.ID
                     ,A.program
                     ,A.NAME
                     ,A.academic_year
                     ,a.student_status
               from Academic_Study A
                     
               where
                    A.academic_YEAR = PARM_year
                        AND
                    A.college = PARM_College
                        AND
                    A.STUDENT_status in('AS', 'LA')
                        AND
               DMC_IF_STUDENT_REG_COLLEG_TERM(A.PERSON_UID, A.ACADEMIC_PERIOD, A.COLLEGE) = PARM_yesNO

               ORDER BY A.ACADEMIC_YEAR, A.NAME
             ) B;
 
 END IF;    
     
  return lngCount;

end DMC_COUNT_ENROLL_BY_YEAR_PGM;
0
Comment
Question by:mytfein
  • 5
  • 4
  • 2
11 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 38861024
First, you don't need an order by on the inner select since the outer is doing a count.

Never used discoverer.

Do you have access to sqplus?

I would like to see an execution plan (you might need to dummy up data for your parameters):

explain plan for
SELECT distinct A.ID
                     ,A.program
... the rest of your select
/

Then show the plan:
select * from table(dbms_xplan.display);
0
 
LVL 34

Accepted Solution

by:
johnsone earned 250 total points
ID: 38861028
The first thing that I see is that you have an ORDER BY on your sub-query.  Your ultimate goal is a count, so the ORDER BY is doing a wasted sort.

The DISTINCT would also be causing a sort, so is that really necessary?  I would think that ID is unique, so the DISTINCT is again doing a wasted sort.

What are the indexes on the table?

I would think you would want a composite indexes on these:

(COLLEGE,ACADEMIC_YEAR, STUDENT_STATUS)
(PROGRAM, ACADEMIC_YEAR, STUDENT_STATUS)

Are you statistics up to date?

What is DMC_IF_STUDENT_REG_COLLEG_TERM?  Seems like you are calling another function which could be an additional problem.  Possibly create a function based index to have these values already computed.
0
 

Author Comment

by:mytfein
ID: 38861488
Hi Experts,

 tx so much for replying

1)  will eliminate the ORDER BY


2)
@ slight:
     i do not have access to pl sql, will supply the dba with sql and ask him to do explain

3) @johnsone
    a) am doing a distinct bec. the student has a row for each term he attended
      and i just want to know if he attended in that year
      so each row has academic_period  and academic_year
       for student 123  attended 2011   these terms: 201110, 201120, 201130
             so has 3 rows, yet i just want to count him once

      if i distinct on academic_year, will only count him once

     b) will ask dba about composite index

     c) can you provide info on "statistics up to date"?

     d) can you provide info on a "function based index"?

         i went here, yet am not understanding:

http://stackoverflow.com/questions/177240/how-to-use-a-function-based-index-on-a-column-that-contains-nulls-in-oracle-10

pls advise on c and d if you can, tx, sandra
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38861521
>> i do not have access to pl sql

pl/sql is Oracle's procedural language.  If you can connect to the database with Discoverer, you should have a username and password to connect to the database.

If you have this, you can use sqlplus or SQL Developer to connect and issue queries against the database.

This is all you need to create an execution plan.
0
 

Author Comment

by:mytfein
ID: 38861556
Hi Slight,

so i went to pl/developer and did this:

explain plan for
               SELECT distinct A.ID
                     ,A.program
                     ,A.NAME
                     ,A.academic_year
                     ,a.student_status
               from Academic_Study A
                     
               where
                    A.academic_YEAR = '2008'
                        AND
                    A.college = 'NR'
                        AND
                    A.STUDENT_status in('AS', 'LA')
                        AND
               DMC_IF_STUDENT_REG_COLLEG_TERM(A.PERSON_UID, A.ACADEMIC_PERIOD, A.COLLEGE) = 'Y'

/
select * from table(dbms_xplan.display);

and got this result which pasted into an excel file
     can you pls explain what this explain does?

i took out the order by for this explain

tx, s
2012-02-06-explain.xls
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38861595
Did you mean to close this question?  If you want to continue the discussion, it should really be open.  If so, just let me know and I'll reopen it for you.

Next time a flat text file will be good enough.

Also, I don't believe the explain plan will help with that the function does.

The docs talk about execution plans in decent detail so I'll not go into everything here.

Is Academic_Study a view?  If so, we will likely need the view text.

I would take a look at the following line of the plan:
TABLE ACCESS FULL         | MST_GENERAL_STUDENT

This is the biggest cost item in the plan.  Look at the predicate information to see what it is using and see if you think it should be using an index on the MST_GENERAL_STUDENT table.

This is where statistics come into play.  The docs talk about these as erll.
0
 

Author Comment

by:mytfein
ID: 38861643
Hi Slight,

tx for writing...

i did not intend to permanently close the conversation

a) the fact that you guys mentioned to remove the ORDER BY is quite a solution

b) anywhay, the other info is quite depp.... and maybe i should open a new post
    pls advise if you want me to open a new post

c) what do you mean by:
     " a flat file will be good enough"

d) i do not have pl/sql screen, i have oracle developer, buy i am more comfortable using
pl developer. so i right click on explain results, and chose copy to excel.

e) what are "docs"/statistics

f)academic study is a view
   we use "data warehouse" style tables that are not normalized built off of prod normalized tables

g) i think i understand that cost is the cpu time, and the item with the highest cpu time,
we need to look at where clause to see if we should create an index....

ok, so our dba will be in tomm, and will discuss with her...

tx so much, guys...
0
 
LVL 34

Expert Comment

by:johnsone
ID: 38861645
Based on that explain plan, as stated by slightwv, ACADEMIC_STUDY does appear to be a view.

The view definition as well as the indexes on the underlying tables of the view is going to be helpful.

You are most likely missing an index on MST_GENERAL_STUDENT.  However, not knowing which columns are in which underlying table, it is difficult to guess what that index should be.
0
 

Author Comment

by:mytfein
ID: 38861851
tx guys,

you've been a great help

will take this up with my colleague tomm. as she is out today...

tx again, s
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38861900
>>  pls advise if you want me to open a new post

If you wan help interpreting plan output then it should probably be a new question.

>>what do you mean by: " a flat file will be good enough"

Instead of uploading an Excel file, the output could have been a txt file saved in Notepad or just pasted into a "code" block here.

To make it more readable, I copied and pasted it into notepad..

>>what are "docs"/statistics

The "docs" refer to the online documentation.  Statistics are a pretty length conversation.  Basically Oracle collects statistics about your objects that the Cost Based Optimizer (CBO) then uses to choose what it thinks is the best way to execute a query.

Think of this:
You create a table with two rows.  Best execution plan is a FULL TABLE SCAN (FTS).

You then load 1 million rows.  If Oracle thinks there are still only two rows, it will choose a FTS.  If you update statistics on the table, Oracle will now know about the 1 million rows.

A good starting place in the docs is:
http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#PFGRF94712

>>i think i understand that cost is the cpu time

Incorrect.  COST is basically a made up number (the docs talk about this).  It is solely used by the CBO.  When tuning, cost should be used as a starting estimate.  The CBO can make mistakes when you know your system better than software can.

At times, a higher cost query can actually perform faster.

The definitive answer is tracing the SQL and diving into the weeds.  tkpof is a tool that helps make trace files better to read.  Again, doc diving on tracing and tkpof is the best here.
0
 

Author Comment

by:mytfein
ID: 38863935
tx slight for all your help....

sandra
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now