Re: cursor: pin S wait on X querying data dictionary
Date: Mon, 2 Jul 2018 07:41:53 +0000
Message-ID: <MM1P123MB0842ED49FD0745C74B4D77A6A5430_at_MM1P123MB0842.GBRP123.PROD.OUTLOOK.COM>
Answering the question about parallelism and query coordinators.
Anything goes when you get close enough to internal technology - the query is NOT running parallel, but a call to the containters() mechanism - which is hidden inside the view definition for all_col_comments - is presumably invoking messaging through PX slaves between PDBs as it's general mechanism. (In the same way that a query involving a GV$ view will call cross-instance through PX slave. The thing that surprises me is that all_col_comments uses containers() when all_tab_cols doesn't - I would have expected symmetry: both, or neither.
I would emulate the query from SQL*Plus - declare a couple of VARIABLEs for the bind values, set serveroutput off, set a big linesize and page size, add in a comment that makes the text easy to find, and isolates it from all the executions from the application, set statistics_level = all, enable extended tracing with waits=true, then run the query and:
select * from dbms_xplan.display_cursor(null,null,'allstats cost parallel partition')); select * from v$pq_tqstat;
If you're licensed for the diagnostic pack use dbms_sqltune. report_sql_monitor to get a full report of the query - which could be most helpful for the parallel parts - in particular how the parallel execution slaves behaved.
Find out the SQL_ID for your version of the query and pull everything from v$active_session_history (from all instances) for that SQL_ID. A particularly interesting detail would be the IN_PARSE, IN_HARD_PARSE flags - does the query spend all it's time parsing.
I can't tell you exactly what to look for because I don't know - beyond the idea that the problem probably relates to what's going on with that containers() call.
Regards
Jonathan Lewis
From: Chris Stephens <cstephens16_at_gmail.com> Sent: 01 July 2018 15:53:46
To: Jonathan Lewis
Cc: oracle-l
Subject: Re: cursor: pin S wait on X querying data dictionary
Here are the execution plans and counts by qc_session_id. Why would oracle compute auto DP of 1 and fire up a PQ coordinator?
1 select qc_sess, count(*) from
2 (select case qc_session_id when null then 'NULL' else 'NOT NULL' end qc_sess
3 from dba_hist_active_sess_history
4 where sample_time >= TIMESTAMP'2018-06-28 18:45:00'
5 and sample_time <= TIMESTAMP'2018-06-29 00:00:00'
6 and sql_id = '0rz9dn5v75czn')
7* group by qc_sess
SQL> /
QC_SESS COUNT(*)
-------- ----------
NOT NULL 37535
However, both plans in AWR are adaptive. Not real sure about the automatic DOP note either.
Plan hash value: 2901843923
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
| 0 | SELECT STATEMENT | | | | 31 (100)| | | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 39 | 3 (0)| 00:00:01 | | | | | |
| 2 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 39 | 3 (0)| 00:00:01 | | | | | |
| 4 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 39 | 3 (0)| 00:00:01 | | | | | |
| 6 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 39 | 3 (0)| 00:00:01 | | | | | |
| 8 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 39 | 3 (0)| 00:00:01 | | | | | |
| 10 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 | | | | | |
| 11 | SORT ORDER BY | | 1 | 2434 | 31 (7)| 00:00:01 | | | | | |
| 12 | FILTER | | | | | | | | | | |
| 13 | NESTED LOOPS OUTER | | 1 | 2434 | 15 (7)| 00:00:01 | | | | | |
| 14 | HASH JOIN OUTER | | 1 | 2429 | 14 (8)| 00:00:01 | | | | | |
| 15 | HASH JOIN OUTER | | 1 | 229 | 13 (0)| 00:00:01 | | | | | |
| 16 | HASH JOIN | | 1 | 225 | 12 (0)| 00:00:01 | | | | | |
| 17 | NESTED LOOPS OUTER | | 1 | 201 | 11 (0)| 00:00:01 | | | | | |
| 18 | NESTED LOOPS OUTER | | 1 | 191 | 10 (0)| 00:00:01 | | | | | |
| 19 | NESTED LOOPS OUTER | | 1 | 148 | 6 (0)| 00:00:01 | | | | | |
| 20 | NESTED LOOPS | | 1 | 120 | 5 (0)| 00:00:01 | | | | | |
| 21 | NESTED LOOPS | | 1 | 73 | 4 (0)| 00:00:01 | | | | | |
| 22 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 18 | 1 (0)| 00:00:01 | | | | | |
| 23 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | | | |
| 24 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 1 | 55 | 3 (0)| 00:00:01 | | | | | |
| 25 | INDEX RANGE SCAN | I_OBJ5 | 1 | | 2 (0)| 00:00:01 | | | | | |
| 26 | TABLE ACCESS CLUSTER | COL$ | 1 | 47 | 1 (0)| 00:00:01 | | | | | |
| 27 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| | | | | | |
| 28 | TABLE ACCESS CLUSTER | COLTYPE$ | 1 | 28 | 1 (0)| 00:00:01 | | | | | |
| 29 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 43 | 4 (0)| 00:00:01 | | | | | |
| 30 | INDEX RANGE SCAN | I_OBJ3 | 23 | | 1 (0)| 00:00:01 | | | | | |
| 31 | INDEX RANGE SCAN | I_HH_OBJ#_INTCOL# | 1 | 10 | 1 (0)| 00:00:01 | | | | | |
| 32 | INDEX FULL SCAN | I_USER2 | 1 | 24 | 1 (0)| 00:00:01 | | | | | |
| 33 | INDEX FULL SCAN | I_USER2 | 1 | 4 | 1 (0)| 00:00:01 | | | | | |
| 34 | VIEW | ALL_COL_COMMENTS | 1 | 2200 | 1 (100)| 00:00:01 | | | | | |
| 35 | FILTER | | | | | | | | | | |
| 36 | PX COORDINATOR | | | | | | | | | | |
| 37 | PX SEND QC (RANDOM) | :TQ10000 | 2 | 4478 | 1 (100)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 38 | PX PARTITION LIST ALL | | 2 | 4478 | 1 (100)| 00:00:01 | 1 | 2 | Q1,00 | PCWC | |
| 39 | EXTENDED DATA LINK FULL | INT$DBA_COL_COMMENTS | 2 | 4478 | 1 (100)| 00:00:01 | | | Q1,00 | PCWP | |
| 40 | NESTED LOOPS SEMI | | 1 | 15 | 2 (0)| 00:00:01 | | | | | |
| 41 | FIXED TABLE FULL | X$KZSRO | 2 | 12 | 0 (0)| | | | | | |
| 42 | INDEX RANGE SCAN | I_OBJAUTH2 | 1 | 9 | 1 (0)| 00:00:01 | | | | | |
| 43 | TABLE ACCESS CLUSTER | TAB$ | 1 | 5 | 1 (0)| 00:00:01 | | | | | |
| 44 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| | | | | | |
| 45 | TABLE ACCESS CLUSTER | TAB$ | 1 | 13 | 2 (0)| 00:00:01 | | | | | |
| 46 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 | | | | | |
| 47 | NESTED LOOPS SEMI | | 1 | 15 | 2 (0)| 00:00:01 | | | | | |
| 48 | FIXED TABLE FULL | X$KZSRO | 2 | 12 | 0 (0)| | | | | | |
| 49 | INDEX RANGE SCAN | I_OBJAUTH2 | 1 | 9 | 1 (0)| 00:00:01 | | | | | |
| 50 | FIXED TABLE FULL | X$KZSPR | 1 | 9 | 0 (0)| | | | | | |
| 51 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 | | | | | |
| 52 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 | | | | | |
| 53 | NESTED LOOPS SEMI | | 1 | 29 | 2 (0)| 00:00:01 | | | | | |
| 54 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 | | | | | |
| 55 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 | | | | | |
Peeked Binds (identified by position):
1 - :TABLE_NAME (VARCHAR2(30), CSID=873): 'X15_L1DbProtoVisits' 2 - :OWNER (VARCHAR2(30), CSID=873): 'LSST_ALERTS' Note
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
- this is an adaptive plan
Plan hash value: 1846450148
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
| 0 | SELECT STATEMENT | | | | 31 (100)| | | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 39 | 3 (0)| 00:00:01 | | | | | |
| 2 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 39 | 3 (0)| 00:00:01 | | | | | |
| 4 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 39 | 3 (0)| 00:00:01 | | | | | |
| 6 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 39 | 3 (0)| 00:00:01 | | | | | |
| 8 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 39 | 3 (0)| 00:00:01 | | | | | |
| 10 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 | | | | | |
| 11 | SORT ORDER BY | | 1 | 2434 | 31 (7)| 00:00:01 | | | | | |
| 12 | FILTER | | | | | | | | | | |
| 13 | NESTED LOOPS OUTER | | 1 | 2434 | 15 (7)| 00:00:01 | | | | | |
| 14 | HASH JOIN OUTER | | 1 | 2429 | 14 (8)| 00:00:01 | | | | | |
| 15 | HASH JOIN OUTER | | 1 | 229 | 13 (0)| 00:00:01 | | | | | |
| 16 | HASH JOIN | | 1 | 225 | 12 (0)| 00:00:01 | | | | | |
| 17 | HASH JOIN OUTER | | 1 | 201 | 11 (0)| 00:00:01 | | | | | |
| 18 | HASH JOIN OUTER | | 1 | 191 | 10 (0)| 00:00:01 | | | | | |
| 19 | NESTED LOOPS OUTER | | 1 | 148 | 6 (0)| 00:00:01 | | | | | |
| 20 | NESTED LOOPS | | 1 | 120 | 5 (0)| 00:00:01 | | | | | |
| 21 | NESTED LOOPS | | 1 | 73 | 4 (0)| 00:00:01 | | | | | |
| 22 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 18 | 1 (0)| 00:00:01 | | | | | |
| 23 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | | | |
| 24 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 1 | 55 | 3 (0)| 00:00:01 | | | | | |
| 25 | INDEX RANGE SCAN | I_OBJ5 | 1 | | 2 (0)| 00:00:01 | | | | | |
| 26 | TABLE ACCESS CLUSTER | COL$ | 1 | 47 | 1 (0)| 00:00:01 | | | | | |
| 27 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| | | | | | |
| 28 | TABLE ACCESS CLUSTER | COLTYPE$ | 1 | 28 | 1 (0)| 00:00:01 | | | | | |
| 29 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 43 | 4 (0)| 00:00:01 | | | | | |
| 30 | INDEX SKIP SCAN | I_OBJ1 | 23 | | 1 (0)| 00:00:01 | | | | | |
| 31 | INDEX FAST FULL SCAN | I_HH_OBJ#_INTCOL# | 1 | 10 | 1 (0)| 00:00:01 | | | | | |
| 32 | INDEX FULL SCAN | I_USER2 | 1 | 24 | 1 (0)| 00:00:01 | | | | | |
| 33 | INDEX FULL SCAN | I_USER2 | 1 | 4 | 1 (0)| 00:00:01 | | | | | |
| 34 | VIEW | ALL_COL_COMMENTS | 1 | 2200 | 1 (100)| 00:00:01 | | | | | |
| 35 | FILTER | | | | | | | | | | |
| 36 | PX COORDINATOR | | | | | | | | | | |
| 37 | PX SEND QC (RANDOM) | :TQ10000 | 2 | 4478 | 1 (100)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 38 | PX PARTITION LIST ALL | | 2 | 4478 | 1 (100)| 00:00:01 | 1 | 2 | Q1,00 | PCWC | |
| 39 | EXTENDED DATA LINK FULL | INT$DBA_COL_COMMENTS | 2 | 4478 | 1 (100)| 00:00:01 | | | Q1,00 | PCWP | |
| 40 | NESTED LOOPS SEMI | | 1 | 15 | 2 (0)| 00:00:01 | | | | | |
| 41 | FIXED TABLE FULL | X$KZSRO | 2 | 12 | 0 (0)| | | | | | |
| 42 | INDEX RANGE SCAN | I_OBJAUTH2 | 1 | 9 | 1 (0)| 00:00:01 | | | | | |
| 43 | TABLE ACCESS CLUSTER | TAB$ | 1 | 5 | 1 (0)| 00:00:01 | | | | | |
| 44 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| | | | | | |
| 45 | TABLE ACCESS CLUSTER | TAB$ | 1 | 13 | 2 (0)| 00:00:01 | | | | | |
| 46 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 | | | | | |
| 47 | NESTED LOOPS SEMI | | 1 | 15 | 2 (0)| 00:00:01 | | | | | |
| 48 | FIXED TABLE FULL | X$KZSRO | 2 | 12 | 0 (0)| | | | | | |
| 49 | INDEX RANGE SCAN | I_OBJAUTH2 | 1 | 9 | 1 (0)| 00:00:01 | | | | | |
| 50 | FIXED TABLE FULL | X$KZSPR | 1 | 9 | 0 (0)| | | | | | |
| 51 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 | | | | | |
| 52 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 | | | | | |
| 53 | NESTED LOOPS SEMI | | 1 | 29 | 2 (0)| 00:00:01 | | | | | |
| 54 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 | | | | | |
| 55 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 | | | | | |
Peeked Binds (identified by position):
1 - :TABLE_NAME (VARCHAR2(30), CSID=873): 'X15_DiaObject' 2 - :OWNER (VARCHAR2(30), CSID=873): 'LSST_ALERTS' Note
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
- statistics feedback used for this statement
- this is an adaptive plan
On Sat, Jun 30, 2018 at 6:35 AM Chris Stephens <cstephens16_at_gmail.com<mailto:cstephens16_at_gmail.com>> wrote: Thanks so much for taking the time to look at this. Yes, this is a single tenant CDB/PDB.
The developer reworked the code to eliminate the metadata queries and problem vanished.
I'll have access to the system tomorrow and will check and let you know what I find. ..assuming ASH data is still around.
Thanks again!
On Sat, Jun 30, 2018 at 4:16 AM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> wrote:
Is this a PDB running inside a CDB, or is it a non-PDB database ? Have you checked the execution plan for the query.
Running on a PDB inside a CDB (which is all I can get hands on at the moment) I can see that there's a cross database call which causes parallel servers to start up. It's possible that the the issue with cursor pinning is a side effect of this parallelism; when I ran the query it was one of my PX slaves that had happened to wait on 'cursor: pin S wait on X'.
A quick check on ASH to see if the parallelism is a problem would be to split the counts for the wait into one ones where qc_session_id was null (the query coordinators) and qc_session_id is not null (PX slaves).
I don't know what the parallelism is for - but possibly the effect is made worse by the presence of RAC - perhaps some of the slaves are being allocated remotely when ideally they should be allocated locally.
The other problem, of course, is that the data is likely to be extremely skewed for a few owners - so the optimizer probably does a lot of work calculating inflection points (my plan show 6 possible inflection points). Have you checked the data dictionary to see if the stats are reasonably accurate, and whether you could create some column groups that might help the optimizer get better estimates of cardinality. Anything which reduces actual optimisation time might help.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Chris Stephens <cstephens16_at_gmail.com<mailto:cstephens16_at_gmail.com>> Sent: 29 June 2018 15:26:23
To: oracle-l
Subject: cursor: pin S wait on X querying data dictionary
3-node RAC 12.2 database on Centos7
We have an application which makes use of SQLAlchemy that is suffering mightily from "cursor: pin S wait on X" executing the following SQL:
SELECT col.column_name, col.data_type, col.char_length,
col.data_precision, col.data_scale, col.nullable,
col.data_default, com.comments
FROM all_tab_columns col
LEFT JOIN all_col_comments com
ON col.table_name = com.table_name
AND col.column_name = com.column_name
AND col.owner = com.owner
WHERE col.table_name = :table_name
AND col.owner = :owner ORDER BY col.column_id
The application will launch ~200 concurrent sessions on startup. There is a long initial period where many of them are waiting on the event then things start clearing up and processing proceeds as expected. There are several different plan hash values for the SQL, one of which didn't seem to suffer from the issue so I created a sql baseline and crossed my fingers. I've collected dictionary statistics as well but that made no difference.
While I do my own searching, does any one have any suggestions on how to remove the delays?
Here is some ASH data showing the issue:
SQL> _at_ashtop sql_id,event "sql_id='0rz9dn5v75czn'" "TIMESTAMP'2018-06-28 18:45:00'" "TIMESTAMP'2018-06-29 00:00:00'"
Total
Seconds AAS %This SQL_ID EVENT FIRST_SEEN LAST_SEEN DIST_SQLEXEC_SEEN
--------- ------- ------- ------------- ---------------------------------------- ------------------- ------------------- -----------------
75469 4.0 84% | 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:11:34 2018-06-28 20:38:54 1
6441 .3 7% | 0rz9dn5v75czn library cache lock 2018-06-28 20:11:41 2018-06-28 20:38:36 1
6243 .3 7% | 0rz9dn5v75czn kksfbc child completion 2018-06-28 20:11:44 2018-06-28 20:38:43 1
1592 .1 2% | 0rz9dn5v75czn 2018-06-28 20:11:34 2018-06-28 20:38:55 105
182 .0 0% | 0rz9dn5v75czn cursor: pin S 2018-06-28 20:12:34 2018-06-28 20:38:34 1
133 .0 0% | 0rz9dn5v75czn library cache: mutex X 2018-06-28 20:12:09 2018-06-28 20:38:32 1
18 .0 0% | 0rz9dn5v75czn enq: PS - contention 2018-06-28 20:13:17 2018-06-28 20:38:55 18
15 .0 0% | 0rz9dn5v75czn cursor: pin X 2018-06-28 20:13:41 2018-06-28 20:38:36 1
select snap_id, end_interval_time
8 .0 0% | 0rz9dn5v75czn PGA memory operation 2018-06-28 20:15:24 2018-06-28 20:38:44 1
2 .0 0% | 0rz9dn5v75czn PX Deq: Join ACK 2018-06-28 20:13:26 2018-06-28 20:32:12 2
1 .0 0% | 0rz9dn5v75czn row cache read 2018-06-28 20:35:18 2018-06-28 20:35:18 1
11 rows selected.
SQL> _at_ashtop session_id,sql_id,event "sql_id='0rz9dn5v75czn'" "TIMESTAMP'2018-06-28 18:45:00'" "TIMESTAMP'2018-06-29 00:00:00'"
Total
Seconds AAS %This SESSION_ID SQL_ID EVENT FIRST_SEEN LAST_SEEN DIST_SQLEXEC_SEEN
--------- ------- ------- ---------- ------------- ---------------------------------------- ------------------- ------------------- -----------------
934 .0 1% | 2065 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:11:34 2018-06-28 20:37:03 1
925 .0 1% | 1459 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:11:34 2018-06-28 20:35:26 1
917 .0 1% | 613 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:13:26 2018-06-28 20:38:53 1
893 .0 1% | 4360 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:11:34 2018-06-28 20:38:45 1
873 .0 1% | 973 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:11:44 2018-06-28 20:38:26 1
861 .0 1% | 3276 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:11:44 2018-06-28 20:38:44 1
854 .0 1% | 3877 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:11:34 2018-06-28 20:38:52 1
828 .0 1% | 4602 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:11:44 2018-06-28 20:38:50 1
827 .0 1% | 2547 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:11:34 2018-06-28 20:38:47 1
826 .0 1% | 491 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:11:44 2018-06-28 20:38:44 1
823 .0 1% | 251 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:15:06 2018-06-28 20:38:51 1
818 .0 1% | 2790 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:15:06 2018-06-28 20:38:35 1
798 .0 1% | 124 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:11:44 2018-06-28 20:32:06 1
761 .0 1% | 129 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:11:34 2018-06-28 20:38:45 1
745 .0 1% | 6 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:11:44 2018-06-28 20:38:51 1
734 .0 1% | 738 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:11:44 2018-06-28 20:37:01 1
731 .0 1% | 123 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:13:24 2018-06-28 20:38:37 1
730 .0 1% | 4117 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:11:34 2018-06-28 20:38:43 1
726 .0 1% | 1216 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:17:17 2018-06-28 20:38:52 1
710 .0 1% | 3 0rz9dn5v75czn cursor: pin S wait on X 2018-06-28 20:16:46 2018-06-28 20:36:23 1
20 rows selected.
SQL> select distinct(plan_hash_value) from dba_hist_sqlstat where sql_id = '0rz9dn5v75czn' and snap_id >= 2378;
PLAN_HASH_VALUE
2901843923
1846450148
0
SQL> l
1 select plan_name, last_executed, enabled, accepted, fixed, executions
2 from dba_sql_plan_baselines
3* where sql_text like '%col.column_name, col.data_type, col.char_length%'
SQL> /
PLAN_NAME LAST_EXECUTED ENABLED ACCEPTED FIXED EXECUTIONS
---------------------------------------- -------------------- ---------- ---------- ---------- ---------- SQL_PLAN_78xa0f27qf69ye98653aa YES YES YES 38562
Anyone have any ideas?
Thanks as always!
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jul 02 2018 - 09:41:53 CEST