Hi Tom,
One of my oracle procedure is not giving desire performance. From last few days I am monitoring sessions for that particular procedure and I am getting such sessions those are not written inside code. I just wanted to know does the oracle is creating his own session for better execution plan?
Below is the session details,
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) OPT_ESTIMATE(@"innerQuery", TABLE, "CCAA", ROWS=1.958339844) OPT_ESTIMATE(@"innerQuery", INDEX_FILTER, "CCAA", "CCAA_PERF_IDX03", ROWS=1) */
C1, C2, C3
FROM (SELECT /*+ qb_name("innerQuery") INDEX( "CCAA" "CCAA_PERF_IDX01") */
COUNT(*) AS C1, 4294967295 AS C2, COUNT(*) AS C3
FROM ZAIN_CMS."COL_CUR_AGREEMENTALLOC" "CCAA"
WHERE ("CCAA"."SZORGCODE" = '001')
AND ("CCAA"."SZPARTITIONCODE" = '6')) innerQuerySELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */
SUM(C1)
FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "CTC") */
1 AS C1
FROM ZAIN_CMS."COL_TRN_CASE" SAMPLE BLOCK(53.4213, 8) SEED(8) "CTC"
WHERE ("CTC"."ICASESEQNO" = 8137346)
AND ("CTC"."SZFIELD2" = 'I')
AND ("CTC"."SZPARTITIONCODE" = '6')
AND ("CTC"."SZORGCODE" = '001')) innerQueryI continuously observe above session when my procedure calls below statement,
SELECT 'Y'
FROM V_Ent_Queue V
WHERE V.szOrgCode = ?
AND V.iCaseSeqNo = ?
AND V.iAllocSeqNo = NVL(?, iAllocSeqNo)
AND V.szPartitionCode = ?
AND szCategoryType = ?
And below is the view definition,
select *
FROM Col_Mst_Customer CMC,
Col_Trn_Agreement CTA,
Col_Cur_AgreementAlloc CCAA,
Col_Trn_Case CTC,
Col_Cur_Case CCC
WHERE CMC.szOrgCode = CTA.szOrgCode
AND CMC.szPartitionCode = CTA.szPartitionCode
AND CMC.iCustomerSeqNo = CTA.iCustomerSeqNo
AND CTA.szOrgCode = CTC.szOrgCode
AND CTA.szPartitionCode = CTC.szPartitionCode
AND CTA.iCaseSeqNo = CTC.iCaseSeqNo
AND CTC.szOrgcode = CCAA.szOrgcode
AND CTC.szPartitioncode = CCAA.szPartitioncode
AND CTC.iCaseSeqNo = CCAA.iCaseSeqNo
AND CTC.szOrgcode = CCC.szOrgcode
AND CTC.szPartitioncode = CCC.szPartitioncode
AND CTC.iCaseSeqNo = CCC.iCaseSeqNoPlease help me to get idea about these unknown sessions. Because these sessions takes 2-3 seconds for each record.
Thanks in advance.
The DS_SVC indicates this is a dynamic statistics query. Oracle runs these to get additional stats for your query to enable better execution plans.
There are a few cases when this can happen:
- There are no stats on the tables
- optimizer_dynamic_sampling >= 4 and you have conditions on multiple columns of a table ANDed or ORed together
- (12c) You have optimizer_dynamic_sampling = 11 or it has the default value and any of the following are true:
- The stats are stale
- The stats are insufficient
- There are SQL plan directives
- You're using parallel query
You can read more about how this works in the documentation:
http://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL348 You can change the level of sampling with the dynamic_sampling hint. Zero disables it completely. So if you want to stop it for this query, place the hint at the start of your SQL:
select /*+ dynamic_sampling (0) */...
from ...