asked on

Slow Query Performance: Root cause may be table setup

Good Morning All (well depending on when you read this):

Typically in the past, I've had people create the Oracle tables for me and I just interact with them. The current project I've had to, so I think therin lies the problem of my issue. Let me generically explain the setup:

t_request: id (pk), pers_id_requester (fk), pers_id_reqFor (fk), cur_title (fk), future_title (fk), status (fk), various other attributes for table

t_pers: id (pk), name

t_titles: id (pk), name

t_status: id (pk), name

All id's are NUMBERs and all Names are VARCHAR2, 256 BYTEs.

So the query below is taking 1.5 -2 seconds to run initially when t_request has 80 records, t_pers has 30, and the remaining have less than 15; prior to being cached obviously

SELECT r.title, r.request_date, requester.name, emp.name, cur.name, fut.name, stat.name
FROM t_request r, t_pers requester, t_pers emp, t_title cur, t_title fut, t_status stat
WHERE r.pers_id_requester = requester.id AND r.pers_id_reqFor = emp.id
AND r.cur_title = cur.id AND r.future_title = fut.id
AND r.status = stat.id AND r.name = 'Sample Name'
ORDER BY r.request_date;

So what that means is that anytime someone intially comes into view this information (it's the default query) they'll have at least a 1.5s wait time; that's way too high for less than 50 records being returned. I also have another query that takes less than .02s run that I'm joining this query; together they take 4-5s to run. So I split the queries down and saw this was the problem one. So actually once I join the two, it increases the wait time significantly when first run.

I'm assuming that the pk -> fk indexes are wokring as they should; however I turned on AUTOTRACE and I all the joins are hash joins with a cost varying from 10 -27 and I also see an identical amount of 'TABLE ACCES FULL' operations. That doesn't seem right.

So I'm looking at the query and it's pretty straight forward, and that's why I'm thinking it has to do with the way the tables are setup; help, guidance, tips are much appreciated. Or even links to some doco would be helpful as well.. I just can't seem to find any searching; it seems that all the good stuff is probably buried 15 pages deeps in a Google search once you get past the sites that want to train you...

Thanks in Advance

Guy Hengel [angelIII / a3]

try to check out adding indexes like this:
1 index on request ( name )
1 index on t_pers ( id )
1 index on t_title ( id )
1 index on t_status ( id )

also, you should try to use the following JOIN syntax instead of that "old" syntax:

SELECT r.title, r.request_date, requester.name, emp.name, cur.name, fut.name, stat.name
FROM t_request r
JOIN t_pers requester
  ON r.pers_id_requester = requester.id 
JOIN t_pers emp
  ON r.pers_id_reqFor = emp.id
JOIN t_title cur
  ON r.cur_title = cur.id 
JOIN t_title fut
  ON r.future_title = fut.id 
JOIN t_status stat
  ON r.status = stat.id  
WHERE r.name = 'Sample Name'
ORDER BY r.request_date;

Open in new window

AWarrenM

ASKER

Hi Angel: thanks for the response.

Sorry about my, "OLD" syntax.. guess I'm just old school. Is the oracle optimization different for JOIN statements vs. "old" school?

Also, the index sytax you provided isn't working: unknown command.
Thanks.

Guy Hengel [angelIII / a3]

>Also, the index sytax you provided isn't working: unknown command.
the indexes I suggested was just suggestion, not code, I assumed you knew how to create indexes ...
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/indexes003.htm

>Is the oracle optimization different for JOIN statements vs. "old" school?
yes, as you "tell" oracle optimizer already how to "join" the tables, as though with the "old schoold", oracle has to determine it from the where clauses...

AWarrenM

ASKER

Thanks Angel:

I'm confused, aren't the primary key's indexed by default? I should have stated that as well.

Guy Hengel [angelIII / a3]

>I'm confused, aren't the primary key's indexed by default?
yes, they are implicitly.

now, what is the explain plan of this:

SELECT r.title, r.request_date 
FROM t_request r
WHERE r.name = 'Sample Name'

Open in new window

AWarrenM

ASKER

Here's the query:
SELECT r.id, p.name
FROM t_request r
JOIN t_person p ON r.requester_id = p.id
ORDER BY r.id

83 Rows are returned in about a half second (way too long for this?)

I ran it with SET AUTOTRACE ON

Plan is attached. I have no idea what's going on with my copy/paste, but I had to type that in.

Question: from the link you gave me I saw, CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;
Does that create an index for each column seperately or as a whole? e.g. do I have do reference those 4 created in the same query for the index to be used..

SELECT STATEMENT Rows = 82, Bytes = 2378, Cost = 8  CPU (25), Time 00:00:01
  SORT ORDER BY	Rows = 82, Bytes = 2378, Cost = 8  CPU (25), Time 00:00:01
    HASH JOIN			Rows = 82, Bytes = 2378, Cost = 7  CPU (25), Time 00:00:01
      TABLE ACCESS FULL 		Name =t_person, Rows 40, Bytes 740, Cost 3, Time 00:00:01
      VIEW				Name=index$_join$_001, Rows 82, Bytes 740, Cost 3, Time 00:00:01
	HASH JOIN
	 INDEX FAST FULL SCAN	Name, t_request_index, Rows 82, Bytes 656, Cost 1, Time 00:00:01
	INDEX FAST FULL SCAN	Name, t_request_pk , Rows 82, Bytes 656, Cost 1, Time 00:00:01

Open in new window

Guy Hengel [angelIII / a3]

can you please ensure the statistics on the tables are up to date?

AWarrenM

ASKER

Angel, I would if I knew what you were asking for... I apologize for my ignorance.

It's an extremely small app with the tables I mentioned above and I'm not getting DBA support; hence my dilemma here.

Guy Hengel [angelIII / a3]

analyze table <tablename> compute statistics;

or better, the full schema:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'schema_name' );

