Oracle Dataware House Features
- Show file IO
- Show Table Partitions
- Create Table PartitionsEach partition creates an initial extent.
- Create Local indexesCan be using the same or different columns as the table partition
- Explain Plan, New ColumnsBe sure to add the new columns Pstart and Pstop to your explain plan, or use UTLXPLS.SQL
- Parallel Query –vs- Partition elimination
- Hash Joins –vs- Sort JoinsHash Joins
- Select with Rollup
- Select with Cube
- Select with Sample Clause
- Select with Top NTop N SQL
- Long Running Queries Some types of queries, like full table scans, can now be monitored.
- Character SetsPerformance
- All TPC timing use USASCII7There is a small performance penalty for moving even to WEISO9660P1 (about 4-10%).
- Data Types, Dump them
1. Show file IO
SELECT FILE#, PHYRDS, PHYWRTS, total, Name FROM
(
SELECT f.FILE#,f.PHYRDS,f.PHYWRTS,f.PHYBLKRD,f.PHYBLKWRT,
(f.PHYBLKRD+f.PHYBLKWRT)+(f.PHYRDS+f.PHYWRTS) total, d.BYTES/1024 Kbytes, substr(d.NAME, 1, 35) Name
FROM v$filestat f,v$datafile d
WHERE f.FILE# = d.FILE#
ORDER by 6 desc
)
WHERE ROWNUM < 31;
FILE# PHYRDS PHYWRTS TOTAL NAME
-------- --------- --------- --------- -------------------------------
46 22402 2311 449391 /emc/fs2/temp3dw8.dbf
16 13590 3 447675 /emc/fs3/ord_lin_1_dat.dbf
40 22307 2282 447072 /emc/fs2/temp4dw8.dbf
28 13009 3 428402 /emc/fs5/ord_lin_1_dat2.dbf
22 10393 3 342078 /emc/fs3/ord_lin_2_dat.dbf
29 9915 3 326403 /emc/fs5/ord_lin_2_dat2.dbf
FILE# PHYRDS PHYWRTS PHYBLKRD PHYBLKWRT TOTAL BYTES NAME
---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------------
19 6345112 179509 8696501 179509 15400631 2097152000 /rdbms1/lg500_2_dat.dbf
46 231709 20531 2346540 2601897 5200677 2097152000 /rdbms2/temp3dw8.dbf
43 898269 947182 898406 947182 3691039 2097152000 /rdbms3/lg1000_4_idx.dbf
3 209318 13310 1376811 1683960 3283399 2097152000 /rdbms2/tempdw8.dbf
14 1007471 88711 1967517 88711 3152410 2097152000 /rdbms1/lg500_1_dat.dbf
2. Show Table Partitions
SELECT TABLE_NAME,TABLESPACE_NAME,PCT_FREE,NUM_ROWS,BLOCKS,AVG_ROW_LEN,PARTITION_NAME,HIGH_VALUE
FROM dba_tab_partitions
TABLE_NAME TABLESPACE_NAME PCT_FREE NUM_ROWS BLOCKS AVG_ROW_LE PARTITION_NAME HIGH_VALUE
-------------- ---------------- ---------- ---------- ---------- ---------- ----------------------------- -----------
ORD_LIN_ITM ORD_LIN_DAT2 5 30878460 509525 117 ORDL_1997 '1998'
ORD_LIN_ITM ORD_LIN_DAT 5 28983978 476719 117 ORDL_1998 '1999'
ORD_LIN_ITM ORD_LIN_DAT2 5 13108973 214564 116 ORDL_199Q2 '199907'
ORD_LIN_ITM ORD_LIN_DAT 5 6125637 100255 116 ORDL_199Q3 '199910'
ORD_LIN_ITM ORD_LIN_DAT2 5 4053131 65537 115 ORDL_199Q4 '2000'
ORD_LIN_ITM ORD_LIN_DAT 5 12 1 107 ORDL_2000Q1 '200004'
ORD_LIN_ITM ORD_LIN_DAT2 5 13676 7254 111 ORDL_OTHER MAXVALUE
ORD_LIN_ITM ORD_LIN_DAT 5 25858761 429325 118 ORDL_1996 '1997'
Use Tablespace Manager from OEM 1.6 or newer to display table partitions
Tablespace Manager will also display global indexes and local index partitions.
3. Create Table Partitions
Each partition creates an initial extent.
PROMPT Creating Table 'ORD_HDR'
CREATE TABLE ORD_HDR
(ID NUMBER(8) NOT NULL
,CUST_ID NUMBER(8) NOT NULL
,ENTRY_DT DATE NOT NULL
,SHIP_DT DATE
,PRICE_AMT NUMBER(10,6)
,TAX_AMT NUMBER(10,6)
,SHIP_ADD_LIN1 VARCHAR2(50)
,SHIP_CITY VARCHAR2(50)
,SHIP_ST_CD VARCHAR2(3)
,SHIP_ZIP VARCHAR2(16)
)
PCTUSED 40
PCTFREE 10
STORAGE ( INITIAL 10K NEXT 10K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 121 )
TABLESPACE USER_DAT
PARTITION BY RANGE (ENTRY_DT)
(PARTITION O199912 VALUES LESS THAN (TO_DATE('01-01-2000', 'MM-DD-YYYY'))
STORAGE ( INITIAL 10K NEXT 10K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 121 )
TABLESPACE USER_DAT
,PARTITION O200001 VALUES LESS THAN (TO_DATE('02-01-2000', 'MM-DD-YYYY'))
STORAGE ( INITIAL 10K NEXT 10K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 121 )
TABLESPACE USER_DAT
,PARTITION O200002 VALUES LESS THAN (TO_DATE('03-01-2000', 'MM-DD-YYYY'))
STORAGE ( INITIAL 10K NEXT 10K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 121 )
TABLESPACE USER_DAT
,PARTITION O200003 VALUES LESS THAN (TO_DATE('04-01-2000', 'MM-DD-YYYY'))
STORAGE ( INITIAL 10K NEXT 10K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 121 )
TABLESPACE USER_DAT
);
This example will create a new partitioned version of the table ord_hdr
3 months per partition for last 12 months.
Drop table sale_hdr
/
create table sale_hdr
pctfree 5
partition by range (batch_id)
(
partition ordh_199q1 values less than ('199904')
tablespace lg1000_dat
storage (initial 200m next 100m),
partition ordh_199q2 values less than ('199907')
tablespace lg1000_dat
storage (initial 200m next 100m),
partition ordh_199q3 values less than ('199910')
tablespace lg1000_dat
storage (initial 100m next 100m),
partition ordh_199q4 values less than ('2000')
tablespace lg1000_dat
storage (initial 100m next 100m),
partition ordh_2000q1 values less than ('200004')
tablespace lg1000_dat
storage (initial 100m next 100m),
partition ordh_other values less than (MAXVALUE)
tablespace lg1000_dat
storage (initial 100m next 100m)
)
as select * from ord_hdr
/
rename ord_hdr to ord_hdr_old
/
rename sale_hdr to ord_hdr
/
4. Create Local indexes
Can be using the same or different columns as the table partition
PROMPT Creating Index 'ORD_HDR_IDX1'
CREATE INDEX ORD_HDR_IDX1 ON ORD_HDR
(ENTRY_DT)
PCTFREE 5
STORAGE
(
INITIAL 10K
NEXT 10K
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 121
)
TABLESPACE USER_IDX
LOCAL
(PARTITION O199912
TABLESPACE USER_IDX
,PARTITION O200001
TABLESPACE USER_IDX
,PARTITION O200002
TABLESPACE USER_IDX
,PARTITION O200003
TABLESPACE USER_IDX
)
/
5. Explain Plan, New Columns
Be sure to add the new columns Pstart and Pstop to your explain plan, or use UTLXPLS.SQL
Example:
EXPLAIN PLAN FOR SELECT * FROM EMP_RANGE;
Then enter the following to display the EXPLAIN PLAN output:
@d:/orant/RDBMS/ADMIN/UTLXPLS.SQL
Oracle displays something similar to:
Plan Table
-------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart | Pstop|
-------------------------------------------------------------------------------
| SELECT STATEMENT | | 105 | 8K| 1 | | |
| PARTITION RANGE ALL | | | | | 1 | 5 |
| TABLE ACCESS FULL |EMP_RANGE | 105 | 8K| 1 | 1 | 5 |
-------------------------------------------------------------------------------
6 rows selected.
Plan table definition statement in the file UTLXPLAN.SQL
create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(30),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long);
6. Parallel Query –vs- Partition elimination
Which makes a bigger performance difference?
7. Hash Joins –vs- Sort Joins
Hash Joins
Set your hash_area_size init.ora parameter big enough but not too big.
For a large data warehouse, HASH_AREA_SIZE may range from 8MB to 32MB or more. This parameter provides for adequate memory for hash joins. Each process performing a parallel hash join uses an amount of memory equal to HASH_AREA_SIZE.
Hash join performance is more sensitive to HASH_AREA_SIZE than sort performance is to SORT_AREA_SIZE. As with SORT_AREA_SIZE, too large a hash area may cause the system to run out of memory.
If the sort area is too small, an excessive amount of I/O is required to merge a large number of sort runs. If the sort area size is smaller than the amount of data to sort, then the sort will move to disk, creating sort runs. These must then be merged again using the sort area.
If the sort area is too large, the operating system paging rate will be excessive
8. Select with Rollup
The expr in the GROUPING function must match one of the expressions in the GROUP BY clause. The function returns a value of 1 if the value of expr in the row is a null representing the set of all values. Otherwise, it returns zero. The datatype of the value returned by the GROUPING function is an Oracle NUMBER datatype. See the group_by_clause of the SELECT statement on page 7-549 for a discussion of these terms.
Example
SELECT DECODE(GROUPING(dname), 1, 'All Departments', dname) AS dname,
DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal"
FROM emp, dept
WHERE dept.deptno = emp.deptno
GROUP BY ROLLUP (dname, job);
DNAME JOB Total Empl Average Sa
--------------- --------- ---------- ----------
ACCOUNTING CLERK 1 15600
ACCOUNTING MANAGER 1 29400
ACCOUNTING PRESIDENT 1 60000
ACCOUNTING All Jobs 3 35000
RESEARCH ANALYST 2 36000
RESEARCH CLERK 2 11400
RESEARCH MANAGER 1 35700
RESEARCH All Jobs 5 26100
SALES CLERK 1 11400
SALES MANAGER 1 34200
SALES SALESMAN 4 16800
SALES All Jobs 6 18800
All Departments All Jobs 14 24878.5714
9. Select with Cube
SELECT DECODE(GROUPING(dname), 1, 'All Departments',dname) AS dname,
DECODE(GROUPING(job), 1, 'All Jobs', job) AS job, COUNT(*) "Total Emp", AVG(sal) * 12 "Average Sal"
FROM emp, dept
WHERE dept.deptno = emp.deptno
GROUP BY CUBE (dname, job);
DNAME JOB Total Emp Average Sal
--------------- ---------- -------- -----------
ACCOUNTING CLERK 1 15600
ACCOUNTING MANAGER 1 29400
ACCOUNTING PRESIDENT 1 60000
ACCOUNTING All Jobs 3 35000
RESEARCH ANALYST 2 36000
RESEARCH CLERK 2 11400
RESEARCH MANAGER 1 35700
RESEARCH All Jobs 5 26100
SALES CLERK 1 11400
SALES MANAGER 1 34200
SALES SALESMAN 4 16800
SALES All Jobs 6 18800
All Departments ANALYST 2 36000
All Departments CLERK 4 12450
All Departments MANAGER 3 33100
All Departments PRESIDENT 1 60000
All Departments SALESMAN 4 16800
All Departments All Jobs 14 24878.5714
10. Select with Sample Clause
sample_clause causes Oracle to select from a random sample of rows from the table, rather than from the entire table.
• BLOCK instructs Oracle to perform random block sampling instead of random row sampling. See Oracle8i Concepts.
• sample_percent is a number specifying the percentage of the total row or block count to be included in the sample. The value must be in the range .000001 to 99.
Restrictions:
• You can specify SAMPLE only in a query that selects from a single table. Joins are not supported.
• When you specify SAMPLE, Oracle automatically uses the cost-based optimizer. The rule-based optimizer is not supported.
FROM TABLE SAMPLE BLOCK ( sample_percent )
FROM PARTITION (partition)
11. Select with Top N
Top N SQL
SELECT * FROM
(SELECT empno FROM emp ORDER BY empno)
WHERE ROWNUM < 6;
EMPNO
--------
7369
7499
7521
7566
7654
12. Long Running Queries
Some types of queries, like full table scans, can now be monitored.
select username, serial#, to_char(start_time, 'HH:MI') as start_time,
trunc((sofar/totalwork+0.0001)*100, 2) as Percent,
substr(opname,1,20) as operation_type
from v$session_longops
where sofar/(totalwork+0.0001) < .99
/
USERNAME SERIAL# START PERCENT OPERATION_TYPE
------------------------------ --------- ----- --------- ---------------------------
SCOTT 32 05:36 80.1 Table Scan
13. Character Sets
Performance
All TPC timing use USASCII7
There is a small performance penalty for moving even to WEISO9660P1 (about 4-10%).
Unicode
UTC2: 2 byte fixed width, not near enough(Windows)
UTF8: 1 to 3 bytes variable width comes close, best choice for now (IE)
Client Support
7.3.4 or 8.0.4 or newer Client required for connecting to database with Unicode character set.
Requires third parties to recompile with new Oracle support files, we had a vendor who hadn’t so test yours.
Character Set Conversion
Changing the database character set to a new character set
Possible but painful unless you only have 7-bit ASCII data
14. Data Types, Dump them
Who knows Oracles internal type code for a varchar2?
Number (type 2)
first byte is sign High order bit (0 = negative, 1 = positive),
and exponent (64 is added),
remaining bytes are the mantissa (two numbers per byte, 1 is added)
Select 1200 number, dump (1200) from dual;
Date (type 13)
8-byte format for internal tables and sysdate
Date (type 12)
7-byte format
First 2 bytes: century and year (excess 100 format)
Next 2 bytes: month and day
Last 3 bytes: Hours minutes and seconds
19-Jul-1999 17:07:54 is stored as 119,199,7,19,18,8,55
Select to_char(hiredate, ‘dd-Mon-yyyy hh24:mi:ss’) dump (hiredate) from emp where ename = ‘SCOTT’;
Char (type 96)
Blank padded
Select dname, dump(dname) from dept; (assume dname is char(10))
Varchar2 (type 1)
Select dname, dump(dname) from dept; (assume dname is varchar2(10))