Home » RDBMS Server » Performance Tuning » Query Perfomance (Oracle 10g, Windows XP)
Query Perfomance [message #488894] |
Wed, 12 January 2011 01:27 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/a95ded9a53e67a9bd90e5bc7015ab474?s=64&d=mm&r=g) |
a_oracle
Messages: 98 Registered: November 2010
|
Member |
|
|
Hi guys,
I am new to Oracle. Need help on this one. The query below is not getting executed when run on local machine. but the same when run on client machine using a remote connection
gets executed. I have attached the explain plan for the same taken from remote machine as in local the same doesn't get executed so I am unable to take out an explain plan.
Also, if i run the queries one at a time they both get executed in local machine but using union all and running them as same query halts the execution.Please advice:
The query:
SELECT '1' AS REP_SQ,
SR.SOURCE_SYS_NM AS SOURCE_NM,
UR.REINVEST_SQ AS RECORD_ID,
UR.COLL_ACCT_ID AS COLL_ACCT,
'' AS TRADE_SEQ,
UR.SOURCE_CTPY_CD AS SOURCE_CODE,
NVL(UR.DEAL_CONTRACT_AMT, 0.00) AS TRADE_AMT,
NVL(UR.SEC_MKT_VAL_AMT, 0.00) AS COLL_AMT,
'' AS COMMENTS,
UR.SEC_ID AS SECURITY_ID,
UR.SEC_ISSUE_CTRY_CD AS SEC_COUNTRY_CODE,
UR.SEC_CCY_ID AS SEC_CCY_ID,
UR.SEC_TYPE_CD AS SEC_TYP_CD,
UR.SEC_INVEST_TYPE_CD AS SEC_INVST_TYP,
0.00 AS SHR_QUANTITY,
0.00 As CLEAN_PRICE,
UR.SEC_DIRTY_PRICE AS DIRTY_PRICE,
EM.ERROR_ID AS ERROR_ID,
EM.ERROR_SEVERITY AS ERROR_STATUS,
EM.ERROR_DESC_TXT AS ERROR_DESC
FROM CHECKPOINT_ERROR CE,
UNFORMATTED_REINVESTMENT UR,
ERROR_MASTER EM,
SOURCE SR
Where CE.CALENDAR_DT = '27-Aug-2008'
AND UR.CALENDAR_DT = '27-Aug-2008'
AND CE.RECORD_SQ = UR.REINVEST_SQ
AND CE.ERROR_ID = EM.ERROR_ID
AND SR.SOURCE_SQ = UR.SOURCE_SQ
AND CE.TABLE_NM_TXT = 'UNFORMATTED_REINVESTMENT'
AND UR.STATUS_CD <> 'X'
AND CE.ERROR_ID NOT IN (808, 809, 810, 811)
UNION ALL
SELECT '2' AS REP_SQ,
SR.SOURCE_SYS_NM AS SOURCE_NM,
UD.DEAL_SQ AS RECORD_ID,
UD.COLL_ACCT_ID AS COLL_ACCT,
UD.TRIPARTY_DEAL_SEQ_NUM AS TRADE_SEQ,
UD.SOURCE_CTPY_CD AS SOURCE_CODE,
NVL(UD.DEAL_CONTRACT_AMT, 0.00) AS TRADE_AMT,
NVL(UD.DEAL_COLL_AMT, 0.00) AS COLL_AMT,
UD.COLL_ACCT_NM As COMMENTS,
'' AS SECURITY_ID,
'' AS SEC_COUNTRY_CODE,
'' AS SEC_CCY_ID,
'' AS SEC_TYP_CD,
'' AS SEC_INVST_TYP,
0.00 AS SHR_QUANTITY,
0.00 As CLEAN_PRICE,
0.00 AS DIRTY_PRICE,
EM.ERROR_ID AS ERROR_ID,
EM.ERROR_SEVERITY AS ERROR_STATUS,
EM.ERROR_DESC_TXT AS ERROR_DESC
FROM CHECKPOINT_ERROR CE,
(SELECT RECORD_SQ, COUNT(*) AS REC_COUNT
FROM CHECKPOINT_ERROR
WHERE CALENDAR_DT = '15-Aug-2008'
AND TABLE_NM_TXT = 'UNFORMATTED_DEAL'
GROUP BY RECORD_SQ) CH,
UNFORMATTED_DEAL UD,
ERROR_MASTER EM,
SOURCE SR
WHERE CE.CALENDAR_DT = '15-Aug-2008'
AND UD.CALENDAR_DT = '15-Aug-2008'
AND CE.RECORD_SQ = CH.RECORD_SQ
AND CE.RECORD_SQ = UD.DEAL_SQ
AND CE.TABLE_NM_TXT = 'UNFORMATTED_DEAL'
AND CE.ERROR_ID = EM.ERROR_ID
AND CE.ERROR_ID IN
(DECODE(CH.REC_COUNT,
1,
CE.ERROR_ID,
DECODE(CE.ERROR_ID, 102, '', CE.ERROR_ID)))
AND CE.ERROR_ID NOT IN (803, 804, 805, 806)
AND SR.SOURCE_SQ = UD.SOURCE_SQ
AND UD.STATUS_CD <> 'X'
ORDER BY REP_SQ,
RECORD_ID,
ERROR_ID
Below are the table rows count:
select count(*) from checkpoint_error where calendar_dt = '27-aug-2008'; -- total rows = 92266
select count(*) from checkpoint_error; -- total rows = 635710
select count(*) from unformatted_reinvestment where calendar_dt = '27-aug-2008' -- total rows = 1598
select count(*) from unformatted_reinvestment; -- total rows = 52507
select count(*) from source; -- total rows = 56
select count(*) from error_master; -- total rows = 94
CM: added [code] tags, please do so yourself next time, see the orafaq forum guide if you're not sure how.
[Updated on: Wed, 12 January 2011 02:11] by Moderator Report message to a moderator
|
|
|
|
Re: Query Perfomance [message #488917 is a reply to message #488897] |
Wed, 12 January 2011 04:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Some points
1) Please read the forum guide and follow it next time, especially the part about formatting your post. [code] tags make things a lot easier to read. I've added them for you this time.
2) Explain plans should be generated using the following method:
SQL> explain plan for select 1 from dual;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1546270724
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
8 rows selected.
SQL>
and posted inline, not as an attachment, some people are unwilling or unable to download them.
3) You need to update the copy of the plan_table on your db.
4) Define local and client machines - do you mean two seperate DB's?
5) This:
AND CE.ERROR_ID IN
(DECODE(CH.REC_COUNT,
1,
CE.ERROR_ID,
DECODE(CE.ERROR_ID, 102, '', CE.ERROR_ID)))
Can be rewritten as:
AND (CE.ERROR_ID != 102 OR CH.REC_COUNT = 1)
6) You haven't supplied the index list or rowcount for unformatted_deal
7) You're comparing dates to strings, use to_date with the correct format mask.
8) An index on checkpoint_error (CALENDAR_DT, TABLE_NM_TXT, RECORD_SQ, ERROR_ID) may well improve things.
9) '' is equivalent to null, if you mean null say null.
|
|
|
|
Re: Query Perfomance [message #488933 is a reply to message #488894] |
Wed, 12 January 2011 06:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/a95ded9a53e67a9bd90e5bc7015ab474?s=64&d=mm&r=g) |
a_oracle
Messages: 98 Registered: November 2010
|
Member |
|
|
4) Define local and client machines - do you mean two seperate DB's?
-- >> No, they are the same DBs. Just run on different machines.
5) This:
AND CE.ERROR_ID IN
(DECODE(CH.REC_COUNT,
1,
CE.ERROR_ID,
DECODE(CE.ERROR_ID, 102, '', CE.ERROR_ID)))
Can be rewritten as:
AND (CE.ERROR_ID != 102 OR CH.REC_COUNT = 1)
-->> Error_id column is a nullable column
6) You haven't supplied the index list or rowcount for unformatted_deal
-->> select count(*) from unformatted_deal -- 44215
select count(*) from unformatted_deal where calendar_dt = '27-AUG-2008' -- 1247
indexes:
Normal UNFORMATTED_DEAL_NU1 CALENDAR_DT, SOURCE_SQ
Unique UNFORMATTED_DEAL_PK CALENDAR_DT, DEAL_SQ
Hi,
I made the changes as adviced by you but still the query seems to be not running. Any more advice if further anything can be done
|
|
|
Re: Query Perfomance [message #488935 is a reply to message #488933] |
Wed, 12 January 2011 06:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
4) What exactly is run on different machines? What tool are you using to connect to the DB? There is no way a query can work when run remotely but locally.
5)
AND (CE.ERROR_ID != 102 OR CH.REC_COUNT = 1)
AND CE.ERROR_ID IS NOT NULL
Post revised explain plan
|
|
|
|
|
|
|
Re: Query Perfomance [message #489082 is a reply to message #489068] |
Thu, 13 January 2011 03:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Not without seeing both explain plans. The one you've attached indicates that the query should be very fast. I want to see the explain from where it's running slow.
|
|
|
|
|
|
|
|
Re: Query Perfomance [message #489309 is a reply to message #489306] |
Fri, 14 January 2011 06:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Then the problem is no longer a performance one. Explain plan does not execute the query, it just generates the plan. Do you have some DBA's you can talk to about this?
|
|
|
|
Re: Query Perfomance [message #489319 is a reply to message #489310] |
Fri, 14 January 2011 07:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
It'd be a lot easier to advise if you explained clearly and concisly what your setup actually is. What is system to you? Why do you say remote database? Normally we only talk about remote database (as opposed to just database) if we have more than one database.
Lets be clear. When you run explain plan for a query oracle does not execute that query, it doesn't fetch the data, it doesn't access the tables. All it does is look at the stats for the tables and generate the plan it thinks it will use if you subsequently run the actual query. Consquently the amount of data makes no difference to the ability to generate an explain plan. The place you run it from should make no difference as the amount of data to be transfered is the command and the resulting plan, nothing more, we're talking tiny amounts of data.
If you're getting an ORA-03113 when trying to run one then that is most likely an oracle bug. Since you have DBA's you should consult them.
|
|
|
Goto Forum:
Current Time: Mon Sep 09 10:03:07 CDT 2024
|