SQL & PL/SQL

TKPROF: Release 8.1.7.0.0 - Production on Wed Sep 23 23:04:43 2009

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Trace file: pindb_ora_9943.trc
Sort options: prsela  exeela  fchela  fchela  
********************************************************************************
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
********************************************************************************

SELECT f.poid_id0, COUNT (*), SUM (amount), d.subscription_amt, d.free_mb
FROM event_t a,
event_bal_impacts_t b,
(SELECT DISTINCT a.obj_id0 AS account_obj_id0, a.node_location,
a.plan_obj_id0
FROM account_products_t a
WHERE plan_obj_id0 > 0 AND service_obj_id0 > 0
UNION
SELECT DISTINCT a.account_obj_id0, a.node_location, a.plan_obj_id0
FROM audit_account_products_t a
WHERE plan_obj_id0 > 0
AND service_obj_id0 > 0
AND effective_end_t > 0) c,
iq_prov_tags_t d,
event_session_dialup_t e,
plan_t f
WHERE a.poid_id0 = b.obj_id0
AND a.poid_id0 = e.obj_id0
AND b.node_location = c.node_location
AND a.account_obj_id0 = c.account_obj_id0
AND c.plan_obj_id0 = d.plan_poid_id0
AND d.plan_poid_id0 = f.poid_id0
AND a.end_t >= pin_date_to_seconds (TRUNC (  SYSDATE
- 1), NULL)
AND a.end_t < pin_date_to_seconds (TRUNC (SYSDATE), NULL)
AND b.resource_id IN (1000051)
GROUP BY f.poid_id0, d.subscription_amt, d.free_mb

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.09       0.08          0          0          0           0
Execute      2      0.07       0.07          0          0          0           0
Fetch        2    235.91    2295.25    1509704    1674184        346          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5    236.07    2295.40    1509704    1674184        346          11

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 28  

Rows     Row Source Operation
-------  ---------------------------------------------------
     11  SORT GROUP BY 
     69   NESTED LOOPS 
     70    NESTED LOOPS 
     70     NESTED LOOPS 
     70      HASH JOIN 
     70       HASH JOIN 
 300499        TABLE ACCESS BY INDEX ROWID EVENT_T 
 300500         INDEX RANGE SCAN (object id 26458)
 816394        TABLE ACCESS BY INDEX ROWID EVENT_BAL_IMPACTS_T 
 816395         INDEX RANGE SCAN (object id 26463)
2385556       VIEW 
2385556        SORT UNIQUE 
3124105         UNION-ALL 
 638617          TABLE ACCESS FULL ACCOUNT_PRODUCTS_T 
2485488          TABLE ACCESS FULL AUDIT_ACCOUNT_PRODUCTS_T 
    138      TABLE ACCESS BY INDEX ROWID IQ_PROV_TAGS_T 
    138       INDEX UNIQUE SCAN (object id 26554)
    138     INDEX UNIQUE SCAN (object id 23148)
     69    INDEX UNIQUE SCAN (object id 27372)

********************************************************************************

ALTER SESSION SET SQL_TRACE = TRUE


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 28  



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.09       0.08          0          0          0           0
Execute      3      0.07       0.07          0          0          0           0
Fetch        2    235.91    2295.25    1509704    1674184        346          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6    236.07    2295.40    1509704    1674184        346          11

Misses in library cache during parse: 1
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch       20      0.00       0.01          0         40          0          18
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       24      0.01       0.01          0         40          0          18

Misses in library cache during parse: 1

    2  user  SQL statements in session.
    2  internal SQL statements in session.
    4  SQL statements in session.
********************************************************************************
Trace file: pindb_ora_9943.trc
Trace file compatibility: 8.00.04
Sort options: prsela  exeela  fchela  fchela  
       1  session in tracefile.
       2  user  SQL statements in trace file.
       2  internal SQL statements in trace file.
       4  SQL statements in trace file.
       3  unique SQL statements in trace file.
     118  lines in trace file.