Categories

JAVA DATEBASE
Technology Network Community
Oracle Database
Fusion Middleware
Development Tools
Java
Desktop
Server & Storage Systems
Enterprise Management
Berkeley DB Family
Cloud Computing
Big Data
Business Intelligence
Architecture
Migration and Modernization
E-Business Suite
Siebel
PeopleSoft Enterprise
JD Edwards World
Industries
JD Edwards EnterpriseOne
User Productivity Kit Pro (UPK) and Tutor
Governance, Risk & Compliance (GRC)
Master Data Management (MDM)
Oracle CRM On Demand
On Demand: SaaS and Managed Applications
AutoVue Enterprise Visualization
Primavera
ATG
Agile PLM
Endeca Experience Management
Fusion Applications
Archived Forums

 



Tags

General Questions


SQL - performance


I have a query of below format. The second query is very slow. Why is that? I have attached the execution report with query column of so high value. What should be the remedy?  select * from tab1, tbl2.... where (join conditions) and tbl1.id in ('111') call     count       cpu    elapsed       disk      query    current        rows  ------ ---------- ----------  Parse        1      0.11       0.10          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        9      0.49       0.49          0      26310          0         112  ------ ---------- ----------  total       11      0.60       0.59          0      26310          0         112  select * from tab1, tbl2.... where (join conditions) and tbl1.id in ('111','222') call     count       cpu    elapsed       disk      query    current        rows  ------ ---------- ----------  Parse        1      0.14       0.12          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        1    148.85     145.91          0     172269          0           0  ------ ---------- ----------  total        3    148.99     146.04          0     172269          0           0

http://forums.oracle.com/forums/thread.jspa?messageID=1812597

Insufficient information. What is needed is Oracle version (4 digits) Optimizer being used Explain plans for both queries.  The first query will have the in (<single element>) rewritten in = <single element>  The second query might use the INLIST ITERATOR method, or the CONCAT method, but probably the INLIST ITERATOR. Explain plan would show that.  --  Sybrand Bakker Senior Oracle DBA

