Borndba

Pls see this interesting note,

http://blogs.oracle.com/optimizer/2011/02/how_do_i_deal_with_a_third_party_application_that_has_embedded_hints_that_result_in_a_sub-optimal_ex.html

convert this post to pdf.

Sometime you may want to run the rda.sh has nohup. But it take lot of input. You can create the setup.cfg using

./rda.sh -S

Again you will have to input the password if you are collecting via system.

./rda.sh -A system

enter the system password. In the same session

nohup ./rda.sh &

password what you set is valid only that session. You can run nohup from anothe r session.

you can see in the setup.cfg, newly added content like below.

#B.Multi-run collection indicator
S999END_MRC=0
#T.Password for ‘SYSTEM’
SQL_PASSWORD_SYSTEM=’(<WDR8F%S93(`’

convert this post to pdf.

If we could simulate the performance issue in sqlplus, consider we can fix the issue most of the time. Incase if you need oracle supports help in a query, you can build the test case like below.

SQL> DECLARE tc clob;
  2  BEGIN
  3  DBMS_SQLDIAG.EXPORT_SQL_TESTCASE(
  4  DIRECTORY=>'DATA_PUMP_DIR',
  5  SQL_ID=>'6bcwxgty705p8',
  6  TESTCASE=>tc);
  7  END;
  8  /

PL/SQL procedure successfully completed.

You need to supply sql_id and directory name. You can zip this and sent it to support. If you give exportdata to true, expdp will include table data.

This is tested in 11gR2.

convert this post to pdf.

Many of our sessions are waiting on ‘cursor: pin S wait on X’ in one our DB. Connecting to DB via Toad,sql developer is hanging.

Most of the sessions from toad,sql developer is waiting on, as the query all_objects during the startup.

SELECT 'X' FROM SYS.ALL_OBJECTS WHERE    OBJECT_NAME = :1 AND OWNER = :2 AND
OBJECT_TYPE = 'TABLE'

Some kind of contention around all_objects.

To find the blocking session,

SELECT sid,last_call_et,p2raw
,to_number(substr(to_char(rawtohex(p2raw)), 1, 8), 'XXXXXXXX') sid "Blocking Sid"
FROM v$session
WHERE event = 'cursor: pin S wait on X'
order by last_call_et;

Get the record having max last_call_et and take the correspond “Blocking Sid”. Killing the “Blocking Sid” has cleared the DB issue. Most likely this case killing sessions will take time, so kill -9 the os process.

Blocking Sid in our case is waiting on sql_id 1j4husc7c0k5u

To get the query,

SQL> select * from table(dbms_xplan.display_awr('1j4husc7c0k5u'));

SQL_ID 1j4husc7c0k5u
--------------------
select i.obj#, i.rowcnt, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey,i.clufac, i.blevel,
i.analyzetime, i.samplesize, decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),
i.flags, ist.cachedblk, ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist where
i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#

Plan hash value: 1414749717

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |       |       |     4 (100)|          |
|   1 |  SORT ORDER BY                |                   |     4 |   400 |     4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER          |                   |     4 |   400 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS CLUSTER       | IND$              |     4 |   192 |     2   (0)| 00:00:01 |
|   4 |     INDEX UNIQUE SCAN         | I_OBJ#            |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| IND_STATS$        |     1 |    52 |     1   (0)| 00:00:01 |
|   6 |     INDEX UNIQUE SCAN         | I_IND_STATS$_OBJ# |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

May be auto stats gathering happening on that time creating contention in IND$. This behaviour is documented in note#6011045.8

convert this post to pdf.

Refer to below link for manually creating and evolving the baseline plan.

http://www.oracle-base.com/articles/11g/SqlPlanManagement_11gR1.php

In the example given in the url above, after adding index we need to manually evolve. This helps in accepting the right plan.

But what happens if i drop the index.

SQL> drop index spm_test_tab_idx;

Index dropped.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> SET AUTOTRACE TRACE
SQL> SELECT description
2  FROM   spm_test_tab
3  WHERE  id = 99;

Execution Plan
----------------------------------------------------------
Plan hash value: 1107868462

--------------------------------------------------------------------------------

--

| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------

--

|   0 | SELECT STATEMENT  |              |     1 |    25 |    13   (0)| 00:00:01

|

|*  1 |  TABLE ACCESS FULL| SPM_TEST_TAB |     1 |    25 |    13   (0)| 00:00:01

|

--------------------------------------------------------------------------------

--

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=99)

Note
-----
- SQL plan baseline "SQL_PLAN_7qxjk7bch8h5tb65c37c8" used for this statement ==> Old base line is taken automatically

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
47  consistent gets
0  physical reads
0  redo size
441  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

Let me try to create that index back and see what happens,

SQL> CREATE INDEX spm_test_tab_idx ON spm_test_tab(id);

Index created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT description
2  FROM   spm_test_tab
3  WHERE  id = 99;

Execution Plan
----------------------------------------------------------
Plan hash value: 3121206333

--------------------------------------------------------------------------------

----------------

| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%

CPU)| Time     |

--------------------------------------------------------------------------------

----------------

|   0 | SELECT STATEMENT            |                  |     1 |    25 |     2
(0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| SPM_TEST_TAB     |     1 |    25 |     2
(0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | SPM_TEST_TAB_IDX |     1 |       |     1
(0)| 00:00:01 |

--------------------------------------------------------------------------------

----------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=99)

Note
-----
- SQL plan baseline "SQL_PLAN_7qxjk7bch8h5ted3324c0" used for this statement  ==> Goes for the best plan

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
4  consistent gets
0  physical reads
0  redo size
448  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

Note: I have not evolved 2nd time after putting back the index.

Need to check how the auto Evolving of baseline works.

convert this post to pdf.

I generally create the profile via oem. The below link explains how to create via command line,

http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php

convert this post to pdf.

We have a DB which connects to other DB via LDAP connection. We had an issue where db link to another db server is slow.

I have limited expertise on LDAP. This is how i solved the issue.

select * from dual@db_link;

it’s slow first time. In the same session if we run it again, it’s fast.

Opened a SR.Got all the trace files but no update.

I enabled tnsping trace using below command

#TNSPING.TRACE_LEVEL = 16
#TNSPING.TRACE_DIRECTORY=/tmp

It didn’t have time info. So enabled the client and server level trace.

#trace_level_server=16
#trace_level_client=16
#trace_directory_server=/tmp
#trace_directory_client=/tmp
#trace_file_client=cli1
#trace_file_server=srv1
#trace_unique_client=true

in the trace

[04-AUG-2010 05:20:19:035] nnflilc:  Opening sync conn to server1.domain.com:123
[04-AUG-2010 05:21:33:947] nnflilc:   ldap_open failed ld:0

almost a minute it waits on server1, then it goes to server 2 and connects

[04-AUG-2010 05:21:33:954] nnflilc:  Opening sync conn to server2.domain.com:123
[04-AUG-2010 05:21:33:979] nnflalc: entry
[04-AUG-2010 05:21:34:009] nnflalc:     bind call returns 0

This gives the clue that server1 oid is having issue. So we changed the ldap.ora look up

DIRECTORY_SERVERS= (server1.domain.com:123:636,server2.domain.com:123:636)

to

DIRECTORY_SERVERS= (server2.domain.com:123:636,server1.domain.com:123:636)

command to start and stop oid.

oidmon start
oidctl server=oidldapd1 instance=1 configset=1 start
oidctl server=oidrepld1 instance=1 flags="-h server1 -p 389" start

stop

oidctl server=oidldapd1 instance=1 configset=1 stop;sleep 10
oidctl server=oidrepld1 instance=1 stop;sleep 10
oidmon stop

convert this post to pdf.

In sqlt.zip there is a script called coe_xfr_sql_profile.sql which will help in generating the sql_profile from other_xml of v$sql_plan and output in executable format. This helps in moving the plan across the environment and also before creating profile helps you in editing the hints.

SQL> @/tmp/coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: bcmu3by7zdb03

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 2415307779

Values passed:
~~~~~~~~~~~~~
SQL_ID         : "bcmu3by7zdb03"
PLAN_HASH_VALUE: "2415307779"

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

convert this post to pdf.

New diagonistic tool is introduced in 11g. Using which we can gather all the information relevant to an issue and upload to oracle or investigate by yourself.

adrci is executable in $ORACLE_HOME/bin location

$ adrci

ADRCI: Release 11.1.0.7.0 - Production on Mon Jul 26 04:08:33 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

ADR base = "/opt/apps/oracle"
adrci>

adrci> show home
ADR Homes:
diag/rdbms/db1
diag/rdbms/db2
diag/rdbms/db3
diag/rdbms/db4
diag/tnslsnr/hostname/listener_bor3rep

adrci> set homepath diag/rdbms/db1

adrci> show problem ==> this will report the problem recently poped up in the db.

*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
3                    ORA 7445 [_memset()+120]                                    32093                2010-07-25 18:01:06.962092 -04:00
4                    ORA 7445 [_doprnt()+44]                                     32077                2010-07-25 18:01:03.642031 -04:00
2                    ORA 4030                                                    32270                2010-07-25 18:01:00.837604 -04:00
1                    ORA 7445 [ioc_pin_shared_executable_object()+952]           28842                2010-06-05 22:39:34.757161 -04:00
4 rows fetched

If you want to diagonise 4030 error, then

adrci> ips pack problem 2 in /tmp
Generated package 3 in file /tmp/ORA4030_20100726041042_COM_1.zip, mode complete

Upload the trace file to oracle or use it for investigation. Has all the information relevant to 4030 error.

convert this post to pdf.

When the primary archive location fills, there is an option of writing to alternate archivelog location. These parameter needs to be set.

log_archive_dest_1='LOCATION=use_db_recovery_file_dest NOREOPEN  ALTERNATE=LOG_ARCHIVE_DEST_2'
log_archive_dest_2='LOCATION=/other_destination_for_archiving'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='alternate'

You could use the following to revert back after failover:

alter system set  log_archive_dest_state_1 = enable;
alter system set log_archive_dest_state_2  = alternate;

But make note of below restriction when setting this.Metalink#369120.1

” The REOPEN attribute takes precedence over the ALTERNATE attribute. The alternate destination is
used only if one of the following is true:
- The NOREOPEN attribute is specified.
- A value of zero (0) is specified for the REOPEN attribute.
- A nonzero REOPEN attribute and a nonzero MAX_FAILURE count have been exceeded. ”

It’s preferable to set NOREOPEN to the default location and enable this option.

convert this post to pdf.

We had  an outage, where pga eating up all the memory crashing the vcs. We temporarily solved the issue by moving to shared server and reducing the pga size.

Came across some of the suggestion to restrict pga,

1. Private_sga in MTS

2. 10261 event

10261, 00000, "Limit the size of the PGA heap"
// *Cause:    the limit is one kilobyte times the level of the event. If the
//            pga grows bigger than this signal an internal error.

to set a session to 100mb

useful note is found in

http://dioncho.wordpress.com/2010/06/14/rapid-pga-size-increase/

for setting multiple events in spfile, you can use below command
ALTER SYSTEM SET event='10261 trace name context forever, level 15000000','600 trace name heapdump, level 0x20000001' COMMENT='pga issue' SCOPE=SPFILE;

for resetting use,





2:


ALTER SYSTEM RESET EVENT SCOPE=SPFILE SID='*' ;




Note: Have tried setting 10261 event in shared server db, but process didn't crash once reached the limit.

3. DBMS_SESSION.FREE_UNUSED_USER_MEMORY

convert this post to pdf.

When one of the db got upgraded from 10202 to 10205, we found out one of the query is very slow. Didn’t taken the export of stats before upgrade. This is one way we forced a profile to this query.

declare
pln_sql_id varchar2(20) :='fathgmks3vb1g';
pln_plan_hash_value number := 3396554601;
orig_sql_id varchar2(20) := 'fathgmks3vb1g';
new_prof_name varchar2(20) := 'SQL_PROFILE_1';
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
begin

select extractvalue(value(d), '/hint') as outline_hints
bulk collect into ar_profile_hints
from xmltable('/*/outline_data/hint'
passing (select xmltype(other_xml) as xmlval from dba_hist_sql_plan
where sql_id = pln_sql_id and plan_hash_value = pln_plan_hash_value
and other_xml is not null)) d;

select sql_text into cl_sql_text
from dba_hist_sqltext where sql_id = orig_sql_id;

dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => ar_profile_hints,
name => new_prof_name,
force_match => true);

end;
/

The below script can be used to see what’s the access path for a hash_value. This can be seen when you create outln and query ol_hint$

select
substr(extractvalue(value(d), '/hint'), 1, 100) as outline_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
v$sql_plan
where
hash_value = 2378699969
and child_number = 0
and other_xml is not null
)
) d;

You need to drop any sql_profile created before forcing a plan.

exec DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_014a4a1e346a8004', ignore => TRUE);

convert this post to pdf.

Steps for export and importing the db stats. This helps when you change the stats gathering approach.

exec dbms_stats.create_stat_table(ownname => ‘SYS’, stattab => ‘db_stat’, tblspace => ‘SYSTEM’)

exec dbms_stats.export_database_stats(stattab => ‘db_stat’,statown => ‘SYS’)

exec dbms_stats.import_database_stats(stattab => ‘db_stat’,statown => ‘SYS’)

convert this post to pdf.

if you have used OEM, then you should have used sql tuning advisor. Sometime if the plan is very large you will not be able to implement the profile. You will get message like,
“There was a problem creating the SQL Profile setString can only process strings of less than 32766 chararacters”

Instead of implementing via OEM, use the below script to force the profile. But remember the tuning name what you have generated.

begin
dbms_output.put_line(
dbms_sqltune.accept_sql_profile(
task_name => 'sql_tuning_xyz',
name => 'manual_forcing',
replace => true,
force_match => true
)
);
end;
/

convert this post to pdf.

From 10g there is a change in No_Invalidate parameter of dbms_stats. Default value of No_Invalidate in 10g is dbms_stats.Auto_Invalidate. Means the cursor will remain valid for 5 hrs after the stats gathering. This is to avoid spike in cpu and othe resources due to hard parsing. 5 hrs time limit is governed by underscore parameter “_optimizer_invalidation_period”

convert this post to pdf.

delete from adv_temp.recs_type_966
where 1 = 1
and exists (
  select  *
  from adv_master.RECS_TYPE_966_VIEW r
  where r.plot_fk in (-1,5419,5420)
  and r.plot_fk between 5419 and 5420
  and r.p019 = recs_type_966.p019
  and r.p027 = recs_type_966.p027
  and r.f001 = recs_type_966.f001
);

delete from adv_temp.recs_type_966
where 1 = 1
and exists (
  select /*+rule*/ *
  from adv_master.RECS_TYPE_966_VIEW r
  where r.plot_fk in (-1,5419,5420)
  and r.plot_fk between 5419 and 5420
  and r.p019 = recs_type_966.p019
  and r.p027 = recs_type_966.p027
  and r.f001 = recs_type_966.f001
);

Visitors Count