Detecting Procedure Name and Tables

[haifrancis]

Dear All,
1.) I have a list of queries to be tuned. Once i finish tuning, i need to edit the procedures where these queries are in. So i need some script or method to find the Procedure name, given the Query. I tried USER_SOURCE and ALL_SOURCE, but the system hangs cos, i have around 25,000 stored procedures. Is there any way to find it out using the address or something like that.

2.) I also need to find out the tables that are going for a Full Table Scan, in the application. If there be any script or way where we can find those tables that go for a full scan, then i can try applying some indexes on those tables.

-Tanks in advance.

[OracleDoc]

1.)If you're wanting to see the proceedures you can query the user_objects table to see the list of proceedures in a schema.

2. you can run an explain plan on your query to see what it is doing. I've attached a script that you can run to give you the explain plan of a query. You do have to have a plan_table in your schema though.

[davey23uk]

1. Find the original source code

2. Why do you think all full table scans are bad?

[OracleDoc]

Originally posted by davey23uk
1. Find the original source code

2. Why do you think all full table scans are bad?

Ohhh now you've gone an done it Davey. No, you just couldn't let the man drive on and create indexes for each column of the table. You had to go and open a can of worms. Next thing you know you'll be telling him to put rule hints in all his code

[davey23uk]

okok, go create indexes on every column you could ever think off - dont come running here when it goes slower

[gandolf989]

Originally posted by davey23uk
okok, go create indexes on every column you could ever think off - dont come running here when it goes slower

Forget the indexes, I just keep wondering why he has 25,000 procedures. I like maintaining code but that is rediculous. If it were me, and of course it isn't, I could combine as much code into common procedures to whittle that down by a factor of 25 or even 50.

[davey23uk]

paid by lines of code no doubt

[gandolf989]

Originally posted by davey23uk
paid by lines of code no doubt

That is why I write code like this.

CREATE
OR
REPLACE
PACKAGE
thiswillbeapackageaboutlogging
AS
FUNCTION
thisfunctionwillreturndata
(
p1
IN
BOOLEAN
)
RETURN
BOOLEAN
;
...