command to collect full stats on oracle SAP database.

here is the command to collect full stats on Oracle SAP database.

brconnect -u / -c -f stats -t all -p 4

t---> all tables

f --> force

p-->paralellism

query eating more Temp space and throwing ORA-01652: unable to extend temp segment by even for one row

Hi All,

I had situation  when tried to fetch some amount of records from v$rman_backup_job_details,its keep eating lot of temp space and finally throws error on 10204.

ORA-01652: unable to extend temp segment by 256 in tablespace TEMP 

finally identified there was a bug and below is the work around.

you are likely to be running in to Bug 5466436

The workaround for this issue is to delete statistics from the X$KCCRSR ie:

exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR');

- this deletes the statistics on the fixed object  and

exec dbms_stats.LOCK_TABLE_STATS('SYS','X$KCCRSR');

- this locks that object so that statistics will not be collected in future.

Difference between SQL profile Vs SQL plan management baselines

BASIC INFO	SQL PROFILES	SPM BASELINES
What they are	Stored collections of Hints (plus some technical information for the optimizer)	Stored collections of Hints (plus some technical information for the optimizer)
Available from	10g	11g
They affect	Individual SQL	Individual SQL
What they do	Adjust Optimizer cardinality estimations	Direct SQL to follow specific execution plan
Motto (as far asSQL Plans are concerned)	Be the Best you can be !	Only the Worthy may Pass !
Managed by PL/SQL package	dbms_sqltune	dbms_spm
How are they created ?	Run SQL Tuning task (dbms_sqltune.execute_tuning_task) to analyze existing SQL and IF cardinality is skewed, store it as SQLProfile	Take existing execution plan from SQL that already ran and store it as SPM baseline
Can their creation be forced ?	YES, but this is not fully supported. I.e.
		YES, ifoptimizer_capture_sql_plan_baselines=TRUE
Can they be created manually for individual SQL?	YES, bydbms_sqltune.execute_tuning_task()	YES, but SQL needs to already have run:dbms_spm.load_plans_from_cursor_cache(sql_id => …)
Can they be captured for the ongoing workload ?	YES, through SQL Tuning Sets	YES, if optimizer_capture_sql_plan_baselines=TRUE
Can they be “group loaded” from SQLs in the shared pool ?	YES, through SQL Tuning Sets	YES, directly
Can they be “group loaded” from SQLs in AWRrepository ?	YES, through SQL Tuning Sets	YES, through SQL Tuning Sets
Are they “activated” upon creation ?	NO, SQL Profiles need to be explicitly accepted	MAYBE, Baseline is activated if it is the first baseline captured (for the SQL) OR if loaded from cursor cache, AWR etc
Can they be activated automatically ?	YES, if accept_sql_profiles is set forSQL Tuning AutoTask	MAYBE, SPM baseline is activated if it is the first baseline captured (for the SQL)
Can they be deactivated globally ?	NO	YES, Setoptimizer_use_sql_plan_baselines=FALSE
Can they be deactivated locally ?	YES, set sqltune_category	NO
Can they be transferred to another database ?	YES	YES
Can they “fire” for the object in different schema ?	YES	YES
Can they “fire” when object has a different structure ?	YES	YES
Can they “fire” when table is replaced withMVIEW ?	YES	NO
Can they “fire” when some objects (i.e. indexes) used in the original plan are missing for the new object ?	YES	NO
Available in Standard Edition ?	NO	NO
Available in genericENTERPRISEEdition ?	NO, you need to also licenseDIAGNOSTICS and TUNING packs	YES