Unit size in DBA_HIST_TBSPC_SPACE_USAGE

Question : What is the unit of the below columns in DBA_HIST_TBSPC_SPACE_USAGE?
a. tablespace_size
b. tablespace_maxsize
c. tablespace_usedsize

Answer : They are reprensted in no. of blocks. In other words to get the actual size from this view you need to multiply the values with the DBA_TABLESPACES.BLOCK_SIZE

I used this simple query ( you can tune this query if you see it running slow) to get the tablespace growth report on weekly basis.

select /*+ parallel(c, 5) */
to_char(c.BEGIN_INTERVAL_TIME,'MON'),
to_char(c.BEGIN_INTERVAL_TIME,'WW'),
b.name,
round(max(a.tablespace_size*d.block_size)/1024/1024) tblsize_in_MB,
round(max(a.tablespace_maxsize*d.block_size)/1024/1024) totalsize_in_MB,
round(max(a.tablespace_usedsize*d.block_size)/1024/1024) usedsize_in_MB
from DBA_HIST_TBSPC_SPACE_USAGE a, v$tablespace b,
DBA_HIST_SNAPSHOT c, dba_tablespaces d
where a.tablespace_id=b.ts#
and a.snap_id=c.snap_id
and b.name=d.tablespace_name
and b.name in ('USERS')
group by to_char(c.BEGIN_INTERVAL_TIME,'MON'),
to_char(c.BEGIN_INTERVAL_TIME,'WW'),
b.name
order by 3,2,1;


Keywords : DBA_HIST_TBSPC_SPACE_USAGE, size, unit

STORED OUTLINE in Oracle 10g

Scope of this document

1. This applies to Oracle 10g
2. How to use STORED Outline to fix a performance issue?
3. Ho to change the plan in the outline after generating it?
4. Some Pros & Cons of STORED OUTLINE
   

This document also highlight some of the key facts that an Oracle DBA should be aware of while using STORED OUTLINE.

Facts

1. When the desired execution plan is occuring for a SQL statement which is not identical in SQL text (whitespacing and case differences ignored) to the original SQL statement, then a Stored Outline for that SQL is not usable as the signature of the Stored Outline does not match
2. In other words, STORED OUTLINE are driven by the SIGNATURE of an inidivual SQL statement.
3. To create private stored outline do not use the procedure DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES in Oracle10g and Oracle11g
   
4. DBMS_OUTLN.CREATE_OUTLINE procedure is hard parsing and using the optimizer environment of the session executing the procedure instead of using the one from the library cache. This is a BUG. Hence the stored outline created out of this procedure might differ from the original plan.
   
5. Unlike the traditional way, creating the stored outline in the lower environment and do export/import to get the outline to production, Oracle 10g has got the new way of creating it directly using the SQL ID, SQL_HASH_VALUE, SQL_CHILD_NUMBEr from the memory.
   
6. STORED OUTLINES doesnot work when you try it as SYS user.
   
7. Important : Though outlines are used and you follow all the steps correctly, NOT ALWAYS the execution plan is imposed as expected/desired.
   
8. When swapping the outline (to replace the BAD plan with a GOOD plan) ensure the HINTCOUNT column is taken care.
   
9. Generally you may have read CURSOR_SHARING = FORCE disables the use of stored outlines. Actually the interpretation should be the other way.
   i.e.
   STORED OUTLINE creation also captures the essential optimizer paramaters which is also assessed before overwriting a plan.
   If there is a mismatch then STORED OUTLINE will not be used for a SQL statement.
   For Eg.
   If OL1 is created with CURSOR_SHARING=FORCE, then a session with CURSOR_SHARING=EXACT/SIMILAR would not be able to use the outline OL1.
   In this scenario STORED OUTLINE is disabled.
   
10. PRIVATE outlines are automatically created in SYSTEM.OL$ table and this is a local temporary table. Hence the records vanishes the moment you exit the session
    
11. To use stored outline for your SQL statements a. alter session set use_stored_outlines=true; b. Ensure CURSOR_SHARING is NOT set to FORCE
    

Scenario

I have a single query which is using a BAD plan in the production database.You have the SQL_ID, SQL_HASH_VALUE & SQL_CHILD_NUMBER of this query which is using the BAD plan.

On the other side you either tune it by adding HINTS or have another version of this same query which is runinng fine in the same production database. What do I mean by another version of this same query?

For example, I ran the same SQL statement from another SQL prompt after substituting the BIND variables with the literalsand confirmed the execution plan was GOOD.

