PERFORMANCE AND TUNING

The Automatic Database Diagnostic Monitor

The Automatic Database Diagnostic Monitor ( ADDM ) is an advisor which detects problem area’ s in the database and and which gives recommendations. ADDM uses the statistical data from the AWR - Automatic Workload Repository - stored in the sysaux tablespace. By default the statistical data is gathered / written to disk by snapshots every hour and kept in the sysaux tablespace for 7 days, assuming you haven’ t set the instance parameter statistics_level to basic. ( default = typical ). Please note you can also take AWR Reports which are an equivalent of the statspack Reports from previous releases. (AWR Reports gives even more detailed information )

We can however create a snapshot manually

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

As well we can change the retention period and the snapshot interval. In the example below we change the retention period to 5 days ( 5 x 24 x 60 ) and the snapshot interval to 30 minutes.

SQL> exec dbms_workload_repository.modify_snapshot_settings (retention => 7200,interval => 30);

PL/SQL procedure successfully completed.

Here is an example of how one can take a ADDM report.

SQL> @/app/oracle10g/10.2.0/rdbms/admin/addmrpt.sql;

WHAT IF I AM USING "_ALLOW_RESETLOGS_CORRUPTION"

It’s a Tricky question for Everyone , that has no technically correct answer . Question is ..?

Oracle suggest to rebuild your database after using parameter "_ALLOW_RESETLOGS_CORRUPTION" . Is it mandatory to do that when there is no error reported in alert log or nothing looks suspicious even plying around database .

Well , I thought to go on my way .. its long back my application team is using same GHOST ( Oracle View) Database . But still , I find it like a gentle man without any issue .

So I conclude here : You may feel free to use parameter _ALLOW_RESETLOGS_CORRUPTION , if left with no option .

Call with Metalink on same

August 26, 2009 11:26:05 PM GMT-08:00

Hi,

Thanks for Update .

Regards

Vivek

Oracle Support - August 26, 2009 7:59:07 PM GMT-08:00

(Update for record id(s): 374274329)

UPDATE

======

Hi,

The technical reason behind the suggestion is that , when we set the parameter for that matter any unsupported parameters we are asking the code (oracle code) to behave in a different manner diverging from the actual access path.

Hence when this happens the information in the data dictionary would not be updated properly and at a future point in time there could be some issues raised because of this and at that time we may land up in a situation where it cannot be recovered.

Hope this is helpful.

August 26, 2009 8:25:20 AM GMT-08:00

Hi,

Thanks for your answers . Shall I conclude it like using unsupported parameter Database may work normally but on users own Risk as Oracle won't support it .

As still I don't have any technical reason to rebuild my database .

Regards

Oracle Support - August 24, 2009 6:38:36 PM GMT-08:00

(Update for record id(s): 373995595)

UPDATE

======

Hi Vivek,

There is no way to determine the inconsistencies by querying any data dictionary views .

Even the health check on the data dictionary does not reveal the underlying the inconsistencies.

Hence you can inform the user that , the moment the unsupported methods are used , the database is no longer a supported one and hence this needs to be rebuild so that in future there are any errors they can be addressed.

Regards

August 24, 2009 6:30:16 AM GMT-08:00

Hi,

Thanks for response . As you commented "development team has strongly suggested that in case the parameters are used the data dictionary would not be consistent and not all the dependencies can be figured out manually."

-> Is there a way , I can figure out such inconsistency in database or even any way to produce error or warning .

Please advise me to explore and find out a single chunk of data or dictionary which may have specious status and convince an user to rebuild database .

Thanks in Advance .

Vivek

Oracle Support - August 24, 2009 1:44:38 AM GMT-08:00

(Update for record id(s): 373834603)

UPDATE

======

Hi,

Thank you for using My Oracle Support.

I noticed that you have logged this SR without uploading an OCM configuration. We strongly encourage you to provide the latest configuration. The information to create configuration can be accessed using the following hyperlink:

https://metalink.oracle.com/CSP/ui/flash.html#tab=Collector(page=Collector&id=fx9hk8fu())

Uploading configuration information can help users achieve problem avoidance and faster problem resolution. Associating configurations to service requests provides Oracle with accurate system configuration details that can help accelerate the problem resolution time.

We are currently reviewing/researching the situation and will update the Service Request (SR) or call you as soon as we have relevant information. Thank you for your patience.

Regards

Oracle Support - August 24, 2009 1:44:38 AM GMT-08:00

(Update for record id(s): 373834661)

QUESTION

========

1) In general oracle suggest to rebuild your database after using parameter "_ALLOW_RESETLOGS_CORRUPTION" . Is it mandatory to do that when there is no error reported in alert log or nothing looks suspicious even plying around database .

2) If I don't plan to rebuild DB as I don't see any reason , Can you please give me reason to do so .

3)Having Fuzzy column "Yes" , is an issue ?

ANSWER

======

Hi,

In general oracle suggests customers to rebuild the database in case any unsupported parameters are used because , development team has strongly suggested that in case the parameters are used the data dictionary would not be consistent and not all the dependencies can be figured out manually.

Hence even though there are no issues reported in the alert.log after the use of the unsupported parameters , the database needs to be rebuild using the export/import method.

Also when the database is up and running the status of the fuzzy column would always be YES. Hence we should not consider the o/p when the database is up and running.

Let me know in case you have any further queries.

Regards

August 23, 2009 9:51:08 PM GMT-08:00

Hi ,

While restoring a database from Prod to UAT, I found database backup was not consistent means performed hot backup happened when datafiles were not in begin backup mode . I wanted to deal with same backup set and started recovering database with resetlogs option . After getting tired with applying arch log , I used option "_ALLOW_RESETLOGS_CORRUPTION = TRUE" .

