Home » SQL & PL/SQL » SQL & PL/SQL » Select query took too long to execute on one schema but very little time on another scema (oracle 11 g r2 RAC of 2 nodes, solaris 10 and ASM)
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 Go to next message
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 #676871 is a reply to message #676870] Wed, 24 July 2019 13:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post EXPLAIN PLAN for both fast & slow SQL doing like below

EXPLAIN PLAN FOR <SQL statement>
SELECT * FROM table(dbms_xplan.display);
Re: Select query took too long to execute on one schema but very little time on another scema [message #676875 is a reply to message #676870] Wed, 24 July 2019 14:40 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
janakors wrote on Wed, 24 July 2019 13:07
Hi,
i have 11G R2 RAC of two nodes .we have two schemas on each node in RAC configuration.
No, you don't have two schemas "on each node".
You have two schemas in your one database.

RAC is one single database (data store - physical data files), with instances (memory and processes) on multiple nodes. Your schemas are within that one database, regardless of RAC or non-RAC, and regardless of how many RAC nodes may or may not be configured.

Re: Select query took too long to execute on one schema but very little time on another scema [message #676883 is a reply to message #676875] Thu, 25 July 2019 04:27 Go to previous messageGo to next message
janakors
Messages: 232
Registered: September 2009
Senior Member
Yes you are correct, they are instances of the same schema. so in this way i have 2 schema with 4 instances in my Database?

thank u for your time
Re: Select query took too long to execute on one schema but very little time on another scema [message #676886 is a reply to message #676883] Thu, 25 July 2019 06:54 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
janakors wrote on Thu, 25 July 2019 04:27
Yes you are correct, they are instances of the same schema. so in this way i have 2 schema with 4 instances in my Database?

thank u for your time
You have two schemas in your database.
You said you have a 2-node RAC, so you have two instances accessing your one database. The schemas are not "in" an instance. They are not duplicated across instances/nodes. They, along with everything else in the one, single database, are simply accessible from the two instances running on the two nodes.

I think you need to spend some time reviewing the Concepts manual, especially the section that discusses the Database Instance.
Re: Select query took too long to execute on one schema but very little time on another scema [message #676891 is a reply to message #676871] Thu, 25 July 2019 12:26 Go to previous messageGo to next message
janakors
Messages: 232
Registered: September 2009
Senior Member
hi
i finally got screen shot of 2 x execution plan in attatch file . when i saw it so there is huge diference in time of diferent parts of execution please help me in understnading the issue as it is a simple query.

i am looking forward for your detail answer. thank you


Regards

[Updated on: Thu, 25 July 2019 12:27]

Report message to a moderator

Re: Select query took too long to execute on one schema but very little time on another scema [message #676892 is a reply to message #676891] Thu, 25 July 2019 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I'm looking forward to actually seeing with my own eyes both well formatted EXPLAIN PLANS posted here
Re: Select query took too long to execute on one schema but very little time on another scema [message #676893 is a reply to message #676892] Thu, 25 July 2019 12:39 Go to previous messageGo to next message
janakors
Messages: 232
Registered: September 2009
Senior Member
tehy are both available in the att document as sceen shots of my system. when i try to copy it so the format get distorted so please check

thank u
Re: Select query took too long to execute on one schema but very little time on another scema [message #676894 is a reply to message #676892] Thu, 25 July 2019 12:44 Go to previous messageGo to next message
janakors
Messages: 232
Registered: September 2009
Senior Member
ok let me try to get it
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 Go to previous messageGo to next message
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 #676899 is a reply to message #676898] Fri, 26 July 2019 03:13 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You know as well as I do that is unreadable. Format it properly with [code] tags. You have done this correctly before.
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 Go to previous messageGo to next message
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

Re: Select query took too long to execute on one schema but very little time on another scema [message #676903 is a reply to message #676902] Fri, 26 July 2019 04:38 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Cone on, man. What about the query itself? A bit of information about the the table structures? Number of rows? Indexes?
Re: Select query took too long to execute on one schema but very little time on another scema [message #676904 is a reply to message #676903] Fri, 26 July 2019 04:45 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
And I should have added that for a book, you will not find better than this:

https://www.amazon.co.uk/Oracle-Performance-Tuning-Optimization-Cardinalities/dp/1501022695
Re: Select query took too long to execute on one schema but very little time on another scema [message #676905 is a reply to message #676903] Fri, 26 July 2019 04:50 Go to previous messageGo to next message
janakors
Messages: 232
Registered: September 2009
Senior Member
now how to fix it let me give you some more info about my system

we have two database home say db_home1 and DB_home2 and two grid home as well, similalry two enviornment (env) files x.env and y.env so when i run say x.env so the query is good to have but when i run y.env so it take time and this is with every query.

so please explain

thanks
Re: Select query took too long to execute on one schema but very little time on another scema [message #676907 is a reply to message #676903] Fri, 26 July 2019 06:40 Go to previous messageGo to next message
janakors
Messages: 232
Registered: September 2009
Senior Member
how to provide u info about indexes, rows.
query is

   select count (*) from user.docs_vw where doc_status like 'Queued';

what should i give you about table structure ? what actually u want? all tables ? relationship etc

please clear

thank u
Re: Select query took too long to execute on one schema but very little time on another scema [message #676911 is a reply to message #676907] Fri, 26 July 2019 07:42 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
how to provide u info about indexes, rows.

You can read Performances Tuning sticky, 4th post.

Previous Topic: How to use sysdate as a column header
Next Topic: Load multiline data in table using sql loader
Goto Forum:
  


Current Time: Thu Mar 28 16:13:34 CDT 2024