Jun-13

June 28

Oracl 12c optimization changes:-

Adaptive Plan:  Upto oracle 11gR2, otpimizer decides a final plan during the parse time and execute the plan.  In 12c optimizer willm decide next step b ased on the rows returned by previous step.  So, instaed of choosing a final execution plan at parse time, optimizer defers the final choice until execution time.  SO, the plan will be dynamic even during the execution time.

Adaptive reoptimization: similar to cardinalty feed back in the previous versions.  Make use of the past execution statistics and create new better plan.

Dynamic Statistics:  prior to 12c, Oracle gather Dymnaic statistics if the statistics is not available for a table for each execution.  12c onwards, Oracle will store the dynamic statisitcs (some docs also says, if the quality statistics is not available, then Oracle will do a dynamic statistics gathering during the execution time – need to test this  )  and use for the subsequent executions.  I think very soon, gathering statisitcs for any table will be history, rather Oracle will take care everything automatically!

About dynamic statistics and various levels.  Byt setting OPTIMIZER_DYNAMIC_SAMPLING to ‘0’ will disbale dynamic sampling.  Level 11 will cause optimizer to take decision to capture dynamic statistics and the resultant statistics will kept in the repository for subsequent exectuions, is the new feature in 12c.

http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_astat.htm#TGSQL451

Database can gather stats for any bulk load.  For a partitioned table, global level stats will be collected for any bulk load.  In case of a single partition bulk load, then partition elvel stats will be auto calculated.  You can see this DBA_TAB_COL_STATISTICS   view columns called “NOTES” and value will  be “STATS_ON_LOAD”.

You can skip auto load statistics gather for a tbale by using a hint  /*+NO_GATHER_OPTIMIZER_STATISTICS */

Oracle 12c review by Riyaj.

http://www.infoworld.com/d/data-management/oracle-database-12c-review-finally-true-cloud-database-221549?page=0,0

June 26

Wow, it is there now.  In 12c, you can do TABLE or even a TABLE PARTITION level  recovery.  You can use RECOVER TABLE option from an RAMN backup residing either in disk or tape.  Tables from SYSTEM or SYSAUX can not be recovered.  Recovery can be based on Time, SCN or sequence number.

Sample code from the documentation – (yet to install my 12c!!)


RECOVER TABLE HR.PDB_EMP OF PLUGGABLE DATABASE HR_PDB
UNTIL TIME 'SYSDATE-4'
AUXILIARY DESTINATION '/tmp/backups'
REMAP TABLE 'HR'.'PDB_EMP':'EMP_RECVR';

Now you can grant a privilage SYSBACKUP to manage backup and recovery operations.


SQL> GRANT SYSBACKUP TO thomas;

RMAN> CONNECT  TARGET "thomas/thomas@testdb AS SYSBACKUP"

http://docs.oracle.com/cd/E16655_01/backup.121/e17630/rcmresind.htm#BRADV696

Oracle 12c is out for download

https://blogs.oracle.com/UPGRADE/entry/finally_oracle_database_12c_is

Upgrade, migrate and consolidate to Oracle 12c – Oracle slide deck

http://apex.oracle.com/pls/apex/f?p=202202:2:::::P2_SUCHWORT:migrate12c

Oracle 12c documentation – finally out – enjoy now!!

http://www.oracle.com/pls/db121/homepage

Exadata at Thomson Reuters

http://www.oracle.com/technetwork/database/availability/thomsonreuters-showcase-a-1940577.pdf

June 21

July/August Oracle magazine published

http://www.oraclemagazine-digital.com/oraclemagazine/20130708?sub_id=GALZ5xRNfPAP#pg2

Oracle e-delivery site – select the product and download

https://edelivery.oracle.com/EPD/Search/handle_go

June 20

Some facts about ZFS file system.

ZFS uses RAM to cache the metadata about the file system storage.  1GB RAM should be reserve for each 1 TB data plus 1GB for OS.   lack of this caching can sevelry affect the performance of the system.

http://constantin.glez.de/blog/2010/04/ten-ways-easily-improve-oracle-solaris-zfs-filesystem-performance

To get pool wise I/O statistics

$ zpool iostat

To get iostat for pool on intervel

$ zpool iostat <pool name> < intervel>

To get I/O stats on a virtual device

$zpool iostat -v

http://docs.oracle.com/cd/E19253-01/819-5461/gammt/index.html

There are two caches associated with ZFS file system – Primary Cache (ARC) and Secondry cache (L2ARC).  For a memory straved server, remember to cache metadata in the PRIAMRYCACHE.  In the database environment, avoid caching the user data.

