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
AWarrenMAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
AWarrenMAuthor Commented:
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.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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...
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

AWarrenMAuthor Commented:
Thanks Angel:

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

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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

0
AWarrenMAuthor Commented:
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

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please ensure the statistics on the tables are up to date?
0
AWarrenMAuthor Commented:
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.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
analyze table <tablename> compute statistics;

or better, the full schema:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'schema_name' );
0
AWarrenMAuthor Commented:
Done. No effect.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
AWarrenMAuthor Commented:
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

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
looks better, doesn't it?
what about the time?
0
AWarrenMAuthor Commented:
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.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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.

they are "not" indexes separatly, but as combination.
however, you don't necessarily need to have all columns in the where clause for the index to be used, but the column mentioned first in the index have to be part of the where clause indeed.

ie, say:
index ( col1, col2, col3 )

then, with
where col1=x
 -> index can be used

where col1=x and col2 = y
 -> index can be used

where col1=x and col3 = y
 -> index can be used, but only for col1=x part

where col2=x  
 -> index cannot be used

where col3=x  
 -> index cannot be used

where col2=x  and col3 = y
 -> index cannot be used

note:
"index can be used" does not mean that it will be used, the choice is up to the query optimized, based on the statistics (ie cardinality) of the columns...





>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.

well, for tables that are small, you should NOT test with the buffer cleared, as once the tables are queried, and assuming that they are queried "regulary", the data of them will stay in RAM, which is one of the most important performance gains of a database system: avoid to read from disk over and over again.
so, if for your "small" query, you need to read from up to 4-5 tables, each time several blocks for the indexes and then several blocks for the actual table data, this WILL take some time the first run, and this is nothing different than a normal flat-file database system with index files for example.

when working with big tables, on the other side, when properly indexed, you will also be able to see the difference from a flat-file system vs dbms system.

now, if you want to make your "small" query faster, what you will need to do then is essentially throw faster disks into your server. the faster the disks, the better a initial query versus a table can be.
after that, only having more RAM available (and of course, also fast RAM) and more/faster CPU will be noticeable in a large dbms environment.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AWarrenMAuthor Commented:
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 ;-)


0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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?
0
AWarrenMAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.