Home » RDBMS Server » Performance Tuning » How Can I Tune this Query (Oracle SE 10g R2 - 10.2.0.1.0)
How Can I Tune this Query [message #637709] Fri, 22 May 2015 00:34 Go to next message
mdsirajoddin
Messages: 20
Registered: July 2011
Location: Hyderabad
Junior Member

SELECT   MAX (fi_year) fiyear, MOD (m.period, 100) MONTH,
         TRUNC (m.period / 100) YEAR, pay_center_code pcode,
         SUM (NVL (m.pf_amout, 0)) emp_pf, SUM (NVL (m.comp_pf, 0)) comp_pf,
         SUM (NVL (m.vpf_amount, 0)) emp_vpf,
         MAX ((SELECT SUM (NVL (-pf_amout, 0))
                 FROM pf_monthly_trans_tab
                WHERE fi_year = m.fi_year
                  AND id_code = 'L'
                  AND TRUNC (period / 100) = TRUNC (m.period / 100)
                  AND MOD (period, 100) = MOD (m.period, 100)
                  AND pay_center_code = m.pay_center_code)
             ) non_emp_pf,
         MAX ((SELECT SUM (NVL (-comp_pf, 0))
                 FROM pf_monthly_trans_tab
                WHERE fi_year = m.fi_year
                  AND id_code = 'L'
                  AND TRUNC (period / 100) = TRUNC (m.period / 100)
                  AND MOD (period, 100) = MOD (m.period, 100)
                  AND pay_center_code = m.pay_center_code)
             ) non_comp_pf,
         MAX ((SELECT SUM (NVL (-vpf_amount, 0))
                 FROM pf_monthly_trans_tab
                WHERE fi_year = m.fi_year
                  AND id_code = 'L'
                  AND TRUNC (period / 100) = TRUNC (m.period / 100)
                  AND MOD (period, 100) = MOD (m.period, 100)
                  AND pay_center_code = m.pay_center_code)
             ) non_emp_vpf
    FROM pf_monthly_trans_tab m
   WHERE id_code <> 'L' AND m.fi_year = 20142015 AND m.pay_center_code = 'MPK'
GROUP BY TRUNC (m.period / 100), MOD (m.period, 100), pay_center_code
ORDER BY MAX (fi_year),
         TRUNC (m.period / 100),
         MOD (m.period, 100),
         pay_center_code


------------ table structure


CREATE TABLE PF_MONTHLY_TRANS_TAB
(
  FI_YEAR           VARCHAR2(8 BYTE),
  PERIOD            NUMBER(6),
  TRUST_CODE        VARCHAR2(4 BYTE),
  PAY_CENTER_CODE   VARCHAR2(5 BYTE),
  ID_CODE           CHAR(1 BYTE)                NOT NULL,
  PF_AC_CODE        VARCHAR2(8 BYTE),
  EMP_NO            VARCHAR2(8 BYTE),
  OLD_EMP_NO        VARCHAR2(8 BYTE),
  BASIC             NUMBER(8,2),
  OTHER_TOT_FOR_PF  NUMBER(8,2),
  REF_LOAN_INST     NUMBER(8,2),
  REF_LOAN_INT      NUMBER(8,2),
  PF_AMOUT          NUMBER(12,2),
  VPF_AMOUNT        NUMBER(12,2),
  COMP_PF           NUMBER(12,2),
  EPS               NUMBER(8,2),
  NCP_DAYS          NUMBER(8,2),
  FLAG              CHAR(1 BYTE),
  TRANS_TYPE        CHAR(1 BYTE)
)
TABLESPACE PAYROLL
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          72K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX PF_MONTHLY_TRANS ON PF_MONTHLY_TRANS_TAB
(PERIOD, FI_YEAR)
LOGGING
TABLESPACE PAYROLL
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT );





Can anyone please help me by showing some alternative for this query so that my application can work bit faster.


Thanks in Advance.
Re: How Can I Tune this Query [message #637710 is a reply to message #637709] Fri, 22 May 2015 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 65385
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.orafaq.com/forum/mv/msg/197402/637679/#msg_637679

Re: How Can I Tune this Query [message #637712 is a reply to message #637710] Fri, 22 May 2015 01:51 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
CREATE INDEX PF_MONTHLY_TRANS ON PF_MONTHLY_TRANS_TAB
(PERIOD, FI_YEAR)

So you have an index on (PERIOD, FI_YEAR)

WHERE id_code <> 'L'
AND m.fi_year = 20142015 
AND m.pay_center_code = 'MPK'

and you are querying on (FI_YEAR, PAY_CENTER_CODE)

If it was my SQL, the first thing I'd try is a new index on (FI_YEAR, PAY_CENTER_CODE).
RANGE partitioning on FI_YEAR would probably work even better, but it's a more fundamental change to your database. That's something you should discuss wth your DBA.

Ross Leishman
Re: How Can I Tune this Query [message #637713 is a reply to message #637709] Fri, 22 May 2015 02:04 Go to previous messageGo to next message
John Watson
Messages: 7261
Registered: January 2010
Location: Global Village
Senior Member
Without proper informtion, one cannot be sure. But....
First, you are projecting the results of three correlated subqueries. This is terrible. You need to rewrite the query to join to a single subquery, that you run ony once.
Second, you indes bad, you need to reverse the order of the key olumns. At the moment the only possibility for index access is a skip scan,
Third, you need to upgrade from 10.2.0.1. Later releases have much better uery re-write capabilities, which might do the work for you.
Re: How Can I Tune this Query [message #637714 is a reply to message #637713] Fri, 22 May 2015 02:48 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
I assume period is YYYYMM, in which case this:
AND TRUNC (period / 100) = TRUNC (m.period / 100)
                  AND MOD (period, 100) = MOD (m.period, 100)

Will give the same results as:
AND period = m.period 
Re: How Can I Tune this Query [message #637737 is a reply to message #637714] Fri, 22 May 2015 22:58 Go to previous messageGo to next message
mdsirajoddin
Messages: 20
Registered: July 2011
Location: Hyderabad
Junior Member

Thanks a Lot cookiemonster, you are really super analyzer.

as per your suggestion I modified it in my Oracle Report. before it was taking more than 1 minute. But now its only 3-4 Seconds.

Thanks a lot again..
Re: How Can I Tune this Query [message #637823 is a reply to message #637737] Tue, 26 May 2015 09:04 Go to previous message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
First rule of tuning - don't use functions (oracle own or custom) unless you really need them.
Previous Topic: Materialized view option (suggestion)
Next Topic: properly tune parallel parameters?
Goto Forum:
  


Current Time: Tue Feb 20 11:35:10 CST 2018

Total time taken to generate the page: 0.02790 seconds