Histograms ========== For uniformly distributed data, the cost-based approach makes fairly accurate guesses at the cost of executing a particular statement. However, when the data is not uniformly distributed, the optimizer cannot accurately estimate the selectivity of a query. Beginning in release 7.3, for columns which do not have uniform data distribution, Oracle will allow…
To see only files use follwoing command. ls -l | grep -v “^d” To see only folders use follwoing command. ls -l | grep “^d”
Remember : 2 commands to see OS stats. (1) TOP : This command will provide following information. SIZE column display Memory allocated to this process. RES column display Actually allocated memory for the process. tuningdb21->AWRTEST@(/db1/home/oracle/paresh)top load averages: 0.17, 0.15, 0.14 10:51:36 120 processes: 119 sleeping, 1 on cpu CPU states: % idle, % user, %…
Read More “Process Map for CPU and Memory for OS processes” »
Note:102334.1 Subject: How to Automate Change Based Statistic Gathering – Monitoring Tables
select /*+ USE_NL(A,B) INDEX(a ATS_FLORIST_FITTYPE_I)*/ a.FIT_ID from ATS_FLORISTS a,ATS_FLORISTS_TRACKING b where b.FIN_PROC = ‘N’ AND b.OLD_FRESH_PAY = ‘Y’ and b.NEW_FRESH_PAY=’N’ and a.FIT_TYPE=’P’ and a.ID= b.fit_id
If your index is NONUNIQUE, you cant enforce Index Unique Scan.
One test case today i found with copy command. When we are using sqlplus copy command with “APPEND” clause, and if source table contains any NUMBER datatype without Precision and Scale, then COPY statement fails with error indicating “NUMERIC ERROR and Number data type precision is higher than 38 digit”.
For Index ( Range Scan and Fast Full Scan Setings )in query (1) RULE uses index range scan (2) CBO uses Index fast full scan ( called Index FFS ) Here CBO uses multi block IO reading as per db_file_multi_block_read_count). By default, the CBO costs physical IO and logical IO the same. So it ignores…
Read More “Optimizer_Index init.ora parameter explaination.” »
Executing CTAS statement having TO_LOB(LONG COLUMN). For example: create table blah as select GNT_ID.nextval, 1060 as gnt_ref_id, org.gnt_id, org.gnt_group_note, org.gnt_language, to_lob(org.gnt_text_string) as gnt_text_string, org.gnt_text_emphasize from asra_002.gnt_groupnote_txt org where org.gnt_group_note in (select gnr_note from asra_002.gnr_groupnote_ref)
How to Copy Data from a Table with a LONG Column into an Existing Table ======================================================================= Purpose: ======== The purpose of this article is to provide a working example of how to COPY data from a table with a LONG column into another existing table with the same structure definition. This document is especially useful…