RESETLOGS is being done without consistancy checks. This may result

in a corrupted database. The database should be recreated. Message from alert log . After this no consistancy checks and database was opned . I checked the all files status lokked fine .

SQL> select * from v$recover_file;

no rows selected

I need your expert advice on ,

1) In genral oracle suggest to rebuild your database after using parameter "_ALLOW_RESETLOGS_CORRUPTION" . Is it mandotary to do that when there is no error reported in alert log or nothing looks suspecious even plying around database .

2) If i dont plan to rebuild DB as i dont see any reson , Can you please give me reason to do so .

3)Having Fuzzy column "Yes" , is an issue ?

FILE# STATUS FUZ

---------- ------- ---

1 ONLINE YES

2 ONLINE YES

3 ONLINE YES

4 ONLINE YES

5 ONLINE YES

6 ONLINE YES

7 ONLINE YES

8 ONLINE YES

9 ONLINE YES

10 ONLINE YES

11 ONLINE YES

Appreciate your suggestion on my queries

-------------------------------------------------------

DAILY NEEDED SCRIPTS FOR DBA

---------------------- विवेक गुप्ता ------------------------------ To Check Locks select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ; ----------------------------------- Object Lock SELECT c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine FROM v$locked_object a , v$session b, dba_objects c WHERE b.sid = a.session_id AND a.object_id = c.object_id; --------------------------------------- REM: Script to find Database locks REM: REM:***************************************** REM: Author: Babu REM: Date Submitted: 27-Dec-2008 REM: REM: REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE. REM: Author will not be responsible for any damage that may be cause by this script. REM:***************************************** column blocker format a25 column blockee format a25 column sid_serial format a10 select (select username || ' - ' || osuser from v$session where sid=a.sid) blocker, a.sid || ', ' || (select serial# from v$session where sid=a.sid) sid_serial, ' is blocking ', (select username || ' - ' || osuser from v$session where sid=b.sid) blockee, b.sid || ', ' || (select serial# from v$session where sid=b.sid) sid_serial from v$lock a, v$lock b where a.block = 1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2; ---------------------------------------------------------- ------------------------ col username form A10 col sid form 9990 col type form A4k col lmode form 990 col request form 990 col objname form A15 Heading "Object Name" SELECT sn.username, m.sid, m.type, DECODE(m.lmode, 0, 'None' , 1, 'Null' , 2, 'Row Share' , 3, 'Row Excl.' , 4, 'Share' , 5, 'S/Row Excl.' , 6, 'Exclusive' , lmode, ltrim(to_char(lmode,'990'))) lmode, DECODE(m.request, 0, 'None' , 1, 'Null' , 2, 'Row Share' , 3, 'Row Excl.' , 4, 'Share' , 5, 'S/Row Excl.' , 6, 'Exclusive' , request, ltrim(to_char(request,'990'))) request, obj1.object_name objname, obj2.object_name objname FROM v$session sn, V$lock m, dba_objects obj1, dba_objects obj2 WHERE sn.sid = m.sid AND m.id1 = obj1.object_id (+) AND m.id2 = obj2.object_id (+) AND lmode != 4 ORDER BY id1,id2, m.request -------------------------------------- --SPID ---sid---- SELECT s.sid, p.spid, s.osuser, s.program FROM v$process p, v$session s WHERE p.addr = s.paddr and s.sid=496; ; To check SQL text for particular sid select s.sid, q.sql_text from v$sqltext q, v$session s where q.address = and s.sid = 205 order by piece select do.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) from v$session s, dba_objects do where sid=479 and s.ROW_WAIT_OBJ# = do.OBJECT_ID SELECT s.sid, s.serial#, st.sql_text sql_text FROM v$session s, v$sql st, v$process p WHERE s.sql_hash_value = st.hash_value AND s.sql_address = st.address AND s.paddr = p.addr and ( s.sid=392) Full text select x.sql_text from v$session s,v$sqltext x where s.sql_address=x.address and s.sid =205 order by sid, piece asc ---------------------------------- to get sid base on unix process id : SELECT s.sid,s.serial# from v$process p, v$session s where s.paddr=p.addr and p.spid = '&spid'; to get unix process id from sid : SELECT s.sid,s.serial#, p.spid from v$process p, v$session s where s.paddr=p.addr and s.sid=&1 GRANT PERMISSION ON WHOLE SCHEMA set verify off set feedback off set pagesize 10000 set linesize 120ACCEPT main_owner CHAR PROMPT 'MAIN OWNER: ' ACCEPT grant_to CHAR PROMPT 'GRANT TO: ' ACCEPT permissions CHAR PROMPT 'PERMISSIONS (i.e. SELECT,UPDATE): ' spool grant_from_&&main_owner._to_&&grant_to..sql select 'grant &&permissions on "&&main_owner"."'||table_name||'" to "&&grant_to";' "---" from dba_tables where owner=upper('&&main_owner'); Tablespace Usage select a.TABLESPACE_NAME, a.BYTES/1024/1024 mb_used, b.BYTES/1024/1024 mb_free, b.largest/1024/1024 mb_largest, round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used from ( select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files group by TABLESPACE_NAME ) a, ( select TABLESPACE_NAME, sum(BYTES) BYTES , max(BYTES) largest from dba_free_space group by TABLESPACE_NAME ) b where a.TABLESPACE_NAME=b.TABLESPACE_NAME order by ((a.BYTES-b.BYTES)/a.BYTES) desc --Total cursors open, by session select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current'; --Total cursors open, by username & machine select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, s.username, s.machine from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' group by s.username, s.machine order by 1 desc;