Version - 10.2.0.1 Opt Mode - ALL_ROWS  SELECT C.BUSINESS_UNIT,C.DEPTID,C.PROJECT_ID,C.ACCOUNT,C.ACCOUNTING_DT, C.INVOICE,C.JOURNAL_ID,B.ACTIVITY_ID ,SUM(C.FOREIGN_AMOUNT) FROM PS_BI_LINE B, PS_BI_ACCT_ENTRY C ,(SELECT * FROM PS_CB_DA_PROJECT P1                                         WHERE P1.EFFDT =                                              (SELECT MAX(C_ED1.EFFDT) FROM PS_CB_DA_PROJECT C_ED1                                              WHERE P1.BUSINESS_UNIT = C_ED1.BUSINESS_UNIT                                                AND P1.PROJECT_ID = C_ED1.PROJECT_ID                                                AND C_ED1.EFFDT <= SYSDATE) ) C1 WHERE B.BUSINESS_UNIT = C.BUSINESS_UNIT   AND B.INVOICE = C.INVOICE   AND B.LINE_SEQ_NUM = C.LINE_SEQ_NUM   AND C.BUSINESS_UNIT = C1.BUSINESS_UNIT    AND C.PROJECT_ID = C1.PROJECT_ID    AND ((B.ACTIVITY_ID IN ('ER','MN','MS')) OR (B.ACTIVITY_ID LIKE '20%'))   AND C.ACCOUNT BETWEEN '100000' AND '599999'   AND C.ACCOUNTING_DT BETWEEN '01-JUL-2007' AND  '31-JUL-2007' AND C.DEPTID IN('011047') GROUP BY C.BUSINESS_UNIT         ,C.DEPTID         ,C.PROJECT_ID         ,C.ACCOUNT         ,C.ACCOUNTING_DT         ,C.INVOICE       	,C.JOURNAL_ID         ,B.ACTIVITY_ID  call     count       cpu    elapsed       disk      query    current        rows  ------ ---------- ----------  Parse        1      0.11       0.10          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        9      0.49       0.49          0      26310          0         112  ------ ---------- ----------  total       11      0.60       0.59          0      26310          0         112  Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 55    Rows     Row Source Operation       112  HASH GROUP BY (cr=26310 pr=0 pw=0 time=493527 us)    6343   HASH JOIN OUTER (cr=26310 pr=0 pw=0 time=462811 us)    6343    NESTED LOOPS  (cr=19551 pr=0 pw=0 time=292374 us)    6399     TABLE ACCESS BY INDEX ROWID PS_BI_ACCT_ENTRY (cr=352 pr=0 pw=0 time=77291 us)    6399      INDEX SKIP SCAN PS_BI_ACCT_ENTRY_IDX_001 (cr=36 pr=0 pw=0 time=38855 us)(object id 144858)    6343     TABLE ACCESS BY INDEX ROWID PS_BI_LINE (cr=19199 pr=0 pw=0 time=206921 us)    6399      INDEX UNIQUE SCAN PS_BI_LINE (cr=12800 pr=0 pw=0 time=91103 us)(object id 146142)    6695    VIEW  (cr=6759 pr=0 pw=0 time=118771 us)    6695     NESTED LOOPS  (cr=6759 pr=0 pw=0 time=112066 us)    6695      VIEW  VW_SQ_1 (cr=62 pr=0 pw=0 time=58464 us)    6695       HASH GROUP BY (cr=62 pr=0 pw=0 time=51761 us)   11539        INDEX FAST FULL SCAN PS_CB_DA_PROJECT (cr=62 pr=0 pw=0 time=11710 us)(object id 156520)    6695      INDEX UNIQUE SCAN PS_CB_DA_PROJECT (cr=6697 pr=0 pw=0 time=46031 us)(object id 156520)  ************************************************************************************************  SELECT C.BUSINESS_UNIT,C.DEPTID,C.PROJECT_ID,C.ACCOUNT,C.ACCOUNTING_DT, C.INVOICE,C.JOURNAL_ID,B.ACTIVITY_ID ,SUM(C.FOREIGN_AMOUNT) FROM PS_BI_LINE B, PS_BI_ACCT_ENTRY C ,(SELECT * FROM PS_CB_DA_PROJECT P1                                         WHERE P1.EFFDT =                                              (SELECT MAX(C_ED1.EFFDT) FROM PS_CB_DA_PROJECT C_ED1                                              WHERE P1.BUSINESS_UNIT = C_ED1.BUSINESS_UNIT                                                AND P1.PROJECT_ID = C_ED1.PROJECT_ID                                                AND C_ED1.EFFDT <= SYSDATE) ) C1 WHERE B.BUSINESS_UNIT = C.BUSINESS_UNIT   AND B.INVOICE = C.INVOICE   AND B.LINE_SEQ_NUM = C.LINE_SEQ_NUM   AND C.BUSINESS_UNIT = C1.BUSINESS_UNIT    AND C.PROJECT_ID = C1.PROJECT_ID    AND ((B.ACTIVITY_ID IN ('ER','MN','MS')) OR (B.ACTIVITY_ID LIKE '20%'))   AND C.ACCOUNT BETWEEN '100000' AND '599999'   AND C.ACCOUNTING_DT BETWEEN '01-JUL-2007' AND  '31-JUL-2007' AND C.DEPTID IN('011047','234234') GROUP BY C.BUSINESS_UNIT         ,C.DEPTID         ,C.PROJECT_ID         ,C.ACCOUNT         ,C.ACCOUNTING_DT         ,C.INVOICE       	,C.JOURNAL_ID         ,B.ACTIVITY_ID  call     count       cpu    elapsed       disk      query    current        rows  ------ ---------- ----------  Parse        1      0.14       0.12          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        1    148.85     145.91          0     172269          0           0  ------ ---------- ----------  total        3    148.99     146.04          0     172269          0           0  Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 55    Rows     Row Source Operation         0  HASH GROUP BY (cr=0 pr=0 pw=0 time=307 us)    2558   NESTED LOOPS OUTER (cr=172252 pr=0 pw=0 time=145854614 us)    2559    NESTED LOOPS  (cr=8791 pr=0 pw=0 time=340850 us)    2566     INLIST ITERATOR  (cr=1090 pr=0 pw=0 time=169825 us)    2566      TABLE ACCESS BY INDEX ROWID PS_BI_ACCT_ENTRY (cr=1090 pr=0 pw=0 time=164684 us)    2566       INDEX RANGE SCAN PS_BI_ACCT_ENTRY_IDX_001 (cr=982 pr=0 pw=0 time=129735 us)(object id 144858)    2559     TABLE ACCESS BY INDEX ROWID PS_BI_LINE (cr=7701 pr=0 pw=0 time=163070 us)    2566      INDEX UNIQUE SCAN PS_BI_LINE (cr=5134 pr=0 pw=0 time=82195 us)(object id 146142)    2554    VIEW PUSHED PREDICATE  (cr=163464 pr=0 pw=0 time=145507408 us)    2554     HASH JOIN  (cr=163464 pr=0 pw=0 time=145496155 us)    7490      INDEX RANGE SCAN PS_CB_DA_PROJECT (cr=5118 pr=0 pw=0 time=92904 us)(object id 156520)    2554      VIEW  VW_SQ_1 (cr=158348 pr=0 pw=0 time=145064611 us) 17099030       SORT GROUP BY (cr=158348 pr=0 pw=0 time=122714921 us) 29472034        INDEX FAST FULL SCAN PS_CB_DA_PROJECT (cr=158360 pr=0 pw=0 time=172659 us)(object id 156520)  ************************************************************************************************

Any help? Thanks

The reason the performance is different is because the execution plans are not the same.   This is influenced by the number of values in the "in" list.  In the first query (fast one) you only have one value.  In the second, there are two.  The addition of the second value is changing the cardinality estimate enough to cause a plan change.  I would suggest checking the data distribution of C.DEPTID as well as if there is a histogram or not on this column.  Also check user_tab_col_statistics to see if the num_distict estimate is accurate.  If there is data skew, the dbms_stats.gather may need to be tweaked to get a representative sample.  One easy way to get a sanity check is to add a  /*+ gather_plan_statistics */   hint, execute the query, then execute  select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));  This will show you the estimated and actual rows returned by each row source.  If the estimate and actual are off, then the stats more likely are not representative.  Hope this helps, Greg Rahn http://structureddata.org


Related Links

Materialized View not refreshing
Memory allocation for SGA
Open database error
problem storing chinese characters
Oracle Data Guard 9i
Date and Time Format global for a database?
General
Java Pet Store
Bug in SQL*Plus 9.2.0.1.0 with 8.1.6 instance.
"shared memory realm" error -- cross-posted in "installation"
Oracle 9i new instance create scripts
Modify connect_timeout in tnsnames.ora ?
Oracle service won't start...
Oracle9i database
CKPT fails on startup with Error 220
cache configuration failure