Home » RDBMS Server » Performance Tuning » Ref :"Understanding Indexes" from Home Page Link
Ref :"Understanding Indexes" from Home Page Link [message #221137] Sat, 24 February 2007 05:37 Go to next message
josh_snehal
Messages: 15
Registered: May 2006
Location: BANGALORE
Junior Member
Hello ALL,

I was trying to simulate the commands given in the article at home page regarding Understanding Indexes by Mr Rleishman.
http://www.orafaq.com/node/1403

I find the material quite helpful in understanding fundamentals of indexes.Thanks a lot to Mr Rleishman for the same.

But while I was trying to practice myself of the commands on my PC in Scott.emp table using SQL*Plus, I was not able to be successful in executing commands.Here is the sequence of commands I followed as per suggested and the errors I received.


SQL> ACCEPT index_name PROMPT "Index Name:"
Index Name:PK_EMP
SQL> 
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='&index_name';
old   1: ALTER SESSION SET TRACEFILE_IDENTIFIER='&index_name'
new   1: ALTER SESSION SET TRACEFILE_IDENTIFIER='PK_EMP'

Session altered.

SQL> 
SQL> COLUMN object_id NEW_VALUE object_id
SQL> 
SQL> SELECT object_id
  2  FROM user_objects
  3  WHERE object_type='INDEX'
  4  AND object_name=upper('&index_name');
old   4: AND object_name=upper('&index_name')
new   4: AND object_name=upper('PK_EMP')

 OBJECT_ID
----------
     30140

SQL> 
SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL &object_id';
old   1: ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL &object_id'
new   1: ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL      30140'
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01426: numeric overflow


SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER=" "
  2  ;

Session altered.

SQL> SHOW PARAMETER user_dump_test
ORA-00942: table or view does not exist


SQL> 




I am not very clear from the sequence above what I am trying to accomplish , I believe if done properly above sequence will prepare a trace file.

Can someone plz explain what I am doing wrong here, or what is stopping here from getting desirable output.
Why I am getting Numeric Error Flow & Ora-00942.

Also it will be good if someone can explain or point to links/docs which explain the stuff what's going here.

It was requested not to post questions or comments on Blog but on to Forums,so I am posting the question here.
Sincere thanks to all of you to take a look at this message.

Thanks & Cheers,

[Updated on: Sat, 24 February 2007 05:41]

Report message to a moderator

Re: Ref :"Understanding Indexes" from Home Page Link [message #221154 is a reply to message #221137] Sat, 24 February 2007 08:36 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
He does read & respond in this forum.
Decent post SHOWING what you did & using code tags.
Post Operating system name & version; plus Oracle version to 4 decimal places.
Re: Ref :"Understanding Indexes" from Home Page Link [message #221183 is a reply to message #221154] Sat, 24 February 2007 20:44 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Well, I can help you with the last one: the DBA has not granted you SELECT privs on the V$_PARAMETER view - SHOW PARAMETER requires that privilege. This is clearly documented in the SQL*Plus manual.

As for the TREEDUMP problem, I don't know what's causing that, sorry. It is failing on a recursive SQL (ie. a background SQL that Oracle runs). If you were to start a SQL Trace, you would be able to find which SQL - you might not be able to fix it though. I doubt you'd get much help from Oracle either - I don't know whether that is a supported command.

Look in the Performance Tuning manual for instructions on starting SQL Trace, and using TK*Prof to view the trace file.

Ross Leishman



Re: Ref :"Understanding Indexes" from Home Page Link [message #221201 is a reply to message #221154] Sun, 25 February 2007 05:43 Go to previous messageGo to next message
josh_snehal
Messages: 15
Registered: May 2006
Location: BANGALORE
Junior Member
Thanks for your concerns.
I am using
OS : Microsoft Windows 2000 Service Pack 4
Database :Oracle 9i Enterprise Edition Release 9.2.0.1.0

Can you plz point me towards links/online docs where I can understand what's going in the commands which I followed as shown in previous post.

Thanks..
Re: Ref :"Understanding Indexes" from Home Page Link [message #221202 is a reply to message #221183] Sun, 25 February 2007 05:45 Go to previous messageGo to next message
josh_snehal
Messages: 15
Registered: May 2006
Location: BANGALORE
Junior Member
Thank you Mr Rleishman for your responce.
I shall now run the command sequence using trace on and shall get back to you on what I find.

Thanks..
Re: Ref :"Understanding Indexes" from Home Page Link [message #221856 is a reply to message #221137] Wed, 28 February 2007 12:54 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Not that I can help with the error you are receiving, but there really should be no reason for using 9.2.0.1. Ever.

Not only is it quite old, but it had numerous bugs which have since been corrected.

If you are using 9iR2 in production, then you should patch yourself to 9.2.0.8 or whatever the current release is.

If you are just using this as a test/sandbox database to work on, then download and use a 10g version.
Re: Ref :"Understanding Indexes" from Home Page Link [message #221859 is a reply to message #221137] Wed, 28 February 2007 13:07 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
For what it is worth, when I am in:

MYDBA@orcl > select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


And I run:

create table emp2 as select * from emp;

alter table emp2 add constraint emp_pk primary key(empno);

ACCEPT index_name PROMPT "Index Name:"
ALTER SESSION SET TRACEFILE_IDENTIFIER='&index_name';

COLUMN object_id NEW_VALUE object_id

SELECT object_id FROM user_objects
WHERE object_type='INDEX' AND object_name=upper('&index_name');

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL &object_id';

drop table emp2;



My result is:

MYDBA@orcl > start index_trace;

Table created.


Table altered.

Index Name:emp_pk
old   1: ALTER SESSION SET TRACEFILE_IDENTIFIER='&index_name'
new   1: ALTER SESSION SET TRACEFILE_IDENTIFIER='emp_pk'

Session altered.

old   2: WHERE object_type='INDEX' AND object_name=upper('&index_name')
new   2: WHERE object_type='INDEX' AND object_name=upper('emp_pk')

 OBJECT_ID
----------
     54921

old   1: ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL &object_id'
new   1: ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL      54921'

Session altered.


Table dropped.



And I get a file called "orcl_ora_540_emp_pk.trc":


*** TRACE DUMP CONTINUED FROM FILE  ***


Dump file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_540_emp_pk.trc
Wed Feb 28 14:00:38 2007
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU                 : 2 - type 586
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:287M/1014M, Ph+PgF:793M/1429M, VA:1672M/2047M
Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 16

Windows thread id: 540, image: ORACLE.EXE (SHAD)


*** 2007-02-28 14:00:38.946
*** ACTION NAME:() 2007-02-28 14:00:38.946
*** MODULE NAME:(SQL*Plus) 2007-02-28 14:00:38.946
*** SERVICE NAME:(SYS$USERS) 2007-02-28 14:00:38.946
*** SESSION ID:(143.1492) 2007-02-28 14:00:38.946
----- begin tree dump
leaf: 0x1000234 16777780 (0: nrow: 14 rrow: 14)
----- end tree dump



Now I'm off to read the article you referenced that Ross wrote, which I'm sure (based on his forum responses I've seen) is quite good.

[Updated on: Wed, 28 February 2007 13:10]

Report message to a moderator

Previous Topic: How to Purge tables ??
Next Topic: Performance with materialized view
Goto Forum:
  


Current Time: Sat Dec 03 04:15:26 CST 2016

Total time taken to generate the page: 0.16953 seconds