AWarrenM

ASKER

Done. No effect.

Guy Hengel [angelIII / a3]

actually:
SELECT r.id, p.name
FROM t_request r
JOIN t_person p ON r.requester_id = p.id
ORDER BY r.id

is actually best to perform a full table scan on either of the 2 tables, as you don´t limit to anything except on the join condition.

what about the explain plan of:

SELECT r.id, p.name
FROM t_request r
JOIN t_person p ON r.requester_id = p.id
WHERE r.name = 'Sample Name'
ORDER BY r.id

AWarrenM

ASKER

Below:

SELECT STATEMENT Rows = 12, Bytes = 300, Cost = 6  CPU (34), Time 00:00:01
  SORT ORDER BY	Rows = 12, Bytes = 300, Cost = 6  CPU (34), Time 00:00:01
    HASH JOIN			Rows = 12, Bytes = 300, Cost = 5  CPU (20), Time 00:00:01
      	TABLE ACCESS BY INDEX ROWID     Name t_person,Rows 1, Bytest 19, Cost 1 CPU(0)
	Index Unique Scan 		Name =t_person_indx, Rows 1, Bytes 0, Cost 0, Time 00:00:01
      VIEW				Name=index$_join$_001, Rows 82, Bytes 740, Cost 3, Time 00:00:01
	HASH JOIN
	 INDEX FAST FULL SCAN	Name, t_request_index, Rows 82, Bytes 656, Cost 1, Time 00:00:01
	INDEX FAST FULL SCAN	Name, t_request_pk , Rows 82, Bytes 656, Cost 1, Time 00:00:01

Open in new window

Guy Hengel [angelIII / a3]

looks better, doesn't it?
what about the time?

AWarrenM

ASKER

Hi Angel: thanks for all your help this far. You're right, that does look better.
Any guidance on creating indexes? My tables are all relatively small in size, so I didn't think it was an issue. I'm still not convinced I have everything set properly because on initial run, the query takes too long in my opinion (stil over 1s). When I say initial run: ALTER SYSTEM FLUSH BUFFER CACHE is included prior to the statement.

Regarding indexes, you may have missed my question from before: Say you create an index named, z_index for t_table and you specify col1, col2, and col3 in that index. Are those all indexed seperately OR does that index only apply when you're querying against each of those columns.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AWarrenM

ASKER

So in my case, lets refer back to t_request with id, requester_id, requested_for_id, title_id

Keep in mind the *_id columns are all FKs. So, the idea would be to create a seperate index for each?
request_requester_index(requester_id)
request_requested_for_index(requested_for_id)
request_title_index(title_id)

Is that the most efficient way to implement an index for this table? I think based on the next answer, I should be good to go...

With regard to hardware improvements; I know it's always nice to have more, but I'm trying to make due with what's available ;-)

Guy Hengel [angelIII / a3]

>Keep in mind the *_id columns are all FKs. So, the idea would be to create a seperate index for each?
yes

>Is that the most efficient way to implement an index for this table?
yes, that will be crutial in most tables to have a index on the fk columns?

AWarrenM

ASKER

haha, I know having the index is crucial; I just didn't know if the index should structured by itself or with the pk:

t_index (t_table_id (pk), t_table_other_id (fk))

Probably a silly question.. but it's been a long week!

Thanks angel.