Oracle Technology Network
Oracle Magazine Issue Archive
2010
January 2010
Oracle Magazine Issue Archive
2010
January 2010
Code Listing 1:
Complete query results.
SQL> set echo on
SQL> set serveroutput on size unlimited
SQL> PROMPT <== LOC_NOCOMP ==>
<== LOC_NOCOMP ==>
SQL> alter system flush buffer_cache
2 /
System altered.
Elapsed: 00:00:05.80
SQL> col value noprint new_value start_cpu
SQL> select value
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('CPU used by this session')
6 /
Elapsed: 00:00:00.15
SQL> col value noprint new_value start_reads
SQL> select value
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('session logical reads')
6 /
Elapsed: 00:00:00.00
SQL> set autot traceonly explain stat
SQL> set timing on
SQL> select city,APPLICATION_ID,
2 avg((sysdate-CREATION_DATE))
3 from loc_nocomp
4 group by city,APPLICATION_ID
5 order by city,APPLICATION_ID
6 /
16378 rows selected.
Elapsed: 00:00:05.31
Execution Plan
----------------------------------------------------------
Plan hash value: 1298412388
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 113M| 5842M| 7022 (3)| 00:01:39 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10002 | 113M| 5842M| 7022 (3)| 00:01:39 | Q1,02 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 113M| 5842M| 7022 (3)| 00:01:39 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 113M| 5842M| 7022 (3)| 00:01:39 | Q1,02 | PCWP | |
| 5 | PX SEND RANGE | :TQ10001 | 113M| 5842M| 7022 (3)| 00:01:39 | Q1,01 | P->P | RANGE |
| 6 | SORT GROUP BY | | 113M| 5842M| 7022 (3)| 00:01:39 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 113M| 5842M| 7022 (3)| 00:01:39 | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 113M| 5842M| 7022 (3)| 00:01:39 | Q1,00 | P->P | HASH |
| 9 | HASH GROUP BY | | 113M| 5842M| 7022 (3)| 00:01:39 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 113M| 5842M| 6981 (2)| 00:01:38 | Q1,00 | PCWC | |
| 11 | TABLE ACCESS STORAGE FULL| LOC_NOCOMP | 113M| 5842M| 6981 (2)| 00:01:38 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
1131 recursive calls
1 db block gets
2507953 consistent gets
2098606 physical reads
168 redo size
786847 bytes sent via SQL*Net to client
12524 bytes received via SQL*Net from client
1093 SQL*Net roundtrips to/from client
257 sorts (memory)
0 sorts (disk)
16378 rows processed
SQL> set autot off
SQL> select value - &start_cpu cpu_consumed
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('CPU used by this session')
6 /
old 1: select value - &start_cpu cpu_consumed
new 1: select value - 4114266 cpu_consumed
CPU_CONSUMED
------------
14637
Elapsed: 00:00:00.01
SQL> select value - &start_reads logical_reads
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('session logical reads')
6 /
old 1: select value - &start_reads logical_reads
new 1: select value - 19463093 logical_reads
LOGICAL_READS
-------------
2512966
Elapsed: 00:00:00.01
SQL>
SQL> PROMPT <== LOC_COMPQRYLOW ==>
<== LOC_COMPQRYLOW ==>
SQL> REM Next
SQL> clear columns
SQL> alter system flush buffer_cache
2 /
System altered.
Elapsed: 00:00:00.02
SQL> col value noprint new_value start_cpu
SQL> select value
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('CPU used by this session')
6 /
Elapsed: 00:00:00.00
SQL> col value noprint new_value start_reads
SQL> select value
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('session logical reads')
6 /
Elapsed: 00:00:00.00
SQL> set autot traceonly explain stat
SQL> set timing on
SQL> select city,APPLICATION_ID,
2 avg((sysdate-CREATION_DATE))
3 from LOC_COMPQRYLOW
4 group by city,APPLICATION_ID
5 order by city,APPLICATION_ID
6 /
16378 rows selected.
Elapsed: 00:00:03.17
Execution Plan
----------------------------------------------------------
Plan hash value: 3641572576
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 110M| 5707M| 1276 (11)| 00:00:18 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10002 | 110M| 5707M| 1276 (11)| 00:00:18 | Q1,02 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 110M| 5707M| 1276 (11)| 00:00:18 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 110M| 5707M| 1276 (11)| 00:00:18 | Q1,02 | PCWP | |
| 5 | PX SEND RANGE | :TQ10001 | 110M| 5707M| 1276 (11)| 00:00:18 | Q1,01 | P->P | RANGE |
| 6 | SORT GROUP BY | | 110M| 5707M| 1276 (11)| 00:00:18 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 110M| 5707M| 1276 (11)| 00:00:18 | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 110M| 5707M| 1276 (11)| 00:00:18 | Q1,00 | P->P | HASH |
| 9 | HASH GROUP BY | | 110M| 5707M| 1276 (11)| 00:00:18 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 110M| 5707M| 1236 (9)| 00:00:18 | Q1,00 | PCWC | |
| 11 | TABLE ACCESS STORAGE FULL| LOC_COMPQRYLOW | 110M| 5707M| 1236 (9)| 00:00:18 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
971 recursive calls
1 db block gets
551861 consistent gets
350109 physical reads
168 redo size
785871 bytes sent via SQL*Net to client
12524 bytes received via SQL*Net from client
1093 SQL*Net roundtrips to/from client
257 sorts (memory)
0 sorts (disk)
16378 rows processed
SQL> set autot off
SQL> select value - &start_cpu cpu_consumed
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('CPU used by this session')
6 /
old 1: select value - &start_cpu cpu_consumed
new 1: select value - 4128904 cpu_consumed
CPU_CONSUMED
------------
14936
Elapsed: 00:00:00.01
SQL> select value - &start_reads logical_reads
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('session logical reads')
6 /
old 1: select value - &start_reads logical_reads
new 1: select value - 21976063 logical_reads
LOGICAL_READS
-------------
552684
Elapsed: 00:00:00.01
SQL> PROMPT <== LOC_COMPQRYHIGH ==>
<== LOC_COMPQRYHIGH ==>
SQL> REM Next
SQL> clear columns
SQL> alter system flush buffer_cache
2 /
System altered.
Elapsed: 00:00:00.02
SQL> col value noprint new_value start_cpu
SQL> select value
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('CPU used by this session')
6 /
Elapsed: 00:00:00.01
SQL> col value noprint new_value start_reads
SQL> select value
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('session logical reads')
6 /
Elapsed: 00:00:00.00
SQL> set autot traceonly explain stat
SQL> set timing on
SQL> select city,APPLICATION_ID,
2 avg((sysdate-CREATION_DATE))
3 from LOC_COMPQRYHIGH
4 group by city,APPLICATION_ID
5 order by city,APPLICATION_ID
6
SQL> /
16378 rows selected.
Elapsed: 00:00:03.19
Execution Plan
----------------------------------------------------------
Plan hash value: 2740595817
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 117M| 6026M| 846 (18)| 00:00:12 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10002 | 117M| 6026M| 846 (18)| 00:00:12 | Q1,02 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 117M| 6026M| 846 (18)| 00:00:12 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 117M| 6026M| 846 (18)| 00:00:12 | Q1,02 | PCWP | |
| 5 | PX SEND RANGE | :TQ10001 | 117M| 6026M| 846 (18)| 00:00:12 | Q1,01 | P->P | RANGE |
| 6 | SORT GROUP BY | | 117M| 6026M| 846 (18)| 00:00:12 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 117M| 6026M| 846 (18)| 00:00:12 | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 117M| 6026M| 846 (18)| 00:00:12 | Q1,00 | P->P | HASH |
| 9 | HASH GROUP BY | | 117M| 6026M| 846 (18)| 00:00:12 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 117M| 6026M| 804 (14)| 00:00:12 | Q1,00 | PCWC | |
| 11 | TABLE ACCESS STORAGE FULL| LOC_COMPQRYHIGH | 117M| 6026M| 804 (14)| 00:00:12 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
971 recursive calls
1 db block gets
401043 consistent gets
220973 physical reads
168 redo size
782595 bytes sent via SQL*Net to client
12524 bytes received via SQL*Net from client
1093 SQL*Net roundtrips to/from client
257 sorts (memory)
0 sorts (disk)
16378 rows processed
SQL> set autot off
SQL> select value - &start_cpu cpu_consumed
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('CPU used by this session')
6 /
old 1: select value - &start_cpu cpu_consumed
new 1: select value - 4143841 cpu_consumed
CPU_CONSUMED
------------
14783
Elapsed: 00:00:00.02
SQL> select value - &start_reads logical_reads
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('session logical reads')
6 /
old 1: select value - &start_reads logical_reads
new 1: select value - 22528751 logical_reads
LOGICAL_READS
-------------
401666
Elapsed: 00:00:00.00
SQL>
SQL> PROMPT <== LOC_COMPARCLOW ==>
<== LOC_COMPARCLOW ==>
SQL>
SQL> REM
SQL> clear columns
SQL> alter system flush buffer_cache
2 /
System altered.
Elapsed: 00:00:00.03
SQL> col value noprint new_value start_cpu
SQL> select value
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('CPU used by this session')
6 /
Elapsed: 00:00:00.00
SQL> col value noprint new_value start_reads
SQL> select value
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('session logical reads')
6 /
Elapsed: 00:00:00.01
SQL> set autot traceonly explain stat
SQL> set timing on
SQL> select city,APPLICATION_ID,
2 avg((sysdate-CREATION_DATE))
3 from LOC_COMPARCLOW
4 group by city,APPLICATION_ID
5 order by city,APPLICATION_ID
6 /
16378 rows selected.
Elapsed: 00:00:05.57
Execution Plan
----------------------------------------------------------
Plan hash value: 1092122105
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 348M| 17G| 1014 (44)| 00:00:15 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10002 | 348M| 17G| 1014 (44)| 00:00:15 | Q1,02 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 348M| 17G| 1014 (44)| 00:00:15 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 348M| 17G| 1014 (44)| 00:00:15 | Q1,02 | PCWP | |
| 5 | PX SEND RANGE | :TQ10001 | 348M| 17G| 1014 (44)| 00:00:15 | Q1,01 | P->P | RANGE |
| 6 | SORT GROUP BY | | 348M| 17G| 1014 (44)| 00:00:15 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 348M| 17G| 1014 (44)| 00:00:15 | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 348M| 17G| 1014 (44)| 00:00:15 | Q1,00 | P->P | HASH |
| 9 | HASH GROUP BY | | 348M| 17G| 1014 (44)| 00:00:15 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 348M| 17G| 881 (36)| 00:00:13 | Q1,00 | PCWC | |
| 11 | TABLE ACCESS STORAGE FULL| LOC_COMPARCLOW | 348M| 17G| 881 (36)| 00:00:13 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
971 recursive calls
1 db block gets
314644 consistent gets
185706 physical reads
168 redo size
783730 bytes sent via SQL*Net to client
12524 bytes received via SQL*Net from client
1093 SQL*Net roundtrips to/from client
257 sorts (memory)
0 sorts (disk)
16378 rows processed
SQL> set autot off
SQL> select value - &start_cpu cpu_consumed
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('CPU used by this session')
6 /
old 1: select value - &start_cpu cpu_consumed
new 1: select value - 4158625 cpu_consumed
CPU_CONSUMED
------------
15966
Elapsed: 00:00:00.03
SQL> select value - &start_reads logical_reads
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('session logical reads')
6 /
old 1: select value - &start_reads logical_reads
new 1: select value - 22930421 logical_reads
LOGICAL_READS
-------------
315292
Elapsed: 00:00:00.00
SQL>
SQL> PROMPT <== LOC_COMPARCHIGH==>
<== LOC_COMPARCHIGH==>
SQL> REM next
SQL> clear columns
SQL> alter system flush buffer_cache
2 /
System altered.
Elapsed: 00:00:00.02
SQL> col value noprint new_value start_cpu
SQL> select value
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('CPU used by this session')
6 /
Elapsed: 00:00:00.01
SQL> col value noprint new_value start_reads
SQL> select value
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('session logical reads')
6 /
Elapsed: 00:00:00.00
SQL> set autot traceonly explain stat
SQL> set timing on
SQL> select city,APPLICATION_ID,
2 avg((sysdate-CREATION_DATE))
3 from LOC_COMPARCHIGH
4 group by city,APPLICATION_ID
5 order by city,APPLICATION_ID
6 /
16378 rows selected.
Elapsed: 00:00:05.73
Execution Plan
----------------------------------------------------------
Plan hash value: 1629204427
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 83M| 4283M| 541 (20)| 00:00:08 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10002 | 83M| 4283M| 541 (20)| 00:00:08 | Q1,02 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 83M| 4283M| 541 (20)| 00:00:08 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 83M| 4283M| 541 (20)| 00:00:08 | Q1,02 | PCWP | |
| 5 | PX SEND RANGE | :TQ10001 | 83M| 4283M| 541 (20)| 00:00:08 | Q1,01 | P->P | RANGE |
| 6 | SORT GROUP BY | | 83M| 4283M| 541 (20)| 00:00:08 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 83M| 4283M| 541 (20)| 00:00:08 | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 83M| 4283M| 541 (20)| 00:00:08 | Q1,00 | P->P | HASH |
| 9 | HASH GROUP BY | | 83M| 4283M| 541 (20)| 00:00:08 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 83M| 4283M| 511 (15)| 00:00:08 | Q1,00 | PCWC | |
| 11 | TABLE ACCESS STORAGE FULL| LOC_COMPARCHIGH | 83M| 4283M| 511 (15)| 00:00:08 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
971 recursive calls
1 db block gets
228167 consistent gets
143629 physical reads
168 redo size
736415 bytes sent via SQL*Net to client
12524 bytes received via SQL*Net from client
1093 SQL*Net roundtrips to/from client
257 sorts (memory)
0 sorts (disk)
16378 rows processed
SQL> set autot off
SQL> select value - &start_cpu cpu_consumed
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('CPU used by this session')
6 /
old 1: select value - &start_cpu cpu_consumed
new 1: select value - 4174592 cpu_consumed
CPU_CONSUMED
------------
15452
Elapsed: 00:00:00.01
SQL> select value - &start_reads logical_reads
2 from v$sesstat s, v$statname n
3 where sid = (select sid from v$mystat where rownum < 2)
4 and s.statistic# = n.statistic#
5 and n.name in ('session logical reads')
6 /
old 1: select value - &start_reads logical_reads
new 1: select value - 23245717 logical_reads
LOGICAL_READS
-------------
228630
Elapsed: 00:00:00.00
SQL> spool off
|
Printer View