Important Note : This GOOD one was having different SQL_ID & SQL_HASH_VALUE

Goal of this article,

a. From the same database, create the OUTLINE out of the SQL statement which is having the GOOD plan

b. Make the original query adopt the above outline
Remember, all these steps are done in the same production database and out of the buffer cache.

Assumptions:

a. connect to a schema with CREATE ANY OUTLINE privilege

b. Perform these tasks as any schema other than SYS

c. Using DEFAULT category

d. The schema should have privilege to query v$sql & v$session

e. HINTCOUNT is same for both GOOD & BAD plan

Overall Steps

a) Generate the outline, name it OL1, for the BAD plan using the seperate SQL_HASH_VALUE & SQL_CHILD_NUMBER.

b) Generate an another outline, name it OL2, for the GOOD plan using seperate SQL_HASH_VALUE & SQL_CHILD_NUMBER.
Eg.
GOOD : select * from emp; ===> OL1

BAD : select * from emp where ename='scott'; ===> OL2

Method 1

c) Update/Edit the outline OL2 by replacing with the values of OL1, so that both OL1 & OL2 should look alike.

Method 2

c) Rename the outline OL2 to OL1 and viceversa.

Method 3

c) Create a new private outline "PRIVOL2" out of OL2

d) Edit the private outline

f) resynchronize the private Stored Outline

g) Replace the original outline OL2 with this edited private outline "PRIVOL2"

Method 4

c) Create a new private outline "PRIVOL1" out of OL1

d) Create a new private outline "PRIVOL2" out of OL2

e) Edit the private outline

f) resynchronize the private Stored Outline

g) Replace the original outline OL2 with this edited private outline "PRIVOL2"

Detailed Steps

create table emp (empno number, ename varchar2(10)) tablespace users01;

create index empidx on emp(empno,ename) tablespace users01;

begin
for i in 1..200 loop
insert into emp values (i,'blake');
end loop;
commit;
end;
/

begin
for i in 1..200 loop
insert into emp values (i,'scott');
end loop;
commit;
end;
/

execute DBMS_STATS.GATHER_TABLE_STATS(ownname =>'SCOTT', tabname => 'EMP', estimate_percent => 100, cascade => TRUE);

Open two seperate sqlplus session as SCOTT user
In Session 1 : select distinct sid from v$mystat; ----- Lets assume the SID # 514

In Session 1 : select * from emp;

In session 2 :

select sid,serial#,sql_id,sql_child_number,sql_hash_value,prev_sql_id,prev_hash_value,prev_child_number from v$session where sid=514;

In session 1 : select * from emp where ename='scott';

In session 2 :

select sid,serial#,sql_id,sql_child_number,sql_hash_value,prev_sql_id,prev_hash_value,prev_child_number from v$session where sid=514;

From the above exercise we got SQL ID, SQL HASH VALUE and CHILD NUMBER of both GOOD & BAD sql statement.

GOOD : select * from emp; ===> OL1

BAD : select * from emp where ename='scott'; ===> OL2

SQL_ID SQL_HASH_VALUE SQL_PLAN_HASH_VALUE SQL_CHILD_NUMBER SQL_TEXT============== ============== =================== ================ =====================================a2dk8bdn0ujx7 1745700775 2872589290 0 select * from emp;7h38thzz3wug9 4265503209 818394873 0 select * from emp where ename='badri'
select * from TABLE(dbms_xplan.display_cursor('a2dk8bdn0ujx7',0));
Plan hash value: 2872589290
-------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time -------------------------------------------------------------------------- 0 SELECT STATEMENT 2 (100) 1 TABLE ACCESS FULL EMP 60 540 2 (0) 00:00:01

select * from TABLE(dbms_xplan.display_cursor('7h38thzz3wug9',0));
Plan hash value: 818394873
--------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time --------------------------------------------------------------------------- 0 SELECT STATEMENT 1 (100) * 1 INDEX FULL SCAN EMPIDX 20 180 1 (0) 00:00:01

Goal : To make the BAD sql statement to use FULL TABLE SCAN instead of INDEX FULL SCAN.
Steps Common to ALL methods

1) Create outline using the SQL HASH VALUE & CHILD NUMBER

alter session set create_stored_outlines=true;

exec dbms_outln.CREATE_OUTLINE(1745700775,0);

exec dbms_outln.CREATE_OUTLINE(4265503209,0);

alter session set create_stored_outlines=false;

2) Query them to confirm the presence
col ol_name form a30