http://docs.oracle.com/cd/E19082-01/817-2271/ghbxt/index.html

June 17

==> Incremental statistics collection on partitioned tables.   Oracle collects stats for a table when the current stats for a table is stale, means 10% of the data changed in the table.    In case of the global stats for a partitioned table, any of the partition got 10% changes will triger a global stats collection.   Stats will be gathered not only for the partition, rather it will affect all the partitions.  Oracle 11g new feature, ‘incremental stats colelction’ will reduce this burden and will calculate stats only for the required partitions.  First enale incremental statistics for a table,

SQL> exec DBMS_STATS.SET_TABLE_PREFS(‘OE’,’ORDERS’,INCREMENTAL’,’TRUE’);

SQL> SELECT DBMS_STATS.GET_PREFS(‘INCREMENTAL’,’OE’,’ORDERS’) from dual;

http://docs.oracle.com/cd/B28359_01/server.111/b28274/stats.htm#i42218

https://blogs.oracle.com/optimizer/entry/incremental_statistics_maintenance_what_statistics

Incremental statistics stores current information in the below two tables, which is residing in SYSAUX tablespace.

SYS.WRI$_OPTSTAT_SYNOPSIS_HEAD$    and

SYS.WRI$_OPTSTAT_SYNOPSIS$

For a large partitioned table can populate large number of records to this table.  There are times, stats colelction will slow down just becasue of managing this large data during the stats gathering.   Also, he is talking about the bugs on incremental statistics.

http://rnm1978.wordpress.com/2010/12/31/data-warehousing-and-statistics-in-oracle-11g-incremental-global-statistics/

The slowness was attributed to deleting old records from SYS.WRI$_OPTSTAT_SYNOPSIS$ table.   Below blog post, just did trick of enabling parallel process on the table WRI$_OPTSTAT_SYNOPSIS$ table, so that the delete was faster.    However the real issue of large number of rows process ‘may be’ still an issue.

SQL> ALTER TABLE WRI$_OPTSTAT_SYNOPSIS$ PARALLEL;

http://jhdba.wordpress.com/2012/01/04/speeding-up-the-gathering-of-incremental-stats-on-partitioned-tables/

Note: What will be the impact of enabling parallelism on WRI$_OPTSTAT_SYNOPSIS$?  Why Oracle did not do that?  Is that a bug?  – Answers, I don’t know.  So, use it your own risk or contact Oracle support for details.

Bug 8310339: SLOW PERFORMANCE WHEN DBMS_STATS GATHER INCREMENTAL STATS ON A PARTITION TABLE

June 07

Possible disk sort candidate SQLs from AWR


select SQL_ID ,count(*),sum(DIRECT_WRITES_DELTA) from dba_hist_sqlstat
where SORTS_DELTA > 0 and snap_id >= &startsnap_id and snap_id <= &endsnap_id
group by sql_id
having sum(DIRECT_WRITES_DELTA) > 0
order by sum(DIRECT_WRITES_DELTA);

June 05

To purge Trace /log files automatically (MMON process)  in 11gR -use the adrci utility as below

$ adrci

adrci> show homes

adrci> set homepath <home path>

adrci> show control

–> You can change the SHORTP_POLICY or LONGP_POLICY

adrci>  set control (SHORTP_POLICY = 168)             ==> 168 Hrs or 7 days applies to traces, dumps and incident packaging information ( default 720 Hrs or 30 days))

adrci> set control (LONGP_POLICY = 720)            ==> 720 Hrs or 30 days, applies to incidents and alerts (default is 8760 Hrs or 365 days)

==> To manually purge based on the policy

adrci> purge

==> To purge manually

ardci> purge -age  <retention in hrs>  -type <type of  files>

–> A file type can be TRACE,

==> workign with  incidents and trace files

adrci> show incident                => show all incidents

adrci> show incident -last 10   => Last 10 days incidents

adrci> show tracefile  -i <incident number>   => List all trace files related to an incident

adrci> show incdir <incident #>

adrci> show incdir <start incident#> <end incident#>

adrci> show incident -mode brief  -p “incident_id=<incident #>”

adrci> show incident -p “CREATE_TIME > ‘2011-05-25 13:22:10′”

adrci> show alert  -p “module_id=’DBMS_SCHEDULAR'”

adrci> show tracefile -t

adrci> show tracefile %lgwr% -t

adrci> show tracefile %654% -t

adrci> show alert

adrci> show alert -tail 10

adrci> describe alert_ext

adrci> spool outfile.txt

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: