Discussions
Categories
- 382.6K All Categories
- 2.1K Data
- 213 Big Data Appliance
- 1.9K Data Science
- 448.4K Databases
- 221.1K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 25 Multilingual Engine
- 541 MySQL Community Space
- 469 NoSQL Database
- 7.8K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 500 SQLcl
- 3.9K SQL Developer Data Modeler
- 186.3K SQL & PL/SQL
- 21.1K SQL Developer
- 293.9K Development
- 9 Developer Projects
- 130 Programming Languages
- 290.6K Development Tools
- 96 DevOps
- 3K QA/Testing
- 645.6K Java
- 24 Java Learning Subscription
- 36.9K Database Connectivity
- 151 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.9K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 146 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 201 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 294 LiveLabs
- 36 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 168 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 234 Portuguese
Performance issue - Oracle Index not working
Hi Gurus,
I'm facing performance issue in my application. we are having two trans tables and each tables having more than Millions of records.
From application layer we are fetching data this tables for summary screen. In that screen we are using default filters. after that filter we are getting nearly 40% of data.
So the indexing is not working. please give your suggestions .
@Thanks Advance
-VK
Answers
-
Hi,
1. You have not shared the query
2. You have not shared oracle db version
3. Have you verified the statistics of table / indexes -
4. Can you post the last good plan - used for this query
5. Can you compare the stats for both plans good and bad plan ? is this query entirely new one ?
6. what is value for Optimizer feature version ?
- Pavan Kumar N
-
My car is not working.
Please tell me how to make my car go.
-
Thanks for replay :d
-
We are using Oracle 11GR2 version
Please find below Explain Plan
Plan hash value: 1223010966 -------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 2931 | 24187 (2)| 00:04:51 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 3 | 2931 | 24187 (2)| 00:04:51 | | 3 | UNION-ALL | | | | | | | 4 | SORT AGGREGATE | | 1 | 8 | | | |* 5 | TABLE ACCESS BY INDEX ROWID | T_DLF_ATTACHDOCSI | 2 | 16 | 2 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | T_DLF_ATTACHDOCSI_IDX2 | 2 | | 1 (0)| 00:00:01 | |* 7 | VIEW | | 2 | 1954 | 24174 (2)| 00:04:51 | | 8 | COUNT | | | | | | | 9 | VIEW | | 2 | 1928 | 24174 (2)| 00:04:51 | | 10 | HASH UNIQUE | | 2 | 740 | 24174 (2)| 00:04:51 | | 11 | NESTED LOOPS | | | | | | | 12 | NESTED LOOPS | | 2 | 740 | 24173 (2)| 00:04:51 | |* 13 | HASH JOIN OUTER | | 2 | 726 | 24171 (2)| 00:04:51 | | 14 | NESTED LOOPS | | | | | | | 15 | NESTED LOOPS | | 2 | 552 | 4969 (1)| 00:01:00 | | 16 | NESTED LOOPS | | 345 | 63825 | 3933 (1)| 00:00:48 | | 17 | SORT UNIQUE | | 6926 | 90038 | 263 (2)| 00:00:04 | | 18 | VIEW | M_BLF_AD_BRN_VW | 6926 | 90038 | 263 (2)| 00:00:04 | | 19 | HASH UNIQUE | | 6926 | 293K| 263 (58)| 00:00:04 | | 20 | UNION-ALL | | | | | | |* 21 | HASH JOIN | | 1 | 46 | 113 (2)| 00:00:02 | |* 22 | TABLE ACCESS FULL | M_USER_BRANCH_MAP | 23 | 414 | 6 (0)| 00:00:01 | |* 23 | TABLE ACCESS FULL | M_ORGANIZATION | 583 | 16324 | 107 (2)| 00:00:02 | |* 24 | HASH JOIN | | 5793 | 243K| 113 (2)| 00:00:02 | |* 25 | TABLE ACCESS FULL | M_USER_BRANCH_MAP | 22 | 330 | 6 (0)| 00:00:01 | |* 26 | TABLE ACCESS FULL | M_ORGANIZATION | 4850 | 132K| 107 (2)| 00:00:02 | | 27 | NESTED LOOPS | | 1132 | 50940 | 36 (0)| 00:00:01 | | 28 | TABLE ACCESS BY INDEX ROWID | M_USER | 1 | 17 | 2 (0)| 00:00:01 | |* 29 | INDEX UNIQUE SCAN | M_USER_PK | 1 | | 1 (0)| 00:00:01 | |* 30 | TABLE ACCESS BY INDEX ROWID | M_ORGANIZATION | 1132 | 31696 | 34 (0)| 00:00:01 | |* 31 | INDEX RANGE SCAN | M_ORGANIZATION_BRN_USERID_IDX | 286 | | 1 (0)| 00:00:01 | | 32 | TABLE ACCESS BY INDEX ROWID | T_INVOICE_ORDR | 345 | 59340 | 3669 (1)| 00:00:45 | |* 33 | INDEX RANGE SCAN | T_INVOICE_ORDR_UK1 | 9453 | | 118 (0)| 00:00:02 | |* 34 | INDEX RANGE SCAN | DISC_REQ_TEMP_DET_BUY_REF_IDX | 1 | | 2 (0)| 00:00:01 | |* 35 | TABLE ACCESS BY INDEX ROWID | T_ORDR_REQUEST_MAPR_DETAILS | 1 | 91 | 3 (0)| 00:00:01 | | 36 | VIEW | | 2 | 174 | 19201 (2)| 00:03:51 | | 37 | UNION-ALL | | | | | | | 38 | NESTED LOOPS | | | | | | | 39 | NESTED LOOPS | | 1 | 225 | 16978 (2)| 00:03:24 | | 40 | NESTED LOOPS | | 1 | 198 | 16975 (2)| 00:03:24 | |* 41 | HASH JOIN RIGHT SEMI | | 120 | 15600 | 16615 (2)| 00:03:20 | | 42 | VIEW | M_BLF_AD_BRN_VW | 6926 | 90038 | 263 (2)| 00:00:04 | | 43 | UNION-ALL | | | | | | |* 44 | HASH JOIN | | 1 | 46 | 113 (2)| 00:00:02 | |* 45 | TABLE ACCESS FULL | M_USER_BRANCH_MAP | 23 | 414 | 6 (0)| 00:00:01 | |* 46 | TABLE ACCESS FULL | M_ORGANIZATION | 583 | 16324 | 107 (2)| 00:00:02 | |* 47 | HASH JOIN | | 5793 | 243K| 113 (2)| 00:00:02 | |* 48 | TABLE ACCESS FULL | M_USER_BRANCH_MAP | 22 | 330 | 6 (0)| 00:00:01 | |* 49 | TABLE ACCESS FULL | M_ORGANIZATION | 4850 | 132K| 107 (2)| 00:00:02 | | 50 | NESTED LOOPS | | 1132 | 50940 | 36 (0)| 00:00:01 | | 51 | TABLE ACCESS BY INDEX ROWID| M_USER | 1 | 17 | 2 (0)| 00:00:01 | |* 52 | INDEX UNIQUE SCAN | M_USER_PK | 1 | | 1 (0)| 00:00:01 | |* 53 | TABLE ACCESS BY INDEX ROWID| M_ORGANIZATION | 1132 | 31696 | 34 (0)| 00:00:01 | |* 54 | INDEX RANGE SCAN | M_ORGANIZATION_BRN_USERID_IDX | 286 | | 1 (0)| 00:00:01 | |* 55 | TABLE ACCESS FULL | T_INVC_REQUEST | 583K| 65M| 16345 (2)| 00:03:17 | | 56 | TABLE ACCESS BY INDEX ROWID | T_INVOICE_ORDR | 1 | 68 | 3 (0)| 00:00:01 | |* 57 | INDEX UNIQUE SCAN | T_INVOICE_ORDR_UK1 | 1 | | 2 (0)| 00:00:01 | |* 58 | INDEX RANGE SCAN | ORDR_REF_PYMT_MAP_PYMT_ID | 1 | | 2 (0)| 00:00:01 | |* 59 | TABLE ACCESS BY INDEX ROWID | T_ORDR_REF_INVC_MAPPING | 1 | 27 | 3 (0)| 00:00:01 | | 60 | NESTED LOOPS SEMI | | 1 | 224 | 2223 (1)| 00:00:27 | | 61 | NESTED LOOPS | | 1 | 211 | 2213 (1)| 00:00:27 | | 62 | NESTED LOOPS | | 1 | 184 | 2210 (1)| 00:00:27 | |* 63 | TABLE ACCESS FULL | T_INVC_REQUEST_TRANS | 73 | 8468 | 1991 (1)| 00:00:24 | | 64 | TABLE ACCESS BY INDEX ROWID | T_INVOICE_ORDR | 1 | 68 | 3 (0)| 00:00:01 | |* 65 | INDEX UNIQUE SCAN | T_INVOICE_ORDR_UK1 | 1 | | 2 (0)| 00:00:01 | |* 66 | TABLE ACCESS BY INDEX ROWID | T_ORDR_REF_INVC_MAPPING | 1 | 27 | 3 (0)| 00:00:01 | |* 67 | INDEX RANGE SCAN | ORDR_REF_PYMT_MAP_PYMT_ID | 1 | | 2 (0)| 00:00:01 | | 68 | VIEW | M_BLF_AD_BRN_VW | 1 | 13 | 10 (0)| 00:00:01 | | 69 | UNION ALL PUSHED PREDICATE | | | | | | | 70 | NESTED LOOPS | | 1 | 46 | 4 (0)| 00:00:01 | |* 71 | TABLE ACCESS BY INDEX ROWID | M_ORGANIZATION | 1 | 28 | 2 (0)| 00:00:01 | |* 72 | INDEX UNIQUE SCAN | M_ORGANIZATION_PK | 1 | | 1 (0)| 00:00:01 | |* 73 | TABLE ACCESS BY INDEX ROWID | M_USER_BRANCH_MAP | 1 | 18 | 2 (0)| 00:00:01 | |* 74 | INDEX RANGE SCAN | M_USER_BRANCH_MAP_UK | 1 | | 1 (0)| 00:00:01 | | 75 | NESTED LOOPS | | 1 | 43 | 3 (0)| 00:00:01 | |* 76 | TABLE ACCESS BY INDEX ROWID | M_ORGANIZATION | 1 | 28 | 2 (0)| 00:00:01 | |* 77 | INDEX UNIQUE SCAN | M_ORGANIZATION_PK | 1 | | 1 (0)| 00:00:01 | |* 78 | TABLE ACCESS BY INDEX ROWID | M_USER_BRANCH_MAP | 22 | 330 | 1 (0)| 00:00:01 | |* 79 | INDEX UNIQUE SCAN | M_USER_BRANCH_MAP_PK | 1 | | 0 (0)| 00:00:01 | | 80 | NESTED LOOPS | | 1 | 45 | 3 (0)| 00:00:01 | |* 81 | TABLE ACCESS BY INDEX ROWID | M_ORGANIZATION | 1 | 28 | 2 (0)| 00:00:01 | |* 82 | INDEX UNIQUE SCAN | M_ORGANIZATION_PK | 1 | | 1 (0)| 00:00:01 | |* 83 | TABLE ACCESS BY INDEX ROWID | M_USER | 1 | 17 | 1 (0)| 00:00:01 | |* 84 | INDEX UNIQUE SCAN | M_USER_PK | 1 | | 0 (0)| 00:00:01 | |* 85 | INDEX UNIQUE SCAN | M_CURRENCY_PK | 1 | | 0 (0)| 00:00:01 | | 86 | TABLE ACCESS BY INDEX ROWID | M_CURRENCY | 1 | 7 | 1 (0)| 00:00:01 | | 87 | SORT AGGREGATE | | 1 | 8 | | | |* 88 | TABLE ACCESS BY INDEX ROWID | T_DLF_ATTACHDOCSI | 2 | 16 | 2 (0)| 00:00:01 | |* 89 | INDEX RANGE SCAN | T_DLF_ATTACHDOCSI_IDX2 | 2 | | 1 (0)| 00:00:01 | |* 90 | VIEW | | 1 | 408 | 13 (8)| 00:00:01 | | 91 | COUNT | | | | | | | 92 | VIEW | | 1 | 395 | 13 (8)| 00:00:01 | | 93 | SORT ORDER BY | | 1 | 395 | 13 (8)| 00:00:01 | | 94 | VIEW | | 1 | 395 | 13 (8)| 00:00:01 | | 95 | HASH UNIQUE | | 1 | 547 | 13 (8)| 00:00:01 | | 96 | NESTED LOOPS SEMI | | 1 | 547 | 12 (0)| 00:00:01 | | 97 | NESTED LOOPS | | 1 | 534 | 2 (0)| 00:00:01 | | 98 | NESTED LOOPS | | 1 | 527 | 2 (0)| 00:00:01 | | 99 | TABLE ACCESS FULL | T_REORDERD_DISC_REQ_TMP_DTL | 1 | 167 | 2 (0)| 00:00:01 | | 100 | TABLE ACCESS BY INDEX ROWID | T_REORDERD_INVOICE_PYMT | 1 | 360 | 0 (0)| 00:00:01 | |*101 | INDEX RANGE SCAN | T_RECALL_INVOICE_PYMT_IDX02 | 1 | | 0 (0)| 00:00:01 | | 102 | TABLE ACCESS BY INDEX ROWID | M_CURRENCY | 1 | 7 | 0 (0)| 00:00:01 | |*103 | INDEX UNIQUE SCAN | M_CURRENCY_PK | 1 | | 0 (0)| 00:00:01 | | 104 | VIEW | M_BLF_AD_BRN_VW | 1 | 13 | 10 (0)| 00:00:01 | | 105 | UNION ALL PUSHED PREDICATE | | | | | | | 106 | NESTED LOOPS | | 1 | 46 | 4 (0)| 00:00:01 | |*107 | TABLE ACCESS BY INDEX ROWID | M_ORGANIZATION | 1 | 28 | 2 (0)| 00:00:01 | |*108 | INDEX UNIQUE SCAN | M_ORGANIZATION_PK | 1 | | 1 (0)| 00:00:01 | |*109 | TABLE ACCESS BY INDEX ROWID | M_USER_BRANCH_MAP | 1 | 18 | 2 (0)| 00:00:01 | |*110 | INDEX RANGE SCAN | M_USER_BRANCH_MAP_UK | 1 | | 1 (0)| 00:00:01 | | 111 | NESTED LOOPS | | 1 | 43 | 3 (0)| 00:00:01 | |*112 | TABLE ACCESS BY INDEX ROWID | M_ORGANIZATION | 1 | 28 | 2 (0)| 00:00:01 | |*113 | INDEX UNIQUE SCAN | M_ORGANIZATION_PK | 1 | | 1 (0)| 00:00:01 | |*114 | TABLE ACCESS BY INDEX ROWID | M_USER_BRANCH_MAP | 22 | 330 | 1 (0)| 00:00:01 | |*115 | INDEX UNIQUE SCAN | M_USER_BRANCH_MAP_PK | 1 | | 0 (0)| 00:00:01 | | 116 | NESTED LOOPS | | 1 | 45 | 3 (0)| 00:00:01 | |*117 | TABLE ACCESS BY INDEX ROWID | M_ORGANIZATION | 1 | 28 | 2 (0)| 00:00:01 | |*118 | INDEX UNIQUE SCAN | M_ORGANIZATION_PK | 1 | | 1 (0)| 00:00:01 | |*119 | TABLE ACCESS BY INDEX ROWID | M_USER | 1 | 17 | 1 (0)| 00:00:01 | |*120 | INDEX UNIQUE SCAN | M_USER_PK | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------------------------------
-
problems posted with NO details result in responses with no answers.
You are welcome.
If you provide needed details you may get a helpful response or two.
-
"11GR2" tells us nothing, really. We need the EXACT version you're using, such as 11.2.0.4, 11.2.0.3, etc. What I can tell you from the plan is you're using a 'where rownum <= ...' predicate, which still doesn't tell us much. I agree with Pavan that you need to post all of the information he requested if you want anyone to provide an accurate assessment.
David Fitzjarrell
-
could you add the query and the predicate section of the plan (which would tell us something about the access predicates and filter conditions used by the optimizer)? Could you also tell us something about the number of rows in the tables?
-
Hi,
1. Given a try from explain to understand 14 -35 are repeated (38-59 and semi ) with hash outer join (on result from 38-59 and nested loop join with lines 61-84)
2. Difference is it picked up |* 55 | TABLE ACCESS FULL | T_INVC_REQUEST | 583K| 65M| 16345 (2)| 00:03:17 |
-Instead of pickup index it went for FTS , you need to check predicates utilized which is opting for wrong access path
3 You can optimize the query instead of re-iterating the set of same fetching set of rows from the same tables.
Hope it helps
- Pavan Kumar N
-
Would be great to have the query, the predicates and the estimate and actual rows. (execute your query with /*+ gather_plan_statistics */ hint)
Point given by Kumar is you target.
-> cost of the operation is 16345 , more than 67% of the total cost !
jko
-
Let's say you have to get students from their school to their rival school for a football game 50 miles away. You have a hundred students, and ten volunteer drivers who can each take 5 students at 70 mph. Wouldn't a bus that can hold 100 students and go 55 mph be quicker?
When you do an index access, you have to get an index block, then get a data block. When you do a full table scan, you can skip getting the index blocks entirely, as well as get more blocks at a time with multi block reads. The exact volume where the fts gets faster varies based on the data distribution and query access, but 40% of the data is likely to favor the fts. The optimizer figures that out based on the statistics, and possibly some additional information it can acquire or you can give it. Sometimes the engine can transform the query.
If you are doing multiple full table scans, that might not be so good. The real trick to performance is to get rid of unnecessary work.
So:
First, you should apply the latest patches, improvements and bug fixes happen all the time.
Second, follow the directions in the link sol.beach gave you.
Help us help you.
