Wednesday, March 31, 2010

Difference with "traceonly explain", "autotrace on" and "traceonly statistics"

Explain plan is supported by default:

SQL> set autotrace traceonly explain


But reporting statistics requires plustrace role.

SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

SQL> set autotrace traceonly statistics
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report



SQL> get ?\sqlplus\admin\plustrce.sql
1 --
2 -- Copyright (c) Oracle Corporation 1995, 2002. All Rights Reserved.
3 --
4 -- NAME
5 -- plustrce.sql
6 --
7 -- DESCRIPTION
8 -- Creates a role with access to Dynamic Performance Tables
9 -- for the SQL*Plus SET AUTOTRACE ... STATISTICS command.
10 -- After this script has been run, each user requiring access to
11 -- the AUTOTRACE feature should be granted the PLUSTRACE role by
12 -- the DBA.
13 --
14 -- USAGE
15 -- sqlplus "sys/knl_test7 as sysdba" @plustrce
16 --
17 -- Catalog.sql must have been run before this file is run.
18 -- This file must be run while connected to a DBA schema.
19 set echo on
20 drop role plustrace;
21 create role plustrace;
22 grant select on v_$sesstat to plustrace;
23 grant select on v_$statname to plustrace;
24 grant select on v_$mystat to plustrace;
25 grant plustrace to dba with admin option;
26* set echo off
27

Monday, March 15, 2010

Partition by range with Interval example



SQL> select min(time_id) from sh.sales;

MIN(TIME_ID)
--------------------
1998-JAN-01 00:00:00

SQL> create table sales
2 partition by range (time_id)
3 interval (numtoyminterval(1,'MONTH'))
4 (
5 partition p199801 values less than (to_date('1998-02-01','yyyy-mm-dd'))
6 )
7 as select * from sh.sales;

Table created.

SQL> create index sales_n1 on sales(time_id) local;

Index created.

SQL> exec dbms_stats.gather_table_stats('','sales');

PL/SQL procedure successfully completed.

SQL> set lin 120
SQL> col partition for a10
SQL> col high_value for a80
SQL> col num_rows for 99999999
SQL> set pages 999


SQL> select partition_name,high_value,num_rows
2 from user_tab_partitions
3 where table_name='SALES';

PARTITION_ HIGH_VALUE NUM_ROWS
---------- -------------------------------------------------------------------------------- ---------
P199801 TO_DATE(' 1998-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 15132
SYS_P69 TO_DATE(' 1998-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 14307
SYS_P70 TO_DATE(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 14248
SYS_P71 TO_DATE(' 1998-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 11818
SYS_P72 TO_DATE(' 1998-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 12309
SYS_P73 TO_DATE(' 1998-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 11631
SYS_P74 TO_DATE(' 1998-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 16257
SYS_P75 TO_DATE(' 1998-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 17199
SYS_P76 TO_DATE(' 1998-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 17059
SYS_P77 TO_DATE(' 1998-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 19878
SYS_P79 TO_DATE(' 1998-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 14612
SYS_P78 TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 14384
SYS_P80 TO_DATE(' 1999-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 20437
SYS_P81 TO_DATE(' 1999-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 24122
SYS_P82 TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 19627
SYS_P83 TO_DATE(' 1999-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 17004
SYS_P84 TO_DATE(' 1999-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 19213
SYS_P85 TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 18016
SYS_P86 TO_DATE(' 1999-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 21889
SYS_P87 TO_DATE(' 1999-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 22225
SYS_P88 TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 23024
SYS_P89 TO_DATE(' 1999-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 22256
SYS_P91 TO_DATE(' 1999-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 21259
SYS_P90 TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 18873
SYS_P92 TO_DATE(' 2000-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 22135
SYS_P93 TO_DATE(' 2000-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 20609
SYS_P94 TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 19453
SYS_P95 TO_DATE(' 2000-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 17481
SYS_P96 TO_DATE(' 2000-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 20046
SYS_P97 TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 17988
SYS_P98 TO_DATE(' 2000-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 18534
SYS_P99 TO_DATE(' 2000-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 20369
SYS_P100 TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 20047
SYS_P101 TO_DATE(' 2000-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 21542
SYS_P102 TO_DATE(' 2000-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 19709
SYS_P103 TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 14733
SYS_P104 TO_DATE(' 2001-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 20739
SYS_P105 TO_DATE(' 2001-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 19185
SYS_P106 TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 20684
SYS_P107 TO_DATE(' 2001-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 21667
SYS_P108 TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 19188
SYS_P109 TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 22437
SYS_P110 TO_DATE(' 2001-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 21860
SYS_P112 TO_DATE(' 2001-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 23330
SYS_P111 TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 20579
SYS_P115 TO_DATE(' 2001-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 24537
SYS_P113 TO_DATE(' 2001-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 22403
SYS_P114 TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 22809

48 rows selected.