Detecting Procedure Name and Tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Detecting Procedure Name and Tables

  1. #1
    Join Date
    Nov 2004
    Location
    India
    Posts
    1

    Detecting Procedure Name and Tables

    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.

  2. #2
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    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.
    Attached Files Attached Files
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    1. Find the original source code

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

  4. #4
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    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
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    okok, go create indexes on every column you could ever think off - dont come running here when it goes slower

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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.
    this space intentionally left blank

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    paid by lines of code no doubt

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    Originally posted by davey23uk
    paid by lines of code no doubt
    That is why I write code like this.

    Code:
    CREATE
    OR
    REPLACE
    PACKAGE
    thiswillbeapackageaboutlogging
    AS
    FUNCTION
    thisfunctionwillreturndata
    (
    p1
    IN
    BOOLEAN
    )
    RETURN
    BOOLEAN
    ;
    ...
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width