col sql_text form a40

col category form a10

col hint_text form a75

set lines 200
select ol_name,hintcount,HASH_VALUE,CATEGORY,HASH_VALUE2,SIGNATURE,sql_text from outln.ol$;

select ol_name,hint#,hint_text from outln.ol$hints order by 1,2;

3) Rename them for easy use

alter outline SYS_OUTLINE_09061700365898670 rename to OL1;

commit;

alter outline SYS_OUTLINE_09061700365899172 rename to OL2;

commit;

Method 1

UPDATE outln.ol$hints a SET a.hint_text=(select b.hint_text from outln.ol$hints b where b.ol_name='OL1' and a.hint#=b.hint#) where a.ol_name='OL2'; and a.hint#=1;

commit;

Method 2

UPDATE outln.ol$hints SET ol_name = decode(ol_name, 'OL1','OL2','OL2','OL1') WHERE ol_name in ('OL1','OL2');

commit;

Method 3

create private outline PRIVOL2 from OL2;

commit;

update system.ol$hints set hint_text='FULL(@"SEL$1" http://www.blogger.com/'mailto: where ol_name='PRIVOL1' and hint#=6;

commit;

execute dbms_outln_edit.refresh_private_outline('PRIVOL2');

create or replace outline OL2 from private PRIVOL2;

Method 4

create private outline PRIVOL1 from OL1;

commit;

create private outline PRIVOL2 from OL2;

commit;

UPDATE system.ol$hints SET ol_name = decode(ol_name, 'PRIVOL1','PRIVOL2','PRIVOL2','PRIVOL1') WHERE ol_name in ('PRIVOL1','PRIVOL2');

commit;

execute dbms_outln_edit.refresh_private_outline('PRIVOL1');

commit;

execute dbms_outln_edit.refresh_private_outline('PRIVOL2');

commit;

create or replace outline OL2 from private PRIVOL2;

Keywords:

stored; outline; create;memory ; cache; edit;swap;dbms_outln.CREATE_OUTLINE

SELECT/DML operation on a Table HANGS

You would wonder why an operation on a particular table hangs for ever whereas the table has got just hundred thousand records.In this context operation means SELECT/INSERT/UPDATE/DELETE.
We had a scenario in our production environment where users were unable to transact on a particular table. As a first step, we confirmed that this HANG is specific only to the table and not for the entire Database. Subsequently we started with our below approach

a. Checked and confirmed that this table had the recent statistics which was generated on the day begining.

b. Opened a new session to run the query
"select count(*) from table_name

;"

We expected this to do a FTS (Full Table Scan).

c. Wait Events

We checked for any wait events in the database for that session as well as for the whole database, using the below command.
SELECT EVENT,COUNT(*) FROM V$SESSION_WAIT WHERE WAIT_TIME=0 GROUP BY EVENT;
We couldn't spot out any particular event affecting this hang as all where regular wait events. This particular session was waiting for DB FILE SEQUENTIAL READ wait event which we felt was an obvious though we expected to wait for DB FILE SCATTERED READ. as it should do a FTS.

d. Optimizer issue : DELETE STATISTICS

Cancelled the previous session, deleted the table statistics using DBMS_STATS.DELETE_TABLE_STATS and then ran the same query again. No change the same behaviour and same wait event.

e. 10046 event trace

Hence we decided to cancel the above session inorder to open a new one and to start doing 10046 trace to find out where it goes.

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
followed by the select count(*).

Trace file started displaying the wait event "DB FILE SEQUENTIAL READ" on the undo tablespace file. Here we got to know that server process is scanning undo blocks which would probably mean that it is doing a CONSISTENT READ. But neither a transactions is happening on that table nor a rollback at the background. After spotting the last few data blocks, which were before the server process decides to scan undo blocks, dumped those and found nothing suspecious.

ALTER SYSTEM DUMP DATAFILE file_no BLOCK block_number;

f. SYSTEMSTATE dump

Finally we decided to take a systemstate dump on the HANGING process.

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 266';

From the trace file we got a hint. The trace file got a blockdump which belongs to the table on which the problem was reported. The spurious fact which we identified from the blockdump was an active transaction in the ITL list whilst there is no active TXN on that table. We could easily guess that PMON could have failed to clean somewhere the failed txns. Hence we the solution was to clear the ITL.

g. Solution

We just ran a simple update followed by a rollback which resolved the problem.

UPDATE table_name set date_field=sysdate where ROWNUM <>

ROLLBACK;

Regards Badri