Articles Comments

AskDba.org Weblog » Entries tagged with "9i"

Mview Complete Refresh and Atomic_refresh parameter

This post discusses issue faced by me while working on Materialized view (mview) creation at new site(database). Starting Oracle 10g, how atomic_refresh works with complete refresh has undergone a change. Atomic_refresh parameter is to allow either all of the tasks of a transaction are performed or none of them (Atomicity). If set to TRUE, then all refreshes are done in one transaction. If set to FALSE, then the refresh of each specified materialized view is done in a separate transaction. In case of 9i if a single mview was refreshed , it used to be truncated and then refreshed (which actually was not atomic). In case of multiple mviews being refreshed (part of refresh group), rows were deleted and then the insert was done. As part of change in 10g, if atomic_refresh is … Read entire article »

Filed under: database, oracle

CleanUp Temporary Segments Occupying Permanent Tablespace

There are situations when we see “temporary segments” in permanent tablespaces hanging around and not getting cleaned up. These temporary segments in permanent tablespace can be created by DDL operations like CTAS and “alter index..rebuild” because the new object is created as a temporary segment in the target tablespace and when the DDL action finishes it will be changed to permanent type. These temporary segments take actual disk space when SMON fails to perform its assigned job to cleanup stray temporary segments. Following query finds out these segments: SQL > select tablespace_name, owner, sum(bytes/1024/1024) from dba_segments where segment_type = 'TEMPORARY' group by tablespace_name, owner; TABLESPACE_NAME     OWNER          SEGMENT_NAME     SUM(BYTES/1024/1024) ------------------- ------------   ------------     -------------------- xxxx_DATA           SYS              123.8365          137706 BDEPST_INDEX        SYS              345.8756            8910 KMRPT_DATA          SYS                345.87       25284.875 BILL_INDEX          SYS                                   .25 DSS_DATA            SYS                                   798 MRKT_INDEX          SYS                                   208 SPCT_DATA           SYS                              69642.25 SPCT_INDEX          SYS                              956.4375 Here we can see that tablespace KMRPT_DATA, … Read entire article »

Filed under: database, oracle

Index Access best approach?? Not Always…

There is a common misconception that Index access is the best access method for query execution. This approach leads to people concentrate on removing Full Table Scans (FTS) from the Query execution plan. I recently worked on a performance tuning issue where I found such scenario. Basically customer was trying to generate report for 3 years and query was running for more then 8 hours without producing any results. We were asked to look into this problem. Following diagnostic data was collected to diagnose the issue. 1)    10046 trace at level 12 2)    Execution plan from V$SQL_PLAN (Database version is 9.2) 3)    Explain plan for offending query From 10046 trace and V$session_wait, I found out query was waiting on  “db file sequential read” wait event for table SCOTT_BILL. This indicated that we were reading this … Read entire article »

Filed under: oracle, performance