I posted a query tuning request last week and received some suggestions; thanks to all of you for your help. I've been doing some testing and have more information and more questions. At the end of this I'll post the SQL code, tkprof output, etc.
The original query was returning a dataset in approx. 40-45 seconds. Here's my first question: First thing in the morning, it still takes about the same time, but as the day wears on, something must get stored in a buffer because the execution time drops to about 20-23 seconds. This is true of the original query
and the 'improved' query. What is happening to lower the execution time throughout the day?
Here's what I've done so far:
In the following code:
-- exclude all procedures approved by Peds faculty ONLY FOR CLASS OF 2011 AND LATER
-- EXCEPT FOR the Peds Block code (A0021 and A0022) - always include those
-- NOTE: this is the last part of a WHERE clause
AND NOT
(
TRX."AppUser" IN (SELECT "User" FROM USERS WHERE "Custom3" = 'YES') -- Peds faculty
AND
TO_CHAR(P."EndDate",'YYYY') >= '2011'
AND
TRIM(TO_CHAR(P."EndDate",'YYYY')) IS NOT NULL
AND
TRX."Procedure" NOT IN ('A0021','A0022')
)
USERS."Custom3" = 'YES' on only 9 of 1,727 rows. So, I created an index on USERS."Custom3" and executed the following code:
select /*+ gather_plan_statistics */ "User" from USERS where "Custom3" = 'YES'
;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
;
Which gave me this:
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| USERS | 1 | 9 | 9 |00:00:00.01 | 18 |
|* 2 | INDEX RANGE SCAN | USERS_CUSTOM3 | 1 | 9 | 9 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Custom3"='YES')
18 rows selected
So it appears that Oracle is using the index.
Next, I modified column datatypes and lengths so that I could remove as many TRIMs and UPPERs as possible:
-- OLD CODE
INNER JOIN CLASS
ON TRIM(QRP."axiUm_Discipline") = TRIM(CLASS."Class")
-- farther down in the view
INNER JOIN LLU_EVALUATION_DESCRIPTIONS LLU
ON (TRIM(UPPER(GI."QuestionText")) = TRIM(UPPER(LLU."GRADITEM_QuestionText")))
AND (TRIM(UPPER(GI."Text")) = TRIM(UPPER(LLU."GRADITEM_Text")))
AND (TRIM(TRX."Procedure") = TRIM(LLU."ProcedureCode"))
modified as follows:
-- NEW CODE
INNER JOIN CLASS
ON QRP."axiUm_Discipline" = CLASS."Class"
-- farther down in the view
INNER JOIN LLU_EVALUATION_DESCRIPTIONS LLU
ON GI."QuestionText" = LLU."GRADITEM_QuestionText"
AND GI."Text" = LLU."GRADITEM_Text"
AND TRX."Procedure" = LLU."ProcedureCode"
I re-ran the query and did not notice any appreciable improvement in execution time. The query is composed of four separate SQL statements UNIONed together (shown below). I ran each one of them separately and each one returned a dataset in about 20-25 seconds (as mentioned above) except for the last of the four SQL statements. It runs in 2 seconds but only returns 150-200 rows. The total output of the query is as follows:
x "Source" #Rows
SQL statement 1: QR 125,485
SQL statement 2: axiUm TRX 468,439
SQL statement 3: axiUm GRADING 43,146
SQL statement 4: ClinPtsAdj 176
And the entire query executes in approx. 20-25 seconds. Question: I am determining how long the query runs as follows: I issue the command
SELECT * FROM LLU_V_PRODUCTION_DETAIL_04
in SQL Developer. When it finishes executing, a time value appears just above the upper SQL statement window.
Is that value accurate in determining the execution time of a query? If that is the case, is it unreasonable to expect anything less than 20-25 seconds?
If you've made it this far and still want to help, blessings on you. Thanks a bunch, and here's some additional info that might help you help me.
PLAN_TABLE_OUTPUT
--------------------
Plan hash value: 2211286181
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4628K| 10G| | 60802 (2)| 00:12:10 |
| 1 | VIEW | LLU_V_PRODUCTION_DETAIL_04 | 4628K| 10G| | 60802 (2)| 00:12:10 |
| 2 | UNION-ALL | | | | | | |
|* 3 | FILTER | | | | | | |
|* 4 | HASH JOIN | | 15M| 5352M| | 32980 (2)| 00:06:36 |
| 5 | VIEW | index$_join$_007 | 1725 | 25875 | | 4 (25)| 00:00:01 |
|* 6 | HASH JOIN | | | | | | |
| 7 | INDEX FAST FULL SCAN | USERS_PRIMARY | 1725 | 25875 | | 1 (0)| 00:00:01 |
| 8 | INDEX FAST FULL SCAN | USERS_PRODUCER | 1725 | 25875 | | 2 (0)| 00:00:01 |
|* 9 | HASH JOIN | | 149K| 49M| | 32876 (1)| 00:06:35 |
| 10 | TABLE ACCESS FULL | CLASS | 20 | 1660 | | 3 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 149K| 37M| | 32872 (1)| 00:06:35 |
| 12 | TABLE ACCESS FULL | PRODUCER | 1396 | 118K| | 24 (0)| 00:00:01 |
|* 13 | HASH JOIN | | 222K| 37M| 12M| 32846 (1)| 00:06:35 |
| 14 | TABLE ACCESS FULL | PATIENT | 189K| 10M| | 6979 (1)| 00:01:24 |
|* 15 | HASH JOIN | | 222K| 24M| | 23860 (2)| 00:04:47 |
|* 16 | TABLE ACCESS FULL | PROCEDUR | 888 | 44400 | | 11 (0)| 00:00:01 |
|* 17 | TABLE ACCESS FULL | TRX | 442K| 28M| | 23845 (2)| 00:04:47 |
|* 18 | TABLE ACCESS BY INDEX ROWID | USERS | 1 | 11 | | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | USERS_CUSTOM3 | 9 | | | 1 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 493 | | 25799 (1)| 00:05:10 |
| 21 | NESTED LOOPS | | 1 | 433 | | 25798 (1)| 00:05:10 |
| 22 | NESTED LOOPS | | 1 | 418 | | 25797 (1)| 00:05:10 |
| 23 | NESTED LOOPS | | 1 | 331 | | 25796 (1)| 00:05:10 |
|* 24 | HASH JOIN | | 9 | 2907 | | 25794 (1)| 00:05:10 |
| 25 | TABLE ACCESS FULL | LLU_EVALUATION_DESCRIPTIONS | 95 | 8075 | | 3 (0)| 00:00:01 |
|* 26 | HASH JOIN | | 4630 | 1076K| | 25791 (1)| 00:05:10 |
|* 27 | HASH JOIN | | 9607 | 1623K| | 23834 (1)| 00:04:47 |
| 28 | MERGE JOIN | | 888 | 91464 | | 13 (8)| 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | CLASS | 20 | 1660 | | 1 (0)| 00:00:01 |
| 30 | INDEX FULL SCAN | CLASS_PRIMARY | 20 | | | 1 (0)| 00:00:01 |
|* 31 | SORT JOIN | | 888 | 17760 | | 12 (9)| 00:00:01 |
|* 32 | TABLE ACCESS FULL | PROCEDUR | 888 | 17760 | | 11 (0)| 00:00:01 |
|* 33 | TABLE ACCESS FULL | TRX | 19125 | 1307K| | 23820 (1)| 00:04:46 |
|* 34 | TABLE ACCESS FULL | GRADITEM | 655K| 40M| | 1952 (1)| 00:00:24 |
|* 35 | TABLE ACCESS BY INDEX ROWID | GRADING | 1 | 8 | | 1 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | GRADING_PRIMARY | 1 | | | 1 (0)| 00:00:01 |
| 37 | TABLE ACCESS BY INDEX ROWID | PRODUCER | 1 | 87 | | 1 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | PRODUCER_PRIMARY | 1 | | | 1 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | USERS | 103 | 1545 | | 1 (0)| 00:00:01 |
|* 40 | INDEX RANGE SCAN | USERS_PRODUCER | 1 | | | 1 (0)| 00:00:01 |
| 41 | TABLE ACCESS BY INDEX ROWID | PATIENT | 1 | 60 | | 1 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | PATIENT_PRIMARY | 1 | | | 1 (0)| 00:00:01 |
| 43 | TABLE ACCESS BY INDEX ROWID | USERS | 103 | 1545 | | 1 (0)| 00:00:01 |
| 44 | NESTED LOOPS | | 1 | 438 | | 2023 (1)| 00:00:25 |
| 45 | NESTED LOOPS | | 1 | 423 | | 2022 (1)| 00:00:25 |
| 46 | NESTED LOOPS | | 1 | 363 | | 2021 (1)| 00:00:25 |
| 47 | NESTED LOOPS | | 1 | 276 | | 2020 (1)| 00:00:25 |
| 48 | NESTED LOOPS | | 1 | 193 | | 2019 (1)| 00:00:25 |
| 49 | NESTED LOOPS | | 1 | 185 | | 2018 (1)| 00:00:25 |
| 50 | NESTED LOOPS | | 1 | 173 | | 2017 (1)| 00:00:25 |
| 51 | NESTED LOOPS | | 1 | 140 | | 2016 (1)| 00:00:25 |
|* 52 | TABLE ACCESS FULL | GRADITEM | 317 | 23141 | | 1953 (2)| 00:00:24 |
|* 53 | TABLE ACCESS BY INDEX ROWID| TRX | 1 | 67 | | 1 (0)| 00:00:01 |
|* 54 | INDEX UNIQUE SCAN | TRX_PRIMARY | 1 | | | 1 (0)| 00:00:01 |
|* 55 | TABLE ACCESS BY INDEX ROWID | TRX | 1 | 33 | | 1 (0)| 00:00:01 |
|* 56 | INDEX UNIQUE SCAN | TRX_PRIMARY | 1 | | | 1 (0)| 00:00:01 |
|* 57 | TABLE ACCESS BY INDEX ROWID | GRADITEM | 1 | 12 | | 1 (0)| 00:00:01 |
|* 58 | INDEX RANGE SCAN | GRADITEM_ID | 19 | | | 1 (0)| 00:00:01 |
|* 59 | TABLE ACCESS BY INDEX ROWID | GRADING | 1 | 8 | | 1 (0)| 00:00:01 |
|* 60 | INDEX UNIQUE SCAN | GRADING_PRIMARY | 1 | | | 1 (0)| 00:00:01 |
| 61 | TABLE ACCESS BY INDEX ROWID | CLASS | 1 | 83 | | 1 (0)| 00:00:01 |
|* 62 | INDEX UNIQUE SCAN | CLASS_PRIMARY | 1 | | | 1 (0)| 00:00:01 |
| 63 | TABLE ACCESS BY INDEX ROWID | PRODUCER | 1 | 87 | | 1 (0)| 00:00:01 |
|* 64 | INDEX UNIQUE SCAN | PRODUCER_PRIMARY | 1 | | | 1 (0)| 00:00:01 |
| 65 | TABLE ACCESS BY INDEX ROWID | PATIENT | 1 | 60 | | 1 (0)| 00:00:01 |
|* 66 | INDEX UNIQUE SCAN | PATIENT_PRIMARY | 1 | | | 1 (0)| 00:00:01 |
|* 67 | INDEX RANGE SCAN | USERS_PRODUCER | 1 | | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter( NOT EXISTS (SELECT 0 FROM AXIUM."USERS" "USERS" WHERE "Custom3"='YES' AND LNNVL("User"<>:B1)) OR
TO_CHAR(INTERNAL_FUNCTION("P"."EndDate"),'YYYY')<'2011' OR TRIM(TO_CHAR(INTERNAL_FUNCTION("P"."EndDate"),'YYYY')) IS
NULL OR "TRX"."Procedure"='A0021' OR "TRX"."Procedure"='A0022')
4 - access("TRX"."Producer"="U1"."Producer")
6 - access(ROWID=ROWID)
9 - access("PROC"."Discipline"="CLASS"."Class")
11 - access("TRX"."Producer"="P"."Producer")
13 - access("TRX"."Patient"="PAT"."Patient")
15 - access("TRX"."Procedure"="PROC"."Procedure")
16 - filter("PROC"."Discipline" IS NOT NULL)
17 - filter("TRX"."Deleted"=0 AND "TRX"."Status"='C' AND "TRX"."Procedure" NOT LIKE 'D0149%' AND
"TRX"."Procedure"<>'D5001C')
18 - filter(LNNVL("User"<>:B1))
19 - access("Custom3"='YES')
24 - access("GI"."QuestionText"="LLU"."GRADITEM_QuestionText" AND "GI"."Text"="LLU"."GRADITEM_Text" AND
"TRX"."Procedure"="LLU"."ProcedureCode")
26 - access("TRX"."Type"="GI"."Type" AND "TRX"."Id"="GI"."Id" AND "TRX"."Treatment"="GI"."Treatment")
27 - access("TRX"."Procedure"="PROC"."Procedure")
31 - access("PROC"."Discipline"="CLASS"."Class")
filter("PROC"."Discipline"="CLASS"."Class")
32 - filter("PROC"."Discipline" IS NOT NULL)
33 - filter("TRX"."Grading"<>0 AND "TRX"."Deleted"=0 AND "TRX"."Status"='C')
34 - filter("GI"."Grading"<>0)
35 - filter("G"."Deleted"=0)
36 - access("TRX"."Grading"="G"."Grading")
filter("G"."Grading"<>0 AND "G"."Grading"="GI"."Grading")
38 - access("TRX"."Producer"="P"."Producer")
40 - access("TRX"."Producer"="U1"."Producer")
42 - access("TRX"."Patient"="PAT"."Patient")
52 - filter("GI"."IsHeading"=3 AND TRIM("GI"."QuestionText")='Comments' AND "GI"."Grading"<>0)
53 - filter("TRX"."Grading"<>0 AND "TRX"."Deleted"=0 AND "TRX"."Status"='C' AND ("TRX"."Procedure"='G1002' OR
"TRX"."Procedure"='G1003'))
54 - access("GI"."Type"="TRX"."Type" AND "GI"."Id"="TRX"."Id" AND "GI"."Treatment"="TRX"."Treatment")
55 - filter("TRX"."Grading"<>0 AND "TRX"."Deleted"=0 AND "TRX"."Status"='C' AND ("TRX"."Procedure"='G1002' OR
"TRX"."Procedure"='G1003') AND "TRX"."Grading"="TRX"."Grading")
56 - access("TRX"."Type"="TRX"."Type" AND "TRX"."Id"="TRX"."Id" AND "TRX"."Treatment"="TRX"."Treatment")
57 - filter("GI"."RelValue"<>0 AND "TRX"."Type"="GI"."Type" AND "TRX"."Treatment"="GI"."Treatment")
58 - access("TRX"."Id"="GI"."Id")
59 - filter("G"."Deleted"=0)
60 - access("TRX"."Grading"="G"."Grading")
filter("G"."Grading"<>0 AND "GI"."Grading"="G"."Grading")
62 - access("TRX"."Discipline"="CLASS"."Class")
64 - access("TRX"."Producer"="P"."Producer")
66 - access("TRX"."Patient"="PAT"."Patient")
67 - access("TRX"."Producer"="U1"."Producer")
123 rows selected.
Statistics
----------------------------------------------------------
615 recursive calls
0 db block gets
2956548 consistent gets
0 physical reads
0 redo size
85073034 bytes sent via SQL*Net to client
56588 bytes received via SQL*Net from client
5116 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
511488 rows processed
This is from tkprof:
select * from llu_v_production_detail_04
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.46 0.46 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5116 52.35 52.51 0 2956443 0 511488
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5118 52.82 52.98 0 2956443 0 511488
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57
Rows Row Source Operation
------- ---------------------------------------------------
511488 VIEW LLU_V_PRODUCTION_DETAIL_04 (cr=2956443 pr=0 pw=0 time=49865193 us)
511488 UNION-ALL (cr=2956443 pr=0 pw=0 time=49353701 us)
468175 FILTER (cr=964033 pr=0 pw=0 time=6330921 us)
472475 HASH JOIN (cr=146796 pr=0 pw=0 time=4496790 us)
1727 VIEW index$_join$_007 (cr=24 pr=0 pw=0 time=4811 us)
1727 HASH JOIN (cr=24 pr=0 pw=0 time=3084 us)
1727 INDEX FAST FULL SCAN USERS_PRIMARY (cr=7 pr=0 pw=0 time=51 us)(object id 55023)
1727 INDEX FAST FULL SCAN USERS_PRODUCER (cr=17 pr=0 pw=0 time=14 us)(object id 55024)
490890 HASH JOIN (cr=146772 pr=0 pw=0 time=3682413 us)
20 TABLE ACCESS FULL CLASS (cr=7 pr=0 pw=0 time=83 us)
513573 HASH JOIN (cr=146765 pr=0 pw=0 time=4867239 us)
1396 TABLE ACCESS FULL PRODUCER (cr=107 pr=0 pw=0 time=15 us)
513573 HASH JOIN (cr=146658 pr=0 pw=0 time=3833055 us)
189447 TABLE ACCESS FULL PATIENT (cr=31792 pr=0 pw=0 time=189474 us)
513573 HASH JOIN (cr=114866 pr=0 pw=0 time=2058955 us)
896 TABLE ACCESS FULL PROCEDUR (cr=46 pr=0 pw=0 time=66 us)
515127 TABLE ACCESS FULL TRX (cr=114820 pr=0 pw=0 time=1547639 us)
8656 TABLE ACCESS BY INDEX ROWID USERS (cr=817237 pr=0 pw=0 time=1523133 us)
402823 INDEX RANGE SCAN USERS_CUSTOM3 (cr=97544 pr=0 pw=0 time=312677 us)(object id 77876)
43137 NESTED LOOPS (cr=512849 pr=0 pw=0 time=3784423 us)
43137 NESTED LOOPS (cr=426124 pr=0 pw=0 time=3266774 us)
45738 NESTED LOOPS (cr=304430 pr=0 pw=0 time=2532984 us)
45738 NESTED LOOPS (cr=212520 pr=0 pw=0 time=2121329 us)
45792 HASH JOIN (cr=120555 pr=0 pw=0 time=1710469 us)
95 TABLE ACCESS FULL LLU_EVALUATION_DESCRIPTIONS (cr=7 pr=0 pw=0 time=133 us)
99263 HASH JOIN (cr=120548 pr=0 pw=0 time=1420060 us)
47769 HASH JOIN (cr=110519 pr=0 pw=0 time=913399 us)
786 MERGE JOIN (cr=50 pr=0 pw=0 time=1571 us)
20 TABLE ACCESS BY INDEX ROWID CLASS (cr=4 pr=0 pw=0 time=101 us)
20 INDEX FULL SCAN CLASS_PRIMARY (cr=1 pr=0 pw=0 time=12 us)(object id 53850)
786 SORT JOIN (cr=46 pr=0 pw=0 time=793 us)
896 TABLE ACCESS FULL PROCEDUR (cr=46 pr=0 pw=0 time=23 us)
47969 TABLE ACCESS FULL TRX (cr=110469 pr=0 pw=0 time=866368 us)
705607 TABLE ACCESS FULL GRADITEM (cr=10029 pr=0 pw=0 time=279 us)
45738 TABLE ACCESS BY INDEX ROWID GRADING (cr=91965 pr=0 pw=0 time=422437 us)
45739 INDEX UNIQUE SCAN GRADING_PRIMARY (cr=46226 pr=0 pw=0 time=219792 us)(object id 54088)
45738 TABLE ACCESS BY INDEX ROWID PRODUCER (cr=91910 pr=0 pw=0 time=359758 us)
45738 INDEX UNIQUE SCAN PRODUCER_PRIMARY (cr=46172 pr=0 pw=0 time=159886 us)(object id 54581)
43137 TABLE ACCESS BY INDEX ROWID USERS (cr=121694 pr=0 pw=0 time=719570 us)
43137 INDEX RANGE SCAN USERS_PRODUCER (cr=46606 pr=0 pw=0 time=253267 us)(object id 55024)
43137 TABLE ACCESS BY INDEX ROWID PATIENT (cr=86725 pr=0 pw=0 time=409047 us)
43137 INDEX UNIQUE SCAN PATIENT_PRIMARY (cr=43571 pr=0 pw=0 time=196851 us)(object id 54370)
176 TABLE ACCESS BY INDEX ROWID USERS (cr=49782 pr=0 pw=0 time=1754680 us)
367 NESTED LOOPS (cr=49505 pr=0 pw=0 time=6011929 us)
190 NESTED LOOPS (cr=49311 pr=0 pw=0 time=405916 us)
190 NESTED LOOPS (cr=48928 pr=0 pw=0 time=404012 us)
190 NESTED LOOPS (cr=48545 pr=0 pw=0 time=402104 us)
191 NESTED LOOPS (cr=48352 pr=0 pw=0 time=406618 us)
193 NESTED LOOPS (cr=47965 pr=0 pw=0 time=416120 us)
193 NESTED LOOPS (cr=47344 pr=0 pw=0 time=411274 us)
193 NESTED LOOPS (cr=46762 pr=0 pw=0 time=409140 us)
14424 TABLE ACCESS FULL GRADITEM (cr=9601 pr=0 pw=0 time=72218 us)
193 TABLE ACCESS BY INDEX ROWID TRX (cr=37161 pr=0 pw=0 time=105613 us)
8310 INDEX UNIQUE SCAN TRX_PRIMARY (cr=28851 pr=0 pw=0 time=52469 us)(object id 54930)
193 TABLE ACCESS BY INDEX ROWID TRX (cr=582 pr=0 pw=0 time=1436 us)
193 INDEX UNIQUE SCAN TRX_PRIMARY (cr=389 pr=0 pw=0 time=618 us)(object id 54930)
193 TABLE ACCESS BY INDEX ROWID GRADITEM (cr=621 pr=0 pw=0 time=3274 us)
1547 INDEX RANGE SCAN GRADITEM_ID (cr=393 pr=0 pw=0 time=1058 us)(object id 54093)
191 TABLE ACCESS BY INDEX ROWID GRADING (cr=387 pr=0 pw=0 time=1309 us)
191 INDEX UNIQUE SCAN GRADING_PRIMARY (cr=196 pr=0 pw=0 time=669 us)(object id 54088)
190 TABLE ACCESS BY INDEX ROWID CLASS (cr=193 pr=0 pw=0 time=897 us)
190 INDEX UNIQUE SCAN CLASS_PRIMARY (cr=3 pr=0 pw=0 time=365 us)(object id 53850)
190 TABLE ACCESS BY INDEX ROWID PRODUCER (cr=383 pr=0 pw=0 time=1129 us)
190 INDEX UNIQUE SCAN PRODUCER_PRIMARY (cr=193 pr=0 pw=0 time=500 us)(object id 54581)
190 TABLE ACCESS BY INDEX ROWID PATIENT (cr=383 pr=0 pw=0 time=1350 us)
190 INDEX UNIQUE SCAN PATIENT_PRIMARY (cr=193 pr=0 pw=0 time=620 us)(object id 54370)
176 INDEX RANGE SCAN USERS_PRODUCER (cr=194 pr=0 pw=0 time=737 us)(object id 55024)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 5116 0.00 0.00
SQL*Net message from client 5116 0.01 7.78
SQL*Net more data to client 39939 0.00 0.58
********************************************************************************
I attempted to post this message including the SQL statement and it exceeded the message length limit.
I will post this message and then reply to it with the SQL statement.
Thank you in advance for your help - I am learning a lot about query tuning through this experience.
Perhaps it's not too late to teach an old dog...
Carl