$cd ~
/home/oracle 10:43 $cd /oracle/database/admin/testdb1/udump
/oracle/database/admin/testdb1/udump 10:43 $rm ./*
/oracle/database/admin/testdb1/udump 10:43 $ls
/oracle/database/admin/testdb1/udump 10:43 $sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Dec 6 10:43:26 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant alter session to scott;
conn scott/tiger;
set time on;
alter session set tracefile_identifier='10046';
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever, level 4';
alter session set "_b_tree_bitmap_plans"=TRUE;
Grant succeeded.
SQL> alter session set "_disable_image_check"=FALSE;
select sum(SAL)
from (select sign(t1.HIREDATE - to_date('20110101', 'yyyymmdd')) as mon,
Connected.
decode(sign(t1.HIREDATE - to_date('20110101', 'yyyymmdd')),
SQL> 10:43:37 SQL> 10:43:37 SQL>
Session altered.
10:43:37 SQL>
Session altered.
10:43:37 SQL>
Session altered.
10:43:37 SQL>
Session altered.
10:43:37 SQL>
Session altered.
10:43:37 SQL> 10:43:37 SQL>
Session altered.
10:43:37 SQL> alter session set "_disable_image_check"=FALSE
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option
10:43:37 SQL> 10:43:37 SQL> 10:43:37 2 10:43:37 3 10:43:37 4 -1,
10:43:37 5 sum(SAL),
10:43:37 6 0) as SAL
10:43:37 7 from emp t1
10:43:37 8 group by sign(t1.HIREDATE - to_date('20110101', 'yyyymmdd')));
decode(sign(t1.HIREDATE - to_date('20110101', 'yyyymmdd')),
*
ERROR at line 3:
ORA-00979: not a GROUP BY expression
10:43:37 SQL>
10:43:37 SQL> alter session set events '10046 trace name context off';
Session altered.
10:43:37 SQL>
10:43:37 SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
/oracle/database/admin/testdb1/udump 10:43 $grep sum\(SAL\) ./*
./testdb1_ora_31143_10046.trc:select sum(SAL)
./testdb1_ora_31143_10046.trc:sum(SAL),
/oracle/database/admin/testdb1/udump 10:43 $cat ./testdb1_ora_31143_10046.trc
Dump file /oracle/database/admin/testdb1/udump/testdb1_ora_31143_10046.trc
*** TRACE DUMP CONTINUED FROM FILE /oracle/database/admin/testdb1/udump/testdb1_ora_31143.trc ***
EXEC #4:c=0,e=92,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1292128337686158
=====================
PARSING IN CURSOR #3 len=41 dep=0 uid=191 oct=42 lid=191 tim=1292128337686424 hv=1994160639 ad='0'
alter session set timed_statistics = true
END OF STMT
PARSE #3:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1292128337686421
EXEC #3:c=0,e=14,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1292128337686473
=====================
PARSING IN CURSOR #4 len=38 dep=0 uid=191 oct=42 lid=191 tim=1292128337686724 hv=1078465550 ad='0'
alter session set statistics_level=all
END OF STMT
PARSE #4:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1292128337686721
EXEC #4:c=1000,e=766,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1292128337687524
=====================
PARSING IN CURSOR #3 len=48 dep=0 uid=191 oct=42 lid=191 tim=1292128337687875 hv=3940589419 ad='0'
alter session set max_dump_file_size = unlimited
END OF STMT
PARSE #3:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1292128337687872
EXEC #3:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1292128337687920
=====================
PARSING IN CURSOR #4 len=68 dep=0 uid=191 oct=42 lid=191 tim=1292128337688290 hv=1896049376 ad='0'
alter session set events '10046 trace name context forever, level 4'
END OF STMT
PARSE #4:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1292128337688287
EXEC #4:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1292128337688370
=====================
PARSING IN CURSOR #3 len=45 dep=0 uid=191 oct=42 lid=191 tim=1292128337688675 hv=3862676628 ad='0'
alter session set "_b_tree_bitmap_plans"=TRUE
END OF STMT
PARSE #3:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1292128337688672
EXEC #3:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1292128337688724
=====================
PARSE ERROR #4:len=47 dep=0 uid=191 oct=42 lid=191 tim=1292128337689175 err=2096
alter session set "_disable_image_check"=FALSE
KGX cleanup...
KGX Atomic Operation Log 0x15d771bb0
Mutex 0x15ea61690(945, 0) idn 100000000 oper EXAM
Cursor Parent uid 945 efd 5 whr 26 slp 0
oper=DEFAULT pt1=(nil) pt2=(nil) pt3=(nil)
pt4=(nil) u41=0 stt=0
=====================
PARSING IN CURSOR #3 len=264 dep=0 uid=191 oct=3 lid=191 tim=1292128337723600 hv=1406633123 ad='5ed90fb8'
select sum(SAL)
from (select sign(t1.HIREDATE - to_date(:"SYS_B_0", :"SYS_B_1")) as mon,
decode(sign(t1.HIREDATE - to_date(:"SYS_B_2", :"SYS_B_3")),
-:"SYS_B_4",
sum(SAL),
:"SYS_B_5") as SAL
from emp t1
group by sign(t1.HIREDATE - to_date(:"SYS_B_6", :"SYS_B_7")))
END OF STMT
PARSE #3:c=0,e=453,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1292128337723597
=====================
PARSING IN CURSOR #4 len=55 dep=0 uid=191 oct=42 lid=191 tim=1292128337736792 hv=2217940283 ad='0'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #4:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1292128337736789
EXEC #4:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1292128337736854
XCTEND rlbk=0, rd_only=1
/oracle/database/admin/testdb1/udump 10:44 $tkprof ./testdb1_ora_31143_10046.trc
output = testdb1_ora_31143_10046.trc.tfprof
TKPROF: Release 10.2.0.4.0 - Production on Tue Dec 6 10:44:35 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
/oracle/database/admin/testdb1/udump 10:44 $cat ./testdb1_ora_31143_10046.trc.tfprof
TKPROF: Release 10.2.0.4.0 - Production on Tue Dec 6 10:44:35 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: ./testdb1_ora_31143_10046.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
********************************************************************************
The following statement encountered a error during parse:
alter session set "_disable_image_check"=FALSE
Error encountered: ORA-02096
********************************************************************************
alter session set timed_statistics = true
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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 191
********************************************************************************
alter session set statistics_level=all
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 191
********************************************************************************
alter session set max_dump_file_size = unlimited
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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 191
********************************************************************************
alter session set events '10046 trace name context forever, level 4'
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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 191
********************************************************************************
alter session set "_b_tree_bitmap_plans"=TRUE
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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 191
********************************************************************************
select sum(SAL)
from (select sign(t1.HIREDATE - to_date(:"SYS_B_0", :"SYS_B_1")) as mon,
decode(sign(t1.HIREDATE - to_date(:"SYS_B_2", :"SYS_B_3")),
-:"SYS_B_4",
sum(SAL),
:"SYS_B_5") as SAL
from emp t1
group by sign(t1.HIREDATE - to_date(:"SYS_B_6", :"SYS_B_7")))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 0 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: 1
Optimizer mode: ALL_ROWS
Parsing user id: 191
********************************************************************************
alter session set events '10046 trace name context off'
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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 191
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
7 user SQL statements in session.
0 internal SQL statements in session.
7 SQL statements in session.
********************************************************************************
Trace file: ./testdb1_ora_31143_10046.trc
Trace file compatibility: 10.01.00
Sort options: default
0 session in tracefile.
7 user SQL statements in trace file.
0 internal SQL statements in trace file.
7 SQL statements in trace file.
7 unique SQL statements in trace file.
63 lines in trace file.
0 elapsed seconds in trace file.
/oracle/database/admin/testdb1/udump 10:44 $