CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Tuning
Version 11.1
 
General
Question:

How does the latch process in the shared pool work?

Answer:
  1. Compute the hash value of the statement
  2. Use the hash value to determine the part of the shared pool to be latched (locked)
  3. Find the statement (if already in the Shared Pool)
  4. If not present hard-parse the statement (syntax and lexical check, privilege check, optimize)
  5. If it is present skip the syntax and lexical check. Perform the privilege check and optimize
  6. Release the latch
 
Disk I/O

A high ratio is indicative of full table scans
-- phyrds is the number of physical reads
-- phyblkrd is the number of physical blocks read during the physical reads.


SELECT d.tablespace_name, f.file#, round(f.phyblkrd / f.phyrds, 3) RATIO
FROM gv$filestat f, dba_data_files d
WHERE f.file# = d.file_id;
 
Hinting

Full Hinting Demo
CREATE TABLE t1 AS
SELECT * FROM all_objects
WHERE ROWNUM = 1;

ALTER TABLE t1
ADD CONSTRAINT pk_t1
PRIMARY KEY(object_id)
USING INDEX;

CREATE TABLE t2 AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';

ALTER TABLE t2
ADD CONSTRAINT pk_t2
PRIMARY KEY(object_id)
USING INDEX;

exec dbms_stats.gather_table_stats('UWCLASS', 'T1', CASCADE=>TRUE);
exec dbms_stats.gather_table_stats('UWCLASS', 'T2', CASCADE => TRUE);

ALTER SESSION SET tracefile_identifier='base plan';
ALTER SESSION SET EVENTS '10053 trace name context forever,level 1';
ALTER SESSION SET EVENTS '10046 trace name context forever,level 1';

SELECT COUNT(*)
FROM t2, t1
WHERE t1.object_id = t2.object_id
AND t2.owner='UWCLASS';

ALTER SESSION SET tracefile_identifier='hinted plan';

SELECT /*+ use_nl(hint2 hint1) */ COUNT(*)
FROM t2, t1
WHERE t1.object_id = t2.object_id
AND t2.owner='UWCLASS';

ALTER SESSION SET tracefile_identifier='fully hinted plan';

SELECT /*+ ordered use_nl(hint2 hint1) */ COUNT(*)
FROM t2, t1
WHERE t1.object_id = t2.object_id
AND t2.owner='UWCLASS';

ALTER SESSION SET EVENTS '10053 trace name context off';
 
Log Buffer and Files

If you see substantial waits for log buffer space consider enlarging the  memory based log buffer.

If substantial waits for log file sync consider examine I/O performance of the online log buffers.
-- waits for space in the log file
SELECT name, value
FROM v$sysstat
WHERE name = 'redo log space requests';

SELECT name, block_size, resize_State, current_size, target_size
FROM gv$buffer_pool;

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%log%buf%';

ALTER SYSTEM SET log_buffer = 10240000 SCOPE=spfile;
 
Memory Optimization

Memory over time

Posted by Steve Howard at c.d.o.server 4-Dec-2007
SELECT time, instance_number,
MAX(DECODE(name, 'free memory',shared_pool_bytes,NULL)) free_memory,
MAX(DECODE(name,'library cache',shared_pool_bytes,NULL)) library_cache,
MAX(DECODE(name,'sql area',shared_pool_bytes,NULL)) sql_area
FROM (
  SELECT TO_CHAR(begin_interval_time,'YYYY_MM_DD HH24:MI') time,
  dhs.instance_number, name, bytes - LAG(bytes, 1, NULL)
  OVER (ORDER BY dhss.instance_number,name,dhss.snap_id) AS
  shared_pool_bytes
  FROM dba_hist_sgastat dhss, dba_hist_snapshot dhs
  WHERE name IN ('free memory', 'library cache', 'sql area')
  AND pool = 'shared pool'
  AND dhss.snap_id = dhs.snap_id
  AND dhss.instance_number = dhs.instance_number
  ORDER BY dhs.snap_id,name)
GROUP BY time, instance_number;
 
NULL and the CBO

The CBO makes different decisions based on whether it is possible for a column to contains NULLs
CREATE TABLE t (
rid  NUMBER(12),
col1 VARCHAR2(30),
col2 VARCHAR2(300));

ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY(rid)
USING INDEX
PCTFREE 0;

CREATE INDEX ix_t_col1
ON t(col1)
PCTFREE 0;

CREATE SEQUENCE seq_t_rid;

