Month: January 2007

REPOST: Pivot and Crosstab Queries

Here is another advanced concept that will come in useful when solving Oracle problems. Imagine you’re trying to create a result set where the rows need to be columns, or vice versa. In essence, you need to "pivot" rows into columns, or vice versa. That is a very common requirement, and this is where you need to look at a pivot (or crosstab) query to get the job done.As always, when you want

Logical Reads and Orange Trees

My previous post was a riddle aimed to challenge us to really think about logical I/O (session logical reads). Usually we think of I/O in terms of OS block(s), memory pages, Oracle blocks, Oracle buffer cache buffers, etc. In Oracle, a logical I/O is n…

Logical Reads and Orange Trees

My previous post was a riddle aimed to challenge us to really think about logical I/O (session logical reads). Usually we think of I/O in terms of OS block(s), memory pages, Oracle blocks, Oracle buffer cache buffers, etc. In Oracle, a logical I/O is n…

Oracle Riddles: What’s Missing From This Code?

The SQL script below has one line intentionally omitted. The missing statement had a material impact on the performance of the targeted query. I have put diagnostic bookends around the targeted query to show that no DML or DDL has been issued to alter the result. In short, the script inserts 32K rows into a test table. I issue a query requiring a full table scan, run a single statement and rerun the same query – also a full table scan. While the second query returns the same number of rows, it performs far fewer logical I/O operations to achieve the same result set. Review the output from the script. Can you fill in the missing statement? Fictitious bonus points will be awarded for the Oracle scholar that can deduce the precise statement 🙂

/* Script blog.sql


spool blog.out
set feed on echo on;
select * from v$version;
drop table mytable;
create table mytable (col1 number) tablespace users;
insert into mytable values (3);
commit;
begin
for i in 1..15 loop
insert into mytable select * from mytable;
commit;
end loop;
end;
/
analyze table mytable compute statistics;
select count(*) from mytable;
select blocks from dba_tables where table_name = ‘MYTABLE’;
select blocks from dba_segments where segment_name = ‘MYTABLE’;
select index_name from user_indexes where table_name = ‘MYTABLE’;
set autot traceonly;
select * from mytable;
set autot off;
REM Bookends to show no DML or DDL statement has been executed.
select statistic#, value from v$mystat where statistic# in (4,134);
… missing statement
REM Bookends to show no DML or DDL statement has been executed.
select statistic#, value from v$mystat where statistic# in (4,134);
set autot traceonly;
select * from mytable;
set autot off;
select blocks from dba_tables where table_name = ‘MYTABLE’;
select blocks from dba_segments where segment_name = ‘MYTABLE’;
select index_name from user_indexes where table_name = ‘MYTABLE’;
select count(*) from mytable;
spool off;


End Script blog.sql */

/* Output

oracle@eemrick:SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bi
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for Solaris: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production
5 rows selected.
oracle@eemrick:SQL> drop table mytable;
Table dropped.
oracle@eemrick:SQL> create table mytable (col1 number) tablespace users;
Table created.
oracle@eemrick:SQL> insert into mytable values (3);
1 row created.
oracle@eemrick:SQL> commit;
Commit complete.
oracle@eemrick:SQL> begin
2 for i in 1..15 loop
3 insert into mytable select * from mytable;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
oracle@eemrick:SQL> analyze table mytable compute statistics;
Table analyzed.
oracle@eemrick:SQL> select count(*) from mytable;
COUNT(*)
———-
32768
1 row selected.
oracle@eemrick:SQL> select blocks from dba_tables where table_name =
‘MYTABLE’;
BLOCKS
———-
61
1 row selected.
oracle@eemrick:SQL> select blocks from dba_segments where segment_name =
‘MYTABLE’;
BLOCKS
———-
64
1 row selected.
oracle@eemrick:SQL> select index_name from user_indexes where table_name =
‘MYTABLE’;
no rows selected
oracle@eemrick:SQL> set autot traceonly;
oracle@eemrick:SQL> select * from mytable;
32768 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1229213413
—————————————————————————–
Id Operation Name Rows Bytes Cost (%CPU) Time

—————————————————————————–
0 SELECT STATEMENT 32768 65536 26 (4) 00:00:01

1 TABLE ACCESS FULL MYTABLE 32768 65536 26 (4) 00:00:01

—————————————————————————–

Statistics
———————————————————-
1 recursive calls
0 db block gets
2248 consistent gets
0 physical reads
0 redo size
668925 bytes sent via SQL*Net to client
24492 bytes received via SQL*Net from client
2186 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32768 rows processed
oracle@eemrick:SQL> set autot off;
oracle@eemrick:SQL> REM Bookends to show no DML or DDL statement has been
executed.
oracle@eemrick:SQL> select statistic#, value from v$mystat where statistic#
in (4,134);
STATISTIC# VALUE
———- ———-
4 18
134 461920
2 rows selected.
oracle@eemrick:SQL> … missing echo of statement
oracle@eemrick:SQL> REM Bookends to show no DML or DDL statement has been
executed.
oracle@eemrick:SQL> select statistic#, value from v$mystat where statistic#
in (4,134);
STATISTIC# VALUE
———- ———-
4 18
134 461920
2 rows selected.
oracle@eemrick:SQL> set autot traceonly;
oracle@eemrick:SQL> select * from mytable;
32768 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1229213413
—————————————————————————–
Id Operation Name Rows Bytes Cost (%CPU) Time

—————————————————————————–
0 SELECT STATEMENT 32768 65536 26 (4) 00:00:01

1 TABLE ACCESS FULL MYTABLE 32768 65536 26 (4) 00:00:01

—————————————————————————–

Statistics
———————————————————-
0 recursive calls
0 db block gets
173 consistent gets
0 physical reads
0 redo size
282975 bytes sent via SQL*Net to client
1667 bytes received via SQL*Net from client
111 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32768 rows processed
oracle@eemrick:SQL> set autot off;
oracle@eemrick:SQL> select blocks from dba_tables where table_name =
‘MYTABLE’;
BLOCKS
———-
61
1 row selected.
oracle@eemrick:SQL> select blocks from dba_segments where segment_name =
‘MYTABLE’;
BLOCKS
———-
64
1 row selected.
oracle@eemrick:SQL> select index_name from user_indexes where table_name =
‘MYTABLE’;
no rows selected
oracle@eemrick:SQL> select count(*) from mytable;
COUNT(*)
———-
32768
1 row selected.
oracle@eemrick:SQL> spool off;

End Output */

Clue: The missing statement is not “alter system set do_less_work = true;”

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569