Re: SQL running very slow
- From: Amit Saroha <eramitsaroha@xxxxxxxxx>
- To: Laurentiu Oprea <laurentiu.oprea06@xxxxxxxxx>
- Date: Mon, 25 Oct 2021 14:01:19 -0400
Thanks, Laurentiu,
Enclosed are the details of snapper and plan details from the test
environment. I can see it's all hash joins in the lower environment plan.
Why would Oracle change the plan in production is a mystery to me now.
Best Regards,
AMIT
On Mon, Oct 25, 2021 at 1:48 PM Laurentiu Oprea <laurentiu.oprea06@xxxxxxxxx>
wrote:
Hello Amit,
Most probable you cannot do anything to speed this query up while it is
running.
Is highly probable that Andy is correct, but just for completeness can you
download Tanel`s snapper from below link and run: SQL> snapper all 60 1
sid_of_you_session
and attach it.
https://github.com/tanelpoder/tpt-oracle/blob/master/snapper.sql
În lun., 25 oct. 2021 la 20:38, Amit Saroha <eramitsaroha@xxxxxxxxx> a
scris:
Thanks, Andy.
I can't cancel the query or kill the session to inject hints because it's
part of a batch and more processes has to complete after this query. Is it
possible to do some workaround?
Best Regards,
AMIT
On Mon, Oct 25, 2021 at 1:30 PM Andy Sayer <andysayer@xxxxxxxxx> wrote:
Hi Amit,
You are doing a nested loop full index scan. Either get it to do that as
a hash join or find it a better index which can be accessed with the join
predicates.
Thanks,
Andy
On Mon, 25 Oct 2021 at 18:15, Amit Saroha <eramitsaroha@xxxxxxxxx>
wrote:
Hi Listers,
Database version - 12.0.1
I have a query that is running from long but couldn't understand the
reason for the long run. I have enclosed the SQL monitoring and SQL plan
for reference.
In LongOps it shows its reading blocks but doesn't know why reading is
so slow. Could you please suggest what could cause such an issue?
Best Regards,
AMIT
snapper:
SPPPRD> snap sesstat 0 4311
[snap#sesstat]: From 2021-10-25 13:55:58 to 2021-10-25 13:55:59:
INST_ID SID NAME *VALUE
------- ----- ------------------------------ -----------
1 4,311 logical read bytes from cache 823,132,160
1 4,311 session logical reads 100,725
1 4,311 consistent gets 100,484
1 4,311 consistent gets from cache 100,484
1 4,311 consistent gets pin 100,484
1 4,311 consistent gets pin (fastpath) 100,480
1 4,311 no work - consistent read gets 99,977
1 4,311 buffer is pinned count 37
1 4,311 index scans kdiixs1 12
1 4,311 table fetch by rowid 11
1 4,311 heap block compress 1
1 4,311 HSC Heap Segment Block Changes 1
SPPPRD>
SPPQA> ora plan 3zkuqckakdbsb -adv
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1027559102
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Ord | Id | Operation | Name | Rows
| Bytes |TempSpc| Cost (%CPU)| Time | Blks | Q.B | Alias
|
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 8 | 0 | UPDATE STATEMENT | |
| | | 16438 (100)| | | |
|
| 7 | 1 | UPDATE | XXIM01T_SPP_PLAN_EXTRACT_TAB |
| | | | | | SEL$D9899398 |
|
| 6 | 2 | HASH JOIN ANTI | | 52106
| 2493K| 13M| 16438 (5)| 00:00:01 | 15773| |
|
| 1 | 3 | TABLE ACCESS FULL | XXIM01T_SPP_PLAN_EXTRACT_TAB |
468K| 8701K| | 4566 (3)| 00:00:01 | 16480| SEL$D9899398 | XXDB@UPD$1
|
| 5 | 4 | VIEW | VW_SQ_1 |
2425K| 69M| | 6646 (6)| 00:00:01 | 6286| SEL$7D4DB4AA |
VW_SQ_1@SEL$C998CFF2 |
| 4 | 5 | HASH JOIN | |
2425K| 60M| | 6646 (6)| 00:00:01 | 6286| SEL$7D4DB4AA |
|
| 2 | 6 | TABLE ACCESS FULL| MSC_TRADING_PARTNERS | 6
| 60 | | 64 (5)| 00:00:01 | 219| SEL$7D4DB4AA | MTP@SEL$1
|
| 3 | 7 | TABLE ACCESS FULL| XXINV01W_ITEMSS_DTLS_SPP360 |
1552K| 23M| | 6501 (5)| 00:00:01 | 22978| SEL$7D4DB4AA |
ITEMSS1@SEL$1 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
----------------------------------------------------------------------------------------------------------------
1 - SEL$D9899398
3 - SEL$D9899398 / XXDB@UPD$1
4 - SEL$7D4DB4AA / VW_SQ_1@SEL$C998CFF2
5 - SEL$7D4DB4AA
6 - SEL$7D4DB4AA / MTP@SEL$1
7 - SEL$7D4DB4AA / ITEMSS1@SEL$1
Outline Data
----------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPT_PARAM('_fast_full_scan_enabled' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_dsdir_usage_control' 0)
OPT_PARAM('_optimizer_adaptive_plans' 'false')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_gather_feedback' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$7D4DB4AA")
OUTLINE_LEAF(@"SEL$D9899398")
UNNEST(@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$C998CFF2")
OUTLINE(@"UPD$1")
FULL(@"SEL$D9899398" "XXDB"@"UPD$1")
NO_ACCESS(@"SEL$D9899398" "VW_SQ_1"@"SEL$C998CFF2")
LEADING(@"SEL$D9899398" "XXDB"@"UPD$1" "VW_SQ_1"@"SEL$C998CFF2")
USE_HASH(@"SEL$D9899398" "VW_SQ_1"@"SEL$C998CFF2")
FULL(@"SEL$7D4DB4AA" "MTP"@"SEL$1")
FULL(@"SEL$7D4DB4AA" "ITEMSS1"@"SEL$1")
LEADING(@"SEL$7D4DB4AA" "MTP"@"SEL$1" "ITEMSS1"@"SEL$1")
USE_HASH(@"SEL$7D4DB4AA" "ITEMSS1"@"SEL$1")
END_OUTLINE_DATA
*/
Other related posts: