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 |
|
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 #637712 is a reply to message #637710] |
Fri, 22 May 2015 01:51 |
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 |
John Watson
Messages: 8938 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 |
cookiemonster
Messages: 13925 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:
|
|
|
Re: How Can I Tune this Query [message #637737 is a reply to message #637714] |
Fri, 22 May 2015 22:58 |
|
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..
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:33:41 CDT 2024
|