Home » RDBMS Server » Performance Tuning » How to fine tune this query ? (Oracle 11g R2)
How to fine tune this query ? [message #471393] |
Fri, 13 August 2010 16:31 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sourabhsharma149
Messages: 5 Registered: May 2010 Location: Gurgaon
|
Junior Member |
|
|
I am running this query on a table with 1.7 million records and it is taking 150 seconds :
SELECT
DATETIME,
DATETIMEZONE,
DATETIMEUTC,
pcrf,
NVL(MAX(NE_VERSION),0) NE_VERSION,
component_name,
process_name,
period,
AVG (DECODE (statistic_name, 'Latency_AAR', average_latency, NULL)) aar_avg,
MIN (DECODE (statistic_name, 'Latency_AAR', min_latency, NULL)) aar_min,
MAX (DECODE (statistic_name, 'Latency_AAR', max_latency, NULL)) aar_max,
MAX(DECODE (statistic_name,'Latency_AAR', max_latency_transaction_id,NULL)) aar_mtid,
MAX (DECODE (statistic_name, 'Latency_AAR', sla1, NULL)) aar_sla1,
MAX (DECODE (statistic_name, 'Latency_AAR', sla2, NULL)) aar_sla2,
SUM(DECODE (statistic_name, 'Latency_AAR', total_transactions, NULL)) aar_tot,
........
........
up 600 columns
FROM PC_STATS
WHERE statistic_name IN
('Latency_AAR',
'AccountInterface',
'Latency_AI_AddSubscriberToPurse',
'Latency_AI_ApplyBalanceDeltas',
'Latency_AI_CreateAccount',
'Latency_AI_CreateBalance',
'Latency_AI_CreatePurse',
...
....... 100 records
)
AND category = 'PM'
GROUP BY DATETIME,
DATETIMEZONE,
DATETIMEUTC,
pcrf,
component_name,
process_name,
period;
Is there any way I can tune this like some hints of query optimization.
I have attached query plan for it.
===============
Plan hash value: 892667434
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1279K| 2319M| 11659 (1)| 00:02:44 | | |
| 1 | PARTITION RANGE ALL| | 1279K| 2319M| 11659 (1)| 00:02:44 | 1 | 15 |
| 2 | SORT GROUP BY | | 1279K| 2319M| 11659 (1)| 00:02:44 | | |
|* 3 | TABLE ACCESS FULL| PCRF_VERTICAL | 1279K| 2319M| 11617 (1)| 00:02:43 | 1 | 15 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("STATISTIC_NAME"='AccountInterface' OR
"STATISTIC_NAME"='CTELink_syncCallDSDStatistic' OR
"STATISTIC_NAME"='CTELink_syncCallResponseStatistic' OR
"STATISTIC_NAME"='CTELink_syncCallStatistic' OR "STATISTIC_NAME"='DsdScriptLatencyMeasure'
OR "STATISTIC_NAME"='Latency_AAR' OR "STATISTIC_NAME"='Latency_AI_AddSubscriberToPurse' OR
"STATISTIC_NAME"='Latency_AI_ApplyBalanceDeltas' OR
"STATISTIC_NAME"='Latency_AI_CreateAccount' OR "STATISTIC_NAME"='Latency_AI_CreateBalance'
OR "STATISTIC_NAME"='Latency_AI_CreatePurse' OR
"STATISTIC_NAME"='Latency_AI_CreateReservation' OR
"STATISTIC_NAME"='Latency_AI_CreateSubscriber' OR
"STATISTIC_NAME"='Latency_AI_DeleteBalance' OR "STATISTIC_NAME"='Latency_AI_EndReservation'
OR "STATISTIC_NAME"='Latency_AI_GetSubscriberBalances' OR
"STATISTIC_NAME"='Latency_AI_GetSubscriberReservation' OR
"STATISTIC_NAME"='Latency_AI_RefillBalance' OR
"STATISTIC_NAME"='Latency_AI_UpdateReservation' OR
"STATISTIC_NAME"='Latency_CCR_INITIAL_REQUEST' OR
"STATISTIC_NAME"='Latency_CCR_TERMINATION_REQUEST' OR
"STATISTIC_NAME"='Latency_CCR_UPDATE_REQUEST' OR "STATISTIC_NAME"='Latency_DB_COMMIT' OR
"STATISTIC_NAME"='Latency_DB_ROLLBACK' OR "STATISTIC_NAME"='Latency_PMAPI_DoDecisionTableLook
up' OR "STATISTIC_NAME"='Latency_PMAPI_GetCiscoQoSProfile' OR
"STATISTIC_NAME"='Latency_PMAPI_GetNextThresholdInformation' OR
"STATISTIC_NAME"='Latency_PMAPI_GetNextUsageMonThresholdInfo' OR
"STATISTIC_NAME"='Latency_PMAPI_GetPeerQOSParameters' OR
"STATISTIC_NAME"='Latency_PMAPI_GetPeerTriggerInfo' OR
"STATISTIC_NAME"='Latency_PMAPI_GetQoSInformation' OR
"STATISTIC_NAME"='Latency_PMAPI_GetRedirectServer' OR
"STATISTIC_NAME"='Latency_PMAPI_GetServiceGroups' OR
"STATISTIC_NAME"='Latency_PMAPI_GetTriggerGroup' OR
"STATISTIC_NAME"='Latency_PMAPI_PCCRuleGroupLookup' OR
"STATISTIC_NAME"='Latency_PMAPI_PCCRuleLookupByName' OR
"STATISTIC_NAME"='Latency_PMAPI_PreloadPull' OR "STATISTIC_NAME"='Latency_RAA' OR
"STATISTIC_NAME"='Latency_SPR_CheckIMEISwapped' OR
"STATISTIC_NAME"='Latency_SPR_CheckRoaming' OR "STATISTIC_NAME"='Latency_SPR_GetUserProfile'
OR "STATISTIC_NAME"='Latency_SPR_LDAP_Bind' OR "STATISTIC_NAME"='Latency_SPR_LDAP_Search' OR
"STATISTIC_NAME"='Latency_SPR_LDAP_Search_Plugin' OR "STATISTIC_NAME"='Latency_SPR_Lookup'
OR "STATISTIC_NAME"='Latency_SPR_MIND_Cache_Add' OR
"STATISTIC_NAME"='Latency_SPR_MIND_Cache_Drop' OR
"STATISTIC_NAME"='Latency_SPR_MIND_Cache_Lookup' OR
"STATISTIC_NAME"='Latency_SPR_Roaming_Classification_Lookup' OR
"STATISTIC_NAME"='Latency_Session_DeleteAllAppliedRules' OR
"STATISTIC_NAME"='Latency_Session_DeleteAllAppliedServiceStatuses' OR
"STATISTIC_NAME"='Latency_Session_DeleteAppliedRule' OR
"STATISTIC_NAME"='Latency_Session_DeleteAppliedServiceStatus' OR
"STATISTIC_NAME"='Latency_Session_DeletePFI' OR
"STATISTIC_NAME"='Latency_Session_DeleteSession' OR
"STATISTIC_NAME"='Latency_Session_InsertAppliedRule' OR
"STATISTIC_NAME"='Latency_Session_InsertAppliedServiceStatus' OR
"STATISTIC_NAME"='Latency_Session_InsertPFI' OR
"STATISTIC_NAME"='Latency_Session_InsertSession' OR
"STATISTIC_NAME"='Latency_Session_SelectAppliedRules' OR
"STATISTIC_NAME"='Latency_Session_SelectAppliedServiceStatuses' OR
"STATISTIC_NAME"='Latency_Session_SelectBySessionId' OR
"STATISTIC_NAME"='Latency_Session_SelectBySySessionId' OR
"STATISTIC_NAME"='Latency_Session_SelectPFI' OR
"STATISTIC_NAME"='Latency_Session_SelectSessionBySubscriber' OR
"STATISTIC_NAME"='Latency_Session_UpdateAppliedRule' OR
"STATISTIC_NAME"='Latency_Session_UpdateAppliedServiceStatus' OR
"STATISTIC_NAME"='Latency_Session_UpdateSession' OR
"STATISTIC_NAME"='Latency_Session_UpdateSySession' OR
"STATISTIC_NAME"='Latency_Sy_AA-Answer' OR "STATISTIC_NAME"='Latency_Sy_Abort-Session-Request
' OR "STATISTIC_NAME"='Latency_Sy_Re-Auth-Request' OR
"STATISTIC_NAME"='Latency_Sy_Session-Termination-Answer' OR
"STATISTIC_NAME"='PCFThreadPoolJobQueuePerformance' OR
"STATISTIC_NAME"='PCFThreadPoolJobWorkPerformance' OR "STATISTIC_NAME"='Scheduler' OR
"STATISTIC_NAME"='fwInterfacePeerR)
Note
-----
- dynamic sampling used for this statement
========================================
Thanks in advanced.
|
|
|
|
|
Re: How to fine tune this query ? [message #471447 is a reply to message #471431] |
Sat, 14 August 2010 10:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sourabhsharma149
Messages: 5 Registered: May 2010 Location: Gurgaon
|
Junior Member |
|
|
My mistake while posting this thread.Sorry..Actually the name is PCRF_PC..
Consider it like:
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1279K| 2319M| 11659 (1)| 00:02:44 | | |
| 1 | PARTITION RANGE ALL| | 1279K| 2319M| 11659 (1)| 00:02:44 | 1 | 15 |
| 2 | SORT GROUP BY | | 1279K| 2319M| 11659 (1)| 00:02:44 | | |
|* 3 | TABLE ACCESS FULL| PCRF_PC | 1279K| 2319M| 11617 (1)| 00:02:43 | 1 | 15 |
-----------------------------------------------------------------------------------------------------
Thanks
|
|
|
|
Re: How to fine tune this query ? [message #471459 is a reply to message #471393] |
Sat, 14 August 2010 11:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sourabhsharma149
Messages: 5 Registered: May 2010 Location: Gurgaon
|
Junior Member |
|
|
Actually it's the query of view..So cleaning up my own mess here I go again :
SELECT
DATETIME,
DATETIMEZONE,
DATETIMEUTC,
pcrf,
NVL(MAX(NE_VERSION),0) NE_VERSION,
component_name,
process_name,
period,
AVG (DECODE (statistic_name, 'Latency_AAR', average_latency, NULL)) aar_avg,
MIN (DECODE (statistic_name, 'Latency_AAR', min_latency, NULL)) aar_min,
MAX (DECODE (statistic_name, 'Latency_AAR', max_latency, NULL)) aar_max,
MAX(DECODE (statistic_name,'Latency_AAR', max_latency_transaction_id,NULL)) aar_mtid,
MAX (DECODE (statistic_name, 'Latency_AAR', sla1, NULL)) aar_sla1,
MAX (DECODE (statistic_name, 'Latency_AAR', sla2, NULL)) aar_sla2,
SUM(DECODE (statistic_name, 'Latency_AAR', total_transactions, NULL)) aar_tot,
........
........
up 600 columns
FROM PCRF_Vertical
WHERE statistic_name IN
('Latency_AAR',
'AccountInterface',
'Latency_AI_AddSubscriberToPurse',
'Latency_AI_ApplyBalanceDeltas',
'Latency_AI_CreateAccount',
'Latency_AI_CreateBalance',
'Latency_AI_CreatePurse',
...
....... 100 records
)
AND category = 'PM'
GROUP BY DATETIME,
DATETIMEZONE,
DATETIMEUTC,
pcrf,
component_name,
process_name,
period;
SELECT * FROM table(dbms_xplan.display) :
Plan hash value: 892667434
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 222 | 412K| 6 (17)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 222 | 412K| 6 (17)| 00:00:01 | 1 | 15 |
| 2 | SORT GROUP BY | | 222 | 412K| 6 (17)| 00:00:01 | | |
|* 3 | TABLE ACCESS FULL| PCRF_VERTICAL | 222 | 412K| 5 (0)| 00:00:01 | 1 | 15 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("STATISTIC_NAME"='AccountInterface' OR
"STATISTIC_NAME"='CTELink_syncCallDSDStatistic' OR
"STATISTIC_NAME"='CTELink_syncCallResponseStatistic' OR
"STATISTIC_NAME"='CTELink_syncCallStatistic' OR "STATISTIC_NAME"='DsdScriptLatencyMeasure'
OR "STATISTIC_NAME"='Latency_AAR' OR "STATISTIC_NAME"='Latency_AI_AddSubscriberToPurse' OR
"STATISTIC_NAME"='Latency_AI_ApplyBalanceDeltas' OR
"STATISTIC_NAME"='Latency_AI_CreateAccount' OR "STATISTIC_NAME"='Latency_AI_CreateBalance'
OR "STATISTIC_NAME"='Latency_AI_CreatePurse' OR
"STATISTIC_NAME"='Latency_AI_CreateReservation' OR
"STATISTIC_NAME"='Latency_AI_CreateSubscriber' OR
"STATISTIC_NAME"='Latency_AI_DeleteBalance' OR "STATISTIC_NAME"='Latency_AI_EndReservation'
OR "STATISTIC_NAME"='Latency_AI_GetSubscriberBalances' OR
"STATISTIC_NAME"='Latency_AI_GetSubscriberReservation' OR
"STATISTIC_NAME"='Latency_AI_RefillBalance' OR
"STATISTIC_NAME"='Latency_AI_UpdateReservation' OR
"STATISTIC_NAME"='Latency_CCR_INITIAL_REQUEST' OR
"STATISTIC_NAME"='Latency_CCR_TERMINATION_REQUEST' OR
"STATISTIC_NAME"='Latency_CCR_UPDATE_REQUEST' OR "STATISTIC_NAME"='Latency_DB_COMMIT' OR
"STATISTIC_NAME"='Latency_DB_ROLLBACK' OR "STATISTIC_NAME"='Latency_PMAPI_DoDecisionTableLook
up' OR "STATISTIC_NAME"='Latency_PMAPI_GetCiscoQoSProfile' OR
"STATISTIC_NAME"='Latency_PMAPI_GetNextThresholdInformation' OR
"STATISTIC_NAME"='Latency_PMAPI_GetNextUsageMonThresholdInfo' OR
"STATISTIC_NAME"='Latency_PMAPI_GetPeerQOSParameters' OR
"STATISTIC_NAME"='Latency_PMAPI_GetPeerTriggerInfo' OR
"STATISTIC_NAME"='Latency_PMAPI_GetQoSInformation' OR
"STATISTIC_NAME"='Latency_PMAPI_GetRedirectServer' OR
"STATISTIC_NAME"='Latency_PMAPI_GetServiceGroups' OR
"STATISTIC_NAME"='Latency_PMAPI_GetTriggerGroup' OR
"STATISTIC_NAME"='Latency_PMAPI_PCCRuleGroupLookup' OR
"STATISTIC_NAME"='Latency_PMAPI_PCCRuleLookupByName' OR
"STATISTIC_NAME"='Latency_PMAPI_PreloadPull' OR "STATISTIC_NAME"='Latency_RAA' OR
"STATISTIC_NAME"='Latency_SPR_CheckIMEISwapped' OR
"STATISTIC_NAME"='Latency_SPR_CheckRoaming' OR "STATISTIC_NAME"='Latency_SPR_GetUserProfile'
OR "STATISTIC_NAME"='Latency_SPR_LDAP_Bind' OR "STATISTIC_NAME"='Latency_SPR_LDAP_Search' OR
"STATISTIC_NAME"='Latency_SPR_LDAP_Search_Plugin' OR "STATISTIC_NAME"='Latency_SPR_Lookup'
OR "STATISTIC_NAME"='Latency_SPR_MIND_Cache_Add' OR
"STATISTIC_NAME"='Latency_SPR_MIND_Cache_Drop' OR
"STATISTIC_NAME"='Latency_SPR_MIND_Cache_Lookup' OR
"STATISTIC_NAME"='Latency_SPR_Roaming_Classification_Lookup' OR
"STATISTIC_NAME"='Latency_Session_DeleteAllAppliedRules' OR
"STATISTIC_NAME"='Latency_Session_DeleteAllAppliedServiceStatuses' OR
"STATISTIC_NAME"='Latency_Session_DeleteAppliedRule' OR
"STATISTIC_NAME"='Latency_Session_DeleteAppliedServiceStatus' OR
"STATISTIC_NAME"='Latency_Session_DeletePFI' OR
"STATISTIC_NAME"='Latency_Session_DeleteSession' OR
"STATISTIC_NAME"='Latency_Session_InsertAppliedRule' OR
"STATISTIC_NAME"='Latency_Session_InsertAppliedServiceStatus' OR
"STATISTIC_NAME"='Latency_Session_InsertPFI' OR
"STATISTIC_NAME"='Latency_Session_InsertSession' OR
"STATISTIC_NAME"='Latency_Session_SelectAppliedRules' OR
"STATISTIC_NAME"='Latency_Session_SelectAppliedServiceStatuses' OR
"STATISTIC_NAME"='Latency_Session_SelectBySessionId' OR
"STATISTIC_NAME"='Latency_Session_SelectBySySessionId' OR
"STATISTIC_NAME"='Latency_Session_SelectPFI' OR
"STATISTIC_NAME"='Latency_Session_SelectSessionBySubscriber' OR
"STATISTIC_NAME"='Latency_Session_UpdateAppliedRule' OR
"STATISTIC_NAME"='Latency_Session_UpdateAppliedServiceStatus' OR
"STATISTIC_NAME"='Latency_Session_UpdateSession' OR
"STATISTIC_NAME"='Latency_Session_UpdateSySession' OR
"STATISTIC_NAME"='Latency_Sy_AA-Answer' OR "STATISTIC_NAME"='Latency_Sy_Abort-Session-Request
' OR "STATISTIC_NAME"='Latency_Sy_Re-Auth-Request' OR
"STATISTIC_NAME"='Latency_Sy_Session-Termination-Answer' OR
"STATISTIC_NAME"='PCFThreadPoolJobQueuePerformance' OR
"STATISTIC_NAME"='PCFThreadPoolJobWorkPerformance' OR "STATISTIC_NAME"='Scheduler' OR
"STATISTIC_NAME"='fwInterfacePeerR)
Note
-----
- dynamic sampling used for this statement
Thanks & do let me know if anything is required from my end...
|
|
|
|
|
|
Re: How to fine tune this query ? [message #471465 is a reply to message #471393] |
Sat, 14 August 2010 14:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sourabhsharma149
Messages: 5 Registered: May 2010 Location: Gurgaon
|
Junior Member |
|
|
select count(*) from cisco_pcrf.pcrf_vertical
1718762
select * from plan_table
See attachment :
SELECT * FROM table(dbms_xplan.display);
Plan hash value: 892667434
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 964K| 1748M| 11641 (1)| 00:02:43 | | |
| 1 | PARTITION RANGE ALL| | 964K| 1748M| 11641 (1)| 00:02:43 | 1 | 15 |
| 2 | SORT GROUP BY | | 964K| 1748M| 11641 (1)| 00:02:43 | | |
|* 3 | TABLE ACCESS FULL| PCRF_VERTICAL | 964K| 1748M| 11610 (1)| 00:02:43 | 1 | 15 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("STATISTIC_NAME"='AccountInterface' OR
"STATISTIC_NAME"='CTELink_syncCallDSDStatistic' OR
"STATISTIC_NAME"='CTELink_syncCallResponseStatistic' OR
"STATISTIC_NAME"='CTELink_syncCallStatistic' OR "STATISTIC_NAME"='DsdScriptLatencyMeasure'
OR "STATISTIC_NAME"='Latency_AAR' OR "STATISTIC_NAME"='Latency_AI_AddSubscriberToPurse' OR
"STATISTIC_NAME"='Latency_AI_ApplyBalanceDeltas' OR
"STATISTIC_NAME"='Latency_AI_CreateAccount' OR "STATISTIC_NAME"='Latency_AI_CreateBalance'
OR "STATISTIC_NAME"='Latency_AI_CreatePurse' OR
"STATISTIC_NAME"='Latency_AI_CreateReservation' OR
"STATISTIC_NAME"='Latency_AI_CreateSubscriber' OR
"STATISTIC_NAME"='Latency_AI_DeleteBalance' OR "STATISTIC_NAME"='Latency_AI_EndReservation'
OR "STATISTIC_NAME"='Latency_AI_GetSubscriberBalances' OR
"STATISTIC_NAME"='Latency_AI_GetSubscriberReservation' OR
"STATISTIC_NAME"='Latency_AI_RefillBalance' OR
"STATISTIC_NAME"='Latency_AI_UpdateReservation' OR
"STATISTIC_NAME"='Latency_CCR_INITIAL_REQUEST' OR
"STATISTIC_NAME"='Latency_CCR_TERMINATION_REQUEST' OR
"STATISTIC_NAME"='Latency_CCR_UPDATE_REQUEST' OR "STATISTIC_NAME"='Latency_DB_COMMIT' OR
"STATISTIC_NAME"='Latency_DB_ROLLBACK' OR "STATISTIC_NAME"='Latency_PMAPI_DoDecisionTableLook
up' OR "STATISTIC_NAME"='Latency_PMAPI_GetCiscoQoSProfile' OR
"STATISTIC_NAME"='Latency_PMAPI_GetNextThresholdInformation' OR
"STATISTIC_NAME"='Latency_PMAPI_GetNextUsageMonThresholdInfo' OR
"STATISTIC_NAME"='Latency_PMAPI_GetPeerQOSParameters' OR
"STATISTIC_NAME"='Latency_PMAPI_GetPeerTriggerInfo' OR
"STATISTIC_NAME"='Latency_PMAPI_GetQoSInformation' OR
"STATISTIC_NAME"='Latency_PMAPI_GetRedirectServer' OR
"STATISTIC_NAME"='Latency_PMAPI_GetServiceGroups' OR
"STATISTIC_NAME"='Latency_PMAPI_GetTriggerGroup' OR
"STATISTIC_NAME"='Latency_PMAPI_PCCRuleGroupLookup' OR
"STATISTIC_NAME"='Latency_PMAPI_PCCRuleLookupByName' OR
"STATISTIC_NAME"='Latency_PMAPI_PreloadPull' OR "STATISTIC_NAME"='Latency_RAA' OR
"STATISTIC_NAME"='Latency_SPR_CheckIMEISwapped' OR
"STATISTIC_NAME"='Latency_SPR_CheckRoaming' OR "STATISTIC_NAME"='Latency_SPR_GetUserProfile'
OR "STATISTIC_NAME"='Latency_SPR_LDAP_Bind' OR "STATISTIC_NAME"='Latency_SPR_LDAP_Search' OR
"STATISTIC_NAME"='Latency_SPR_LDAP_Search_Plugin' OR "STATISTIC_NAME"='Latency_SPR_Lookup'
OR "STATISTIC_NAME"='Latency_SPR_MIND_Cache_Add' OR
"STATISTIC_NAME"='Latency_SPR_MIND_Cache_Drop' OR
"STATISTIC_NAME"='Latency_SPR_MIND_Cache_Lookup' OR
"STATISTIC_NAME"='Latency_SPR_Roaming_Classification_Lookup' OR
"STATISTIC_NAME"='Latency_Session_DeleteAllAppliedRules' OR
"STATISTIC_NAME"='Latency_Session_DeleteAllAppliedServiceStatuses' OR
"STATISTIC_NAME"='Latency_Session_DeleteAppliedRule' OR
"STATISTIC_NAME"='Latency_Session_DeleteAppliedServiceStatus' OR
"STATISTIC_NAME"='Latency_Session_DeletePFI' OR
"STATISTIC_NAME"='Latency_Session_DeleteSession' OR
"STATISTIC_NAME"='Latency_Session_InsertAppliedRule' OR
"STATISTIC_NAME"='Latency_Session_InsertAppliedServiceStatus' OR
"STATISTIC_NAME"='Latency_Session_InsertPFI' OR
"STATISTIC_NAME"='Latency_Session_InsertSession' OR
"STATISTIC_NAME"='Latency_Session_SelectAppliedRules' OR
"STATISTIC_NAME"='Latency_Session_SelectAppliedServiceStatuses' OR
"STATISTIC_NAME"='Latency_Session_SelectBySessionId' OR
"STATISTIC_NAME"='Latency_Session_SelectBySySessionId' OR
"STATISTIC_NAME"='Latency_Session_SelectPFI' OR
"STATISTIC_NAME"='Latency_Session_SelectSessionBySubscriber' OR
"STATISTIC_NAME"='Latency_Session_UpdateAppliedRule' OR
"STATISTIC_NAME"='Latency_Session_UpdateAppliedServiceStatus' OR
"STATISTIC_NAME"='Latency_Session_UpdateSession' OR
"STATISTIC_NAME"='Latency_Session_UpdateSySession' OR
"STATISTIC_NAME"='Latency_Sy_AA-Answer' OR "STATISTIC_NAME"='Latency_Sy_Abort-Session-Request
' OR "STATISTIC_NAME"='Latency_Sy_Re-Auth-Request' OR
"STATISTIC_NAME"='Latency_Sy_Session-Termination-Answer' OR
"STATISTIC_NAME"='PCFThreadPoolJobQueuePerformance' OR
"STATISTIC_NAME"='PCFThreadPoolJobWorkPerformance' OR "STATISTIC_NAME"='Scheduler' OR
"STATISTIC_NAME"='fwInterfacePeerR)
Note
-----
- dynamic sampling used for this statement
execute DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
execute DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
execute DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);
SELECT DBMS_METADATA.GET_DDL (object_type, object_name, object_owner)
FROM plan_table
WHERE object_type IN ('TABLE','VIEW');
CREATE TABLE "CISCO_PCRF"."PCRF_VERTICAL"
( "DATETIME_INS" TIMESTAMP (6) WITH TIME ZONE DEFAULT SYSTIMESTAMP,
"DATETIME" DATE,
"DATETIMEZONE" TIMESTAMP (6) WITH TIME ZONE,
"PCRF" VARCHAR2(500),
"COMPONENT_NAME" VARCHAR2(500),
"STATISTIC_NAME" VARCHAR2(500),
"CATEGORY" VARCHAR2(500),
"ASSOCIATED_NAME" VARCHAR2(500),
"TRANSACTION_ID" VARCHAR2(500),
"DATETIMEUTC" DATE,
"DATETIME_FTP_COLLECTION" DATE,
"EMS_NAME" VARCHAR2(500),
"NE_VERSION" VARCHAR2(500),
"SUSPECTFLAG" VARCHAR2(500),
"ACTION" VARCHAR2(500),
"DISCRIMINATOR" VARCHAR2(500),
"CHECKPOINT_ID" VARCHAR2(500),
"STRING_VALUE" VARCHAR2(500),
"MAX_LATENCY_TRANSACTION_ID" VARCHAR2(500),
"PROCESS_NAME" VARCHAR2(500),
"PERIOD" NUMBER,
"TPS" NUMBER,
"SLA1" NUMBER,
"SLA2" NUMBER,
"NUMERIC_VALUE" NUMBER,
"SINGLE_LATENCY" NUMBER,
"AVERAGE_LATENCY" NUMBER,
"MAX_LATENCY" NUMBER,
"MIN_LATENCY" NUMBER,
"TOTAL_TRANSACTIONS" NUMBER,
CONSTRAINT "PCRF_VERTICAL_PK" PRIMARY KEY ("DATETIME", "PCRF", "COMPONENT_NAME", "STATISTIC_NAME", "CATEGORY", "ASSOCIATED_NAME", "TRANSACTION_ID", "DATETIMEUTC", "PROCESS_NAME") ENABLE
)
PARTITION BY RANGE ("DATETIME")
(PARTITION "P20100813" VALUES LESS THAN (TO_DATE(' 2010-08-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOCOMPRESS ,
PARTITION "P20100814" VALUES LESS THAN (TO_DATE(' 2010-08-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOCOMPRESS ,
PARTITION "P20100815" VALUES LESS THAN (TO_DATE(' 2010-08-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOCOMPRESS ,
PARTITION "P20100816" VALUES LESS THAN (TO_DATE(' 2010-08-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOCOMPRESS ,
PARTITION "P20100817" VALUES LESS THAN (TO_DATE(' 2010-08-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOCOMPRESS ,
PARTITION "P20100818" VALUES LESS THAN (TO_DATE(' 2010-08-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOCOMPRESS ,
PARTITION "P20100819" VALUES LESS THAN (TO_DATE(' 2010-08-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOCOMPRESS ,
PARTITION "P20100820" VALUES LESS THAN (TO_DATE(' 2010-08-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOCOMPRESS ,
PARTITION "P20100821" VALUES LESS THAN (TO_DATE(' 2010-08-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOCOMPRESS ,
PARTITION "P20100822" VALUES LESS THAN (TO_DATE(' 2010-08-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOCOMPRESS ,
PARTITION "P20100823" VALUES LESS THAN (TO_DATE(' 2010-08-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOCOMPRESS ,
PARTITION "P20100824" VALUES LESS THAN (TO_DATE(' 2010-08-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOCOMPRESS ,
PARTITION "P20100825" VALUES LESS THAN (TO_DATE(' 2010-08-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOCOMPRESS ,
PARTITION "P20100826" VALUES LESS THAN (TO_DATE(' 2010-08-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOCOMPRESS ,
PARTITION "P20100827" VALUES LESS THAN (TO_DATE(' 2010-08-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOCOMPRESS )
SELECT DBMS_METADATA.GET_DDL ('INDEX', index_name, owner)
FROM all_indexes
WHERE table_owner='CISCO_PCRF' and table_name='PCRF_VERTICAL'
CREATE UNIQUE INDEX "CISCO_PCRF"."PCRF_VERTICAL_PK" ON "CISCO_PCRF"."PCRF_VERTICAL" ("DATETIME", "PCRF", "COMPONENT_NAME", "STATISTIC_NAME", "CATEGORY", "ASSOCIATED_NAME", "TRANSACTION_ID", "DATETIMEUTC", "PROCESS_NAME")
LOCAL
(PARTITION "P20100813" ,
PARTITION "P20100814" ,
PARTITION "P20100815" ,
PARTITION "P20100816" ,
PARTITION "P20100817" ,
PARTITION "P20100818" ,
PARTITION "P20100819" ,
PARTITION "P20100820" ,
PARTITION "P20100821" ,
PARTITION "P20100822" ,
PARTITION "P20100823" ,
PARTITION "P20100824" ,
PARTITION "P20100825" ,
PARTITION "P20100826" ,
PARTITION "P20100827" )
SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
FROM ALL_IND_COLUMNS
WHERE TABLE_NAME IN (SELECT OBJECT_NAME FROM PLAN_TABLE p WHERE object_type = 'TABLE') ORDER BY 1,2,4; :
PCRF_VERTICAL PCRF_VERTICAL_PK DATETIME 1
PCRF_VERTICAL PCRF_VERTICAL_PK DATETIME 1
PCRF_VERTICAL PCRF_VERTICAL_PK PCRF 2
PCRF_VERTICAL PCRF_VERTICAL_PK PCRF 2
PCRF_VERTICAL PCRF_VERTICAL_PK COMPONENT_NAME 3
PCRF_VERTICAL PCRF_VERTICAL_PK COMPONENT_NAME 3
PCRF_VERTICAL PCRF_VERTICAL_PK STATISTIC_NAME 4
PCRF_VERTICAL PCRF_VERTICAL_PK STATISTIC_NAME 4
PCRF_VERTICAL PCRF_VERTICAL_PK CATEGORY 5
PCRF_VERTICAL PCRF_VERTICAL_PK CATEGORY 5
PCRF_VERTICAL PCRF_VERTICAL_PK ASSOCIATED_NAME 6
PCRF_VERTICAL PCRF_VERTICAL_PK ASSOCIATED_NAME 6
PCRF_VERTICAL PCRF_VERTICAL_PK DATETIMEUTC 7
PCRF_VERTICAL PCRF_VERTICAL_PK TRANSACTION_ID 7
PCRF_VERTICAL PCRF_VERTICAL_PK PROCESS_NAME 8
PCRF_VERTICAL PCRF_VERTICAL_PK DATETIMEUTC 8
PCRF_VERTICAL PCRF_VERTICAL_PK PROCESS_NAME 9
ALTER SESSION SET SQL_TRACE=TRUE;
I Run Query--
ALTER SESSION SET SQL_TRACE=FALSE;
-----------------------
TKPROF: Release 11.1.0.7.0 - Production on Sat Aug 14 13:58:45 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: E:\u01\app\oracle\diag\rdbms\optprod6\optprod6\trace\optprod6_dbrm_3248.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
Trace file: E:\u01\app\oracle\diag\rdbms\optprod6\optprod6\trace\optprod6_dbrm_3248.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
0 user SQL statements in trace file.
0 internal SQL statements in trace file.
0 SQL statements in trace file.
0 unique SQL statements in trace file.
2469 lines in trace file.
0 elapsed seconds in trace file.
----------------------------------------------------------------------
I hope this provide desired info..
Do let me know in case of I missed anything or any concern..
|
|
|
|
|
Goto Forum:
Current Time: Thu Sep 12 10:17:33 CDT 2024
|