Find All internal Parameters
select a.ksppinm “Parameter”, b.ksppstvl “Session Value”, c.ksppstvl “Instance Value” from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx and substr(ksppinm,1,1)=’_’ order by a.ksppinm;
Find Time Consuming SQL Statements in Oracle 10g
spool badsql5 set lines 132 set pages 300 col USR format a14 col PAR heading “Parsing ID” format 9999999 col ELA heading “ELP time(Sec)” format 9999999.0 col HASH heading “Hash Value” col USR heading “Parsing User” col T heading “SQL Text having Elapsed time > 1 sec” break on HASH on USR on ELA skip…
Read More “Find Time Consuming SQL Statements in Oracle 10g” »
Goog notes on X$ tables
http://www.adp-gmbh.ch/ora/misc/x.html http://www.orafaq.com/faqdbain.htm
Histogram Overview
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 and/or folders using LS command
To see only files use follwoing command. ls -l | grep -v “^d” To see only folders use follwoing command. ls -l | grep “^d”
Process Map for CPU and Memory for OS processes
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” »
DBMS_STATS Metalinks Notes
Note:102334.1 Subject: How to Automate Change Based Statistic Gathering – Monitoring Tables
USE_NL and INDEX hints example
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
Index Range Scan
If your index is NONUNIQUE, you cant enforce Index Unique Scan.
SQLPLUS COPY command Precautions.
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”.