Select query took too long to execute on one schema but very little time on another scema [message #676870] |
Wed, 24 July 2019 13:07 |
janakors
Messages: 232 Registered: September 2009
|
Senior Member |
|
|
Hi,
i have 11G R2 RAC of two nodes .we have two schemas on each node in RAC configuration. both schemas hosts same appl but AS per design they have diferent schema which serve diferent user set. now i need to run a select query almost on daily basis on both schemas to find out count of queued documents. now same query took very less (normal)time on schema1 and took longer time on schema2. schema1 have more user to serve and schema 2 have less user to serve. schema1 phy size is 6.4T and schema2 size is 4.8T. i am wondering what to investigate / check for this issue. i used to run the select query staright on sql prompt.
please guide why is there diference of execution time
sorry to admin if i put my question in wrong forum.
Regards,
[Updated on: Wed, 24 July 2019 13:10] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
Re: Select query took too long to execute on one schema but very little time on another scema [message #676898 is a reply to message #676892] |
Fri, 26 July 2019 02:39 |
janakors
Messages: 232 Registered: September 2009
|
Senior Member |
|
|
hi,
Execution plan for Fast query
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 61 | 478 (0)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 61 | | |
|* 2 | HASH JOIN | | 68 | 4148 | 478 (0)| 00:00:06 |
| 3 | NESTED LOOPS | | 72 | 3024 | 474 (0)| 00:00:06 |
| 4 | NESTED LOOPS | | 117 | 3024 | 474 (0)| 00:00:06 |
| 5 | NESTED LOOPS | | 117 | 2925 | 357 (0)| 00:00:05 |
| 6 | TABLE ACCESS BY INDEX ROWID| DOCUMENT_DISPATCH_TYPE | 117 | 1521 | 6 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | DOCUMENT_DISPATCH_TYPE_4 | 117 | | 3 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| DOCUMENT_DISPATCH | 1 | 12 | 3 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | DOCUMENT_DISPATCH_1_PK | 1 | | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | ORGANISATION_1_PK | 1 | | 0 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | ORGANISATION | 1 | 17 | 1 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | DC_INFO | 16 | 304 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DC"."ID"="DC_ID")
7 - access("DIS_TYPE"."DISPATCH_STATUS"='Queued')
8 - filter("DIS"."DOC_TO_INTERNAL_ID" IS NOT NULL)
9 - access("DIS_TYPE"."DOCUMENT_DISPATCH_ID"="DIS"."ID")
10 - access("ID"="DIS"."DOC_TO_INTERNAL_ID")
11 - filter("ONLINE_STATUS"='Online')
12 - filter("DC"."QUEUE_NAME" IS NOT NULL)
Execution Plan for slow query
Plan hash value: 211275883
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | | 78332 (1)| 00:15:40 |
| 1 | SORT AGGREGATE | | 1 | 58 | | | |
|* 2 | HASH JOIN | | 383K| 21M| | 78332 (1)| 00:15:40 |
|* 3 | TABLE ACCESS FULL | DC_INFO | 15 | 270 | | 3 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 383K| 14M| 22M| 78327 (1)| 00:15:40 |
| 5 | TABLE ACCESS BY INDEX ROWID| DOCUMENT_DISPATCH_TYPE | 977K| 11M| | 31141 (1)| 00:06:14 |
|* 6 | INDEX RANGE SCAN | DOCUMENT_DISPATCH_TYPE_4 | 977K| | | 3549 (1)| 00:00:43 |
|* 7 | HASH JOIN | | 4084K| 109M| | 38300 (1)| 00:07:40 |
|* 8 | TABLE ACCESS FULL | ORGANISATION | 16752 | 278K| | 176 (1)| 00:00:03 |
|* 9 | TABLE ACCESS FULL | DOCUMENT_DISPATCH | 10M| 109M| | 38067 (1)| 00:07:37 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DC"."ID"="DC_ID")
3 - filter("DC"."QUEUE_NAME" IS NOT NULL)
4 - access("DIS_TYPE"."DOCUMENT_DISPATCH_ID"="DIS"."ID")
6 - access("DIS_TYPE"."DISPATCH_STATUS"='Queued')
7 - access("ID"="DIS"."DOC_TO_INTERNAL_ID")
8 - filter("ONLINE_STATUS"='Online')
9 - filter("DIS"."DOC_TO_INTERNAL_ID" IS NOT NULL)
[
please guide how to diagnose issue in the above execution plan and then how can i optimize it
Regards
|
|
|
|
Re: Select query took too long to execute on one schema but very little time on another scema [message #676902 is a reply to message #676899] |
Fri, 26 July 2019 04:26 |
janakors
Messages: 232 Registered: September 2009
|
Senior Member |
|
|
Fast Query execution plan
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 61 | 478 (0)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 61 | | |
|* 2 | HASH JOIN | | 68 | 4148 | 478 (0)| 00:00:06 |
| 3 | NESTED LOOPS | | 72 | 3024 | 474 (0)| 00:00:06 |
| 4 | NESTED LOOPS | | 117 | 3024 | 474 (0)| 00:00:06 |
| 5 | NESTED LOOPS | | 117 | 2925 | 357 (0)| 00:00:05 |
| 6 | TABLE ACCESS BY INDEX ROWID| DOCUMENT_DISPATCH_TYPE | 117 | 1521 | 6 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | DOCUMENT_DISPATCH_TYPE_4 | 117 | | 3 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| DOCUMENT_DISPATCH | 1 | 12 | 3 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | DOCUMENT_DISPATCH_1_PK | 1 | | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | ORGANISATION_1_PK | 1 | | 0 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | ORGANISATION | 1 | 17 | 1 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | DC_INFO | 16 | 304 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DC"."ID"="DC_ID")
7 - access("DIS_TYPE"."DISPATCH_STATUS"='Queued')
8 - filter("DIS"."DOC_TO_INTERNAL_ID" IS NOT NULL)
9 - access("DIS_TYPE"."DOCUMENT_DISPATCH_ID"="DIS"."ID")
10 - access("ID"="DIS"."DOC_TO_INTERNAL_ID")
11 - filter("ONLINE_STATUS"='Online')
12 - filter("DC"."QUEUE_NAME" IS NOT NULL)
slow query Execution plan
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | | 78332 (1)| 00:15:40 |
| 1 | SORT AGGREGATE | | 1 | 58 | | | |
|* 2 | HASH JOIN | | 383K| 21M| | 78332 (1)| 00:15:40 |
|* 3 | TABLE ACCESS FULL | DC_INFO | 15 | 270 | | 3 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 383K| 14M| 22M| 78327 (1)| 00:15:40 |
| 5 | TABLE ACCESS BY INDEX ROWID| DOCUMENT_DISPATCH_TYPE | 977K| 11M| | 31141 (1)| 00:06:14 |
|* 6 | INDEX RANGE SCAN | DOCUMENT_DISPATCH_TYPE_4 | 977K| | | 3549 (1)| 00:00:43 |
|* 7 | HASH JOIN | | 4084K| 109M| | 38300 (1)| 00:07:40 |
|* 8 | TABLE ACCESS FULL | ORGANISATION | 16752 | 278K| | 176 (1)| 00:00:03 |
|* 9 | TABLE ACCESS FULL | DOCUMENT_DISPATCH | 10M| 109M| | 38067 (1)| 00:07:37 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DC"."ID"="DC_ID")
3 - filter("DC"."QUEUE_NAME" IS NOT NULL)
4 - access("DIS_TYPE"."DOCUMENT_DISPATCH_ID"="DIS"."ID")
6 - access("DIS_TYPE"."DISPATCH_STATUS"='Queued')
7 - access("ID"="DIS"."DOC_TO_INTERNAL_ID")
8 - filter("ONLINE_STATUS"='Online')
9 - filter("DIS"."DOC_TO_INTERNAL_ID" IS NOT NULL)
please explain in simple words why is the execution time diferent for the same query
please guide how to optimize it as for me it is the system which is doing(giving execution plan). how can i interfare ??
please also suggest some basic book regrading how to read this paln which i am already reading from web but not a book etc and then how to fix it please
detail answer is requested please
Regards
[Updated on: Fri, 26 July 2019 04:31] Report message to a moderator
|
|
|
|
|
|
|
|