INSERT INTO t
SELECT seq_t_rid.NEXTVAL, dbms_crypto.randombytes(15), dbms_crypto.randombytes(150)
FROM dual
CONNECT BY LEVEL<=100000;

COMMIT;

SELECT *
FROM t
WHERE rownum < 11;

exec dbms_stats.gather_table_stats(USER, 'T', CASCADE=>TRUE);

EXPLAIN PLAN FOR
SELECT DISTINCT rid FROM t;

set linesize 121

SELECT * FROM TABLE(dbms_xplan.display);

EXPLAIN PLAN FOR
SELECT DISTINCT col1 FROM t;

SELECT * FROM TABLE(dbms_xplan.display);

ALTER TABLE t
MODIFY col1 NOT NULL;

EXPLAIN PLAN FOR
SELECT DISTINCT col1 FROM t;

SELECT * FROM TABLE(dbms_xplan.display);
 
NULL Pruning

NULL Pruning
CREATE TABLE parent (
parent_id NUMBER(10),
first_name VARCHAR2(20),
last_name VARCHAR2(20));

CREATE TABLE child (
child_id NUMBER(10),
parent_id NUMBER(10),
birth_date DATE);

BEGIN
  FOR i IN 1..500000
  LOOP
    INSERT INTO parent VALUES (i, 'Daniel', 'Morgan');
    INSERT INTO child VALUES (i*2, i, SYSDATE);
    INSERT INTO child VALUES (i*3, i, SYSDATE);
    INSERT INTO child VALUES (i*4, i, SYSDATE);
  END LOOP;
  COMMIT;
END;
/

UPDATE child
SET birth_date = NULL
WHERE TO_CHAR(child_id) LIKE '%2';

UPDATE child
SET birth_date = NULL
WHERE TO_CHAR(child_id) LIKE '%6';

COMMIT;

SELECT 'Is Not Null', COUNT(*)
FROM child
WHERE birth_date IS NOT NULL
UNION
SELECT 'Is Null', COUNT(*)
FROM child
WHERE birth_date IS NULL;

SELECT birth_date, COUNT(*)
FROM child
GROUP BY birth_date;

CREATE INDEX ix_child_dob
ON child(birth_date)
PCTFREE 0;

exec dbms_stats.gather_table_stats('UWCLASS', 'PARENT');
exec dbms_stats.gather_table_stats('UWCLASS', 'CHILD');

set timing on

SELECT COUNT(*)
FROM parent p, child c
WHERE p.parent_id = c.parent_id;

SELECT COUNT(*)
FROM parent p, child c
WHERE p.parent_id = c.parent_id
AND birth_date is NOT NULL;
 
Parsing

Parsing Efficiency
CREATE TABLE t (
mycol NUMBER(5));

set timing on

BEGIN
  FOR i IN 1 .. 10000
  LOOP
    EXECUTE IMMEDIATE 'INSERT INTO t VALUES (:x)'
    USING i;
  END LOOP;
END;
/

DECLARE
 cur    PLS_INTEGER := dbms_sql.open_cursor;
 str    VARCHAR2(200);
 retval NUMBER;
BEGIN
  FOR i IN 10001 .. 20000
  LOOP
    str := 'INSERT INTO t VALUES (' || TO_CHAR(i) || ')';
    dbms_sql.parse(cur, str, dbms_sql.native);
    RetVal := dbms_sql.execute(cur);
  END LOOP;
  dbms_sql.close_cursor(cur);
END;
/

DECLARE
 cur    PLS_INTEGER := dbms_sql.open_cursor;
 str    VARCHAR2(200);
 retval NUMBER;
BEGIN
  str := 'INSERT INTO t VALUES (:x)';
  dbms_sql.parse(cur, str, dbms_sql.native);
  FOR i IN 20001 .. 30000
  LOOP
    dbms_sql.bind_variable(cur,':x', i);
    RetVal := dbms_sql.execute(cur);
  END LOOP;
  dbms_sql.close_cursor(cur);
END;
/

BEGIN
  FOR i IN 30001..40000
  LOOP
    INSERT INTO t VALUES (i);
  END LOOP;
END;
/

-- 0.35 seconds

set timing off
 
Setting Stats

Some joins are better than others
conn scott/tiger

exec dbms_stats.gather_schema_stats(USER, cascade=>TRUE);

set autotrace on

SELECT DISTINCT d.deptno, d.dname
FROM dept d, emp e
WHERE e.deptno = d.deptno
ORDER BY 1;

