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
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.

Report message to a moderator

Re: How to fine tune this query ? [message #471416 is a reply to message #471393] Sat, 14 August 2010 02:11 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
2.5 minutes for a 600 column query over almost 2 million rows doesnt feel that bad to me.

Report message to a moderator

Re: How to fine tune this query ? [message #471431 is a reply to message #471416] Sat, 14 August 2010 08:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>FROM PC_STATS
>|* 3 | TABLE ACCESS FULL| PCRF_VERTICAL | 1279K| 2319M| 11617 (1)| 00:02:43 | 1 | 15 |
Why don't the table names match each other?

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:

Report message to a moderator

Re: How to fine tune this query ? [message #471447 is a reply to message #471431] Sat, 14 August 2010 10:57 Go to previous messageGo to next message
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

Report message to a moderator

Re: How to fine tune this query ? [message #471453 is a reply to message #471447] Sat, 14 August 2010 11:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Actually the name is PCRF_PC..
Which still does not match posted "FROM PC_STATS" clause

GIGO!

Enjoy your mystery!

Report message to a moderator

Re: How to fine tune this query ? [message #471459 is a reply to message #471393] Sat, 14 August 2010 11:43 Go to previous messageGo to next message
sourabhsharma149
Messages: 5
Registered: May 2010
Location: Gurgaon
Junior Member
Embarassed 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...

Report message to a moderator

Re: How to fine tune this query ? [message #471460 is a reply to message #471459] Sat, 14 August 2010 11:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:

Report message to a moderator

Re: How to fine tune this query ? [message #471463 is a reply to message #471459] Sat, 14 August 2010 12:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I have NO idea what to believe from you!

I am running this query on a table with 1.7 million records and it is taking 150 seconds :
|* 3 | TABLE ACCESS FULL| PCRF_VERTICAL | 222 | 412K| 5 (0)| 00:00:01 | 1 | 15 |
|* 3 | TABLE ACCESS FULL| PCRF_PC | 1279K| 2319M| 11617 (1)| 00:02:43 | 1 | 15 |
|* 3 | TABLE ACCESS FULL| PCRF_VERTICAL | 1279K| 2319M| 11617 (1)| 00:02:43 | 1 | 15 |

How many rows being returned; 222 or 1.279M?
If 1.279M rows returned, then FTS is correct!

Report message to a moderator

Re: How to fine tune this query ? [message #471464 is a reply to message #471463] Sat, 14 August 2010 12:35 Go to previous messageGo to next message
sourabhsharma149
Messages: 5
Registered: May 2010
Location: Gurgaon
Junior Member
From last load table was truncated to few records .I realized it today...so preparing it for you from beginning...I appreciate your patience...Thanks..

Report message to a moderator

Re: How to fine tune this query ? [message #471465 is a reply to message #471393] Sat, 14 August 2010 14:06 Go to previous messageGo to next message
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..

Report message to a moderator

Re: How to fine tune this query ? [message #471466 is a reply to message #471465] Sat, 14 August 2010 14:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>|* 3 | TABLE ACCESS FULL| PCRF_VERTICAL | 964K| 1748M| 11610 (1)| 00:02:43 | 1 | 15 |
more than 50% rows returned so FTS is optimal.

Report message to a moderator

Re: How to fine tune this query ? [message #473656 is a reply to message #471393] Tue, 31 August 2010 01:06 Go to previous message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
You can try using materialized view.

HTH.

Report message to a moderator

Previous Topic: search query
Next Topic: Query slow
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 30 08:47:00 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.