Home » SQL & PL/SQL » SQL & PL/SQL » why does SELECT DISTINCT return different rows when used with ORDER BY? (11g)
| why does SELECT DISTINCT return different rows when used with ORDER BY? [message #573929] |
Fri, 04 January 2013 05:54  |
horicel
Messages: 8
Registered: September 2009
|
Junior Member |
|
|
Hi, nex lines showing SELECT DISTINCT return different rows when used with ORDER BY.
Please help me understand why.
CREATE TABLE M1( ID_ NUMBER, A NUMBER, B NUMBER);
CREATE TABLE V1 ( A NUMBER, B2 NUMBER);
DELETE FROM M1;
DELETE FROM V1;
INSERT INTO M1 (ID_, A,B) VALUES (107, 5,1);
INSERT INTO M1 (ID_, A,B) VALUES (108, 11,1);
INSERT INTO M1 (ID_, A,B) VALUES (109, 17,1);
INSERT INTO M1 (ID_, A,B) VALUES (110, 34,1);
INSERT INTO V1 (A,B2) VALUES (5,7);
INSERT INTO V1 (A,B2) VALUES (5,7);
INSERT INTO V1 (A,B2) VALUES (5,7);
INSERT INTO V1 (A,B2) VALUES (5,4);
INSERT INTO V1 (A,B2) VALUES (11,4);
INSERT INTO V1 (A,B2) VALUES (11,4);
INSERT INTO V1 (A,B2) VALUES (11,4);
INSERT INTO V1 (A,B2) VALUES (11,7);
INSERT INTO V1 (A,B2) VALUES (17,7);
INSERT INTO V1 (A,B2) VALUES (17,7);
INSERT INTO V1 (A,B2) VALUES (42,4);
SELECT * FROM M1;
SELECT * FROM V1;
SELECT DISTINCT
V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
FROM V1
WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1;
A B2
-----
5 2
11 2
17 2
42 1 *
SELECT DISTINCT
V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
FROM V1
WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ;
A B2
-----
5 2
11 2
17 2
42 2 *
|
|
|
|
| Re: why does SELECT DISTINCT return different rows when used with ORDER BY? [message #573932 is a reply to message #573929] |
Fri, 04 January 2013 06:25  |
cookiemonster
Messages: 9169
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Looks like a bug. I get:
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
SQL> SELECT DISTINCT
V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
FROM V1
WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1; 2 3 4
A B2
---------- ----------
5 2
11 2
17 2
42 1
SQL> SELECT DISTINCT
V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
FROM V1
WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ; 2 3 4
A B2
---------- ----------
42 1
5 2
11 2
17 2
SQL>
Same results.
Post your exact oracle version and explain plan for both queries.
|
|
|
|
| Re: why does SELECT DISTINCT return different rows when used with ORDER BY? [message #573934 is a reply to message #573929] |
Fri, 04 January 2013 06:54 |
 |
Michel Cadot
Messages: 54246
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
No problem in 10.2.0.4:
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1;
A B2
---------- ----------
5 2
11 2
17 2
42 1
4 rows selected.
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ;
A B2
---------- ----------
5 2
11 2
42 1
17 2
4 rows selected.
SQL> @v
Version Oracle : 10.2.0.4.0
Regards
Michel
[Updated on: Fri, 04 January 2013 06:55] Report message to a moderator |
|
|
|
|
|
| Re: why does SELECT DISTINCT return different rows when used with ORDER BY? [message #574022 is a reply to message #573935] |
Sat, 05 January 2013 09:56 |
horicel
Messages: 8
Registered: September 2009
|
Junior Member |
|
|
hi, version is Oracle Database 11g Release 11.2.0.1.0 - 64bit Production.
plan for first command:
SELECT DISTINCT
V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
FROM V1
WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1
COST CARDINALITY BYTES
SELECT STATEMENT, GOAL = ALL_ROWS 9 2 24
SORT UNIQUE 9 2 24
MERGE JOIN ANTI NA 8 2 24
SORT JOIN 4 11 66
TABLE ACCESS FULL DEV2.V1 3 11 66
SORT UNIQUE 4 4 24
TABLE ACCESS FULL DEV2.M1 3 4 24
plan for SECOND command:
SELECT DISTINCT
V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
FROM V1
WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1)
COST CARDINALITY BYTES
SELECT STATEMENT, GOAL = ALL_ROWS 9 2 24
SORT UNIQUE NOSORT 9 2 24
MERGE JOIN ANTI NA 8 2 24
SORT JOIN 4 11 66
TABLE ACCESS FULL DEV2.V1 3 11 66
SORT UNIQUE 4 4 24
TABLE ACCESS FULL DEV2 M1 3 4 24
Thanks for refering to this behaviour as a "bug", I thought my mind was playing tricks on me.
[Edit MC: add code tags]
[Updated on: Sat, 05 January 2013 10:00] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: why does SELECT DISTINCT return different rows when used with ORDER BY? [message #574043 is a reply to message #574026] |
Sun, 06 January 2013 01:44 |
horicel
Messages: 8
Registered: September 2009
|
Junior Member |
|
|
Hi Michel,
I was able to repro it by playing around with rows (i.e. deleting them, re-inserting them one by one - in a way that I know that should not make a difference, since the tables ended up having the same rows, as shown by SELECT * FROM M1; SELECT * FROM V1;). The latter returns
A B2
----------
5 7
5 7
5 4
11 4
11 4
11 4
11 7
17 7
17 7
42 4
5 7
as expected, and the SELECT DISTINCT queries 1 and 2 return different results, as initially posted.
I am sorry it will take me a little until I can provide with the tkprof-ed trace, as it's my first time using tkprof and apparently I have some issues identifying the right trc file.
Please note that the example provided by me with M1 and V1 is a simple one created in order to point the "bug", I have seen it somewhere else as well. Will get back.
|
|
|
|
|
|
| Re: why does SELECT DISTINCT return different rows when used with ORDER BY? [message #574266 is a reply to message #574045] |
Tue, 08 January 2013 05:59 |
horicel
Messages: 8
Registered: September 2009
|
Junior Member |
|
|
Hi Michel,
I do not have a repro sequence, starting with CREATE TABLE etc. that leads to the erroneous behaviour.
What I do have is the possibility of running the queries over and over ( on the tables that I have already populated ) in order to see the bug and produce trace files.
Below you can find 2 traces , the first showing no bug (it was produced in another schema of my server using same lines of code for setting up M1 and V1 ) and the second showing the bug (somehow the contents of the tables result in different results for query 1 and query 2).
If I replace (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) with (CASE WHEN V1.B2 = 7 THEN 3 ELSE 1 END) I keep seeing the bug.
If I replace (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) with (CASE WHEN V1.B2 = 7 THEN '2' ELSE '1' END) I cannot see the bug.
Thanks for your support.
Horia
-- First session (trace trace28022.txt)
SQL> ALTER SESSION SET SQL_TRACE = TRUE;
Session altered
SQL>
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1;
A B2
---------- ----------
5 2
11 2
17 2
42 1
SQL>
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1;
A B2
---------- ----------
5 2
11 2
17 2
42 1
SQL>
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ;
A B2
---------- ----------
5 2
11 2
42 1
17 2
SQL>
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ;
A B2
---------- ----------
5 2
11 2
42 1
17 2
SQL> select a.spid from v$process a, v$session b where a.addr = b.paddr and b.audsid = userenv('sessionid');
SPID
------------------------
28022
2 -- trace trace28022.txt -- for first session
TKPROF: Release 11.2.0.1.0 - Development on Tue Jan 8 13:29:13 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Trace file: DB11G_ora_28022.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
********************************************************************************
SQL ID: 2p7j1vq4k466w
Plan Hash: 0
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 mode: ALL_ROWS
Parsing user id: 99
********************************************************************************
SQL ID: 9m7787camwh4m
Plan Hash: 0
begin :id := sys.dbms_transaction.local_transaction_id; end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 12 0.00 0.00 0 0 0 0
Execute 12 0.00 0.00 0 0 0 12
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 0.00 0.00 0 0 0 12
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 99
********************************************************************************
SQL ID: cf06fwacdmgfk
Plan Hash: 1388734953
select 'x'
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.00 0.00 0 0 0 0
Execute 6 0.00 0.00 0 0 0 0
Fetch 6 0.00 0.00 0 0 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18 0.00 0.00 0 0 0 6
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 99
Rows Row Source Operation
------- ---------------------------------------------------
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
********************************************************************************
SELECT DISTINCT
V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
FROM V1
WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 82 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 82 0 8
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 99
Rows Row Source Operation
------- ---------------------------------------------------
4 SORT UNIQUE (cr=41 pr=0 pw=0 time=6 us cost=22 size=312 card=12)
8 FILTER (cr=41 pr=0 pw=0 time=56 us)
12 TABLE ACCESS FULL V1 (cr=7 pr=0 pw=0 time=33 us cost=3 size=312 card=12)
2 TABLE ACCESS FULL M1 (cr=34 pr=0 pw=0 time=0 us cost=3 size=104 card=4)
********************************************************************************
SELECT DISTINCT
V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
FROM V1
WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 82 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 82 0 8
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 99
Rows Row Source Operation
------- ---------------------------------------------------
4 HASH UNIQUE (cr=41 pr=0 pw=0 time=18 us cost=22 size=104 card=4)
8 FILTER (cr=41 pr=0 pw=0 time=63 us)
12 TABLE ACCESS FULL V1 (cr=7 pr=0 pw=0 time=44 us cost=3 size=312 card=12)
2 TABLE ACCESS FULL M1 (cr=34 pr=0 pw=0 time=0 us cost=3 size=104 card=4)
********************************************************************************
SQL ID: 0cc34t6tu3twv
Plan Hash: 1456042965
select a.spid
from
v$process a, v$session b where a.addr = b.paddr and b.audsid =
userenv('sessionid')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.01 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 99
Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=0 size=156 card=1)
1 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=0 size=143 card=1)
1120 MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=13055 us cost=0 size=360 card=5)
35 FIXED TABLE FULL X$KSUPR (cr=0 pr=0 pw=0 time=136 us cost=0 size=46 card=1)
1120 BUFFER SORT (cr=0 pr=0 pw=0 time=1705 us cost=0 size=2600 card=100)
32 FIXED TABLE FULL X$KSLWT (cr=0 pr=0 pw=0 time=310 us cost=0 size=2600 card=100)
1 FIXED TABLE FIXED INDEX X$KSUSE (ind:1) (cr=0 pr=0 pw=0 time=0 us cost=0 size=71 card=1)
1 FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
********************************************************************************
SQL ID: f711myt0q6cma
Plan Hash: 0
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,
userhost,terminal,action#,returncode, logoff$lread,logoff$pread,
logoff$lwrite,logoff$dead, logoff$time,comment$text,spare1,clientid,
sessioncpu,proxy$sid,user$guid, instance#,process#,auditid,dbid)
values
(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP), :4,:5,:6,:7,:8, :9,:10,:11,
:12, cast(SYS_EXTRACT_UTC(systimestamp) as date),:13,:14,:15,:16,:17,
:18, :19,:20,:21,:22)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 2 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=0 us)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 23 0.00 0.00 0 0 0 0
Execute 24 0.00 0.00 0 0 0 12
Fetch 11 0.01 0.01 0 164 0 23
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 58 0.02 0.01 0 164 0 35
Misses in library cache during parse: 0
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 2 1
Misses in library cache during parse: 0
24 user SQL statements in session.
1 internal SQL statements in session.
25 SQL statements in session.
********************************************************************************
Trace file: DB11G_ora_28022.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
24 user SQL statements in trace file.
1 internal SQL statements in trace file.
25 SQL statements in trace file.
7 unique SQL statements in trace file.
258 lines in trace file.
60 elapsed seconds in trace file.
3 -- Second session (trace trace_bug.txt)
SQL> ALTER SESSION SET SQL_TRACE = TRUE;
Session altered
SQL>
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1;
A B2
---------- ----------
5 2
11 2
17 2
42 1
SQL>
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1;
A B2
---------- ----------
5 2
11 2
17 2
42 1
SQL>
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ;
A B2
---------- ----------
5 2
11 2
17 2
42 2
SQL>
SQL> SELECT DISTINCT
2 V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
3 FROM V1
4 WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ;
A B2
---------- ----------
5 2
11 2
17 2
42 2
SQL> select a.spid from v$process a, v$session b where a.addr = b.paddr and b.audsid = userenv('sessionid');
SPID
------------------------
28861
4 -- trace trace_bug.txt -- for second session
TKPROF: Release 11.2.0.1.0 - Development on Tue Jan 8 13:32:18 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Trace file: DB11G_ora_28861.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
********************************************************************************
SQL ID: 2p7j1vq4k466w
Plan Hash: 0
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 mode: ALL_ROWS
Parsing user id: 90
********************************************************************************
SQL ID: 9m7787camwh4m
Plan Hash: 0
begin :id := sys.dbms_transaction.local_transaction_id; end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 12 0.00 0.00 0 0 0 0
Execute 12 0.00 0.00 0 0 0 12
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 0.00 0.00 0 0 0 12
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 90
********************************************************************************
SQL ID: cf06fwacdmgfk
Plan Hash: 1388734953
select 'x'
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.00 0.00 0 0 0 0
Execute 6 0.00 0.00 0 0 0 0
Fetch 6 0.00 0.00 0 0 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18 0.00 0.00 0 0 0 6
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 90
Rows Row Source Operation
------- ---------------------------------------------------
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
********************************************************************************
SELECT DISTINCT
V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
FROM V1
WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1) ORDER BY 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 28 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 28 0 8
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 90
Rows Row Source Operation
------- ---------------------------------------------------
4 SORT UNIQUE (cr=14 pr=0 pw=0 time=6 us cost=9 size=24 card=2)
7 MERGE JOIN ANTI NA (cr=14 pr=0 pw=0 time=48 us cost=8 size=24 card=2)
11 SORT JOIN (cr=0 pr=0 pw=0 time=20 us cost=4 size=66 card=11)
11 TABLE ACCESS FULL V1 (cr=7 pr=0 pw=0 time=20 us cost=3 size=66 card=11)
4 SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us cost=4 size=24 card=4)
4 TABLE ACCESS FULL M1 (cr=7 pr=0 pw=0 time=6 us cost=3 size=24 card=4)
********************************************************************************
SELECT DISTINCT
V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) B2
FROM V1
WHERE (V1.A, (CASE WHEN V1.B2 = 7 THEN 2 ELSE 1 END) ) NOT IN (SELECT M1.A, M1.B FROM M1)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 28 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 28 0 8
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 90
Rows Row Source Operation
------- ---------------------------------------------------
4 SORT UNIQUE NOSORT (cr=14 pr=0 pw=0 time=54 us cost=9 size=24 card=2)
7 MERGE JOIN ANTI NA (cr=14 pr=0 pw=0 time=54 us cost=8 size=24 card=2)
11 SORT JOIN (cr=0 pr=0 pw=0 time=20 us cost=4 size=66 card=11)
11 TABLE ACCESS FULL V1 (cr=7 pr=0 pw=0 time=20 us cost=3 size=66 card=11)
4 SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us cost=4 size=24 card=4)
4 TABLE ACCESS FULL M1 (cr=7 pr=0 pw=0 time=6 us cost=3 size=24 card=4)
********************************************************************************
SQL ID: 0cc34t6tu3twv
Plan Hash: 1456042965
select a.spid
from
v$process a, v$session b where a.addr = b.paddr and b.audsid =
userenv('sessionid')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.01 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 90
Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=0 size=156 card=1)
1 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=0 size=143 card=1)
1330 MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=6040 us cost=0 size=360 card=5)
38 FIXED TABLE FULL X$KSUPR (cr=0 pr=0 pw=0 time=185 us cost=0 size=46 card=1)
1330 BUFFER SORT (cr=0 pr=0 pw=0 time=1776 us cost=0 size=2600 card=100)
35 FIXED TABLE FULL X$KSLWT (cr=0 pr=0 pw=0 time=408 us cost=0 size=2600 card=100)
1 FIXED TABLE FIXED INDEX X$KSUSE (ind:1) (cr=0 pr=0 pw=0 time=0 us cost=0 size=71 card=1)
1 FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
********************************************************************************
SQL ID: f711myt0q6cma
Plan Hash: 0
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,
userhost,terminal,action#,returncode, logoff$lread,logoff$pread,
logoff$lwrite,logoff$dead, logoff$time,comment$text,spare1,clientid,
sessioncpu,proxy$sid,user$guid, instance#,process#,auditid,dbid)
values
(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP), :4,:5,:6,:7,:8, :9,:10,:11,
:12, cast(SYS_EXTRACT_UTC(systimestamp) as date),:13,:14,:15,:16,:17,
:18, :19,:20,:21,:22)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 2 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=0 us)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 23 0.00 0.00 0 0 0 0
Execute 24 0.00 0.00 0 0 0 12
Fetch 11 0.01 0.01 0 56 0 23
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 58 0.02 0.01 0 56 0 35
Misses in library cache during parse: 0
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 2 1
Misses in library cache during parse: 0
24 user SQL statements in session.
1 internal SQL statements in session.
25 SQL statements in session.
********************************************************************************
Trace file: DB11G_ora_28861.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
24 user SQL statements in trace file.
1 internal SQL statements in trace file.
25 SQL statements in trace file.
7 unique SQL statements in trace file.
260 lines in trace file.
28 elapsed seconds in trace file.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 25 08:36:37 CDT 2013
Total time taken to generate the page: 0.14805 seconds
|
] 
Members
Search
Help
Register
Login
Home