SELECT d.deptno, d.dname
FROM dept d
WHERE EXISTS (
SELECT NULL
FROM emp e
WHERE e.deptno = d.deptno)
ORDER BY 1;

CREATE INDEX ix_emp_deptno
ON emp(deptno);

exec dbms_stats.set_table_stats(USER, 'EMP', numrows=>1000000, numblks=>10000, avgrlen=>74);

exec dbms_stats.set_index_stats(USER, 'ix_emp_deptno', numrows=>1000000, numlblks=>1000, numdist=>10000, clstfct=>1);

exec dbms_stats.set_column_stats(USER, 'emp', 'deptno', distcnt=>10000);

exec dbms_stats.set_table_stats(USER, 'dept', numrows=>100, numblks=>100);

-- repeat queries

exec dbms_stats.set_table_stats(USER, 'dept', numrows=>100000, numblks=>10000);

-- again repeat queries
 
SQL Statements
Access Objects set linesize 131
col object format a20
col object_type format a11
col owner format a20
col username format a10
col osuser format a25

SELECT a.object, a.type OBJECT_TYPE , a.owner, s.username, s.osuser, s.status, s.type USER_TYPE
FROM gv$access a, gv$session s
WHERE a.sid = s.sid
ORDER BY 2,1;
Resources desc gv$resource_limit

set linesize 121
col event format a30

SELECT *
FROM gv$resource_limit
ORDER BY 2, 1;
Session Environment desc gv$ses_optimizer_env

set linesize 121
col event format a30

SELECT s.inst_id, oe.sid, id, name, isdefault, value
FROM gv$ses_optimizer_env oe, gv$session s
WHERE oe.sid = s.sid
AND s.service_name <> 'SYS$BACKGROUND';

Wait Times
SELECT sid, schemaname
FROM gv$session
ORDER BY 2;

set linesize 121
col event format a30

SELECT inst_id, seq#, event, p1, p2, p3, wait_time
FROM gv$session_wait_history
WHERE sid = 158;

Wait Time Trend Analysis
col interval format a20

SELECT TO_CHAR(begin_interval_time,'YYYY_MM_DD HH24:MI') AS interval,
dhse.instance_number, time_waited_micro - LAG(time_waited_micro, 1, 0) OVER (ORDER BY dhse.instance_number, dhse.snap_id) AS time_waited,
total_waits - LAG(total_waits, 1, 0) OVER (ORDER BY dhse.instance_number, dhse.snap_id) AS total_waits
FROM dba_hist_snapshot dhs, dba_hist_system_event dhse
WHERE dhs.snap_id = dhse.snap_id
AND dhs.instance_number = dhse.instance_number
AND event_name = 'db file sequential read'
ORDER BY 1,2;

What happened during the execution of a SQL statement
set linesize 121
col username format a8
col name format a60

SELECT s.sid, s.serial#, s.username, sn.name, ms.value
FROM gv$statname sn, gv$mystat ms, gv$session s
WHERE MS.SID = s.sid
AND ms.statistic# = sn.statistic#
ORDER BY 4;

-- run your SQL statement here

SELECT s.sid, s.serial#, s.username, sn.name, ms.value
FROM gv$statname sn, gv$mystat ms, gv$session s
WHERE MS.SID = s.sid
AND ms.statistic# = sn.statistic#
ORDER BY 4;
 
Startup Parameters

Examine some of your init parameters and modify them to see if they have a positive affect.
SELECT name, value
FROM gv$parameter
WHERE name IN (
'optimizer_features_enabled',
'optimizer_index_caching',
'optimizer_index_cost_adj',
'optmimizer_mode',
'optimizer_secure_view_merging',
'plsql_optimize_level');
 
Setting OPTIMIZER_INDEX_COST_ADJ

These queries provides a guideline, a starting point, tuning is the next step
SELECT ROUND((s.time_waited/e.time_waited)*100, 0)
optimizer_index_cost_adj
FROM v$system_event s, v$system_event e
WHERE s.event = 'db file sequential read'
AND e.event = 'db file scattered read';

SELECT ROUND(AVG(singleblkrdtim)/AVG(readtim-singleblkrdtim)*100,0)
optimizer_index_cost_adj
FROM v$filestat;
 
Related Topics
Bind Variables
Clustering Factor
Cursor Sharing
DBMS_PROFILER
DBMS_SQLTUNE
DBMS_STATS
Explain Plan
Hints
Histograms
Indexes
TKPROF & Tracing
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [65 users online]    © 2010 psoug.org